性能优化简介
原则:性能即响应时间
第二原则:无法测量就无法有效的优化
通过性能剖析进行优化
测量任务所花费的时间
对结果进行统计和排序,将重要的任务排到前面
两种类型:
基于执行时间的分析
基于等待的分析
理解性能剖析
值得优化的查询
异常情况
未知的未知
被掩藏的细节
对应用程序进行性能剖析
外部资源
需要处理的大量的数据
循环中的昂贵操作(滥用正则表达式)
低效算法
剖析MySQL查询
剖析服务器负载
考虑使用慢查询日志
捕获MySQL的查询到日志文件中
分析查询日志:使用pt-query-digest
brew install percona-toolkit
剖析单条查询
方法:SHOW STATUS、SHOW PROFILE、检查慢查询日志条目
SHOW PROFILE
SET profiling = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1
注意不可排序,实际关心哪个过程开销最大,在这里给出脚本
SET @query_id = 1;
SELECT STATE, SUM(DURATION) AS Total_R,
ROUND(
100 * SUM(DURATION) /
(SELECT SUM(DURATION)
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
), 2) AS Pct_R,
COUNT(*) AS Calls,
SUM(DURATION) / COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
GROUP BY STATE
ORDER BY Total_R DESC;
SHOW STATUS
无法提供基于时间的统计,只能显示比如读索引的频繁程度
FLUSH STATUS;
SELECT * FROM ;
SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%'
关注点:使用临时表,句柄计数器,临时文件,表计数器
使用慢查询日志
同样适用SHOW PROFILE会含有更多的信息
使用Performance Schema
查看官方文档去吧朋友,5.6之后才逐渐强大
使用性能剖析
写出尽可能的剖析报告来防止缓存命中
诊断间歇性问题
避免试错的诊断方式
从外部服务获取信息过慢
memcached重要条目过期,MySQL重新缓存
DNS查询超时
互斥锁争用,内部删除效率过低,导致服务短暂停顿
并发超过阈值,查询优化耗时
查询问题还是服务器问题
使用SHOW GLOBAL STATUS
#!/bin/sh
mysqladmin ext -i1 | awk '
/Queries/{q=$4-qp;qp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'
每秒查询数下跌,其他两个至少一个形成尖刺
使用SHOW PROCESSLIST
mysql -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c |sort -rn
查看不同的列,修改grep模式
MySQL版本较新,可以查询INFORMATION_SCHEMA中的PROCESSLIST
使用查询日志
开启慢查询并全局设置long_query_time = 0
捕获诊断的数据
一个可靠且实时的触发器
一个收集诊断数据的工具
诊断触发器
mysql -e 'SHOW PROCESSLIST\G' | grep -c "State:freeing items"
需要收集的数据
系统状态、CPU利用率、磁盘使用率和可用空间、ps的输出采样、内存利用率以及尽可能更多。
一些工具:oprofile、strace(生产环境有风险)、tcpdump,pt-collect。
等待分析:GDB堆栈跟踪(启动gdb,附加到mysqld进程)工具pt-pmp
解释结果数据:看经验(很明显我经验不够
其他剖析工具
使用user_staistics表
SHOW TABLES FROM INFORMATION_SCHEMA LIKE '%_STATISTICS'
可以查找使用最多或最少的表和索引
可以查找从未使用过的索引
可以查看复制用户的connected_time,busy_time。
ps:mysql5.6之后的performance schema添加了类似功能
使用strace
strace -cfp $(pidof mysqld)