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秒打印日志，注意：若批量插入的数据比较少，则不会打印出来日志，直接将会打印最终的测试结果
• --time=3：测试的时间一定要注意，若是时间给定的太短，可能会导致数据库中写入的数据量与预期的数据量不一致，所以前期一定要做好验证和检查。
  • 示例：向sbtest1中写入100000行数据
    • 设置--time=3，在3秒内跑结束了run的测试，进入数据库，查看sbtest1的数据量写入了多少行，通过select count(*) from sbtest1去查看数据量，实际等于87999，主要是因为--time的值给的太少了，导致数据没有写完。
    • 重新设置--time=10，再次执行run的测试，完成后进入数据库查看，100000行数据全部写入成功

测试场景

 

测试场景一

 

按照不同的行数，不考虑并发，对表进行bulk insert测试，观察在固定的时间内，性能的表现情况

 

测试场景二

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

 

测试策略

• 手动创建数据库，示例命名为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】：表示：执行测试时间的标准偏差（说明：根据如下的表格中的数据可以看出，当10万上数据写入一张表时，是没有时间偏差的，当10万行数据写入到>1张表时，就可能会产生标准偏差，解释为10万行数据写入到5张表中，假如每张表写入2万行，但5张表所写完2万行数据的时间不一致，因此就有了标准偏差）
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.)   ----事务数（每秒事务数）【TPC】
    queries:                             31     (4.88 per sec.)----[QPS]
    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   ----#平均每个线程完成envet的次数.stddev:标准偏差，表示每个表中写入的数据量的相比距离平均数的偏差值
    execution time (avg/stddev):   5.9752/0.00         ----#平均每个线程平均耗时，后一个值是标准差