MySQL性能分析工具profiling

男娘i 2022-05-23 12:44 161阅读 0赞

**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;

\+----------------------+----------+----------+------------+--------------+---------------+-------+

\+----------------------+----------+----------+------------+--------------+---------------+-------+

|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;

\+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+------------------+--------------+-------------+

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