未加星标

sysbench的一点整理

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

还记得刚到公司的第一个任务就是搞性能测试,断断续续也是搞了很久,sysbench这个工具也是用了有一段时间了,不敢说熟练,但是也是踩了一些坑,在此做些整理,欢迎诸位指正补充…

sysbench主要包括一下几种方式的测试:

cpu性能 磁盘io性能 调度程序性能 内存分配及传输速度 POSIX线程性能 数据库性能(OLTP基准测试) 先说安装

一般我是直接从github上copy下来,自己编译

git clone https://github.com/akopytov/sysbench.git // 如果使用0.5 版本: git branch 0.5 ./autogen.sh ./configure make make install

详细安装说明参考 readme

只说 oltp

sysbench 的 oltp 主要用于评估测试各种不同系统参数下的数据库负载情况。目前sysbench的数据库测试支持 mysql、PostgreSQL、Oracle (我的任务主要是做 mysql & tidb 的对比测试), 相比 0.4 版本,后续的版本 oltp 测试主要结合了 lua 脚本,不需要修改源码,通过自定义lua脚本就可以实现不同业务类型的测试。

参数

sysbench 版本

sysbench --version sysbench 1.0.1-7fe53e4   

具体参数

sysbench --help Usage: sysbench [options]... [testname] [command] Commands implemented by most tests: prepare run cleanup help General options: --threads=N 创建测试线程的数量, 默认为 1 --events=N 事件最大数量,默认为 0 ,不限制 --time=N 最大执行时间,单位是 s ,默认是 0 ,不限制 --forced-shutdown=STRING 超过max-time强制中断, 默认是 off --thread-stack-size=SIZE 每个线程的堆栈大小, 默认是 64k --rate=N average transactions rate. 0 for unlimited rate [0] --report-interval=N 报告中间统计信息间隔, 0 代表禁止, 默认为 0 --report-checkpoints=[LIST,...] 转储完全统计信息并在指定时间点复位所有计数器。 参数是逗号分隔值的列表,表示从必须执行报告检查点的测试开始所经过的时间(以秒为单位)。 默认情况下,报告检查点处于关闭状态 --debug[=on|off] 是否显示更多的调试信息, 默认是off --validate[=on|off] 在可能情况下执行验证检查, 默认是off。 --help[=on|off] 输出 help 信息, 并退出 --version[=on|off] 输出版本信息, 并退出 --config-file=FILENAME 配置文件 --tx-rate=N deprecated alias for --rate [0] --max-requests=N deprecated alias for --events [0] --max-time=N deprecated alias for --time [0] --num-threads=N deprecated alias for --threads [1] Pseudo-Random Numbers Generator options: --rand-type=STRING 分布的随机数{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是special --rand-spec-iter=N 产生数的迭代次数。默认是12 --rand-spec-pct=N 值的百分比被视为’special’ (for special distribution)。默认是1 --rand-spec-res=N 'special'的百分比值。默认是75 --rand-seed=N seed for random number generator. When 0, the current time is used as a RNG seed. [0] --rand-pareto-h=N 参数h用于 pareto 分布[0.2] Log options: --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3] --percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95] --histogram[=on|off] print latency histogram in report [off] General database options: --db-driver=STRING specifies database driver to use ('help' to get list of available drivers) --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto] --db-debug[=on|off] print database-specific debug information [off] General database options: --db-driver=STRING specifies database driver to use ('help' to get list of available drivers) --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto] --db-debug[=on|off] print database-specific debug information [off] Compiled-in database drivers: mysql - MySQL driver mysql options: --mysql-host=[LIST,...] MySQL server host [localhost] --mysql-port=[LIST,...] MySQL server port [3306] --mysql-socket=[LIST,...] MySQL socket --mysql-user=STRING MySQL user [sbtest] --mysql-password=STRING MySQL password [] --mysql-db=STRING MySQL database name [sbtest] --mysql-ssl[=on|off] use SSL connections, if available in the client library [off] --mysql-ssl-cipher=STRING use specific cipher for SSL connections [] --mysql-compression[=on|off] use compression, if available in the client library [off] --mysql-debug[=on|off] trace all client library calls [off] --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205] --mysql-dry-run[=on|off] Dry run, pretend that all MySQL client API calls are successful without executing them [off] Compiled-in tests: fileio - File I/O test cpu - CPU performance test memory - Memory functions speed test threads - Threads subsystem performance test mutex - Mutex performance test See 'sysbench <testname> help' for a list of options for each test. sysbench ./lua/oltp_read_write.lua help oltp_read_write.lua options: --distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1] --sum_ranges=N Number of SELECT SUM() queries per transaction [1] --skip_trx[=on|off] Don't start explicit transactions and execute all queries as in the AUTOCOMMIT mode [off] --secondary[=on|off] Use a secondary index in place of the PRIMARY KEY [off] --create_secondary[=on|off] Create a secondary index in addition to the PRIMARY KEY [on] --index_updates=N Number of UPDATE index queries per transaction [1] --range_size=N Range size for range SELECT queries [100] --auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on] --delete_inserts=N Number of DELETE/INSERT combination per transaction [1] --tables=N Number of tables [1] --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb] --non_index_updates=N Number of UPDATE non-index queries per transaction [1] --table_size=N Number of rows per table [10000] --pgsql_variant=STRING Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0 --simple_ranges=N Number of simple range SELECT queries per transaction [1] --order_ranges=N Number of SELECT ORDER BY queries per transaction [1] --range_selects[=on|off] Enable/disable all range SELECT queries [on] --point_selects=N Number of point SELECT queries per transaction [10]

