mysql之优化测试工具

一、mysql调优

1.1 为什么要测试

高性能不是指"绝对性能"强悍,而是指业务能发挥出硬件的最大水平.性能强的服务器并非"设计",而是不断改进,提升短板、测试&＃xff0c;量化找出短板的过程。只有会测试能把数据量化,才能进一步优化.

1.2 测试指标?

1. 吞吐量&＃xff1a;单位时间内的事务处理数,单位tps(每秒事务数)
2. 响应时间&＃xff1a;语句平均响应时间,一般截取某段时间内,95%范围内的平均时间
3. 并发性&＃xff1a;线程同时执行
4. 可扩展性&＃xff1a;资源增加,性能也能正比增加

1.3 用什么工具测试?

1. mysqlslap
2. sysbench
3. tpcc

1.4 mysqlslap测试

mysqlslap --optionsmysqlslap -h 127.0.0.1 -uroot --auto-generate-sql --concurrency 20 --iterations 1 --create-schema&＃61;big_data --query&＃61;&＃39;select * from dict limit 1&＃39;mysqlslap -h 127.0.0.1 -a -c 100 --number-of-queries 1000 -i 10 -u root -p


–concurrency 代表并发数量&＃xff0c;多个可以用逗号隔开concurrency&＃61;10,50,100, 并发连接线程数分别是10、50、100个并发。
–create-schema 代表自定义测试库名 database。(没指定时&＃xff0c;可能会遇到mysqlslap: 1049 Unknown database ‘mysqlslap’)
–engines -e 代表要测试的引擎&＃xff0c;可以有多个&＃xff0c;用分隔符隔开。
–iterations -i 代表要运行这些测试多少次。
–auto-generate-sql -a 代表用系统自己生成的SQL脚本来测试。
–auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的&＃xff08;read,write,update,mixed&＃xff09;
–number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
–debug-info 代表要额外输出CPU以及内存的相关信息。

结果显示&＃xff1a;

BenchmarkAverage number of seconds to run all queries: 1.176 secondsMinimum number of seconds to run all queries: 0.512 secondsMaximum number of seconds to run all queries: 4.017 secondsNumber of clients running queries: 10Average number of queries per client: 10


结果中可以看到执行语句的平均耗时&＃xff0c;最大耗时和最小耗时&＃xff0c;并发线程数等等。

1.5 sysbench 测试

sysbench 安装&＃xff1a;yum install sysbench -y &＃xff0c;等下载完成之后&＃xff0c;在 /usr/share/sysbench/ 目录下会有一些软件自带等lua测试脚本文件&＃xff0c;提供系统的压测脚本。

1.5.1 测试CPU性能

#2个线程寻找20000以内的素数
sysbench --test&＃61;cpu --cpu-max-prime&＃61;20000 --num-threads&＃61;2 run


1.5.2 测试IO性能

sysbench --test&＃61;fileio --file-total-size&＃61;20G prepare
sysbench --test&＃61;fileio --file-total-size&＃61;20G --file-test-mode&＃61;rndrw run
sysbench --test&＃61;fileio --file-total-size&＃61;20G cleanup


seqwr&＃xff1a; 顺序写入
seqrewq&＃xff1a; 顺序重写
seqrd&＃xff1a; 顺序读取
rndrd&＃xff1a; 随机读取
rndwr&＃xff1a; 随机写入
rndrw&＃xff1a; 混合随机读写

1.5.3 mysql压测

准备&＃xff1a;数据库建测试库 test_db&＃xff0c;同时创建好对应的测试账号test_user&＃xff0c;密码也是test_user&＃xff0c;用户有访问权限

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_read_write --db-ps-mode&＃61;disable prepare


参数说明&＃xff1a;

