MySQL 性能监控
show profile查询工具
使用show profile查询剖析工具，可以指定具体的type
速查表
| 关键词 | 说明 | SQL |
|---|---|---|
| all | 显示所有性能信息 | show profile all for query n |
| block io | 显示块io操作的次数 | show profile block io for query n |
| context switches | 显示上下文切换次数，被动和主动 | show profile context switches for query n |
| cpu | 显示用户cpu时间、系统cpu时间 | show profile cpu for query n |
| ipc | 显示发送和接受的消息数量 | show profile ipc for query n |
| Memory | 内存信息 | 暂未实现 |
| page faults | 显示页错误数量 | show profile page faults for query n |
| source | 显示源码中的函数名称与位置 | show profile source for query n |
| swaps | 显示swap的次数 | show profile swaps for query n |
示例
// 执行sql
select * from drafts;
// 查询结果
259 0.00108325 SHOW STATUS
260 0.0008205 SHOW STATUS
261 0.000804 SHOW STATUS
262 0.000903 SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
263 0.00083525 SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.001828*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=248 GROUP BY SEQ, STATE ORDER BY SEQ
264 0.00012 SET PROFILING = 1
265 0.00110675 SHOW STATUS
266 0.000854 SHOW STATUS
267 0.00183225 select * from drafts
268 0.0007695 SHOW STATUS
269 0.0008965 SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
270 0.00086 SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.000789*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=255 GROUP BY SEQ, STATE ORDER BY SEQ
271 0.00015925 SET PROFILING = 1
272 0.00108775 SHOW STATUS
273 0.00092375 SHOW STATUS
// 执行sql
show profile all for query 267;
// 执行结果
starting 0.000044 0.000027 0.000015 0 0 0 0 0 0 0 0 0
checking permissions 0.000007 0.000006 0.000000 0 0 0 0 0 0 0 0 0 check_access sql_authorization.cc 809
Opening tables 0.000014 0.000009 0.000006 0 0 0 0 0 0 0 0 0 open_tables sql_base.cc 5815
init 0.000020 0.000010 0.000010 0 0 0 0 0 0 0 0 0 handle_query sql_select.cc 128
System lock 0.000007 0.000006 0.000000 0 0 0 0 0 0 0 0 0 mysql_lock_tables lock.cc 330
optimizing 0.000004 0.000004 0.000000 0 0 0 0 0 0 0 0 0 optimize sql_optimizer.cc 158
statistics 0.000009 0.000004 0.000005 0 0 0 0 0 0 0 0 0 optimize sql_optimizer.cc 374
preparing 0.000007 0.000006 0.000000 0 0 0 0 0 0 0 0 0 optimize sql_optimizer.cc 482
executing 0.000003 0.000003 0.000000 0 0 0 0 0 0 0 0 0 exec sql_executor.cc 126
Sending data 0.001676 0.000243 0.000112 1 0 0 0 0 0 0 0 0 exec sql_executor.cc 202
end 0.000010 0.000008 0.000000 0 0 0 0 0 0 0 0 0 handle_query sql_select.cc 206
query end 0.000008 0.000002 0.000005 0 0 0 0 0 0 0 0 0 mysql_execute_command sql_parse.cc 4957
closing tables 0.000007 0.000007 0.000000 0 0 0 0 0 0 0 0 0 mysql_execute_command sql_parse.cc 5016
freeing items 0.000009 0.000004 0.000006 0 0 0 0 0 0 0 0 0 mysql_parse sql_parse.cc 5635
cleaning up 0.000009 0.000009 0.000000 0 0 0 0 0 0 0 0 0 dispatch_command sql_parse.cc 1931
performance schema监控MySQL
MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
入门
在mysql的5.7版本中，性能模式是默认开启的，如果想要显式的关闭的话需要修改配置文件，不能直接进行修改，会报错Variable ‘performance_schema’ is a read only variable。
--查看performance_schema的属性
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.01 sec)
--在配置文件中修改performance_schema的属性值，on表示开启，off表示关闭
[mysqld]
performance_schema=ON
--切换数据库
use performance_schema;
--查看当前数据库下的所有表,会看到有很多表存储着相关的信息
show tables;
--可以通过show create table tablename来查看创建表的时候的表结构
mysql> show create table setup_consumers;
+-----------------+---------------------------------
| Table | Create Table
+-----------------+---------------------------------
| setup_consumers | CREATE TABLE `setup_consumers` (
`NAME` varchar(64) NOT NULL,
`ENABLED` enum('YES','NO') NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-----------------+---------------------------------
1 row in set (0.00 sec)
速查SQL
--1、哪类的SQL执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪个表物理IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪个表逻辑IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪个索引访问最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪个索引从来没有用过？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪个等待事件消耗时间最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;
show processlist
使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征
属性说明
| 字段名 | 说明 |
|---|---|
| id | id表示session id |
| user | user表示操作的用户 |
| host | host表示操作的主机 |
| db | db表示操作的数据库 |
| commond | command表示当前状态 |
| info | info表示详细的sql语句 |
| time | time表示相应命令执行时间 |
| state | state表示命令执行状态 |
属性commond状态说明
| 状态 | 说明 |
|---|---|
| sleep | 线程正在等待客户端发送新的请求 |
| query | 线程正在执行查询或正在将结果发送给客户端 |
| locked | 在mysql的服务层，该线程正在等待表锁 |
| analyzing and statistics | 线程正在收集存储引擎的统计信息，并生成查询的执行计划 |
| Copying to tmp table | 线程正在执行查询，并且将其结果集都复制到一个临时表中 |
| sorting result | 线程正在对结果集进行排序 |
| sending data | 线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据 |
851
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
