MySQL性能测试 | 字数总计: 3.1k | 阅读时长: 15分钟 | 阅读量:
sysbench sysbench是基于LuaJIT的可编写多线程基准工具，可用于数据库基准测试，了解系统及数据库的基础性能相关信息，项目地址：sysbench 。sysbench包含以下基准测试：
oltp_*.lua：模拟OLTP下的数据库基准测试的集合
fileio：文件系统基准测试
cpu；简单的CPU基准测试
memory：内存访问基准测试
threads：基于线程的调度程序基准测试
mutex：POSIX互斥基准测试
安装相关依赖及编译程序包
$ yum -y install make automake libtool pkgconfig libaio-devel mariadb-devel openssl-devel $ ./autogen.sh $ ./configure $ make && make install -j 4 $ ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64
sysbench性能基准测试三大步骤：
prepare：初始化测试需要的数据。例如文件系统测试中的文件或数据库测试数据填充
run：运行指定的测试内容
clean：测试运行完成后清理测试临时数据
文件系统测试
查看fileio选项
[root@t-luhx03-v-szzb service]# sysbench fileio help sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2) fileio options: --file-num=N number of files to create [128] --file-block-size=N block size to use in all IO operations [16384] --file-total-size=SIZE total size of files to create [2G] --file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw} --file-io-mode=STRING file operations mode {sync,async,mmap} [sync] --file-async-backlog=N number of asynchronous operatons to queue per thread [128] --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} [] --file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100] --file-fsync-all[=on|off] do fsync() after each write operation [off] --file-fsync-end[=on|off] do fsync() at the end of test [on] --file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync] --file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0] --file-rw-ratio=N reads/writes ratio for combined test [1.5]
初始化测试文件
sysbench fileio --file-total-size=5G --file-num=1 --file-block-size=16384 --file-test-mode=rndrw prepare
执行性能测试
sysbench fileio --file-total-size=5G --file-test-mode=rndrw --time=300 --file-test-mode=rndrw --file-num=1 run File operations: reads/s: 385.54 writes/s: 257.02 fsyncs/s: 6.43 Throughput: read, MiB/s: 6.02 written, MiB/s: 4.02 General statistics: total time: 300.0150s total number of events: 194744 Latency (ms): min: 0.00 avg: 1.54 max: 378.55 95th percentile: 6.67 sum: 299695.52 Threads fairness: events (avg/stddev): 194744.0000/0.00 execution time (avg/stddev): 299.6955/0.00
*注：上述随机读写测试后IOPS=(6.02+4.02)1024/16=642.56
清除测试文件
sysbench fileio --file-total-size=5G --file-test-mode=rndrw --time=300 --file-test-mode=rndrw --file-num=1 clean
mysql测试
常用数据库选项
选项
描述
默认值
–mysql-host
mysql host
–mysql-port
mysql port
–mysql-user
mysql连接用户
–mysql-password
mysql连接用户密码
–mysql-db
mysql测试数据库名
–oltp-tables-count
测试表数量
–oltp-table-size
每张测试表数据量
–report-interval
指定测试进度报告输出的间隔，单位为秒
–oltp-dist-type
指定随机取样类型，可选值有uniform(均匀分布)，Gaussian(高斯分布)，special(空间分布)
special
–rand-init
否随机初始化数据，如果不随机则除了主键不一致其它字段全一致
–events
压力测试产生请求的总数,为0则不限制，由time去控制
–oltp-test-mode
执行模式，可选值有simple(简单查询)、complex(事务模式)、nontrx(非事务模式)
complex
–oltp-read-only
是否设置为只读模式
OFF
–time
压力测试的持续时间，单位为秒
–threads
并发线程数
只读测试：需要将oltp-read-only设置为on的，也可以使用select.lua
混合读写测试：需要把oltp-read-only设置为off，通过分析oltp.lua可以看出单个事务操作的比例select:update_key:update_non_key:delete:insert = 14:1:1:1:1。可通过oltp-point-selects、oltp-simple-ranges、oltp-sum-ranges、oltp-order-ranges、oltp-distinct-ranges，oltp-index-updates、oltp-non-index-updates这些选项去调整读写权重
更新测试：如果只想比较update或insert，可以使用update_index.lua脚本，这里是更新索引列上的值
测试导入数据
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua \ --mysql-host=localhost \ --mysql-port=33006 \ --mysql-user=root \ --mysql-password=Abcd123# \ --mysql-db=sysbench \ --db-driver=mysql \ --oltp-test-mode=complex \ --oltp-tables-count=10 \ --oltp-table-size=1000000 \ --threads=100 \ --report-interval=10 \ --rand-init=on \ --time=300 \ --warmup-time=10 \ prepare
执行后可能存在如下错误
/usr/local/sysbench/bin/sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
解决方案
ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20 echo "/usr/lib64" >> /etc/ld.so.conf ldconfig -v
执行测试
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua \ --mysql-host=localhost \ --mysql-port=33006 \ --mysql-user=root \ --mysql-password=Abcd123# \ --mysql-db=sysbench \ --db-driver=mysql \ --oltp-test-mode=complex \ --oltp-tables-count=10 \ --oltp-table-size=1000000 \ --threads=100 \ --report-interval=10 \ --rand-init=on \ --time=120 \ --warmup-time=10 \ run
执行后会出现如下输出
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 12 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 12 tps: 656.94 qps: 13161.57 (r/w/o: 9213.94/2632.55/1315.08) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 12 tps: 374.98 qps: 7498.46 (r/w/o: 5249.49/1499.01/749.96) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 12 tps: 658.05 qps: 13156.58 (r/w/o: 9209.76/2630.72/1316.11) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 12 tps: 394.16 qps: 7886.18 (r/w/o: 5519.93/1577.94/788.32) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 12 tps: 422.07 qps: 8443.87 (r/w/o: 5910.53/1689.19/844.15) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 12 tps: 556.50 qps: 11130.28 (r/w/o: 7791.06/2226.22/1113.01) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 12 tps: 369.30 qps: 7383.39 (r/w/o: 5168.50/1476.40/738.50) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 12 tps: 632.60 qps: 12647.50 (r/w/o: 8852.80/2529.60/1265.10) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 12 tps: 350.77 qps: 7019.60 (r/w/o: 4914.41/1403.46/701.73) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 12 tps: 487.93 qps: 9757.51 (r/w/o: 6831.13/1950.52/975.86) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 12 tps: 491.07 qps: 9824.38 (r/w/o: 6876.37/1965.88/982.14) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 12 tps: 352.50 qps: 7050.88 (r/w/o: 4935.09/1410.80/705.00) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 804832 write: 229952 other: 114976 total: 1149760 transactions: 57488 (478.77 per sec.) queries: 1149760 (9575.33 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0737s total number of events: 57488 Latency (ms): min: 3.80 avg: 25.06 max: 276.01 95th percentile: 51.94 sum: 1440372.83 Threads fairness: events (avg/stddev): 4790.6667/54.38 execution time (avg/stddev): 120.0311/0.01
注：read表示查询数量，write表示DML数量，transactions则为TPS，Latency为响应时间，queries表示QPS
清理测试数据
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua \ --mysql-host=localhost \ --mysql-port=33006 \ --mysql-user=root \ --mysql-password=Abcd123# \ --mysql-db=sysbench \ --db-driver=mysql \ --oltp-test-mode=complex \ --oltp-tables-count=10 \ --oltp-table-size=1000000 \ --threads=100 \ --report-interval=10 \ --rand-init=on \ --time=120 \ --warmup-time=10 \ cleanup
TPCC-MYSQL tpcc-mysql是percona基于TCP-C衍生的产品，专门用于MySQL TPCC测试，项目地址：tpcc-mysql 。tpcc-mysql内部模拟电商环境数据，其测试表包括仓库、订单、客户，其与sysbench最大的区别在于tpcc-mysql测试过程会不断增加测试数据，而sysbench数据量则保持不变，因此能更好模拟业务不断增长下的性能表现。
$ yum install bzr -y $ cd ./src $ make
初始化测试数据
root@(none) 22:21: create database tpcc; Query OK, 1 row affected (0.04 sec) $ mysql -uroot -p tpcc < create_table.sql $ ./tpcc_load -h localhost -P 33006 -d tpcc -u root -p Abcd123# -w 2
注：-w表示warehouse表的数据量，其它表会按照与这张表的比例关系插入测试数据
创建索引
mysql -uroot -p tpcc < add_fkey_idx.sql
启动测试
tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file
-w：指定仓库数量
-c：指定并发连接数
-r：定开始测试前进行warmup的时间，进行数据预热
-l：测试时长
-i：生成报告间隔时长
-f：指定生成的报告文件名，内容为timestamp对应新订单业务耗时
-t：指定生成的报告文件名，内容为线程对应的CPU TIME
报告分析
$ ./tpcc_start -h localhost -P33006 -d tpcc -uroot -p'Abcd123#' -S /service/mysql/data/mysqld.sock -w 2 -c 32 -r 10 -l 60 -f tpcc_mysql_report *************************************** *** ###easy### TPC-C Load Generator *** *************************************** option h with value 'localhost' option P with value '33006' option d with value 'tpcc' option u with value 'root' option p with value 'Abcd123#' option S (socket) with value '/service/mysql/data/mysqld.sock' option w with value '2' option c with value '32' option r with value '10' option l with value '60' option f with value 'tpcc_mysql_report' <Parameters> [server]: localhost [port]: 33006 [DBname]: tpcc [user]: root [pass]: Abcd123# [warehouse]: 2 [connection]: 32 [rampup]: 10 (sec.) [measure]: 60 (sec.) RAMP-UP TIME.(10 sec.) MEASURING START. 10, trx: 2499, 95%: 24.037, 99%: 35.546, max_rt: 163.790, 2499|231.605, 250|15.369, 250|266.731, 249|42.192 20, trx: 2526, 95%: 23.857, 99%: 32.561, max_rt: 48.550, 2523|222.804, 252|16.321, 253|114.683, 253|58.269 30, trx: 2473, 95%: 23.002, 99%: 31.497, max_rt: 68.122, 2477|226.836, 248|20.634, 247|193.410, 248|50.240 40, trx: 2433, 95%: 24.065, 99%: 38.022, max_rt: 94.433, 2433|270.165, 243|20.539, 244|134.019, 243|42.868 50, trx: 2478, 95%: 22.715, 99%: 30.568, max_rt: 69.342, 2475|246.118, 248|13.359, 247|110.248, 247|50.651 60, trx: 2469, 95%: 22.981, 99%: 31.122, max_rt: 65.746, 2471|257.489, 247|21.066, 247|145.311, 247|52.866 STOPPING THREADS................................ <Raw Results> [0] sc:314 lt:14564 rt:0 fl:0 avg_rt: 14.5 (5) [1] sc:25 lt:14853 rt:0 fl:0 avg_rt: 124.9 (5) [2] sc:1154 lt:334 rt:0 fl:0 avg_rt: 4.7 (5) [3] sc:1062 lt:426 rt:0 fl:0 avg_rt: 85.1 (80) [4] sc:1159 lt:328 rt:0 fl:0 avg_rt: 19.4 (20) in 60 sec. <Raw Results2(sum ver.)> [0] sc:314 lt:14564 rt:0 fl:0 [1] sc:25 lt:14853 rt:0 fl:0 [2] sc:1154 lt:334 rt:0 fl:0 [3] sc:1062 lt:426 rt:0 fl:0 [4] sc:1159 lt:328 rt:0 fl:0 <Constraint Check> (all must be [OK]) [transaction percentage] Payment: 43.48% (>=43.0%) [OK] Order-Status: 4.35% (>= 4.0%) [OK] Delivery: 4.35% (>= 4.0%) [OK] Stock-Level: 4.35% (>= 4.0%) [OK] [response time (at least 90% passed)] New-Order: 2.11% [NG] * Payment: 0.17% [NG] * Order-Status: 77.55% [NG] * Delivery: 71.37% [NG] * Stock-Level: 77.94% [NG] * <TpmC> 14878.000 TpmC
MEASURING START：第一列为10秒间隔时间，第二列为10秒内执行新订单交易数，第三列为95%新订单交易平均时间，第四列为99%新订单交易平均时间，第五列为新订单交易最长响应时间，第五列为支付业务的结果：吞吐量|最大响应时间，第六列为发货业务的结果，第七列为库存业务的结果
Raw Results：分别对应五个业务模块，依次是新订单、支付、订单查询、发货、库存。sc:success表示操作成功的量，rt:retry表示操作重试的量，fl:failure为操作失败的量，avg_rt为平均重试次数
transaction percentage：各个业务的占比
response time：各个业务的响应时间
注：tpcc-mysql测试指标为TPMC，即一分钟的事务量，换算成TPS也就是248
mysqlslap mysqlslap是mysql自带的基准测试功能，可以通过工具自动生成测试数据测试，也可以自身业务内容进行压力测试。详情参考：mysqlslap
[root@t-luhx03-v-szzb ~]# mysqlslap --user=root --password=Abcd123# --concurrency=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 0.490 seconds Minimum number of seconds to run all queries: 0.490 seconds Maximum number of seconds to run all queries: 0.490 seconds Number of clients running queries: 100 Average number of queries per client: 0
我们可以通过自定义表数据，并通过–query选项来指定业务操作脚本，其中我们可以利用substring(MD5(rand()) from 1 for 32)来随机生成字符串，模拟业务增长场景。
当然我们可以看到mysqlslap的输出结果过于简单，这里我们可以通过NMON收集测试过程中的系统状态信息，通过mysqladmin -uroot -p extended-status输出数据库状态信息，对结果进行筛选汇总能得到一个更全面的信息。
