ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > #MySQL# 使用sysbench 做OLTP 基准测试

#MySQL# 使用sysbench 做OLTP 基准测试

原创 MySQL 作者：Kind0f 时间：2017-04-18 18:13:21 0 删除 编辑
一、 安装
① 下载源码包：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。
    如果是压测本地数据库的话 还可以使用 orzdba -sys -d sda 或者 iostat -k -x 1 来查看此时系统的状态，看瓶颈是磁盘IO ? 还是 CPU？


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31385648/viewspace-2137497/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： #MySQL# INNODB存储引擎之插入缓冲
下一篇： #Redis# REDIS应用场景
请登录后发表评论 登录
全部评论
Kind0f

注册时间：2016-08-17

  • 博文量
    39
  • 访问量
    48069

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593