MySQL性能分析工具profiling

MySQL性能分析工具profiling

MySQL数据库中，可以通过配置profiling参数来启用SQL性能分析，找出当前SQL瓶颈，进而优化、调整sql语句。

profiling参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，IPC，CPU，Memory等等。

用法：

mysql> help profile

Name:'SHOW PROFILE'

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

Profiling变量默认值为0（off），可以通过设置profiling为0或ON开启

mysql>select @@profiling;

+-------------+

|@@profiling |

+-------------+

|          0 |

+-------------+

1 row inset (0.03 sec)

mysql>show variables like '%profil%';

+------------------------+-------+

|Variable_name         | Value |

+------------------------+-------+

|profiling             | OFF  | 

|profiling_history_size | 15   |

+------------------------+-------+

2 rows inset (0.00 sec)

profiling：设置是否开启SQL性能剖析

profiling_history_size：设置是SQL性能剖析缓存的记录数

mysql>set profiling=1;

Query OK,0 rows affected (0.03 sec)

mysql>show variables like '%profil%';

+------------------------+-------+

|Variable_name         | Value |

+------------------------+-------+

|profiling             | ON   |

|profiling_history_size | 15   |

+------------------------+-------+

2 rows inset (0.00 sec)

mysql>show variables like 'profiling_history_size';

+------------------------+-------+

|Variable_name         | Value |

+------------------------+-------+

|profiling_history_size | 15   |

+------------------------+-------+

1 row inset (0.00 sec)

show profiles:返回一个列表,显示最近发送到服务器上执行的语句的资源使用情况.

显示的记录数由变量“profiling_history_size”控制,默认15条，最大值为100，可以手动设置该参数值。

mysql>set profiling_history_size = 30;

Query OK,0 rows affected (0.00 sec)

mysql>show variables like '%profil%';

+------------------------+-------+

|Variable_name         | Value |

+------------------------+-------+

|profiling             | ON   |

|profiling_history_size | 30   |

+------------------------+-------+

2 rows inset (0.00 sec)

 

选项注释：

type:

   ALL ：显示所有信息

  |BLOCK IO ：块设备IO输入输出次数

  |CONTEXT SWITCHES：上下文切换相关开销

  |CPU：用户和系统的CPU使用情况

  |IPC：显示发送和接收消息的相关消耗

  |MEMORY：内存消耗情况（该版本is not currently implemented）

  |PAGE FAULTS：显示主要和次要页面故障相关的开销

  |SOURCE：显示和Source_function,Source_file,Source_line相关的开销信息

  |SWAPS：显示交换次数相关的开销

Profilingis enabled per session. When a session ends, itsprofiling

information is lost. 

注意：profiling被应用在每一个会话中，当前会话关闭后，profiling统计的信息将丢失。

用法示例：

mysql>use mydb

mysql>drop table if exists t4;

Query OK,0 rows affected, 1 warning (0.00 sec)

mysql>create table cid(id int);

Query OK,0 rows affected (0.05 sec)

mysql>show profiles;

+----------+------------+----------------------------------------------+

| Query_ID| Duration   |Query                                       |

+----------+------------+----------------------------------------------+

|       1 | 0.00061900 | show variables like '%profil%'              |

|       2 | 0.00061800 | show variables like 'profiling_history_size'|

|       3 | 0.00010600 | set profiling_history_size = 30             |

|       4 | 0.00078200 | show variables like '%profil%'              |

|       5 | 0.00018800 | SELECT DATABASE()                           |

|       6 | 0.00050400 | show databases                              |

|       7 | 0.00032200 | show tables                                 |

|       8 | 0.00027300 | drop table if exists t4                     |

|       9 | 0.04367500 | create table cid(id int)                    |

+----------+------------+----------------------------------------------+

9 rows inset (0.00 sec)

mysql>show profile; -->默认会打开上一个语句的开销信息

+----------------------+----------+

|Status              | Duration |

+----------------------+----------+

|starting            | 0.000074 |

| checkingpermissions | 0.000083 |

| creatingtable      | 0.043427 |

| Aftercreate        | 0.000014 |

