Buffer pool & memory

SELECT @@innodb_buffer_pool_size / 1024/1024/1024 AS gb,
       @@innodb_buffer_pool_instances AS instances,
       @@innodb_buffer_pool_chunk_size / 1024/1024 AS chunk_mb;

-- The single most important health metric on the server.
-- Aim for > 99% on an OLTP workload.
SELECT ROUND(
  (1 - (
    SUM(IF(Variable_name='Innodb_buffer_pool_reads', Variable_value, 0)) /
    SUM(IF(Variable_name='Innodb_buffer_pool_read_requests', Variable_value, 0))
  )) * 100, 3) AS hit_rate_pct
FROM performance_schema.global_status
WHERE Variable_name IN
  ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');

--   read_requests = logical reads (asked the pool)
--   reads         = physical reads (had to hit disk)

-- What is actually in there, by table?
SELECT table_name, index_name, COUNT(*) AS pages,
       ROUND(COUNT(*)*16/1024,1) AS mb
FROM information_schema.innodb_buffer_page
WHERE table_name IS NOT NULL
GROUP BY table_name, index_name
ORDER BY pages DESC LIMIT 15;

SELECT @@innodb_old_blocks_pct, @@innodb_old_blocks_time;

-- Split of the pool right now.
SELECT pool_id, database_pages, old_database_pages,
       pages_made_young, pages_not_made_young
FROM information_schema.innodb_buffer_pool_stats;

-- Take a baseline, run a big scan, then re-check.
SELECT COUNT(*) FROM some_huge_table;

-- pages_not_made_young should jump a lot;
-- pages_made_young should barely move. That is the
-- protection working: the scan filled the old sublist
-- and was evicted from it without ever reaching young.

-- For a workload that scans a lot and wants MORE protection:
--   SET GLOBAL innodb_old_blocks_time = 2000;
--   SET GLOBAL innodb_old_blocks_pct  = 20;   -- smaller probation area

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_ahead%';

--   ..._read_ahead          pages prefetched
--   ..._read_ahead_evicted  prefetched and thrown away UNUSED

-- If evicted is a large fraction of prefetched, read-ahead is
-- doing harm: it is reading pages nobody wanted, evicting pages
-- someone did. Consider raising the threshold or disabling:
--   SET GLOBAL innodb_read_ahead_threshold = 64;  -- max, least eager
--   SET GLOBAL innodb_random_read_ahead = OFF;    -- already default

-- as a fraction of innodb_redo_log_capacity
< 50%   → relaxed. background flushing at io_capacity.
~ 75%   → adaptive flushing ramps up aggressively.
~ 87.5% → async flush: heavy, user threads start to feel it.
~ 95%   → SYNC FLUSH. all writes BLOCK until space is reclaimed.

SELECT
  MAX(IF(name='log_lsn_checkpoint_age', count, NULL)) AS checkpoint_age,
  @@innodb_redo_log_capacity                              AS capacity,
  ROUND(MAX(IF(name='log_lsn_checkpoint_age', count, NULL))
        / @@innodb_redo_log_capacity * 100, 1)           AS pct_used
FROM information_schema.innodb_metrics
WHERE name = 'log_lsn_checkpoint_age';

-- Enable the metric first if it returns NULL:
--   SET GLOBAL innodb_monitor_enable = 'log_lsn_checkpoint_age';

-- pct_used consistently above 75 → enlarge the redo log.
-- Any Innodb_log_waits at all → you are already stalling.
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

SELECT @@innodb_buffer_pool_dump_at_shutdown,
       @@innodb_buffer_pool_load_at_startup,
       @@innodb_buffer_pool_dump_pct;   -- % of each LRU, default 25

-- Dump now, without restarting (do this before planned maintenance):
SET GLOBAL innodb_buffer_pool_dump_now = ON;

-- Watch warming progress after a restart:
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

-- For a big pool, raise dump_pct to 50-75 so more of the
-- working set is restored. The cost is a slower shutdown.

-- What MySQL is ACTUALLY using, by allocation site.
SELECT event_name,
       ROUND(current_alloc/1024/1024) AS mb
FROM sys.memory_global_by_current_bytes
WHERE current_alloc > 1024*1024*10
ORDER BY current_alloc DESC LIMIT 15;

-- Per-connection memory, worst offenders first.
SELECT user, current_count_used, current_allocated,
       current_max_alloc
FROM sys.memory_by_user_by_current_bytes
ORDER BY current_allocated DESC LIMIT 10;

-- Enable memory instrumentation if these are empty:
--   UPDATE performance_schema.setup_instruments
--      SET enabled='YES' WHERE name LIKE 'memory/%';

-- Is anything spilling to disk for lack of buffer?
SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Sort_merge_passes',            -- sorts that needed disk
   'Created_tmp_disk_tables',      -- temp tables on disk
   'Created_tmp_tables');

SELECT @@internal_tmp_mem_storage_engine, @@temptable_max_ram,
       @@tmp_table_size, @@max_heap_table_size;

-- The ratio that matters. High disk_tables = queries hitting disk.
SELECT
  MAX(IF(Variable_name='Created_tmp_tables', Variable_value, 0))      AS tmp_total,
  MAX(IF(Variable_name='Created_tmp_disk_tables', Variable_value, 0)) AS tmp_on_disk
FROM performance_schema.global_status
WHERE Variable_name LIKE 'Created_tmp%';

-- WHICH queries are doing it? This is the actionable one.
SELECT LEFT(digest_text, 70) AS query,
       count_star, sum_created_tmp_disk_tables AS disk_tmp,
       ROUND(avg_timer_wait/1e9, 1) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_created_tmp_disk_tables > 0
ORDER BY sum_created_tmp_disk_tables DESC LIMIT 10;

-- Is ibtmp1 growing without bound? It only shrinks on restart.
SELECT file_name, ROUND(file_size/1024/1024) AS mb
FROM information_schema.files WHERE file_name LIKE '%ibtmp%';