性能优化简介
第一个原则:完成某件任务所需要的时间度量,性能即响应时间。
提升每秒查询量(吞吐量优化)和降低响应时间是
第二个原则:无法测量就无法有效优化。所以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
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