Differences

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

--- mysqlperformance [2019/07/27 19:10] – [Slow Query Log Analysis] z0hpvk
+++ mysqlperformance [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 11: / Line 11: @@
 chmod 750 pt-query-digest \\
 ./pt-query-digest /var/lib/mysql/slow-query.log > /root/slow-query-log.out
+</code>
+==== Performance Schema Scripts ====
+<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
+SELECT PROCESSLIST_ID AS id,
+       PROCESSLIST_USER AS user,
+       PROCESSLIST_HOST AS host,
+       PROCESSLIST_DB AS db,
+       PROCESSLIST_COMMAND AS command,
+       PROCESSLIST_TIME AS time,
+       PROCESSLIST_STATE AS state,
+       LEFT(PROCESSLIST_INFO, 80) AS info
+  FROM performance_schema.threads
+ 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>