–db-driver&＃61;mysql &＃xff1a;基于mysql驱动连接mysql数据库&＃xff0c;如oracle或sqlserver&＃xff0c;那就是其他数据库驱动
–time&＃61;300&＃xff1a;连续访问300秒
–threads&＃61;10&＃xff1a;用10个线程模拟并发访问
–report-interval&＃61;1&＃xff1a;每隔1秒输出一下压测情况
–mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user&＃xff1a;连接到哪台机器的哪个端口上的MySQL库&＃xff0c;用户名和密码是什么
–mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000&＃xff1a;在test_db这个库里&＃xff0c;构造20个测试表&＃xff0c;每个测试表构造100万条测试数据&＃xff0c;测试表名字类似于sbtest1&＃xff0c;sbtest2
oltp_read_write&＃xff1a;执行oltp数据库的读写测试
–db-ps-mode&＃61;disable&＃xff1a;禁止ps模式

测试数据库的综合读写TPS&＃xff0c;用oltp_read_write模式&＃xff08;命令最后是run而非prepare&＃xff0c;即运行压测&＃xff09;&＃xff1a;

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_read_write --db-ps-mode&＃61;disable run


测试数据库的只读性能&＃xff0c;用oltp_read_only模式&＃xff08;命令中oltp_read_write已变为oltp_read_only&＃xff09;&＃xff1a;

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_read_only --db-ps-mode&＃61;disable run


测试数据库的删除性能&＃xff0c;用oltp_delete模式&＃xff1a;

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_delete --db-ps-mode&＃61;disable run


测试数据库的更新索引字段的性能&＃xff0c;用oltp_update_index模式&＃xff1a;

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_update_index --db-ps-mode&＃61;disable run


测试数据库的更新非索引字段的性能&＃xff0c;用oltp_update_non_index模式&＃xff1a;

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_update_non_index --db-ps-mode&＃61;disable run


测试数据库的插入性能&＃xff0c;用oltp_insert模式&＃xff1a;

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_insert --db-ps-mode&＃61;disable run


测试数据库的写入性能&＃xff0c;用oltp_write_only模式&＃xff1a;

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_write_only --db-ps-mode&＃61;disable run


sysbench工具会根据你的指令构造出各种SQL语句去更新或查询20张测试表的数据&＃xff0c;同时监测数据库的压测性能指标&＃xff0c;最后完成压测后&＃xff0c;可执行下面cleanup命令&＃xff0c;清理数据。

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_read_write --db-ps-mode&＃61;disable cleanup


结果分析&＃xff1a;

按照上面命令&＃xff0c;每隔1秒都会输出一次压测报告&＃xff0c;此时他每隔一秒会输出类似下面的东西&＃xff1a;

