Sysbench（Bulk insert）项目实践详解

Bulk insert：批量向表中添加数据

bulk insert语法

INSERT INTO sbtest17 VALUES(1,1),(2,2),(3,3)........

实现原理

  • --threads：在线程中并发执行多行的插入
  • 每个线程都将insert的个数插入到自己的表中
    • 每次执行的每个表的insert个数是不均等的，比如--thread=10，--events=1000，预期是10张表中，每张表数据有100行，但实际结果可能是sbtest1中有80行数据，sbtest2中有120行数据....
  • --time与--events控制每个线程的执行
    • --time：表示在每个配置下测试多少秒
    • --events：表示总共写入多少行数据
  • 【重要记录】比如要测试1000行数据，通过并发的方式来测试bulk insert的性能，将1000行的数据写入到A表中。
    • 预期的实现原理是：将1000行数据，用10个并发，每次并发写入100行数据，只要一次就将1000行数据写入到A表中，并且来计算整体的性能写入结果
    • 实际的实现原理是：通过--threads=10，由此创建了10张表，一次将1000行数据不均等的写入到10张表中，然后在计算出写入的性能测试结果

复杂概念解读

  • stddev：标准偏差。标准差能反映一个数据集的离散程度，标准偏差越小，这些值偏离平均值就越少，反之亦然。详解见链接 https://baike.baidu.com/item/%E6%A0%87%E5%87%86%E5%81%8F%E5%B7%AE/10963562

注意事项

  • --report-interval=2：间隔2秒打印日志，注意：若批量插入的数据比较少，则不会打印出来日志，直接将会打印最终的测试结果

测试场景

按照不同的行数，根据并发量的要求，对每个表行数进行测试，观察在不同并发下的性能体现，同时观察性能的峰值拐点

 

行数

并发

bulk insert

10万行

 5,10,20,30,40,50,100,500     

100万行

500万行

1000万行

测试策略

  • 手动创建数据库，示例命名为z_syw_sysbench
  • 使用sysbench的生成数据，向数据库z_syw_sysbench的库中创建表，
  • 根据不同的测试维度，向每个表中插入测试场景表中给定的行数（如10万行），--thread=100000
  • 每个配置下测试1200秒
  • 每10秒进行一次采样
  • 分别按照测试步骤执行3次，最后的测试结果取平均值（注：测试3次具有可信度，测试1次的数据很难有说服力）
  • 每次的测试结果分别手动记录，后期做数据分析

测试脚本

#生成数据
sysbench /usr/share/sysbench/bulk_insert.lua --threads=60 --mysql-host=192.168.30.118 --mysql-port=3001 --mysql-db=syw_sysbench --mysql-user=kepler --mysql-password=afdd0b4ad2ec172c586e2150770fbf9e prepare

#运行
sysbench /usr/share/sysbench/bulk_insert.lua --threads=1000 --events=100000 --report-interval=10 --time=1200 --mysql-host=192.168.30.118 --mysql-port=3001 --mysql-db=syw_sysbench --mysql-user=kepler --mysql-password=afdd0b4ad2ec172c586e2150770fbf9e run

#删除数据
sysbench /usr/share/sysbench/bulk_insert.lua --threads=1000 --events=100000 --report-interval=10 --time=1200 --mysql-host=192.168.30.118 --mysql-port=3001 --mysql-db=syw_sysbench --mysql-user=kepler --mysql-password=afdd0b4ad2ec172c586e2150770fbf9e cleanup

再次强调的注意事项：bulk insert在使用测试的脚本参数上与insert有所不同，另外，bulk insert在创建表时是空表，不支持在创建表时就写入数据。详解如下

  • --threads=5：表示要创建5张表，表名称命名规则为：sbtest1，sbtest2....sbtest5，如果你想创建更过的表，直接修改--threads的值即可

创建的表结构

执行生成数据的脚本后，将会自动在数据库中创建表

Create Table `sbtest1` (
 `id` int NOT NULL,
 `k` int NOT NULL DEFAULT '0',
 primary key (id)
) DISTRIBUTE BY HASH(`id`) INDEX_ALL='Y'

