DB Wiki

http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/
http://speedemy.com/files/mysql/my.cnf

wget percona.com/get/pt-query-digest \\
chmod 750 pt-query-digest \\
./pt-query-digest /var/lib/mysql/slow-query.log > /root/slow-query-log.out

-- 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;

My SQL 5.7.25, Windows Zip Installation.