《高性能MySQL》——服务器性能分析

性能优化简介

第一个原则:完成某件任务所需要的时间度量,性能即响应时间。
提升每秒查询量(吞吐量优化)和降低响应时间是
第二个原则:无法测量就无法有效优化。所以90%的精力放到测量响应时间。
避免不合适的测量:错误的时间启动和停止测量;测量是聚合后的信息,而不是目标活动本身。

通过性能剖析进行优化

性能剖析是测量和分析时间花费在哪里的主要方法。
通常两个步骤:测试任务所花费的时间;对结果进行统计和排序,将重要任务放到前面。
基于执行时间的分析:什么任务的执行时间最长
执行等待的分析:什么地方被堵塞的最长
performance schema

理解性能剖析

值得优化的查询:优化总响应时间不超过5%的查询是无意义的,优化的成本大于收益则停止优化;
异常情况:某些任务执行很少,但是每次都很慢,影响用户体验则需要优化;
未知的未知:任务总时间和实际测量到的时间之差,如果差很大则要特别注意;
被掩盖的细节:不要只相信平均值,可以利用直方图、百分比等。

进行性能剖析

除了数据库导致的性能问题,还有:
> 外部资源,比如调用了外部的web服务或搜索引擎;
> 应该需要处理大量数据 尤其是IO类的;
> 昂贵的循环:比如正则表达式;
> 低效的算法:比如暴力搜索查找列表。

New Relic、xhprof、Ifp

剖析服务器负载

慢查询:开启对性能影响几乎可以忽略,分析利器。
自顶向下分析,否则可能导致逆优化。使用pt-query-digest分析慢查询日志。

wget percona.com/get/pt-query-digest

chmod u+x pt-query-digest

./pt-query-digest –limit=3 /var/log/mysql/mysql-slow.log

Query 1: 0.31 QPS, 0.00x concurrency, ID 0x3C255F3173ADB148 at byte 22131719

This item is included in the report because it matches –limit.

Scores: V/M = 1.79

Time range: 2015-08-24 06:25:13 to 14:36:22

Attribute pct total min max avg 95% stddev median

============ === ======= ======= ======= ======= ======= ======= =======

Count 15 9124

Exec time 17 46s 55us 7s 5ms 839us 95ms 301us

Lock time 90 33s 20us 2s 4ms 73us 62ms 31us

Rows sent 0 0 0 0 0 0 0 0

Rows examine 9 7.09M 0 2.28k 814.73 1.96k 628.65 592.07

Query size 6 873.20k 98 98 98 98 0 98

String:

Databases *

Hosts localhost

Users *

Query_time distribution

1us

10us

100us

1ms

10ms

100ms

1s

10s+

Tables

SHOW TABLE STATUS FROM shandalu LIKE ‘sdl_arccache’\G

SHOW CREATE TABLE shandalu.sdl_arccache\G

Delete From s_arccache where md5hash=’50fe4ad9614673ae4484ad0d2d419847’ or uptime < 1427438122\G

Converted for EXPLAIN

EXPLAIN /!50100 PARTITIONS/

select * from s_arccache where md5hash=’50fe4ad9614673ae4484ad0d2d419847’ or uptime < 1427438122\G

剖析单条查询

show profile

1

show status

在此观察整体的全局计算器粗略判断。

慢查询日志

 

诊断间歇性问题

慢查询日志有调sql怎么执行测试都没有问题,这是为什么呢?
可能问题:
> 应用CURL访问外币的慢的服务器
> memcached条目过期
> DNS偶尔查询超时
> 互斥锁争用造成短暂停顿
> 并发超过某个阙值,InnoDB的扩展性导致优化时间增加

单条查询问题OR服务器问题
方法一:show global status,每秒执行一次,监控其变化,生成图标,方便发现趋势变化;
方法二:show processlist,不停捕获线程状态,比如:freeing items locked等;
方法三:使用查询日志。

捕获诊断数据

诊断触发器:选择合适的阙值;
收集什么:系统状态和利用率信息、mysql状态信息和tcpdump流量包;
解释结果:寻找排查问题。

诊断案例

监控线程数,分析过程:
> 大部分是索引扫描或范围扫描,很少有全表扫描或表关联情况;
> 每秒大约有20-100次排序,排序行大约1000-1200行;
> 每秒12-90个临时表,3-5个磁盘临时表;
> 没有表锁或查询缓存问题;
> show innodb status,大多数在队列等待;
> IO平均等待时间和队列长度都非常高。

其他剖析工具

user_statistics表
strace