-- ── MySQL: Enable and query the slow log ──
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries over 1 second
SHOW VARIABLES LIKE 'slow_query_log_file';  -- find log location
 
-- ── MySQL: Find tables with most lock contention ──
SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id AS blocking_trx,
  b.trx_query AS blocking_query
FROM information_schema.INNODB_TRX b
JOIN information_schema.INNODB_TRX r ON r.trx_id != b.trx_id;
 
-- ── PostgreSQL: Find long-running queries ──
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
  AND (now() - pg_stat_activity.query_start) > INTERVAL '5 seconds'
ORDER BY duration DESC;
 
-- Kill a specific query if needed:
SELECT pg_cancel_backend(pid);
-- Force kill (for stuck transactions):
SELECT pg_terminate_backend(pid);
 
-- ── PostgreSQL: Buffer cache hit rate (target >99%) ──
SELECT
  sum(heap_blks_read) AS disk_reads,
  sum(heap_blks_hit) AS cache_hits,
  ROUND(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS cache_hit_pct
FROM pg_statio_user_tables;

-- ── Check table bloat (dead tuples) ──
SELECT
  relname AS table_name,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY dead_rows DESC
LIMIT 10;
 
-- ── Manual VACUUM (usually auto-vacuum handles this) ──
VACUUM ANALYZE users;           -- reclaim dead tuples + update stats
VACUUM FULL users;              -- full rewrite — reclaims more space but LOCKS TABLE!
REINDEX TABLE users;            -- rebuild fragmented indexes
 
-- ── Find unused indexes (candidates for removal) ──
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- never used!
  AND indexname NOT LIKE '%pkey'  -- don't drop primary keys!
ORDER BY pg_relation_size(indexrelid) DESC;

-- ── MySQL: Check current database size ──
SELECT
  table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'(MB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
 
-- ── MySQL: Top 10 largest tables ──
SELECT
  TABLE_NAME,
  TABLE_ROWS AS estimated_rows,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size MB'MB'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 10;
 
-- ── PostgreSQL: Active connection count by state ──
SELECT state, COUNT(*) AS connections
FROM pg_stat_activity
GROUP BY state
ORDER BY connections DESC;
 
-- ── PostgreSQL: Replication lag on replicas ──
SELECT
  client_addr,
  state,
  sent_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication;