mysql性能瓶颈_MySQL性能瓶颈分析工具（profiling）

最新推荐文章于 2024-01-03 12:31:15 发布

42号车库

最新推荐文章于 2024-01-03 12:31:15 发布

阅读量427

点赞数

文章标签： mysql性能瓶颈

本文链接：https://blog.csdn.net/weixin_28808145/article/details/113167987

版权

一、介绍

1、分析SQL执行带来的开销是优化SQL的重要手段。

2、在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。

3、该参数可以在全局和session级别来设置。

4、对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。

5、该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。

二、有关profile的描述

show variables like 'version';

show variables like '%profil%';

mysql> show variables like '%profil%';

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

| Variable_name | Value |

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

| have_profiling | YES | --只读变量，用于控制是否由系统变量开启或禁用profiling

| profiling | OFF | --开启SQL语句剖析功能

| profiling_history_size | 15 | --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁

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

3 rows in set (0.00 sec)

--获取profile的帮助

root@localhost[sakila]> help profile;

Name: 'SHOW PROFILE'

Description:

Syntax:

SHOW PROFILE [type [, type] ... ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

type:

ALL --显示所有的开销信息

| BLOCK IO --显示块IO相关开销

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

| CPU --显示CPU相关开销信息

| IPC --显示发送和接收相关开销信息

| MEMORY --显示内存相关开销信息

| PAGE FAULTS --显示页面错误相关开销信息

| SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息

| SWAPS --显示交换次数相关开销的信息

2、开启porfiling

--启用session级别的profiling

root@localhost[sakila]> set profiling=1;

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

--验证修改后的结果

root@localhost[sakila]> show variables like '%profil%';

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

| Variable_name | Value |

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

| have_profiling | YES |

| profiling | ON |

| profiling_history_size | 15 |

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

--发布SQL查询

root@localhost[sakila]> select count(*) from customer;

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

| count(*) |

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

| 599 |

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

--查看当前session所有已产生的profile

root@localhost[sakila]> show profiles;

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

| Query_ID | Duration | Query |

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

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

| 2 | 0.00138150 | select count(*) from customer |

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

2 rows in set, 1 warning (0.01 sec)

--我们看到有2个warning，之前一个，现在一个

root@localhost[sakila]> show warnings; --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉

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

| Level | Code | Message |

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

| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |

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

3、获取SQL语句的开销信息

--可以直接使用show profile来查看上一条SQL语句的开销信息

--注，show profile之类的语句不会被profiling，即自身不会产生Profiling

--我们下面的这个show profile查看的是show warnings产生的相应开销

root@localhost[sakila]> show profile;

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

| Status | Duration |

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

| starting | 0.000141 |

| query end | 0.000058 |

| closing tables | 0.000014 |

| freeing items | 0.001802 |

| cleaning up | 0.000272 |

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

--如下面的查询show warnings被添加到profiles

root@localhost[sakila]> show profiles;

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

| Query_ID | Duration | Query |

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

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

| 2 | 0.00138150 | select count(*) from customer |

| 3 | 0.00228600 | show warnings |

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

--获取指定查询的开销

root@localhost[sakila]> show profile for query 2;

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

| Status | Duration |

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

| starting | 0.000148 |

| checking permissions | 0.000014 |

| Opening tables | 0.000047 |

| init | 0.000023 |

| System lock | 0.000035 |

| optimizing | 0.000012 |

| statistics | 0.000019 |

| preparing | 0.000014 |

| executing | 0.000006 |

| Sending data | 0.000990 |

| end | 0.000010 |

| query end | 0.000011 |

| closing tables | 0.000010 |

| freeing items | 0.000016 |

| cleaning up | 0.000029 |

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

--查看特定部分的开销，如下为CPU部分的开销

root@localhost[sakila]> show profile cpu for query 2 ;

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

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

| starting | 0.000148 | 0.000000 | 0.000000 |

| checking permissions | 0.000014 | 0.000000 | 0.000000 |

| Opening tables | 0.000047 | 0.000000 | 0.000000 |

| init | 0.000023 | 0.000000 | 0.000000 |

| System lock | 0.000035 | 0.000000 | 0.000000 |

| optimizing | 0.000012 | 0.000000 | 0.000000 |

| statistics | 0.000019 | 0.000000 | 0.000000 |

| preparing | 0.000014 | 0.000000 | 0.000000 |

| executing | 0.000006 | 0.000000 | 0.000000 |

| Sending data | 0.000990 | 0.001000 | 0.000000 |

| end | 0.000010 | 0.000000 | 0.000000 |

| query end | 0.000011 | 0.000000 | 0.000000 |

| closing tables | 0.000010 | 0.000000 | 0.000000 |

| freeing items | 0.000016 | 0.000000 | 0.000000 |

| cleaning up | 0.000029 | 0.000000 | 0.000000 |

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

--如下为MEMORY部分的开销

root@localhost[sakila]> show profile memory for query 2 ;

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

| Status | Duration |

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

| starting | 0.000148 |

| checking permissions | 0.000014 |

| Opening tables | 0.000047 |

| init | 0.000023 |

| System lock | 0.000035 |

| optimizing | 0.000012 |

| statistics | 0.000019 |

| preparing | 0.000014 |

| executing | 0.000006 |

| Sending data | 0.000990 |

| end | 0.000010 |

| query end | 0.000011 |

| closing tables | 0.000010 |

| freeing items | 0.000016 |

| cleaning up | 0.000029 |

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

--同时查看不同资源开销

root@localhost[sakila]> show profile block io,cpu for query 2;

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

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

| starting | 0.000148 | 0.000000 | 0.000000 | 0 | 0 |

| checking permissions | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |

| Opening tables | 0.000047 | 0.000000 | 0.000000 | 0 | 0 |

| init | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |

| System lock | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |

| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |

| statistics | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |

| preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |

| executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |

| Sending data | 0.000990 | 0.001000 | 0.000000 | 0 | 0 |

| end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |

| query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |

| closing tables | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |

| freeing items | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |

| cleaning up | 0.000029 | 0.000000 | 0.000000 | 0 | 0 |

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

--下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列

root@localhost[sakila]> set @query_id=2;

root@localhost[sakila]> SELECT STATE, SUM(DURATION) AS Total_R,

-> ROUND(

-> 100 * SUM(DURATION) /

-> (SELECT SUM(DURATION)

-> FROM INFORMATION_SCHEMA.PROFILING

-> WHERE QUERY_ID = @query_id

-> ), 2) AS Pct_R,

-> COUNT(*) AS Calls,

-> SUM(DURATION) / COUNT(*) AS "R/Call"

-> FROM INFORMATION_SCHEMA.PROFILING

-> WHERE QUERY_ID = @query_id

-> GROUP BY STATE

-> ORDER BY Total_R DESC;

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

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

| Sending data | 0.000990 | 71.53 | 1 | 0.0009900000 |--最大耗用时间部分为发送数据

| starting | 0.000148 | 10.69 | 1 | 0.0001480000 |

| Opening tables | 0.000047 | 3.40 | 1 | 0.0000470000 |

| System lock | 0.000035 | 2.53 | 1 | 0.0000350000 |

| cleaning up | 0.000029 | 2.10 | 1 | 0.0000290000 |

| init | 0.000023 | 1.66 | 1 | 0.0000230000 |

| statistics | 0.000019 | 1.37 | 1 | 0.0000190000 |

| freeing items | 0.000016 | 1.16 | 1 | 0.0000160000 |

| preparing | 0.000014 | 1.01 | 1 | 0.0000140000 |

| checking permissions | 0.000014 | 1.01 | 1 | 0.0000140000 |

| optimizing | 0.000012 | 0.87 | 1 | 0.0000120000 |

| query end | 0.000011 | 0.79 | 1 | 0.0000110000 |

| end | 0.000010 | 0.72 | 1 | 0.0000100000 |

| closing tables | 0.000010 | 0.72 | 1 | 0.0000100000 |

| executing | 0.000006 | 0.43 | 1 | 0.0000060000 |

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

--开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表

--如下面的查询，部分信息省略

profiling

root@localhost[information_schema]> select * from profiling limit 3,3\G;

*************************** 1. row ***************************

QUERY_ID: 1

SEQ: 5

STATE: init

DURATION: 0.000020

CPU_USER: 0.000000

CPU_SYSTEM: 0.000000

CONTEXT_VOLUNTARY: 0

CONTEXT_INVOLUNTARY: 0

BLOCK_OPS_IN: 0

BLOCK_OPS_OUT: 0

MESSAGES_SENT: 0

MESSAGES_RECEIVED: 0

PAGE_FAULTS_MAJOR: 0

PAGE_FAULTS_MINOR: 0

SWAPS: 0

SOURCE_FUNCTION: mysql_prepare_select

SOURCE_FILE: sql_select.cc

SOURCE_LINE: 1050

--停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭

root@localhost[sakila]> set profiling=off;

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

42号车库

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql性能瓶颈_MySQL性能瓶颈分析工具（profiling）

一、介绍1、分析SQL执行带来的开销是优化SQL的重要手段。2、在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。3、该参数可以在全局和session级别来设置。4、对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。5、该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步...
复制链接

扫一扫