MySQL性能分析工具profile使用教程
更新時(shí)間：2014年10月31日 11:32:22 作者：Leshami
這篇文章主要介紹了MySQL性能分析工具profile使用教程,本文描述了如何使用MySQL profile,不涉及具體的樣例分析,需要的朋友可以參考下
分析SQL執(zhí)行帶來(lái)的開(kāi)銷是優(yōu)化SQL的重要手段。在MySQL數(shù)據(jù)庫(kù)中，可以通過(guò)配置profiling參數(shù)來(lái)啟用SQL剖析。該參數(shù)可以在全局和session級(jí)別來(lái)設(shè)置。對(duì)于全局級(jí)別則作用于整個(gè)MySQL實(shí)例，而session級(jí)別緊影響當(dāng)前session。該參數(shù)開(kāi)啟后，后續(xù)執(zhí)行的SQL語(yǔ)句都將記錄其資源開(kāi)銷，諸如IO，上下文切換，CPU，Memory等等。根據(jù)這些開(kāi)銷進(jìn)一步分析當(dāng)前SQL瓶頸從而進(jìn)行優(yōu)化與調(diào)整。本文描述了如何使用MySQL profile，不涉及具體的樣例分析。
1、有關(guān)profile的描述
復(fù)制代碼 代碼如下:
--當(dāng)前版本
root@localhost[sakila]> show variables like 'version';
+---------------+---------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------+
| version | 5.6.17-enterprise-commercial-advanced |
+---------------+---------------------------------------+
--查看profiling系統(tǒng)變量
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES | --只讀變量，用于控制是否由系統(tǒng)變量開(kāi)啟或禁用profiling
| profiling | OFF | --開(kāi)啟SQL語(yǔ)句剖析功能
| profiling_history_size | 15 | --設(shè)置保留profiling的數(shù)目，缺省為15，范圍為0至100，為0時(shí)將禁用profiling
+------------------------+-------+
profiling [539]
If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON, statement prof
is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to prof
information. See Section 13.7.5.32, “SHOW PROFILES Syntax”.
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.
profiling_history_size [539]
The number of statements for which to maintain profiling information if profiling [539] is
enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively
disables profiling. See Section 13.7.5.32, “SHOW PROFILES Syntax”.
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.
--獲取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 --顯示所有的開(kāi)銷信息
| BLOCK IO --顯示塊IO相關(guān)開(kāi)銷
| CONTEXT SWITCHES --上下文切換相關(guān)開(kāi)銷
| CPU --顯示CPU相關(guān)開(kāi)銷信息
| IPC --顯示發(fā)送和接收相關(guān)開(kāi)銷信息
| MEMORY --顯示內(nèi)存相關(guān)開(kāi)銷信息
| PAGE FAULTS --顯示頁(yè)面錯(cuò)誤相關(guān)開(kāi)銷信息
| SOURCE --顯示和Source_function，Source_file，Source_line相關(guān)的開(kāi)銷信息
| SWAPS --顯示交換次數(shù)相關(guān)開(kāi)銷的信息
The SHOW PROFILE and SHOW PROFILES statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.
*Note*: These statements are deprecated as of MySQL 5.6.7 and will be
removed in a future MySQL release. Use the Performance Schema instead;
see http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html.
--上面描述從5.6.7開(kāi)始該命令將會(huì)被移除，用Performance Schema instead代替
--在Oracle數(shù)據(jù)庫(kù)中，是通過(guò)autotrace來(lái)剖析單條SQL并獲取真實(shí)的執(zhí)行計(jì)劃以及其開(kāi)銷信息
2、開(kāi)啟porfiling
復(fù)制代碼 代碼如下:
--啟用session級(jí)別的profiling
root@localhost[sakila]> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
--驗(yàn)證修改后的結(jié)果
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
--發(fā)布SQL查詢
root@localhost[sakila]> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 599 |
+----------+
--查看當(dāng)前session所有已產(chǎn)生的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個(gè)warning，之前一個(gè)，現(xiàn)在一個(gè)
root@localhost[sakila]> show warnings; --下面的結(jié)果表明SHOW PROFILES將來(lái)會(huì)被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語(yǔ)句的開(kāi)銷信息
復(fù)制代碼 代碼如下:
--可以直接使用show profile來(lái)查看上一條SQL語(yǔ)句的開(kāi)銷信息
--注，show profile之類的語(yǔ)句不會(huì)被profiling，即自身不會(huì)產(chǎn)生Profiling
--我們下面的這個(gè)show profile查看的是show warnings產(chǎn)生的相應(yīng)開(kāi)銷
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 |
+----------+------------+--------------------------------+
--獲取指定查詢的開(kāi)銷
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 |
+----------------------+----------+
--查看特定部分的開(kāi)銷，如下為CPU部分的開(kāi)銷
root@localhost[sakila]> show profile cpu for query 2 ;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| 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部分的開(kāi)銷
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 |
+----------------------+----------+
--同時(shí)查看不同資源開(kāi)銷
root@localhost[sakila]> show profile block io,cpu for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| 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語(yǔ)句用于查詢query_id為2的SQL開(kāi)銷，且按最大耗用時(shí)間倒序排列
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;
+----------------------+----------+-------+-------+--------------+
| STATE | Total_R | Pct_R | Calls | R/Call |
+----------------------+----------+-------+-------+--------------+
| Sending data | 0.000990 | 71.53 | 1 | 0.0009900000 |--最大耗用時(shí)間部分為發(fā)送數(shù)據(jù)
| 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 |
+----------------------+----------+-------+-------+--------------+
--開(kāi)啟profiling后，我們可以通過(guò)show profile等方式查看，其實(shí)質(zhì)是這些開(kāi)銷信息被記錄到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,可以設(shè)置profiling參數(shù)，或者在session退出之后,profiling會(huì)被自動(dòng)關(guān)閉
root@localhost[sakila]> set profiling=off;
Query OK, 0 rows affected, 1 warning (0.00 sec)
相關(guān)文章
Django創(chuàng)建項(xiàng)目+連通mysql的操作方法
這篇文章主要介紹了Django創(chuàng)建項(xiàng)目+連通mysql的操作方法,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
這篇文章主要介紹了mysql 字段定義不要用null的原因分析,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-07-07
在程序開(kāi)發(fā)過(guò)程中，離不開(kāi)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)離不開(kāi)增刪改查操作，下面小編把我對(duì)mysql學(xué)習(xí)之插入、更新、刪除記錄心得總結(jié)分享給大家，需要的朋友可以參考下2015-08-08
在一些情況下，MySQL可以直接使用索引來(lái)滿足一個(gè) ORDER BY 或 GROUP BY 子句而無(wú)需做額外的排序2012-07-07
這篇文章主要介紹了關(guān)于MySQL中JSON類型的相關(guān)資料,MySQL?5.7.8引入JSON數(shù)據(jù)類型,提供原生支持,相比字符類型,具有優(yōu)勢(shì),JSON數(shù)據(jù)類型對(duì)數(shù)據(jù)進(jìn)行預(yù)處理,自動(dòng)將布爾類型轉(zhuǎn)換為小寫,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-12-12
解決mysql不能插入中文Incorrect string value
首先我的配置文件的設(shè)置的默認(rèn)字符集是utf8即2009-05-05
MySQL中MVCC機(jī)制的實(shí)現(xiàn)原理
這篇文章主要介紹了MySQL中MVCC機(jī)制的實(shí)現(xiàn)原理，MVCC多版本并發(fā)控制，MySQL中一種并發(fā)控制的方法，他主要是為了提高數(shù)據(jù)庫(kù)的讀寫性能，用更好的方式去處理讀寫沖突2022-08-08
MySQL啟動(dòng)失敗報(bào)錯(cuò)：mysqld.service failed to run
在日常運(yùn)維中,MySQL 作為廣泛應(yīng)用的關(guān)系型數(shù)據(jù)庫(kù),其穩(wěn)定性和可用性至關(guān)重要,然而,有時(shí)系統(tǒng)升級(jí)或配置變更后,MySQL 服務(wù)可能會(huì)出現(xiàn)無(wú)法啟動(dòng)的問(wèn)題,本文針對(duì)某次實(shí)際案例進(jìn)行深入分析和處理,需要的朋友可以參考下2024-12-12
本文主要介紹了mysql忘記密碼重置的方法實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
Mysql數(shù)據(jù)庫(kù)表定期備份的實(shí)現(xiàn)詳解
這篇文章主要介紹了Mysql數(shù)據(jù)庫(kù)表定期備份的實(shí)現(xiàn)詳解的相關(guān)資料,需要的朋友可以參考下2017-03-03