mysql优化 - 监控

profiles

show variables like "%profiling%";
SET PROFILING=1;
SHOW PROFILES;
show profile for query 78;

performance_schema

select version(); -- 查询数据库版本
show databases; -- performance_schema
use performance_schema;
show TABLES;

show tables like '%wait%';
select * from setup_instruments; -- 采集mssql各式各样的操作生产的时间信息。
select * from setup_consumers where name like '%wait%'; -- 把采集上来的信息 存储起来
update setup_instruments set enabled='YES',TIMED='YES' WHERE name like '%wait%';
update setup_consumers set enabled = 'YES' where name like '%wait%';
select * from events_waits_current;
desc events_statements_summary_by_digest;
-- 哪类的sql执行次数最多
select digest_text,count_star,first_seen,last_seen from events_statements_summary_by_digest ORDER BY count_star desc;
-- 哪类的sql平均响应时间最多
select digest_text,avg_timer_wait from events_statements_summary_by_digest ORDER BY  avg_timer_wait desc;
-- 哪类排序记录数最多
select digest_text,sum_sort_rows from events_statements_summary_by_digest ORDER BY  sum_sort_rows desc;
-- 扫描记录数最多
select digest_text,sum_rows_examined from events_statements_summary_by_digest ORDER BY  sum_rows_examined desc;
-- 临时表最多
select digest_text,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES from events_statements_summary_by_digest ORDER BY  SUM_CREATED_TMP_TABLES desc;
-- 返回集最多
select digest_text,SUM_ROWS_SENT from events_statements_summary_by_digest ORDER BY  SUM_ROWS_SENT desc;

desc file_summary_by_instance;
-- 表物理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;

desc table_io_waits_summary_by_table;
-- 表逻辑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;

desc table_io_waits_summary_by_index_usage;
select * from table_io_waits_summary_by_index_usage;
-- 索引访问最多
select OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE,COUNT_STAR from table_io_waits_summary_by_index_usage ORDER BY COUNT_STAR desc
-- 索引没用过
select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME from table_io_waits_summary_by_index_usage where OBJECT_NAME is not null and count_star=0 and object_schema<> 'mysql' ORDER BY object_schema,object_name

-- 等待耗时最多
desc events_waits_summary_global_by_event_name；
select * from  events_waits_summary_global_by_event_name where event_name <> 'idle' ORDER BY sum_timer_wait desc;

-- 剖析sql执行情况
desc events_statements_history;
select * from events_statements_history where sql_text like '%select%'；

-- 每个阶段耗时情况
select * from events_stages_history_long;
-- 查看每个阶段的锁等待情况
select event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id from events_waits_history_long;

查看连接情况

show processlist;