MySQL如何使用sysbench做OLTP基准测试

这篇文章给大家分享的是有关MySQL如何使用sysbench做OLTP基准测试的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

一、 安装
① 下载源码包：https://dev.mysql.com/downloads/benchmarks.html
② 安装依赖 yum -y install automake autoconf libtool
③tar xzvf sysbench-0.4.12.10.tar.gz; cd sysbench-0.4.12.10/
④./autogen.sh; ./configure
⑤ make && make install
然后我们可以在sysbench 目录看到可以执行文件sysbench了

二、命令
首先看下命令基本用法 

1. root@10.30.5.2:sysbench# ./sysbench –help

2. Usage:

3. sysbench [general-options]… –test= [test-options]… command

5. General options:

6. –num-threads=N             number of threads to use [1]

7. –max-requests=N            limit for total number of requests [10000]

8. –max-time=N                limit for total execution time in seconds [0]

9. –forced-shutdown=STRING    amount of time to wait after –max-time before forcing shutdown [off]

10. –thread-stack-size=SIZE    size of stack per thread [32K]

11. –init-rng=[on|off]         initialize random number generator [off]

12. –seed-rng=N                seed for random number generator, ignored when 0 [0]

13. –tx-rate=N                 target transaction rate (tps) [0]

14. –tx-jitter=N               target transaction variation, in microseconds [0]

15. –report-interval=N         periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]

16. –report-checkpoints=[LIST,…]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []

17. –test=STRING               test to run

18. –debug=[on|off]            print more debugging info [off]

19. –validate=[on|off]         perform validation checks where possible [off]

20. –help=[on|off]             print help and exit

21. –version=[on|off]          print version and exit

23. Log options:

24. –verbosity=N      verbosity level {5 – debug, 0 – only critical messages} [4]

26. –percentile=N      percentile rank of query response times to count [95]

28. Compiled-in tests:

29. fileio – File I/O test

30. cpu – CPU performance test

31. memory – Memory functions speed test

32. threads – Threads subsystem performance test

33. mutex – Mutex performance test

34. oltp – OLTP test

36. Commands: prepare run cleanup help version

38. See 'sysbench –test= help' for a list of options for each test.

以上可以看到 sysbench可以测试的有 CPU 、磁盘IO、内存、线程、MUTEX 以及OLTP ，
常用参数：
–num-threads=N     并发线程数
–max-requests=N    限制压测请求总数
–max-time=N        限制压测时间

这里看下OLTP测试方法

1. ./sysbench –test=oltp help

2. sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

4. oltp options:

5. –oltp-test-mode=STRING                  test type to use {simple,complex,nontrx,sp} [complex]

6. –oltp-reconnect-mode=STRING             reconnect mode {session,transaction,query,random} [session]

7. –oltp-sp-name=STRING                    name of store procedure to call in SP test mode []

8. –oltp-read-only=[on|off]                generate only 'read' queries (do not modify database) [off]

9. –oltp-avoid-deadlocks=[on|off]          generate update keys in increasing order to avoid deadlocks [off]

10. –oltp-skip-trx=[on|off]                 skip BEGIN/COMMIT statements [off]

11. –oltp-range-size=N                      range size for range queries [100]

12. –oltp-point-selects=N                   number of point selects [10]

13. –oltp-use-in-statement=N                Use IN-statement with 10 PK lookups per query [0]

14. –oltp-simple-ranges=N                   number of simple ranges [1]

15. –oltp-sum-ranges=N                      number of sum ranges [1]

16. –oltp-order-ranges=N                    number of ordered ranges [1]

17. –oltp-distinct-ranges=N                 number of distinct ranges [1]

18. –oltp-index-updates=N                   number of index update [1]

19. –oltp-non-index-updates=N               number of non-index updates [1]

20. –oltp-nontrx-mode=STRING                mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]

21. –oltp-auto-inc=[on|off]                 whether AUTO_INCREMENT (or equivalent) should be used on id column [on]

22. –oltp-connect-delay=N                   time in microseconds to sleep after connection to database [10000]

23. –oltp-user-delay-min=N                  minimum time in microseconds to sleep after each request [0]

24. –oltp-user-delay-max=N                  maximum time in microseconds to sleep after each request [0]

25. –oltp-table-name=STRING                 name of test table [sbtest]

