MySQL性能的指标是指响应时间。对性能的剖析包括对应用程序的剖析和对MySQL查询的剖析。
剖析应用程序
一种比较好的对应用程序进行性能优化工具New Relic,在收藏中有安装步骤。该工具不仅对数据库,还能对整个系统进行监控,即使不能给出优化方案,也能定位问题所在。
剖析MySQL
对MySQL查询的剖析应自上而下进行,即剖析整个数据库服务器,定位到具体压力来源的查询,再单独剖析具体查询,分析哪些子任务是响应时间的主要消耗者。
对于数据库服务器,一般采用慢查询日志进行剖析,但由于某些原因如权限不足等,无法在服务器上记录查询,因此可以采用如下方法:
- 使用pt-query-digest的–processlist选项不断查看SHOW FULL PROCESSLIST的输出,问题是有可能无法捕捉所有的查询。
- 使用tcpdump抓去网络包数据,再用pt-query-digest的–type=tcpdump选项解析并分析查询网络包数据,解决了上一问题,并且精度较高。
对于单独查询进行剖析,首先使用SHOW PROFILE定位耗时多的操作,步骤如下:
- 设置会话级的查询剖析:set profiling = 1;
- 使用查询语句进行查询,该语句即是需要被剖析的对象。
- 查看剖析结果:show profiles;
- 查看详细的剖析结果:show profile for query 5; 5代表查询ID,来源于步骤3。
- 由于步骤4得到表的内容按照执行顺序排序,为了解那一步骤出现问题,需要编写SQL脚本格式化输出。如下例所示:
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_profiling
where query_id = @query_id
group by state
order by total_r desc;
然后,使用SHOW STATUS分析原因。注意show status和show global status的区别,前者在某些计数器上包含了后者。通过对show status的where选择获取所需的计数器,对其进行分析,但此分析大多数是推测性质的。在Percona Server中可以进一步通过show profile获取更加详细的信息。
诊断间歇性问题
间歇性问题如系统偶尔停顿或慢查询,很难诊断,也很难重现。一般步骤包括:
- 使用工具准确描述问题:show global status,show processlist,查询日志等。
- 使用工具捕获诊断数据:通过pt-stalk建立触发器的监控变量、阈值、检查频率等,然后调用pt-collect收集数据。系统中应提前安装好gdb(堆栈跟踪工具)、oprofile(服务器诊断工具)和MySQL的debuginfo包(符号信息)。
- 正确地解释结果数据:通过对上述各个工具收集到的数据,进行两种关注性地条理查找。两个重要的关注包括,主观上假设和猜测的关注,客观上基于合理性和基于可度量的的方式的关注。
坚持信念,工作系统是某种状态机,只要有足够的耐心和细心,逻辑清晰并坚持下去,通常就能得到想要的结果。