DB Wiki

User Tools

Site Tools

Trace: • documents • mysqlperformance
mysqlperformance

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
mysqlperformance [2019/08/05 15:45] – [Performance Schema Scripts] z0hpvkmysqlperformance [2025/03/08 22:24] (current) – external edit 127.0.0.1
Line 16: Line 16:
 <code SQL> <code SQL>
 -- Unused Indexes -- Unused Indexes
 +select * from sys.schema_unused_indexes;
 +
 select object_schema, object_name, index_name select object_schema, object_name, index_name
   from performance_schema.table_io_waits_summary_by_index_usage   from performance_schema.table_io_waits_summary_by_index_usage
  where index name is not null and count_star = 0  where index name is not null and count_star = 0
  order by object_schema, object_name;  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 -- Process List
Line 69: Line 83:
   from sys.user_summary_by_statement_type limit 100;   from sys.user_summary_by_statement_type limit 100;
      
--- Slow Queries  +-- Active Queries  
 SELECT CONCAT_WS( SELECT CONCAT_WS(
 '','# Time: ', date_format(CURDATE(),'%y%m%d'),' ',TIME_FORMAT(NOW(6),'%H:%i:%s.%f'),'\n' '','# Time: ', date_format(CURDATE(),'%y%m%d'),' ',TIME_FORMAT(NOW(6),'%H:%i:%s.%f'),'\n'
mysqlperformance.1565019945.txt.gz · Last modified: (external edit)