Performance Monitoring in MySQL

-- Show current connections
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Kill long-running query
KILL QUERY 1234;
KILL CONNECTION 1234;

-- Server status
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Variables
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb%';

-- DETAILED_TAB:
-- Performance schema queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Table sizes
SELECT 
  table_schema AS 'Database',
  table_name AS 'Table',
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY (data_length + index_length) DESC;

-- Connection stats
SHOW STATUS WHERE Variable_name IN (
  'Connections',
  'Max_used_connections',
  'Aborted_connects',
  'Threads_connected'
);

-- InnoDB metrics
SHOW ENGINE INNODB STATUS\G

-- Buffer pool efficiency
SELECT 
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
  AS buffer_pool_hit_rate
FROM (
  SELECT 
    MAX(IF(variable_name = 'Innodb_buffer_pool_reads', variable_value, NULL)) AS Innodb_buffer_pool_reads,
    MAX(IF(variable_name = 'Innodb_buffer_pool_read_requests', variable_value, NULL)) AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
) AS t;