26. –oltp-table-size=N                      number of records in test table [10000]

27. –oltp-dist-type=STRING                  random numbers distribution {uniform,gaussian,special} [special]

28. –oltp-dist-iter=N                       number of iterations used for numbers generation [12]

29. –oltp-dist-pct=N                        percentage of values to be treated as 'special' (for special distribution) [1]

30. –oltp-dist-res=N                        percentage of 'special' values to use (for special distribution) [75]

31. –oltp-point-select-mysql-handler=[on|off]Use MySQL HANDLER for point select [off]

32. –oltp-point-select-all-cols=[on|off]    select all columns for the point-select query [off]

33. –oltp-secondary=[on|off]                Use a secondary index in place of the PRIMARY index [off]

34. –oltp-num-partitions=N                  Number of partitions used for test table [0]

35. –oltp-num-tables=N                      Number of test tables [1]

37. General database options:

39. –db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)

40. –db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]

43. Compiled-in database drivers:

44. mysql – MySQL driver

46. mysql options:

47. –mysql-host=[LIST,…]       MySQL server host [localhost]

48. –mysql-port=N                MySQL server port [3306]

49. –mysql-socket=STRING         MySQL socket

50. –mysql-user=STRING           MySQL user [sbtest]

51. –mysql-password=STRING       MySQL password []

52. –mysql-db=STRING             MySQL database name [sbtest]

53. –mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]

54. –mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]

55. –mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]

56. –myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]

57. –mysql-create-options=STRING additional options passed to CREATE TABLE []

说明 ：
常用参数
① 基本参数
–db-driver=mysql     对mysql进行 OLTP 基准测试
–mysql-host 、–mysql-port、–mysql-socket、–mysql-user、–mysql-password 这些是基本的参数我就不解释了
–mysql-db=xxx  压测的database，这里得指定一下
② oltp常用参数
–oltp-test-mode=complex/simple/nontrx  测试模式
–oltp-num-tables=10 oltp测试的表数量 0.4.10版本最大表数量16
–oltp-table-size=xxx  测试表的记录数

三、测试
① 测试准备：

1. root@10.30.5.2:sysbench#./sysbench –num-threads=64 –max-requests=200000 –test=oltp –db-driver=mysql –mysql-user=root –mysql-host=10.30.22.2 –mysql-password=x –oltp-test-mode=complex –mysql-db=tab –oltp-table-size=5000000 –oltp-num-tables=16 prepare

2. sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

4. Creating table 'sbtest14'…

5. Creating table 'sbtest7'…

6. Creating table 'sbtest'…

7. Creating table 'sbtest11'…

8. Creating table 'sbtest8'…

9. Creating table 'sbtest6'…

10. Creating table 'sbtest9'…

11. Creating table 'sbtest12'…

12. Creating table 'sbtest3'…

13. Creating table 'sbtest15'…

14. Creating table 'sbtest2'…

15. Creating table 'sbtest4'…

16. Creating table 'sbtest1'…

17. Creating table 'sbtest5'…

18. Creating table 'sbtest13'…

19. Creating table 'sbtest10'…

20. Creating 5000000 records in table 'sbtest11'…

21. Creating 5000000 records in table 'sbtest1'…

22. Creating 5000000 records in table 'sbtest14'…

23. Creating 5000000 records in table 'sbtest'…

24. Creating 5000000 records in table 'sbtest6'…

25. Creating 5000000 records in table 'sbtest2'…

26. Creating 5000000 records in table 'sbtest13'…

27. Creating 5000000 records in table 'sbtest15'…

28. Creating 5000000 records in table 'sbtest12'…

29. Creating 5000000 records in table 'sbtest4'…

30. Creating 5000000 records in table 'sbtest3'…

31. Creating 5000000 records in table 'sbtest9'…

32. Creating 5000000 records in table 'sbtest8'…

33. Creating 5000000 records in table 'sbtest10'…

34. Creating 5000000 records in table 'sbtest5'…

35. Creating 5000000 records in table 'sbtest7'…

② 测试结果

点击(此处)折叠或打开

1. root@10.30.5.2:sysbench# ./sysbench –num-threads=64 –max-requests=200000 –test=oltp –db-driver=mysql –mysql-user=root –mysql-host=10.30.22.xxx –mysql-password=xxx –oltp-test-mode=complex –mysql-db=tab –oltp-table-size=5000000 –oltp-num-tables=16 run

2. sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

4. Running the test with following options:

5. Number of threads: 64

6. Random number generator seed is 0 and will be ignored

9. Doing OLTP test.

10. Running mixed OLTP test

11. Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)

12. Using "BEGIN" for starting transactions

13. Using auto_inc on the id column

14. Maximum number of requests for OLTP test is limited to 200000

15. Using 16 test tables

16. Threads started!

17. Done.

19. OLTP test statistics:

20. queries performed:

21. read:                            2800224

22. write:                           1000080

23. other:                           400032

24. total:                           4200336

25. transactions:                        200016 (2000.64 per sec.)

26. deadlocks:                           0      (0.00 per sec.)

27. read/write requests:                 3800304 (38012.16 per sec.)

28. other operations:                    400032 (4001.28 per sec.)

30. General statistics:

31. total time:                          99.9760s

32. total number of events:              200016

33. total time taken by event execution: 6394.8091

34. response time:

35. min:                                 11.00ms

36. avg:                                 31.97ms

37. max:                                293.00ms

38. approx.  95 percentile:              52.10ms

40. Threads fairness:

41. events (avg/stddev):           3125.2500/50.55

42. execution time (avg/stddev):   99.9189/0.01

③ 测试清除

点击(此处)折叠或打开

1. root@10.30.5.2:sysbench# ./sysbench –num-threads=64 –max-requests=200000 –test=oltp –db-driver=mysql –mysql-user=root –mysql-host=10.30.22.xxx –mysql-password=xxx –oltp-test-mode=complex –mysql-db=tab –oltp-table-size=5000000 –oltp-num-tables=16 cleanup

2. sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

4. Dropping table 'sbtest'…

5. Dropping table 'sbtest1'…

6. Dropping table 'sbtest2'…

7. Dropping table 'sbtest3'…

8. Dropping table 'sbtest4'…

9. Dropping table 'sbtest5'…

10. Dropping table 'sbtest6'…

11. Dropping table 'sbtest7'…

12. Dropping table 'sbtest8'…

13. Dropping table 'sbtest9'…

14. Dropping table 'sbtest10'…

15. Dropping table 'sbtest11'…

16. Dropping table 'sbtest12'…

17. Dropping table 'sbtest13'…

18. Dropping table 'sbtest14'…

19. Dropping table 'sbtest15'…

20. Done.

总结：
1> PREPARE阶段
   在 PREPARE 阶段我们就需要想好，此时mysql 的配置，如 innodb_flush_log_at_trx_commit 、 sync_binlog 以及BP 的大小等。
   然后结合 BP 的大小我们需要创建表的记录数，表的个数，并发线程等，综合考虑
   ① 若数据量 < BP 所有数据都会缓存到内存，此时增加 并发线程数 来测整个此时的CPU核数是否能抗住测试压力
   ② 若数据量 >> BP 则主要测试整个系统的稳定性，我们可以结合监控看缓存命中率( orzdba )，以及对应的 磁盘IO( iostat / orzdba ) 等，来获取整个数据库系统的薄弱点

2> RUN 阶段
    在 RUN 阶段的同时 ，我们可以通过 orzdba/iostat 等工具查看当前的数据库状态
    ① 上面的测试结果我们可以看到，    
     transactions: 200016 (2000.64 per sec.) TPS 大概为 2000
     read/write requests: 3800304 (38012.16 per sec.) QPS 达到 38000
     approx. 95 percentile: 52.10ms  95%的请求相应时间在52.10ms左右
    可以说性能是相当不错了(这里我测试的是腾讯云 CDB , 配置为 1000MB 的BP )
    ② 通过orzdba 结合running过程查看数据库状态

点击(此处)折叠或打开

1. root@10.30.5.2:orzdba_home# ./orzdba -mysql -innodb -rt

2. .=================================================.

3. |       Welcome to use the orzdba tool !          |

4. |          Yep…Chinese English~                 |

5. '=============== Date : 2017-04-18 ==============='

7. HOST: 10.30.22.2   IP: 10.30.5.2

8. DB  : performance_schema|tab

9. Var : binlog_format[MIXED] max_binlog_cache_size[17179869184G] max_binlog_size[1G]