参数太多实在不想一句句翻译了…

开测

sysbench的测试流程

prepare(准备数据) -> run(运行测试) -> cleanup(清理数据) 目前社区提供的lua脚步

bulk_insert.lua oltp_common.lua oltp_delete.lua oltp_insert.lua oltp_point_select.lua oltp_read_only.lua oltp_read_write.lua oltp_update_index.lua oltp_update_non_index.lua oltp_write_only.lua select_random_points.lua select_random_ranges.lua

以 oltp_read_only.lua 为例

./sysbench ./lua/oltp_read_only.lua \ --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=000000 \ --mysql-db=test --tables=10 --table-size=10000 \ --report-interval=10 \ --threads=128 --time=120 \ prepare/run/cleanup

结果解读

sysbench 1.0.1-7fe53e4 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 128 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 128 tps: 1208.30 qps: 19436.82 (r/w/o: 17007.47/0.00/2429.35) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 128 tps: 1264.26 qps: 20233.57 (r/w/o: 17705.05/0.00/2528.52) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 128 tps: 1273.70 qps: 20375.11 (r/w/o: 17827.81/0.00/2547.30) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 128 tps: 1287.20 qps: 20598.39 (r/w/o: 18023.89/0.00/2574.50) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 128 tps: 1281.13 qps: 20491.35 (r/w/o: 17929.10/0.00/2562.26) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 128 tps: 1267.04 qps: 20276.48 (r/w/o: 17742.41/0.00/2534.07) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 128 tps: 1257.80 qps: 20127.47 (r/w/o: 17611.96/0.00/2515.51) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 128 tps: 1243.93 qps: 19894.40 (r/w/o: 17406.43/0.00/2487.96) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 128 tps: 1237.60 qps: 19807.67 (r/w/o: 17332.67/0.00/2475.00) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 128 tps: 1053.60 qps: 16850.29 (r/w/o: 14742.89/0.00/2107.40) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 128 tps: 1001.41 qps: 16021.33 (r/w/o: 14018.60/0.00/2002.73) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 128 tps: 859.76 qps: 13750.85 (r/w/o: 12034.73/0.00/1716.12) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 1994860 // 总 select 数量 write: 0 // 总update、insert、delete语句数量 other: 284980 //commit、unlock tables以及其他mutex的数量 total: 2279840 transactions: 142490 (1186.20 per sec.) //通常需要关注的数字(TPS) queries: 2279840 (18979.13 per sec.) //通常需要关注的数字(QPS) ignored errors: 0 (0.00 per sec.) //忽略的错误数 reconnects: 0 (0.00 per sec.) General statistics: total time: 120.1216s //即time指定的压测实际 total number of events: 142490 //总的事件数,一般与transactions相同 Latency (ms): min: 8.52 avg: 107.84 max: 480.08 95th percentile: 170.48 //95%的语句的平均响应时间 sum: 15365843.76 Threads fairness: events (avg/stddev): 1113.2031/14.76 execution time (avg/stddev): 120.0457/0.04

我们一般关注的指标主要有:

response time avg: 平均响应时间。(后面的95%的大小可以通过 percentile=98的方式去更改) transactions: 精确的说是这一项后面的TPS 。但如果使用了-skip-trx=on,这项事务数恒为0,需要用total number of events 去除以总时间,得到tps(其实还可以分为读tps和写tps) queries: 用它除以总时间,得到吞吐量QPS 当然还有一些系统层面的cpu,io,mem相关指标 建议

sysbench 在最近刚升级了最新版(以上使用的就是最新版),有很多改进之处,让我最惊喜的是prepare数据相比之前变为多线程,准备数据更快, 降低了在prepare数据上耗费的时间(虽然之前自己用别的办法加速prepare数据,但是方法比较挫,这里就不做介绍了)

注意新版本相比 1.0 和 0.5 有许多参数的改变 当然你也可以指定自己定义的lua脚步,实现不同业务类型的测试

参考 github doc sysbench 0.5使用手册 使用sysbench对mysql压力测试

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLPostgreSQL数据性能测试LuaCPUUTAUAUT
分页:12
转载请注明
本文标题:sysbench的一点整理
本站链接:http://www.codesec.net/view/532050.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(16)