MySQL服务器功能_高性能MySQL-服务器性能剖析

性能优化简介

原则：性能即响应时间

第二原则：无法测量就无法有效的优化

通过性能剖析进行优化

测量任务所花费的时间

对结果进行统计和排序，将重要的任务排到前面

两种类型：

基于执行时间的分析

基于等待的分析

理解性能剖析

值得优化的查询

异常情况

未知的未知

被掩藏的细节

对应用程序进行性能剖析

外部资源

需要处理的大量的数据

循环中的昂贵操作(滥用正则表达式)

低效算法

剖析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)