The buffer pool caches 16 KB table and index pages, making innodb_buffer_pool_size (default 128 MB) the key setting. Start at 50-75% of a dedicated host's RAM; --innodb-dedicated-server picks 128 MB below 1 GB of detected memory, half up to 4 GB, 75% above. It also sets innodb_redo_log_capacity to (logical CPUs / 2) GB, at most 16 GB, unless you set either explicitly.
In a container, while the 9.7 variable container_aware is OFF (the default), the server sizes itself from the host. With --memory=6g --cpus=4 on this 62 GB, 36-CPU machine it chose a 48,384 MB pool, eight times the limit, plus 16 GB of redo. --container-aware=ON fixed the pool at 4,608 MB, but a --cpus quota still showed all host CPUs; --cpuset-cpus=0-3 brought the redo log down to 2 GB.
Resizing is online, in 128 MB chunks, with progress in Innodb_buffer_pool_resize_status. To test a size, count page requests and disk reads around scans of page_views (723 MB):
CREATE VIEW bp_io AS SELECT
SUM(IF(VARIABLE_NAME = 'Innodb_buffer_pool_read_requests', VARIABLE_VALUE, 0)) AS requests,
SUM(IF(VARIABLE_NAME = 'Innodb_buffer_pool_reads', VARIABLE_VALUE, 0)) AS disk_reads,
SUM(IF(VARIABLE_NAME = 'Innodb_buffer_pool_read_ahead', VARIABLE_VALUE, 0)) AS read_ahead
FROM performance_schema.global_status;
CREATE TABLE bp_runs (pool_mb INT, pass INT, requests INT, disk_reads INT, read_ahead INT);
DELIMITER //
CREATE PROCEDURE scan_pass(p INT)
BEGIN
DECLARE q0, r0, a0 BIGINT;
SELECT requests, disk_reads, read_ahead INTO q0, r0, a0 FROM bp_io;
SELECT SUM(LENGTH(referrer)) INTO @ignored FROM page_views; -- full clustered-index scan
INSERT INTO bp_runs SELECT @@innodb_buffer_pool_size DIV 1048576, p,
requests - q0, disk_reads - r0, read_ahead - a0 FROM bp_io;
END //
DELIMITER ;
SET GLOBAL innodb_buffer_pool_size = 256 * 1024 * 1024; -- shrink online from 4.5 GB
DO SLEEP(5); -- the resize runs in the background
CALL scan_pass(1); CALL scan_pass(2);
SET GLOBAL innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024;
DO SLEEP(5);
CALL scan_pass(1); CALL scan_pass(2);
SELECT *, ROUND(100 * (1 - disk_reads / requests), 2) AS naive_hit,
ROUND(100 * (1 - (disk_reads + read_ahead) / requests), 2) AS real_hit FROM bp_runs;+---------+------+----------+------------+------------+-----------+----------+ | pool_mb | pass | requests | disk_reads | read_ahead | naive_hit | real_hit | +---------+------+----------+------------+------------+-----------+----------+ | 256 | 1 | 106682 | 653 | 36018 | 99.39 | 65.63 | | 256 | 2 | 106421 | 887 | 35563 | 99.17 | 65.75 | | 2048 | 1 | 99761 | 1279 | 28516 | 98.72 | 70.13 | | 2048 | 2 | 69976 | 1 | 0 | 100.00 | 100.00 | +---------+------+----------+------------+------------+-----------+----------+
The popular ratio 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests claimed 99% for a pool a third the table's size: it ignores pages fetched by read-ahead. At 256 MB each scan re-read 36,000 pages; at 2 GB the second read none. Size for the working set.