| queryend           | 0.000003 |

| freeingitems       | 0.000068 |

| loggingslow query   |0.000004 |

| cleaningup         | 0.000002 |

+----------------------+----------+

8 rows inset (0.02 sec)

mysql>show profile for query 9;    -->获取指定查询的开销 

+----------------------+----------+

|Status              | Duration |

+----------------------+----------+

|starting            | 0.000074 |

| checkingpermissions | 0.000083 |

| creatingtable      | 0.043427 |

| Aftercreate        | 0.000014 |

| queryend           | 0.000003 |

| freeingitems       | 0.000068 |

| loggingslow query   |0.000004 |

| cleaningup         | 0.000002 |

+----------------------+----------+

8 rows inset (0.00 sec)

-->查看特定部分的开销，如CPU,SWAPS,BLOCK IO的开销

mysql>show profile CPU,SWAPS,BLOCK IO for query 9;

+----------------------+----------+----------+------------+--------------+---------------+-------+

|Status              |Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |Swaps |

+----------------------+----------+----------+------------+--------------+---------------+-------+

|starting            | 0.000074 | 0.000000 |  0.000000 |           0|            0 |    0 |

| checkingpermissions | 0.000083 | 0.000000 |  0.000000 |           0 |            0 |    0 |

| creatingtable      | 0.043427 | 0.009998 |  0.017997 |           0 |          256 |    0 |

| Aftercreate        | 0.000014 | 0.000000 |  0.000000 |           0 |            0 |    0 |

| queryend           | 0.000003 | 0.000000 |  0.000000 |           0 |            0 |    0 |

| freeingitems       | 0.000068 | 0.000000 |  0.000000 |           0 |            0 |    0 |

| loggingslow query   |0.000004 | 0.000000 |  0.000000 |           0 |            0 |    0 |

| cleaningup         | 0.000002 | 0.000000 |  0.000000 |           0 |            0 |    0 |

+----------------------+----------+----------+------------+--------------+---------------+-------+

8 rows inset (0.00 sec)

mysql>show profile ALL for query 9;

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+------------------+--------------+-------------+

|Status              | Duration | CPU_user | CPU_system | Context_voluntary |Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent| Messages_received | Page_faults_major | Page_faults_minor | Swaps| Source_function  |Source_file | Source_line |

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+------------------+--------------+-------------+

|starting            | 0.000074 | 0.000000 |  0.000000 |                0 |                  0 |           0 |            0 |            0 |                0 |                0 |                3 |    0 | NULL            |NULL        |       NULL |

| checkingpermissions | 0.000083 | 0.000000 |  0.000000 |                0 |                  0 |           0 |            0 |            0 |                0 |                0 |                4 |    0 | unknown function | sql_parse.cc |       5369 |

| creatingtable      | 0.043427 | 0.009998 |  0.017997 |               17 |                  0 |           0 |          256 |            0 |                0 |                0 |               26 |    0 |unknown function | sql_table.cc |       3975 |

| Aftercreate        | 0.000014 | 0.000000 |  0.000000 |                0 |                  0 |           0 |            0 |            0 |                0 |                0 |                0|    0 | unknown function | sql_table.cc |       4062 |

| queryend           | 0.000003 | 0.000000 |  0.000000 |                0 |                  0 |           0 |            0 |            0 |                0 |                0 |                0|    0 | unknown function | sql_parse.cc |       5133 |

| freeingitems       | 0.000068 | 0.000000 |  0.000000 |                0 |                  0 |           0 |            0 |            0 |                0 |                0|                0 |    0 | unknown function | sql_parse.cc |       6157 |

| loggingslow query   |0.000004 | 0.000000 |  0.000000 |                0 |                  0 |           0 |            0 |            0 |                0 |                0|                0 |    0 | unknown function | sql_parse.cc |       1745 |

| cleaningup         | 0.000002 | 0.000000 |  0.000000 |                0 |                  0 |           0 |            0 |            0 |                0 |                0 |                0 |    0 | unknown function | sql_parse.cc |       1713 |

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+------------------+--------------+-------------+

8 rows inset (0.00 sec)