mysql>set profiling=1;
MySQL中查看SQL的执行计划主要是使用explain来查看,但这个没有办法知道内存及CPU等使用情况,自MySQL 5.0.37以后,mysql提供MySQL Query Profile可以查询到SQL执行所花费的时间,例如SQL执行过程中的SYSTEM LOCK,TABLE LOCK等所花费的时间。
MySQL Query Profile主要功能:
- Bottleneck Analysis
- Workload Analysis
- Ratio-based analysis
MySQL Query Profile使用方法
开启:
mysql>set profiling=1;
查看SQL Profile
mysql>show profiles;
mysql>show profiles;
+----------+------------+--------------------------------------------+
|Query_ID|Duration |Query |
+----------+------------+--------------------------------------------+
| 11|0.00006600|show global variables buffer |
| 12|0.00059900|show global variables like'%buffer%' |
| 15|0.00052900|show global variables like'%buffer%' |
| 16|0.00048700|show global variables like'%cache%' |
| 17|0.00032600|select versioin() |
| 18|0.00010300|select version() |
+----------+------------+--------------------------------------------+
查询各SQL执行所花费的时间
mysql>showprofileforquery19;# Query ID=19
+--------------------------------+----------+
|Status |Duration|
+--------------------------------+----------+
|starting |0.000019|
|checking query cache for query|0.000009|
|checking privileges on cached |0.000005|
|sending cached result to client|0.000011|
|logging slow query |0.000002|
|cleaning up |0.000002|
+--------------------------------+----------+
6 rows in set(0.01sec)
mysql>SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
|Status |Duration|CPU_user|CPU_system|
+----------------------+----------+----------+------------+
|checking permissions|0.000040|0.000038| 0.000002|
|creating table |0.000056|0.000028| 0.000028|
|After create |0.011363|0.000217| 0.001571|
|query end |0.000375|0.000013| 0.000028|
|freeing items |0.000089|0.000010| 0.000014|
|logging slow query |0.000019|0.000009| 0.000010|
|cleaning up |0.000005|0.000003| 0.000002|
+----------------------+----------+----------+------------+
7 rows in set(0.00sec)
Syntax:
SHOWPROFILE[type[,type]...]
[FORQUERYn]
[LIMITrow_count[OFFSEToffset]]
type:
ALL
|BLOCKIO
|CONTEXTSWITCHES
|CPU
|IPC
|MEMORY
|PAGEFAULTS
|SOURCE
|SWAPS
ALL displays all information
BLOCKIO displays counts for block input and output operations
CONTEXTSWITCHES displays counts for voluntary and involuntary context switches
CPU displays user and system CPU usage times
IPC displays counts for messages sent and received
MEMORY is not currently implemented
PAGEFAULTSdisplayscountsformajorandminorpagefaults
SOURCE displays the names off unctions from the source code,together
with the name and line number of the file in which the function
occurs
SWAPS displays swap counts
设置profileing存储记录的size
mysql>showvariableslike'%profiling%';
+------------------------+-------+
|Variable_name |Value|
+------------------------+-------+
|have_profiling |YES |
|profiling |OFF |
|profiling_history_size|15 |
+------------------------+-------+
3rowsinset(0.00sec)
mysql>
关闭profile
mysql>set profiling=0;
Profiling is enabled per session. When a session ends, its profiling
information is lost.