测试方法

  • 手动创建一个数据库
  • 执行生成数据的SQL
  • 进入数据库，查看表是否按照预期的数量进行创建
    1. 示例：--threads=1，则创建sbtest1
    2. 示例：--threads=2，则创建sbtest1、sbtest2，共2张表
  • 执行run的SQL
  • 进入数据库，查看表中的数据是否写入成功
    1. 通过select count(*) from sbtest1去查看数据是否存在
    2. 查看注意查看数据量是否与预期的写入数据量一致
      1. 示例：--threads=1,--events=100。则sbtest1的表中应该有100行数据
      2. 示例：--threads=5,--events=100，则有sbtest1、sbtest2、sbtest3、sbtest4、sbtest5，每个表中有20条数据，注意当events太大，则可能会导致数据在表中的分布不均，因此需要每次写入数据后，通过如下【示例脚本1】的脚本去查看数据量
  • 查看测试结果，关注每个结果中给定的参数，详细的测试结果解读，见如下的说明
  • 将测试结果通过excel记录，每个测试结果中给定的参数都要详细的记录到excel中，方便后期做分析，或与研发人员一起讨论数据背后的结论

【示例脚本1】

select (select count(*) from sbtest1) as sbtest1,
(select count(*)  from sbtest2) as sbtest2,
(select count(*)  from sbtest3) as sbtest3,
(select count(*)  from sbtest4) as sbtest4,
(select count(*) from sbtest5) as sbtest5,

测试结果记录表格示例

表格说明：

  1. 总共的数据量是100 0000行数据的测试结果记录
  2. 表格顶部的1、2、3表示测试次数
  3. events(avg/stddev)：
    1. 【avg】表示平均向表中写的事务数，注意：这是说的是平均，并不代表每个表中写的数据量是一样的，比如：sbtest1的表行数是1200，sbtest2的表行数是300，这2个的平均值是(1200+300)/2=750，因此events(avg/stddev)给定显示的值是750
    2. 【stddev】表示：待研究
  4. execution time(avg/stddev)：
    1. 【avg】：表示执行的平均时间
    2. 【stddev】：表示：待研究
  5. write/total/queries：经过我的观察，此结果值与给定的--threads的值是相等的，但是目前出现了不一致的情况，后期需要再次观察一下
  6. threads fairness后面表格的截图：是select count(*) from sbtestx的数据统计结果，为了后期来观察数据写入的情况（可不用关注）

 测试结果分析

root@op06:~# sysbench /usr/share/sysbench/bulk_insert.lua --threads=1 --events=1000000 --report-interval=2 --time=1200 --mysql-host=192.168.30.118 --mysql-port=3001 --mysql-db=syw_sysbench --mysql-user=kepler --mysql-password=afdd0b4ad2ec172c586e2150770fbf9e run
sysbench 1.0.18 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 2 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!
-----report-interval=2每间隔2秒打印的测试结果
true    db_bulk_insert_init() failed
[ 2s ] thds: 1 tps: 170381.83 qps: 4.49 (r/w/o: 0.00/4.49/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 147548.78 qps: 4.50 (r/w/o: 0.00/4.50/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 163722.82 qps: 5.00 (r/w/o: 0.00/5.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           31      ----写入的次数
        other:                           0
        total:                           31
    transactions:                        1000000 (157519.17 per sec.)   ----事务数（每秒事务数）
    queries:                             31     (4.88 per sec.)----待研究
    ignored errors:                      0      (0.00 per sec.）
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          6.3446s    ----总共耗费时间
    total number of events:              1000000    ----总共写入的事务数量

Latency (ms):
         min:                                    0.00   ---最小时间
         avg:                                    0.01   ----平均耗费时间
         max:                                  264.84   ----最大时间
         95th percentile:                        0.00   ----95%的用户可能耗费的时间
         sum:                                 5975.17   ----总共耗费的时间

Threads fairness:
    events (avg/stddev):           1000000.0000/0.00   ----平均每个线程中的事务数。 stddev:标准偏差，表示每个表中写入的数据量的相比距离平均数的偏差值
    execution time (avg/stddev):   5.9752/0.00         ----每个线程所耗费的平均时间
 
Sysbench

相关

压测工具sysbench的使用

标签