mysql优化之-测试工具

文章目录

• 一、mysql调优
• 1.1 为什么要测试
• 1.2 测试指标?
• 1.3 用什么工具测试?
• 1.4 mysqlslap测试
• 1.5 sysbench 测试
• 1.5.1 测试CPU性能
• 1.5.2 测试IO性能
• 1.5.3 测试事务性能
• 1.5.4 测试事务实例
• 1.6 tpcc测试
• 1.6.1 创建数据库/表
• 1.6.2 tpcc_load装载数据
• 1.6.3 tpcc模拟订单事务
• 1.7 mysql_status观察
• 1.7.1 测试场景：
• 1.7.2 mysql status观察脚本
• 1.7.3 模拟缓存周期失效状态图
• 1.7.4 模拟缓存失效时的状态变化
• 1.7.5 观察mysql进程状态
• 1.7.6 值得注意的mysql进程状态
• 1.7.7 开启慢查询
• 1.7.8 profile分析语句

一、mysql调优

1.1 为什么要测试

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

----想学抓药,先学号脉

1.2 测试指标?

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

1.3 用什么工具测试?

1. mysqlslap
2. sysbench
3. tpcc

1.4 mysqlslap测试

mysqlslap --options

mysqlslap -h 127.0.0.1 -uroot  --auto-generate-sql --concurrency 20 --iterations 1 --create-schema=big_data --query='select * from dict limit 1'

mysqlslap -h 127.0.0.1 -a  -c 100 --number-of-queries 1000 -i 10 -u root -p

–concurrency 代表并发数量，多个可以用逗号隔开concurrency=10,50,100, 并发连接线程数分别是10、50、100个并发。
–engines -e 代表要测试的引擎，可以有多个，用分隔符隔开。
–iterations -i 代表要运行这些测试多少次。
–auto-generate-sql -a 代表用系统自己生成的SQL脚本来测试。
–auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
–number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
–debug-info 代表要额外输出CPU以及内存的相关信息。

1.5 sysbench 测试

1.5.1 测试CPU性能

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

1.5.2 测试IO性能

sysbench --test=fileio --file-total-size=20G prepare
sysbench --test=fileio --file-total-size=20G --file-test-mode=rndrw run
sysbench --test=fileio --file-total-size=20G cleanup

seqwr： 顺序写入
seqrewq： 顺序重写
seqrd： 顺序读取
rndrd： 随机读取
rndwr： 随机写入
rndrw： 混合随机读写

1.5.3 测试事务性能

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

1.5.4 测试事务实例

 sysbench --test=oltp --mysql-table-engine=innodb \
--mysql-user=root --db-driver=mysql --mysql-db=test  \
--oltp-table-size=3000 --oltp-table-name=t1  \
--mysql-socket=/var/lib/mysql/mysql.sock run

[num-threads=N] [max-time=N]

1.6 tpcc测试

1.6.1 创建数据库/表

# mysql -h 192.168.1.201 -uroot -p -e 'create database tpcc1000'        
# mysql -h 192.168.1.201 -uroot -p tpcc1000 < create_table.sql  
# mysql -h 192.168.1.201 -uroot -p tpcc1000 < add_fkey_idx.sql

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 '' -w 5 -c 5 -r 30 -l 30

注：5线程测试5个仓库,30秒预热,30秒测试

1.7 mysql_status观察

1.7.1 测试场景：

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

1.7.2 mysql status观察脚本

#!/bin/bash
while true
do
mysqladmin -h192.168.1.201 -uroot ext |awk ' /Queries/ {q=$4}
/Threads_connected/{tc=$4}
/Threads_running/{tr=$4}
END{printf "%3d %s %s\n",q,tc,tr}
' >> num.txt
sleep 1
done      

#awk 计算每秒查询数
awk '{q=$1-last;last=$1}{printf("%d\t%d\t%d\n",q,$2,$3)}' num.txt > num2.txt                                                         

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

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

1.7.5 观察mysql进程状态

mysql -h 192.168.1.201 -u root -e 'show processlist\G'|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;
+---------------------+-------------------------------+
| Variable_name       | Value   |                    
+---------------------+-------------------------------+
| long_query_time     | 0.001000|
| log_slow_queries    | ON      |
| slow_query_log      | ON      |
| slow_query_log_file |/var/run/mysqld/mysqld-slow.log|
+---------------------+-------------------------------+

1.7.8 profile分析语句

1. 查看 profile 状态

   mysql> show variables like 'profiling';  
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | profiling     | OFF   |
   +---------------+-------+
   

2. 打开 profile

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

3. profiles 查看分析列表

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

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

    mysql> show profile for query 6;
    +--------------------+----------+
    | Status             | Duration |
    +--------------------+----------+
    | 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 |
    +--------------------+----------+
    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