10. max_connect_errors[999999999] max_connections[800] max_user_connections[0]

11. open_files_limit[102400] sync_binlog[0] table_definition_cache[768]

12. table_open_cache[512] thread_cache_size[512]

14. innodb_adaptive_flushing[ON] innodb_adaptive_hash_index[ON] innodb_buffer_pool_size[893M]

15. innodb_file_per_table[ON] innodb_flush_log_at_trx_commit[2] innodb_flush_method[O_DIRECT]

16. innodb_io_capacity[20000] innodb_lock_wait_timeout[7200] innodb_log_buffer_size[64M]

17. innodb_log_file_size[500M] innodb_log_files_in_group[2] innodb_max_dirty_pages_pct[75]

18. innodb_open_files[1024] innodb_read_io_threads[4] innodb_thread_concurrency[0]

19. innodb_write_io_threads[4]

21. ——–                     -QPS- -TPS-         -Hit%- —innodb bp pages status– —–innodb data status—- –innodb log–   his –log(byte)–  read —query— ——threads—— —–bytes—- ——–tcprstat(us)——–

22. time  |  ins   upd   del    sel   iud|     lor    hit|   data   free  dirty flush| reads writes  read written|fsyncs written| list uflush  uckpt  view inside  que| run  con  cre  cac|   recv   send|  count    avg 95-avg 99-avg|

23. 17:24:53|    0     0     0      0     0|       0 100.00|      0      0      0     0|     0      0      0      0|     0       0|    0      0      0     0     0     0|   0    0    0    0|      0      0|      0      0      0      0|

24. 17:24:54| 2153  6454  2153  30115 10760|  478891  97.72|  55869      0  19432  2372| 11355   6686 177.4m  79.2m|     2    5.1m|  137   1.7m  180.6m   57     0     0|  20   68    0    1|   1.1m  10.7m|  23711    267    183    236|

25. 17:24:55| 1960  5891  1962  27470  9813|  437599  97.71|  55872      0  19793  2132| 10416   6063 162.8m  71.4m|     1    4.8m|  142   2.4m  183.5m   62     0     0|  14   68    0    1|  1005k   9.7m|  23004    356    215    283|

26. 17:24:56| 2027  6091  2033  28422 10151|  451846  97.74|  55870      0  20024  2249| 10609   6320 165.8m  75.2m|     3    4.9m|  133   927k  186.5m   59     0     0|   8   68    0    1|   1.0m  10.2m|  22684    299    186    244|

27. 17:24:57| 2291  6865  2286  32067 11442|  511514  97.69|  55870      0  20248  2611| 12314   7194 192.4m  87.0m|     2    5.4m|  128    19k  189.6m   54     0     0|   4   68    0    1|   1.1m  11.5m|  25197    267    188    234|

28. 17:24:58| 2210  6632  2207  30947 11049|  493747  97.77|  55865      0  20361  2371| 11478   6810 179.3m  79.5m|     2    5.4m|  130   267k  192.9m   64     0     0|  48   68    0    1|   1.1m  11.3m|  24586    270    182    232|

29. 17:24:59| 2225  6680  2226  31102 11131|  496716  97.70|  55866      0  20305  2602| 11891   7059 185.8m  86.6m|     1    5.3m|  149   473k  196.0m   53     0     0|  11   68    0    1|   1.1m  11.3m|  20655    371    219    304|

30. 17:25:00| 2126  6377  2130  29819 10633|  472984  97.70|  55868      0  20195  2489| 11332   6749 177.1m  82.8m|     2    5.0m|  125   370k  199.0m   62     0     0|  13   68    0    1|   1.1m  10.8m|   8707    958    664    856|

31. 17:25:01| 2169  6507  2165  30307 10841|  484346  97.71|  55766     99  20214  2485| 11550   6849 180.5m  82.7m|     1    5.1m|  133   808k  202.0m   57     0     0|  15   68    0    1|   1.1m  11.2m|   8578    996    636    844|

    可以发现 在 32个 thread并发进行complex操作的时候，每秒的insert量 update量 delete量 select量可以看得非常清楚，还有innodb_log 的fsync量，以及数据库的response time。

感谢各位的阅读！关于“MySQL如何使用sysbench做OLTP基准测试”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！