MySQL profiling 查找Query瓶颈

thfff

                                今天接触到MySQL系统调优的一个参数：profiling，下面就详细的说一说这个参数是干什么的，怎么用，
        一，干什么的
        MySQL5.0.37版本以上支持PROFILING调试功能，让您可以了解SQL语句消耗资源的详细信息。因为它需要调用系统的getrusage()函数，所以只是在Linux/Unix类平台上才能使用，而不能在Windows平台上使用。而且，PROFILING是针对处理进程(process)而不是线程(thread)的，服务器上的其他应用，可能会影响您的调试结果，因此，这个工具适合开发过程中的调试，如果要在生产环境中调试使用，则要注意它的局限性，而且只对本次回话有效

        二，怎么用
            首先查看帮助
       
mysql> help profiles;
Name: 'SHOW PROFILES'
Description:
Syntax:
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
        type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
        The SHOW PROFILES and SHOW PROFILE statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.
        Profiling is controlled by the profiling session variable, which has a
default value of 0 (OFF). Profiling is enabled by setting profiling to
1 or ON:

        查看系统对该参数的设置
        mysql> show variables like '%profili%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.01 sec)
        mysql>
        可以看到默认是OFF状态，存储的大小为15条Query 然后开启该参数
        mysql> set profiling=1;
Query OK, 0 rows affected (0.01 sec)
        mysql> show variables like '%profili%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)
        mysql>
        开始执行需要测试的SQL语句，MySQL数据库将会记录想关的调试信息
        mysql> select * from mysql.user;
        mysql> show profile; 可以看到刚才执行的这条Query的资源消耗
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000694 |
| checking permissions | 0.000044 |
| Opening tables       | 0.000223 |
| System lock          | 0.000127 |
| init                 | 0.000238 |
| optimizing           | 0.000035 |
| statistics           | 0.000045 |
| preparing            | 0.000022 |
| executing            | 0.000013 |
| Sending data         | 0.000418 |
| end                  | 0.000020 |
| query end            | 0.000008 |
| closing tables       | 0.000227 |
| freeing items        | 0.001550 |
| logging slow query   | 0.000012 |
| cleaning up          | 0.000017 |
+----------------------+----------+
16 rows in set (0.01 sec)
        mysql>

        也可以显示当前所有已经记录的PROFILES 例如
        mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00270600 | show variables like '%profili%' |
|        2 | 0.00369250 | select * from mysql.user        |
+----------+------------+---------------------------------+
2 rows in set (0.00 sec)
        mysql> 默认显示15条 由参数profiling_history_size决定
        当执行了多条SQL的时候 我想看看某一条的 可以通过Query_ID来看
        mysql> show profile for query 2
    -> ;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000694 |
| checking permissions | 0.000044 |
| Opening tables       | 0.000223 |
| System lock          | 0.000127 |
| init                 | 0.000238 |
| optimizing           | 0.000035 |
| statistics           | 0.000045 |
| preparing            | 0.000022 |
| executing            | 0.000013 |
| Sending data         | 0.000418 |
| end                  | 0.000020 |
| query end            | 0.000008 |
| closing tables       | 0.000227 |
| freeing items        | 0.001550 |
| logging slow query   | 0.000012 |
| cleaning up          | 0.000017 |
+----------------------+----------+
16 rows in set (0.01 sec)
        mysql>
         当然也可以查看更多的信息如CPU等等
        type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
        mysql> show profile CPU,SWAPS,BLOCK IO for query 2;
+----------------------+----------+----------+------------+--------------+---------------+-------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting             | 0.000694 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| checking permissions | 0.000044 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| Opening tables       | 0.000223 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| System lock          | 0.000127 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| init                 | 0.000238 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| optimizing           | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| statistics           | 0.000045 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| preparing            | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| executing            | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| Sending data         | 0.000418 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| end                  | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| query end            | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| closing tables       | 0.000227 | 0.002000 |   0.000000 |            0 |             0 |     0 |
| freeing items        | 0.001550 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| logging slow query   | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| cleaning up          | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |     0 |
+----------------------+----------+----------+------------+--------------+---------------+-------+
16 rows in set (0.00 sec)
        mysql>
        测试完成之以后，记得要关闭调试功能，以免影响数据库的正常使用：
        mysql> set profiling=0;