# Rule of thumb: 70-80% of total RAM on a dedicated database server
# On a 32 GB server:
innodb_buffer_pool_size      = 24G
innodb_buffer_pool_instances = 8   # 1 per ~1 GB; reduces mutex contention on multi-core

-- Check the buffer pool hit ratio (target: > 99%)
SELECT
  ROUND(
    (1 - (
      (SELECT VARIABLE_VALUE FROM performance_schema.global_status
       WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
      NULLIF(
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)
    )) * 100, 2
  ) AS buffer_pool_hit_ratio_pct;

-- Also check current pool utilization
SELECT
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_pages_total',
  'Innodb_buffer_pool_pages_free',
  'Innodb_buffer_pool_pages_data'
);

-- Indexes with zero usage since the last server restart
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- Redundant indexes (fully covered by another index)
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

-- Tables doing the most full table scans
SELECT object_schema, object_name,
       SUM(count_read) AS full_scan_reads
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NULL
GROUP BY object_schema, object_name
ORDER BY full_scan_reads DESC
LIMIT 20;

-- Verify with Percona Toolkit (most thorough)
-- pt-duplicate-key-checker --host=localhost --user=root --ask-pass

-- View runtime status counters
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer%';
SHOW GLOBAL STATUS LIKE 'Handler_%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- Top 10 queries by total execution time (best starting point)
SELECT
  DIGEST_TEXT,
  COUNT_STAR                             AS calls,
  ROUND(SUM_TIMER_WAIT / 1e12, 2)       AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1e12, 4)       AS avg_sec,
  ROUND(MAX_TIMER_WAIT / 1e12, 4)       AS max_sec,
  SUM_ROWS_EXAMINED                     AS rows_examined,
  SUM_NO_INDEX_USED                     AS no_index_calls
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Longest currently running queries
SELECT thread_id, event_id,
       ROUND(TIMER_WAIT / 1e12, 2) AS seconds,
       SQL_TEXT
FROM performance_schema.events_statements_current
WHERE TIMER_WAIT > 1e12
ORDER BY TIMER_WAIT DESC;

-- I/O wait per table (find the most contended tables)
SELECT OBJECT_SCHEMA, OBJECT_NAME,
       ROUND((SUM_TIMER_FETCH + SUM_TIMER_INSERT +
              SUM_TIMER_UPDATE + SUM_TIMER_DELETE) / 1e12, 2) AS total_wait_sec
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY total_wait_sec DESC
LIMIT 10;

// Node.js — mysql2 connection pool example
const pool = mysql.createPool({
  host:            'localhost',
  user:            'app',
  password:        'secret',
  database:        'myapp',
  charset:         'utf8mb4',
  waitForConnections: true,
  connectionLimit: 10,       // max pool size (match max_connections / app_servers)
  queueLimit:      0         // 0 = unlimited queue
});

// Always use pool.execute() or pool.query() — automatically manages connections
const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [userId]);

# ProxySQL key capabilities:
# - Connection multiplexing: 10,000 app conns -> 200 real MySQL conns
# - Query routing: send writes to primary, reads to replicas
# - Query caching: cache frequent read queries
# - Query rewriting: transform queries without changing application code
# - Failover: automatically route around failed nodes

-- Force a specific index
SELECT /*+ INDEX(orders idx_status) */ *
FROM orders WHERE status = 'pending';

-- Disable derived table merging (useful for complex CTEs)
SELECT /*+ NO_MERGE(subq) */ * FROM (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
) subq WHERE cnt > 10;

-- Force a join order (left table is the outer loop)
SELECT /*+ JOIN_ORDER(users, orders) */ u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

-- STRAIGHT_JOIN: force left-to-right join order (older syntax, still works)
SELECT STRAIGHT_JOIN u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Set max execution time (kills the query if it exceeds the limit)
SELECT /*+ MAX_EXECUTION_TIME(5000) */ *
FROM large_report_table WHERE year = 2024;

-- On the primary: writes and latency-sensitive reads
INSERT INTO orders (user_id, total) VALUES (42, 99.99);
SELECT * FROM users WHERE id = 42;           -- real-time lookup

-- On the replica: analytics and batch reports (slight replication lag acceptable)
SELECT DATE(created_at) AS day, SUM(total) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY day;

-- BAD: application runs this loop (N+1 queries)
-- SELECT * FROM orders LIMIT 100;
-- for each order: SELECT * FROM users WHERE id = ?

-- GOOD: single query with JOIN
SELECT o.id, o.total, o.created_at,
       u.name, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY o.created_at DESC
LIMIT 100;

-- BAD: correlated subquery — runs once per user row
SELECT u.id, u.name,
  (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- GOOD: LEFT JOIN + GROUP BY — single pass
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- BAD: OFFSET causes MySQL to scan and discard previous pages
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 50000;
-- Scans 50,010 rows to return 10

-- GOOD: keyset pagination — always O(1) regardless of page
-- Pass the last_id seen from the previous page
SELECT * FROM products
WHERE id > :last_id
ORDER BY id
LIMIT 10;
-- Uses the PK index directly — scans only 10 rows

# Install Prometheus mysqld_exporter to scrape MySQL metrics
docker run -d   --name mysqld_exporter   -p 9104:9104   -e DATA_SOURCE_NAME="exporter:password@tcp(mysql:3306)/"   prom/mysqld-exporter

# Key metrics exposed at /metrics:
# mysql_global_status_slow_queries
# mysql_global_status_innodb_buffer_pool_read_requests
# mysql_global_status_innodb_buffer_pool_reads
# mysql_global_status_threads_connected
# mysql_global_status_threads_running

# Grafana dashboard ID 7362 (MySQL Overview) provides
# a ready-made dashboard for these metrics

-- Top 10 queries by total latency with formatted output
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- Queries doing full table scans
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- Queries doing filesort
SELECT * FROM sys.statements_with_sorting LIMIT 10;

-- Unused indexes in the current database
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- Tables with no primary key (fragile for replication and partitioning)
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME NOT IN (
    SELECT TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND CONSTRAINT_TYPE = 'PRIMARY KEY'
  );

-- Find currently running queries taking more 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;

-- Kill a long-running query (use the id from PROCESSLIST)
KILL QUERY 1234;    -- kill just the query, keep connection
KILL 1234;          -- kill the entire connection

-- Check for lock contention (InnoDB row locks)
SELECT
  r.trx_id        AS waiting_trx,
  r.trx_query     AS waiting_query,
  b.trx_id        AS blocking_trx,
  b.trx_query     AS blocking_query,
  TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

-- Full InnoDB status (includes lock info, buffer pool stats, transaction list)
SHOW ENGINE INNODB STATUSG