Mysql性能分析工具 SHOW PROFILE、 SHOW STATUS

 

前言

在实际应用中，有SHOW STATUS、SHOW PROFILE、检查慢查询日志的条目三种方法剖析单条查询。

SHOW PROFILE

Mysql5.1版本以后才有，默认功能禁用，使用SET profiling = 1命令开启。

功能：服务器上所有执行的语句，都会测量其消耗时间和执行状态变更相关的数据。
当一条查询提交到服务器，此工具会记录剖析信息到一张临时表，并且这张表有从1开始的自增id。
注意：show profile之类的语句不会被profiling，即自身不会产生Profiling

命令:
查询是否开启show profile,默认关闭

SHOW VARIABLES LIKE 'profiling';

开启关闭show profile

开启：SET profiling = 1;
关闭：SET profiling = 0;

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

查询语法：show profile [参数[,参数...]] for query [Query_ID]

查询所有测量的查询剖析信息

SHOW PROFILE;

查询指定id的查询剖析信息

SHOW PROFILE FOR QUERY id;

查询指定id的查询的cpu和block剖析信息

SHOW PROFILE cpu, block io FOR QUERY id;

查询id为2的查询剖析信息，且按最大耗用时间倒序排列

set @query_id=2; 
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; 

参数信息

• ALL: 显示所有的开销信息
• BLOCK IO ： 显示块IO相关开销
• CONTEXT SWITCHS: 上下文切换相关开销
• CPU : 显示cpu 相关开销
• IPC: 显示发送和接收相关开销
• MEMORY： 显示内存相关开销
• PAGE FAULTS：显示页面错误相关开销信息
• SOURCE ： 显示和Source_function ,Source_file,Source_line 相关的开销信息
• SWAPS：显示交换次数相关的开销信息
• Status ： sql 语句执行的状态
• Duration: sql 执行过程中每一个步骤的耗时
• CPU_user: 当前用户占有的cpu
• CPU_system: 系统占有的cpu
• Block_ops_in : I/O 输入
• Block_ops_out : I/O 输出

表中Status列如果出现下面四种情况,需要优化:

1. converting HEAP to MySIAM 数据过大MyISAM内存装不下，向磁盘上搬运
2. Creating tmp table 临时表创建
3. Copying to tmp table on disk 复制临时表到磁盘
4. locked 锁，阻塞

SHOW STATUS

SHOW STATUS
命令返回一些计数器，返回会话级、服务器级别的计数器，统计查询的次数。

SHOW GLOBAL STATUS
查看服务器从启动到开始计算的查询次数统计

注意：
1.不同的计数器可见范围不一样，全局的计数器也会出现在SHOW STATUS的结果中。
2.SHOW STATUS本身也会创建一个临时表。
3.SHOW STATUS本身也会统计记录到计数器中。
这些计数器可以猜测哪些操作代价较高或者消耗的时间较多，最有用的计数器包括：
句柄计数器（handler counter）、零时文件和表计数器

常用命令
重置计数器
FLUSH STATUS;

查看查询时间超过long_query_time秒的查询的个数。
show status like 'slow_queries';

查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_launch_threads';

查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
show status like 'table_locks_waited';

查看立即获得的表的锁的次数。
show status like 'table_locks_immediate';

查看激活的(非睡眠状态)线程数。
show status like 'threads_running';

查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
show status like 'threads_created';

查看当前打开的连接的数量。
show status like 'threads_connected';

查看线程缓存内的线程的数量。
show status like 'threads_cached';

查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';

查看delete语句的执行数
show [global] status like 'com_delete';

查看update语句的执行数

show [global] status like 'com_update';

查看insert语句的执行数

show [global] status like 'com_insert';

查看select语句的执行数

show [global] status like 'com_select';

--查看MySQL本次启动后的运行时间(单位：秒)

show status like 'uptime';

更多参数：MySQL运行状态show status中文详解