[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s: 0.00


首先这是第22s输出的一段压测统计报告&＃xff0c;然后是其他一些统计字段&＃xff1a;

thds: 10&＃xff0c;这个意思就是有10个线程在压测
tps: 380.99&＃xff0c;这个意思就是每秒执行了380.99个事务
qps: 7610.20&＃xff0c;这个意思就是每秒可以执行7610.20个请求
(r/w/o: 5132.99/1155.86/1321.35)&＃xff0c;这个意思就是说&＃xff0c;在每秒7610.20个请求中&＃xff0c;有5132.99个请求是读请求&＃xff0c;1155.86个请求是写请求&＃xff0c;1321.35个请求是其他的请求&＃xff0c;就是对QPS进行了拆解
lat (ms, 95%): 21.33&＃xff0c;这个意思就是说&＃xff0c;95%的请求的延迟都在21.33毫秒以下
err/s: 0.00 reconn/s: 0.00&＃xff0c;这两个的意思就是说&＃xff0c;每秒有0个请求是失败的&＃xff0c;发生了0次网络重连

这个压测结果会根据每个人的机器的性能不同有很大差距&＃xff0c;要是机器性能特别高&＃xff0c;可开很多的并发线程去压测&＃xff0c;如100个线程&＃xff0c;此时可能会发现数据库每秒的TPS有上千个&＃xff0c;如果机器性能很低&＃xff0c;可能压测出TPS才二三十个&＃xff0c;QPS才几百个&＃xff0c;这都有可能。

另外在完成压测后&＃xff0c;会显示一个总的压测报告&＃xff0c;如下&＃xff1a;

SQL statistics:queries performed:read: 1480084 // 这就是说在300s的压测期间执行了148万多次的读请求write: 298457 // 这是说在压测期间执行了29万多次的写请求other: 325436 // 这是说在压测期间执行了30万多次的其他请求total: 2103977 // 这是说一共执行了210万多次的请求// 这是说一共执行了10万多个事务&＃xff0c;每秒执行350多个事务transactions: 105180( 350.6 per sec. ) // 这是说一共执行了210万多次的请求&＃xff0c;每秒执行7000&＃43;请求queries: 2103977 ( 7013.26 per sec. )ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)// 下面就是说&＃xff0c;一共执行了300s的压测&＃xff0c;执行了10万&＃43;的事务
General staticstics: total time: 300.0052stotal number of events: 105180
Latency (ms):min: 4.32 // 请求中延迟最小的是4.32msavg: 13.42 // 所有请求平均延迟是13.42msmax: 45.56 // 延迟最大的请求是45.56ms95th percentile: 21.33 // 95%的请求延迟都在21.33ms以内


1.5.4 测试事务性能

sysbench --test&＃61;oltp --mysql-table-engine&＃61;innodb \
--mysql-user&＃61;root --db-driver&＃61;mysql --mysql-db&＃61;test \
--oltp-table-size&＃61;3000 --oltp-table-name&＃61;t1 \
--mysql-socket&＃61;/var/lib/mysql/mysql.sock prepare


1.5.5 测试事务实例

sysbench --test&＃61;oltp --mysql-table-engine&＃61;innodb \
--mysql-user&＃61;root --db-driver&＃61;mysql --mysql-db&＃61;test \
--oltp-table-size&＃61;3000 --oltp-table-name&＃61;t1 \
--mysql-socket&＃61;/var/lib/mysql/mysql.sock run[num-threads&＃61;N] [max-time&＃61;N]


1.6 tpcc测试

1.6.1 创建数据库/表

# mysql -h 192.168.1.201 -uroot -p -e &＃39;create database tpcc1000&＃39;
# mysql -h 192.168.1.201 -uroot -p tpcc1000
# mysql -h 192.168.1.201 -uroot -p tpcc1000


1.6.2 tpcc_load装载数据

命令格式:

tpcc_load [server] [DB] [user] [pass] [warehouse]

./tpcc_load 192.168.2.201 tpcc5 root "" 5
tpcc5库下创建5个仓库(仓库越多数据量越大)


#注:普通机器约需要10分钟的时间

1.6.3 tpcc模拟订单事务

格式:

tpcc_start -h server_host -P port -d database_name -u user -p password -w warehouses -c connections -r warmup_time -l running_time

-w 仓库 -c 连接数 -r预热时间 -l 测试时间


./tpcc_start -h 192.168.1.201 -d tpcc1000 -u root -p &＃39;&＃39; -w 5 -c 5 -r 30 -l 30


注&＃xff1a;5线程测试5个仓库,30秒预热,30秒测试

1.7 mysql_status观察

1.7.1 测试场景&＃xff1a;

总数据3W以上,50个并发,每秒请求500-1000次&＃xff0c;请求结果缓存与memcache,生命周期为5分钟&＃xff0c;观察mysql连接数,每秒请求数的周期变化

1.7.2 mysql status观察脚本

#!/bin/bash
while true
do
mysqladmin -h192.168.1.201 -uroot ext |awk &＃39; /Queries/ {q&＃61;$4}
/Threads_connected/{tc&＃61;$4}
/Threads_running/{tr&＃61;$4}
END{printf "%3d %s %s\n",q,tc,tr}
&＃39; >> num.txt
sleep 1
done #awk 计算每秒查询数
awk &＃39;{q&＃61;$1-last;last&＃61;$1}{printf("%d\t%d\t%d\n",q,$2,$3)}&＃39; num.txt > num2.txt


1.7.3 模拟缓存周期失效状态图

1.7.4 模拟缓存失效时的状态变化

1.7.5 观察mysql进程状态

mysql -h 192.168.1.201 -u root -e &＃39;show processlist\G&＃39;|grep State:|sort|uniq
-c|sort -rn
5 State: Sending data
2 State: statistics
2 State: NULL
1 State: Updating
1 State: update


1.7.6 值得注意的mysql进程状态

converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘
create tmp table 创建临时表(如group时储存中间结果)
Copying to tmp table on disk 把内存临时表复制到磁盘
locked 被其他查询锁住
logging slow query 记录慢查询
#注:把临时表内存变小,重现前


什么情况下产生临时表?

1: group by 的列和order by 的列不同时, 2表边查时,取A表的内容,group/order by另外表的列
2: distinct 和 order by 一起使用时
3: 开启了 SQL_SMALL_RESULT 选项

什么情况下临时表写到磁盘上?

1. 取出的列含有text/blob类型时 —内存表储存不了text/blob类型
2. 在group by 或distinct的列中存在>512字节的string列
3. select 中含有>512字节的string列,同时又使用了union或union all语句

如果服务器频繁出现converting HEAP to MyISAM说明:

1. sql有问题,取出的结果或中间结果过大,内存临时表放不下
2. 服务器配置的临时表内存参数过小. [ tmp_table_size | max_heap_table_size ]

1.7.7 开启慢查询

show variable;
&＃43;---------------------&＃43;-------------------------------&＃43;
| Variable_name | Value |
&＃43;---------------------&＃43;-------------------------------&＃43;
| long_query_time | 0.001000|
| log_slow_queries | ON |
| slow_query_log | ON |
| slow_query_log_file |/var/run/mysqld/mysqld-slow.log|
&＃43;---------------------&＃43;-------------------------------&＃43;


1.7.8 profile分析语句

1. 查看 profile 状态

   mysql> show variables like &＃39;profiling&＃39;;
&＃43;---------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;---------------&＃43;-------&＃43;
| profiling | OFF |
&＃43;---------------&＃43;-------&＃43;


2. 打开 profile

   mysql> set profiling&＃61;on;
Query OK, 0 rows affected (0.00 sec)


3. profiles 查看分析列表

   mysql> show profiles;
&＃43;----------&＃43;------------&＃43;-----------------------------&＃43;
| Query_ID | Duration | Query |
&＃43;----------&＃43;------------&＃43;-----------------------------&＃43;
| 1 | 0.00073300 | SELECT DATABASE() |
| 2 | 0.00734900 | select * from dict limit 1 |
&＃43;----------&＃43;------------&＃43;-----------------------------&＃43;
2 rows in set (0.00 sec)


4. 查看单条语句执行过程

   mysql> show profile for query 6;&＃43;--------------------&＃43;----------&＃43;| Status | Duration |&＃43;--------------------&＃43;----------&＃43;| starting | 0.000052 || Opening tables | 0.000009 || System lock | 0.000003 || Table lock | 0.000006 || init | 0.000016 |... 省略...| freeing items | 0.000029 || logging slow query | 0.000002 || cleaning up | 0.000019 |&＃43;--------------------&＃43;----------&＃43;15 rows in set (0.00 sec)


   请重现create tmp table 等值得注意的状态
   http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
   http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html

提高插入的方法

当mysql大批量插入数据的时候就会变的非常慢, mysql提高insert into 插入速度的方法有三种:

第一种插入提速方法:
如果数据库中的数据已经很多(几百万条), 那么可以 加大mysql配置中的 bulk_insert_buffer_size&＃xff0c;这个参数默认为8M

bulk_insert_buffer_size&＃61;100M
第二种mysql插入提速方法:
改写所有 insert into 语句为 insert delayed into

这个insert delayed不同之处在于&＃xff1a;立即返回结果&＃xff0c;后台进行处理插入。

第三个方法: 一次插入多条数据:
insert中插入多条数据&＃xff0c;举例:

insert into table values(‘11’,‘11’),(‘22’,‘22’),(‘33’,‘33’)…;