MySQL Performance Tuning & Database Optimization Services

Identifies top slow queries from the sys schema, profiling execution count, average rows examined, and temporary disk table usage.

-- 1. Identify top 10 slow queries by total latency and disk spills
SELECT 
  query, 
  exec_count, 
  total_latency, 
  rows_examined_avg, 
  rows_sent_avg, 
  tmp_tables, 
  tmp_disk_tables 
FROM sys.statement_analysis 
ORDER BY total_latency DESC 
LIMIT 10;

-- 2. Inspect active queries running longer than 5 seconds
SELECT 
  id, 
  user, 
  host, 
  db, 
  command, 
  time, 
  state, 
  info 
FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 5 
ORDER BY time DESC;

Computes the real-time InnoDB buffer pool cache hit ratio and audits internal storage engine semaphore contention to prevent thread stall cascades.

-- 1. Inspect InnoDB buffer pool hit ratio (should be >99% for warm OLTP)
SELECT 
  ROUND((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2) AS buffer_pool_hit_pct 
FROM (
  SELECT 
    MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_reads' THEN VARIABLE_VALUE END) AS Innodb_buffer_pool_reads,
    MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_read_requests' THEN VARIABLE_VALUE END) AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
) AS bp_metrics;

-- 2. Inspect InnoDB semaphore waits and dirty page status
SHOW ENGINE INNODB STATUS;