一、mysql调优
1.1 为什么要测试
高性能不是指"绝对性能"强悍,而是指业务能发挥出硬件的最大水平.性能强的服务器并非"设计",而是不断改进,提升短板、测试，量化找出短板的过程。只有会测试能把数据量化,才能进一步优化.
1.2 测试指标?
- 吞吐量：单位时间内的事务处理数,单位tps(每秒事务数)
- 响应时间：语句平均响应时间,一般截取某段时间内,95%范围内的平均时间
- 并发性：线程同时执行
- 可扩展性：资源增加,性能也能正比增加
1.3 用什么工具测试?
- mysqlslap
- sysbench
- 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个并发。
–create-schema 代表自定义测试库名 database。(没指定时，可能会遇到mysqlslap: 1049 Unknown database ‘mysqlslap’)
–engines -e 代表要测试的引擎，可以有多个，用分隔符隔开。
–iterations -i 代表要运行这些测试多少次。
–auto-generate-sql -a 代表用系统自己生成的SQL脚本来测试。
–auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
–number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
–debug-info 代表要额外输出CPU以及内存的相关信息。
结果显示：
Benchmark
Average number of seconds to run all queries: 1.176 seconds
Minimum number of seconds to run all queries: 0.512 seconds
Maximum number of seconds to run all queries: 4.017 seconds
Number of clients running queries: 10
Average number of queries per client: 10
结果中可以看到执行语句的平均耗时，最大耗时和最小耗时，并发线程数等等。
1.5 sysbench 测试
sysbench 安装：yum install sysbench -y ，等下载完成之后，在 /usr/share/sysbench/ 目录下会有一些软件自带等lua测试脚本文件，提供系统的压测脚本。
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 mysql压测
准备：数据库建测试库 test_db，同时创建好对应的测试账号test_user，密码也是test_user，用户有访问权限
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare
参数说明：
–db-driver=mysql ：基于mysql驱动连接mysql数据库，如oracle或sqlserver，那就是其他数据库驱动
–time=300：连续访问300秒
–threads=10：用10个线程模拟并发访问
–report-interval=1：每隔1秒输出一下压测情况
–mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user：连接到哪台机器的哪个端口上的MySQL库，用户名和密码是什么
–mysql-db=test_db --tables=20 --table_size=1000000：在test_db这个库里，构造20个测试表，每个测试表构造100万条测试数据，测试表名字类似于sbtest1，sbtest2
oltp_read_write：执行oltp数据库的读写测试
–db-ps-mode=disable：禁止ps模式
测试数据库的综合读写TPS，用oltp_read_write模式（命令最后是run而非prepare，即运行压测）：
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run
测试数据库的只读性能，用oltp_read_only模式（命令中oltp_read_write已变为oltp_read_only）：
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run
测试数据库的删除性能，用oltp_delete模式：
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run
测试数据库的更新索引字段的性能，用oltp_update_index模式：
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run
测试数据库的更新非索引字段的性能，用oltp_update_non_index模式：
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_non_index --db-ps-mode=disable run
测试数据库的插入性能，用oltp_insert模式：
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run
测试数据库的写入性能，用oltp_write_only模式：
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run
sysbench工具会根据你的指令构造出各种SQL语句去更新或查询20张测试表的数据，同时监测数据库的压测性能指标，最后完成压测后，可执行下面cleanup命令，清理数据。
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup
结果分析：
按照上面命令，每隔1秒都会输出一次压测报告，此时他每隔一秒会输出类似下面的东西：
[ 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输出的一段压测统计报告，然后是其他一些统计字段：
thds: 10，这个意思就是有10个线程在压测
tps: 380.99，这个意思就是每秒执行了380.99个事务
qps: 7610.20，这个意思就是每秒可以执行7610.20个请求
(r/w/o: 5132.99/1155.86/1321.35)，这个意思就是说，在每秒7610.20个请求中，有5132.99个请求是读请求，1155.86个请求是写请求，1321.35个请求是其他的请求，就是对QPS进行了拆解
lat (ms, 95%): 21.33，这个意思就是说，95%的请求的延迟都在21.33毫秒以下
err/s: 0.00 reconn/s: 0.00，这两个的意思就是说，每秒有0个请求是失败的，发生了0次网络重连
这个压测结果会根据每个人的机器的性能不同有很大差距，要是机器性能特别高，可开很多的并发线程去压测，如100个线程，此时可能会发现数据库每秒的TPS有上千个，如果机器性能很低，可能压测出TPS才二三十个，QPS才几百个，这都有可能。
另外在完成压测后，会显示一个总的压测报告，如下：
SQL statistics:
queries performed:
read: 1480084 // 这就是说在300s的压测期间执行了148万多次的读请求
write: 298457 // 这是说在压测期间执行了29万多次的写请求
other: 325436 // 这是说在压测期间执行了30万多次的其他请求
total: 2103977 // 这是说一共执行了210万多次的请求
// 这是说一共执行了10万多个事务，每秒执行350多个事务
transactions: 105180( 350.6 per sec. )
// 这是说一共执行了210万多次的请求，每秒执行7000+请求
queries: 2103977 ( 7013.26 per sec. )
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
// 下面就是说，一共执行了300s的压测，执行了10万+的事务
General staticstics:
total time: 300.0052s
total number of events: 105180
Latency (ms):
min: 4.32 // 请求中延迟最小的是4.32ms
avg: 13.42 // 所有请求平均延迟是13.42ms
max: 45.56 // 延迟最大的请求是45.56ms
95th percentile: 21.33 // 95%的请求延迟都在21.33ms以内
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 prepare
1.5.5 测试事务实例
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 选项
什么情况下临时表写到磁盘上?
- 取出的列含有text/blob类型时 —内存表储存不了text/blob类型
- 在group by 或distinct的列中存在>512字节的string列
- select 中含有>512字节的string列,同时又使用了union或union all语句
如果服务器频繁出现converting HEAP to MyISAM说明:
- sql有问题,取出的结果或中间结果过大,内存临时表放不下
- 服务器配置的临时表内存参数过小. [ 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分析语句
-
查看 profile 状态
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ -
打开 profile
mysql> set profiling=on; Query OK, 0 rows affected (0.00 sec) -
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) -
查看单条语句执行过程
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
提高插入的方法
当mysql大批量插入数据的时候就会变的非常慢, mysql提高insert into 插入速度的方法有三种:
第一种插入提速方法:
如果数据库中的数据已经很多(几百万条), 那么可以 加大mysql配置中的 bulk_insert_buffer_size，这个参数默认为8M
bulk_insert_buffer_size=100M
第二种mysql插入提速方法:
改写所有 insert into 语句为 insert delayed into
这个insert delayed不同之处在于：立即返回结果，后台进行处理插入。
第三个方法: 一次插入多条数据:
insert中插入多条数据，举例:
insert into table values(‘11’,‘11’),(‘22’,‘22’),(‘33’,‘33’)…;
235
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
