mysql query profile_MySQL Query Profile 简单使用

水深无声 2023-01-11 13:22 169阅读 0赞

MySQL Query Profile MySQL 5.0.37 以上开始支持 MySQL Query Profiler, 可以查询到此 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 System lock, Table lock 花多少时间等等.

详细可以参见官方文档：Using the New MySQL Query Profiler

启动

mysql> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

测试查询

mysql> select count(\*) from client where broker\_id=2;

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

| count(\*) |

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

|      200 |

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

1 row in set (0.00 sec)

查看profiles

mysql> show profiles;

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

| Query\_ID | Duration   | Query                                         |

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

|        0 | 0.00007300 | set profiling=1                               |

|        1 | 0.00044700 | select count(\*) from client where broker\_id=2 |

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

2 rows in set (0.00 sec)

查看单条profile

mysql> show profile for query 1;

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

| Status             | Duration   |

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

| (initialization)   | 0.00006300 |

| Opening tables     | 0.00001400 |

| System lock        | 0.00000600 |

| Table lock         | 0.00001000 |

| init               | 0.00002200 |

| optimizing         | 0.00001100 |

| statistics         | 0.00009300 |

| preparing          | 0.00001700 |

| executing          | 0.00000700 |

| Sending data       | 0.00016800 |

| end                | 0.00000700 |

| query end          | 0.00000500 |

| freeing items      | 0.00001200 |

| closing tables     | 0.00000800 |

| logging slow query | 0.00000400 |

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

15 rows in set (0.00 sec)

mysql> alter table t engine=myisam;

Query OK, 112050 rows affected (0.64 sec)

Records: 112050  Duplicates: 0  Warnings: 0

mysql> show profiles;

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

| Query\_ID | Duration   | Query                                         |

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

|        0 | 0.00007300 | set profiling=1                               |

|        1 | 0.00044700 | select count(\*) from client where broker\_id=2 |

|        2 | 0.00003400 | set profiling=0                               |

|        3 | 0.00007400 | set profiling=1                               |

|        4 | 0.63789700 | alter table t engine=myisam                   |

|        5 | 0.00004000 | set profiling=0                               |

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

6 rows in set (0.00 sec)

mysql> show profile for query 4;

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

| Status               | Duration   |

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

| (initialization)     | 0.00002900 |

| checking permissions | 0.00000800 |

| init                 | 0.00004000 |

| Opening table        | 0.00009400 |

| System lock          | 0.00000500 |

| Table lock           | 0.00000700 |

| setup                | 0.00004200 |

| creating table       | 0.00195800 |

| After create         | 0.00010900 |

| copy to tmp table    | 0.52264500 |

| rename result table  | 0.11289400 |

| end                  | 0.00004600 |

| query end            | 0.00000700 |

| freeing items        | 0.00001300 |

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

14 rows in set (0.00 sec)

查看cpu资源等信息

mysql> show profile cpu for query 4;

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

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

| (initialization)     | 0.00002900 | 0.00000000 | 0.00000000 |

| checking permissions | 0.00000800 | 0.00000000 | 0.00000000 |

| init                 | 0.00004000 | 0.00000000 | 0.00000000 |

| Opening table        | 0.00009400 | 0.00100000 | 0.00000000 |

| System lock          | 0.00000500 | 0.00000000 | 0.00000000 |

| Table lock           | 0.00000700 | 0.00000000 | 0.00000000 |

| setup                | 0.00004200 | 0.00000000 | 0.00000000 |

| creating table       | 0.00195800 | 0.00000000 | 0.00100000 |

| After create         | 0.00010900 | 0.00000000 | 0.00000000 |

| copy to tmp table    | 0.52264500 | 0.55591600 | 0.04199300 |

| rename result table  | 0.11289400 | 0.00199900 | 0.00000000 |

| end                  | 0.00004600 | 0.00000000 | 0.00000000 |

| query end            | 0.00000700 | 0.00000000 | 0.00000000 |

| freeing items        | 0.00001300 | 0.00000000 | 0.00000000 |

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

14 rows in set (0.00 sec)

其他属性列表

\* ALL - displays all information

\* BLOCK IO - displays counts for block input and output operations

\* CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches

\* IPC - displays counts for messages sent and received

\* MEMORY - is not currently implemented

\* PAGE FAULTS - displays counts for major and minor page faults

\* SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs

\* SWAPS - displays swap counts

设定profiling保存size

mysql> show variables where variable\_name='profiling\_history\_size'; \# 默认15条

关闭

mysql> set profiling=0;

mysql query profile_MySQL Query Profile 简单使用

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql_query优化之explain&Profiling详解

相关 mysql query profile_MySQL Query Profile 简单使用

相关 Profiling Query Compilation Time with GPORCA

相关 MySQL Query Optimizer

相关 mysql query profile_MySQL Query Profile

相关 MySQL: Query Parsing （MySQL query解析）

相关 MYSQL自带工具Query Profiler使用

相关 mysql使用Profiling分析query语句，sql语句性能优化

相关 dojo 六使用query dojo/query

相关 react-query query监听器