Week 13 — Monitoring, Performance Tuning & Server Administration¶

-- List all instrument categories
SELECT SUBSTRING_INDEX(NAME, '/', 1) AS category, COUNT(*) AS cnt
FROM   performance_schema.setup_instruments
GROUP  BY category
ORDER  BY cnt DESC;

-- Enable all statement instruments (usually already on)
UPDATE performance_schema.setup_instruments
SET    ENABLED = 'YES', TIMED = 'YES'
WHERE  NAME LIKE 'statement/%';

-- Enable all consumers (where data is stored)
UPDATE performance_schema.setup_consumers
SET    ENABLED = 'YES'
WHERE  NAME LIKE '%statements%'
   OR  NAME LIKE '%waits%';

-- Reset statement digests
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- Reset wait summaries
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;

-- Reset file I/O
TRUNCATE TABLE performance_schema.file_summary_by_instance;

-- Top statements by runtime (95th percentile latency)
SELECT query, exec_count, avg_latency, rows_examined_avg
FROM   sys.statements_with_runtimes_in_95th_percentile
ORDER  BY avg_latency DESC
LIMIT  10;

-- Global I/O by file in MB
SELECT file, total_read, total_written, total
FROM   sys.io_global_by_file_by_bytes
ORDER  BY total DESC
LIMIT  10;

-- User connection summary
SELECT user, total_connections, current_connections,
       statement_avg_latency, rows_examined_avg
FROM   sys.user_summary
ORDER  BY current_connections DESC;

-- Tables with full table scans
SELECT object_schema, object_name, count_read,
       rows_full_scanned, latency
FROM   sys.schema_tables_with_full_table_scans
ORDER  BY rows_full_scanned DESC
LIMIT  10;

-- Indexes never used
SELECT object_schema, object_name, index_name
FROM   sys.schema_unused_indexes
WHERE  object_schema = 'university';

-- Current blocking transactions
SELECT waiting_query, waiting_age, blocking_query,
       blocking_pid
FROM   sys.innodb_lock_waits;

SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Key metrics:
-- Innodb_buffer_pool_read_requests: Total read requests
-- Innodb_buffer_pool_reads:         Reads that required disk I/O
-- Hit rate = 1 - (reads / read_requests) — target > 99%

SELECT
    ROUND(100 * (1 - (
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
        /
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )), 2) AS buffer_pool_hit_rate_pct;

SHOW ENGINE INNODB STATUS\G
-- Sections to read:
-- BUFFER POOL AND MEMORY: hit rate, dirty pages, free pages
-- ROW OPERATIONS: rows inserted/read/updated/deleted/s
-- LOG: LSN, checkpoint, log waits (if > 0, redo log is too small)
-- TRANSACTIONS: active transactions, lock waits
-- LATEST DETECTED DEADLOCK: last deadlock graph

-- Quick overview
SHOW FULL PROCESSLIST;

-- Filtered view — queries running > 30 seconds
SELECT
    ID, USER, HOST, DB,
    COMMAND, TIME, STATE,
    LEFT(INFO, 120) AS query_snippet
FROM information_schema.PROCESSLIST
WHERE COMMAND NOT IN ('Sleep', 'Binlog Dump')
  AND TIME > 30
ORDER BY TIME DESC;

-- Kill a specific query (keeps connection open)
KILL QUERY 1042;

-- Kill entire connection
KILL 1042;

SHOW ENGINE INNODB STATUS\G
-- Look for LATEST DETECTED DEADLOCK section:
--
-- TRANSACTION 1, ACTIVE 2 sec starting index read
-- MySQL thread id 45, OS thread id 140..., query id 8921 localhost app_user
-- UPDATE enrollments SET status='enrolled' WHERE section_id=10 AND student_id=201
-- WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS space id 234 page no 7 n bits 72 index PRIMARY
-- of table `university`.`enrollments` trx id 42001 lock_mode X locks rec
--
-- TRANSACTION 2, ACTIVE 2 sec starting index read
-- UPDATE sections SET enrolled_count=enrolled_count+1 WHERE section_id=10
-- HOLDS THE LOCK(S):
-- RECORD LOCKS ... index PRIMARY of table `university`.`sections`
-- WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS ... index PRIMARY of table `university`.`enrollments`

-- BAD: Transaction A locks enrollments then sections
--      Transaction B locks sections then enrollments  → DEADLOCK

-- GOOD: Both transactions always lock in the same order
-- Procedure sp_enroll_student: ALWAYS lock sections first, then enrollments
BEGIN;
    SELECT * FROM sections    WHERE section_id = 10 FOR UPDATE;  -- 1st
    SELECT * FROM enrollments WHERE student_id  = 201
                               AND section_id   = 10 FOR UPDATE; -- 2nd
    -- ... rest of logic
COMMIT;

-- ANALYZE TABLE: Update statistics used by the query optimizer.
-- Run after large bulk loads or significant data changes.
ANALYZE TABLE students, enrollments, sections;

-- OPTIMIZE TABLE: Rebuild table to reclaim fragmented space and
-- re-sort data. Equivalent to ALTER TABLE ... ENGINE=InnoDB.
-- WARNING: Takes a full table copy — use during maintenance window.
OPTIMIZE TABLE audit_log;   -- Good for tables with heavy deletes

-- CHECK TABLE: Verify table integrity (mostly useful for MyISAM).
-- For InnoDB, equivalent to CHECK TABLESPACE.
CHECK TABLE enrollments EXTENDED;

-- REPAIR TABLE: Attempt to repair corrupted MyISAM table.
-- InnoDB tables cannot be repaired this way; restore from backup.
REPAIR TABLE myisam_legacy_table;

-- Which tablespace files are generating the most I/O?
SELECT
    SUBSTRING_INDEX(FILE_NAME, '/', -1) AS filename,
    COUNT_READ,
    COUNT_WRITE,
    ROUND(SUM_NUMBER_OF_BYTES_READ  / 1048576, 1) AS read_MB,
    ROUND(SUM_NUMBER_OF_BYTES_WRITE / 1048576, 1) AS write_MB,
    ROUND((SUM_TIMER_READ + SUM_TIMER_WRITE) / 1e12, 3) AS total_io_s
FROM performance_schema.file_summary_by_instance
WHERE FILE_NAME LIKE '%university%'
ORDER BY total_io_s DESC
LIMIT 10;