Differences

This shows you the differences between two versions of the page.

--- mysqlperformance [2019/08/05 10:11] – [Performance Schema Scripts] z0hpvk
+++ mysqlperformance [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 16: / Line 16: @@
 <code SQL>
 -- Unused Indexes
+select * from sys.schema_unused_indexes;
 select object_schema, object_name, index_name
   from performance_schema.table_io_waits_summary_by_index_usage
  where index name is not null and count_star = 0
  order by object_schema, object_name;
+-- Queries not using Indexes
+SELECT THREAD_ID TID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS,
+       ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED
+  FROM performance_schema.events_statements_history
+ WHERE NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1;
+-- Queries using Full Table Scans
+SELECT query, db, total_latency, no_index_used_count, rows_sent, rows_examined, last_seen
+  FROM sys.statements_with_full_table_scans
+ WHERE db not in ('performance_schema','sys')
+ORDER BY last_seen DESC limit 50;
 -- Process List
@@ Line 33: / Line 47: @@
  WHERE PROCESSLIST_ID IS NOT NULL AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
  ORDER BY PROCESSLIST_TIME ASC;
+-- Hot Databases (Disk Utilisation)
+select sys.extract_schema_from_file_name(file_name) as table_schema,
+       sum(count_read) as io_read_requests,
+       sys.format_bytes(sum(sum_number_of_bytes_read)) as io_read,
+       sys.format_time(sum(sum_timer_read)) as io_read_latency,
+       sum(count_write) as io_write_requests,
+       sys.format_bytes(sum(sum_number_of_bytes_write)) as io_write,
+       sys.format_time(sum(sum_timer_write)) as io_write_latency,
+       sum(count_misc) as io_misc_requests,
+       sys.format_time(sum(sum_timer_misc)) as io_misc_latency
+  from performance_schema.file_summary_by_instance
+  group by table_schema
+  order by io_write_requests DESC limit 20;
+-- Hot Tables (Disk Utilisation)
+select concat(sys.extract_schema_from_file_name(file_name),'.',
+       sys.extract_table_from_file_name(file_name)) as unique_table_name,
+       sum(count_read) as io_read_requests,
+       sys.format_bytes(sum(sum_number_of_bytes_read)) as io_read,
+       sys.format_time(sum(sum_timer_read)) as io_read_latency,
+       sum(count_write) as io_write_requests,
+       sys.format_bytes(sum(sum_number_of_bytes_write)) as io_write,
+       sys.format_time(sum(sum_timer_write)) as io_write_latency,
+       sum(count_misc) as io_misc_requests,
+       sys.format_time(sum(sum_timer_misc)) as io_misc_latency
+  from performance_schema.file_summary_by_instance
+  group by unique_table_name
+  order by io_write_requests DESC limit 20;
+-- User Reports
+select * from sys.user_summary limit 10;
+select user,statement,total,rows_sent,rows_examined,rows_affected,full_scans
+  from sys.user_summary_by_statement_type limit 100;
+-- Active Queries
+SELECT CONCAT_WS(
+'','# Time: ', date_format(CURDATE(),'%y%m%d'),' ',TIME_FORMAT(NOW(6),'%H:%i:%s.%f'),'\n'
+,'# User@Host: ',t.PROCESSLIST_USER,'[',t.PROCESSLIST_USER,'] @ ',PROCESSLIST_HOST,' []  Id: ',t.PROCESSLIST_ID,'\n'
+,'# Schema: ',CURRENT_SCHEMA,'  Last_errno: ',MYSQL_ERRNO,'  ','\n'
+,'# Query_time: ',ROUND(s.TIMER_WAIT / 1000000000000, 6),' Lock_time: ',ROUND(s.LOCK_TIME / 1000000000000, 6),'  Rows_sent: ',ROWS_SENT,'  Rows_examined: ',ROWS_EXAMINED,'  Rows_affected: ',ROWS_AFFECTED,'\n'
+,'# Tmp_tables: ',CREATED_TMP_TABLES,'  Tmp_disk_tables: ',CREATED_TMP_DISK_TABLES,'  ','\n'
+,'# Full_scan: ',IF(SELECT_SCAN=0,'No','Yes'),'  Full_join: ',IF(SELECT_FULL_JOIN=0,'No','Yes'),'  Tmp_table: ',IF(CREATED_TMP_TABLES=0,'No','Yes'),'  Tmp_table_on_disk: ',IF(CREATED_TMP_DISK_TABLES=0,'No','Yes'),'\n'
+, t.PROCESSLIST_INFO,';')
+FROM performance_schema.events_statements_history s
+JOIN performance_schema.threads t using(thread_id)
+WHERE t.TYPE = 'FOREGROUND'
+  AND t.PROCESSLIST_INFO IS NOT NULL
+  AND t.PROCESSLIST_ID != connection_id()
+ORDER BY t.PROCESSLIST_TIME desc;
 </code>