mysql默认关闭
mysql> show variables like '%profiling%';
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
开启profiling
mysql> set profiling=on;
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
查看profiles
mysql> show profiles;
mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00232350 | show variables like '%profiling%' |
| 2 | 0.00013675 | SELECT DATABASE() |
| 3 | 0.00061925 | show databases |
| 4 | 0.00015050 | show tables |
| 5 | 0.00031075 | show tables |
| 6 | 0.00031950 | select * from tb_bk_order |
+----------+------------+-----------------------------------+
查看sql cpu和io开销
mysql> show profile cpu ,block io for query 6;
mysql> show profile cpu ,block io for query 6; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000084 | 0.000040 | 0.000042 | 0 | 0 | | checking permissions | 0.000013 | 0.000005 | 0.000005 | 0 | 0 | | Opening tables | 0.000016 | 0.000007 | 0.000008 | 0 | 0 | | init | 0.000030 | 0.000015 | 0.000015 | 0 | 0 | | System lock | 0.000009 | 0.000005 | 0.000005 | 0 | 0 | | optimizing | 0.000006 | 0.000002 | 0.000003 | 0 | 0 | | statistics | 0.000011 | 0.000006 | 0.000005 | 0 | 0 | | preparing | 0.000010 | 0.000005 | 0.000006 | 0 | 0 | | executing | 0.000004 | 0.000001 | 0.000001 | 0 | 0 | | Sending data | 0.000092 | 0.000046 | 0.000048 | 0 | 0 | | end | 0.000007 | 0.000003 | 0.000003 | 0 | 0 | | query end | 0.000007 | 0.000003 | 0.000004 | 0 | 0 | | closing tables | 0.000007 | 0.000004 | 0.000003 | 0 | 0 | | freeing items | 0.000013 | 0.000006 | 0.000007 | 0 | 0 | | cleaning up | 0.000011 | 0.000006 | 0.000006 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+
其它性能指标:
all -- 显示所有的开销信息
block io -- 显示块io相关的开销
context switches --上下文切换相关的开销
cpu -- 显示cpu相关的开销
ipc -- 显示发送和接收相关的开销
memory -- 显示内存相关的开销
page faults --显示页面错误相关的开销
source -- 显示和source_function, source_file, source_line相关的开销信息
swaps -- 显示交换次数相关的开销.
Status中如果出现以下指标,需要优化:
converting HEAP to MyISAM 查询结果太大,内存不够用往磁盘上搬
creating tmp table 创建临时表,拷贝数据到临时表,用完再删除临时表
coping to tmp table on disk 把内存中的临时表复制到磁盘, 危险!
locked