MySQL1: 如何剖析性能

最近正好在阅读《高性能MySQL》,同时在公司做一次分享会,在这次分享会中我将会从几个方面开展

  1. 如何剖析性能
  2. 正确使用数据类型
  3. 如何创建正确的索引
  4. 优化查询

一、为什么需要性能优化

衡量性能一个重要原则:完成某件任务所需要的时间度量。性能即响应时间。对于数据库服务器,我们关注的是它查询或者其他操作语句(SELECT, UPDATE, INSERT)的响应时间。其次,我们有个误区,认为mysql占用CPU越低越好,这个其实不然,如果在有效的查询中,mysql能够很好的利用CPU资源,即时在100%的情况下,也能非常块的响应,那么我们需要考虑另外一方面的优化。另外,我们只把性能优化看作提升每秒查询量(即吞吐量),对查询的优化可以让服务器每秒执行更多的查询,因为每条查询所执行的时间更加的短了。

我们需要做优化,首先应该进行对数据有个精准的测量,要知道为什么会响应如此之慢。所以本文将具体探讨如何测量以及数据优化。

二、对应用程序进行性能剖析

我们不能只针对mysql服务器进行剖析,在很多情况下应用导致的性能问题也不少,其性能瓶颈可能有很多因素:

  • 外部资源,比如调用了其他web服务
  • 应用需要处理大量的数据,比如分析二进制数据
  • 在循环里执行昂贵的操作。比如滥用正则表达式
  • 使用了低效的算法

分析PHP性能的工具非常多,比如facebook开发的xhprof(https://github.com/facebook/xhprof),xdebug。

三、剖析MySQL查询

分析服务端的负载是很有价值的,因为在服务端可以有效地审计效率低下的查询。定位和优化“坏”的查询能够显著的提升应用性能。
第一种方法,慢查询日志最初只捕捉比较“慢”的查询,在5.1版本之后,做了增强,现在只要设置long_query_time为0就能捕获所有的查询。
第二种方法,通过TCP抓包,然后根据MySQL的客户端/服务端通信协议进行分析,在linux下可以使用tcpdump,windows下可以使用wiresharke。
在分析单条查询,我们可以借助系统自身的SHOW PROFILE、SHOW STATUS两种方式。

1. SHOW PROFILE

这命令是在5.1时候引用进来的,而且默认是禁用的,我们可以通过修改服务器变量启用它。

mysql>SET profiling = 1;

设置完毕后,将会检测其耗费的时间和其他一些查询执行状态变更相关数据。 当一条查询提交给服务器时,服务端将会建立一张临时表,将所有信息全部记录进去。

mysql>select * from pre_forum_post LIMIT 0, 3000;
3000 rows in set (0.16 sec)

然后使用SHOW PROFILES查看有什么结果。

mysql>SHOW PROFILES;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.01831450 | select * from pre_forum_post LIMIT 0, 3000 |
+----------+------------+--------------------------------------------+
1 rows in set, 1 warning (0.00 sec)

要具体看一条数据我们可以这样。

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000068 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000037 |
| init                 | 0.000031 |
| System lock          | 0.000015 |
| optimizing           | 0.000007 |
| statistics           | 0.000015 |
| preparing            | 0.000011 |
| executing            | 0.000004 |
| Sending data         | 0.018052 |
| end                  | 0.000017 |
| query end            | 0.000009 |
| closing tables       | 0.000013 |
| freeing items        | 0.000014 |
| cleaning up          | 0.000015 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

EXPLAIN

mysql> EXPLAIN select * from pre_forum_post LIMIT 0, 3000;
+----+-------------+----------------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows     | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+----------+-------+
|  1 | SIMPLE      | pre_forum_post | ALL  | NULL          | NULL | NULL    | NULL | 36873047 | NULL  |
+----+-------------+----------------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from pre_forum_post as a LEFT JOIN pre_forum_thread as b ON a.tid = b.tid GROUP BY a.author ORDER BY b.tid;
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows     | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+---------------------------------+
|  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL         | 36873047 | Using temporary; Using filesort |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 3       | plu_dx.a.tid |        1 | NULL                            |
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+---------------------------------+
2 rows in set (0.02 sec)

四、诊断间隙性的问题

间隙性处问题是很难诊断的,它可能因为:

  1. 应用服务器与MySQL服务器出现短暂的网络延迟
  2. memcached或者redis中的数据过期,导致这些请求全部需要从MySQL中获取
  3. DNS查询偶尔会有超时
  4. 互斥锁争用,或者内部删除查询缓存的算法效率太低
  5. 并发超过一定的阀值

是因为查询所导致的问题还是服务器?我们可以使用SHOW GLOBAL STATUS、SHOW PROCESSLIST、查询日志等等手段。

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

This site uses Akismet to reduce spam. Learn how your comment data is processed.