MENU


Server Tuning and the Performance Schema

Sections 3.11 and 3.12 made single queries cheaper. This section tunes the server under them and shows how to find the statements worth fixing. The method never changes: read a counter, change one variable, apply a load, read the counter again. All numbers come from a small mysql:9.7 container (9.7.2, 6 GB, four CPUs) under WSL2 on this book's Windows 11 PC, holding shop plus a 3-million-row page_views table. Absolute figures differ on real servers; the direction of each effect does not. Persist what you keep with SET PERSIST or the option file (Section 3.1.8).

3.13.1 The InnoDB Buffer Pool and How to Size It

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;

Listing 3.116: Buffer pool reads around a full scan, with a 256 MB and a 2 GB pool

+---------+------+----------+------------+------------+-----------+----------+

| 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 |

+---------+------+----------+------------+------------+-----------+----------+

Output of Listing 3.116 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.

3.13.2 Buffer Pool Instances, Warm-Up, and the Removed Query Cache

innodb_buffer_pool_instances splits the pool into regions with their own mutexes. At startup it is 1 up to a 1 GB pool, otherwise the smaller of half the chunk count and a quarter of the logical CPUs (9 with 36 CPUs visible, 1 with four). Keep the default.A restart empties the pool, so at shutdown InnoDB saves the page IDs of the hottest 25% (innodb_buffer_pool_dump_pct) to ib_buffer_pool (93 KB here) and reloads them at startup. innodb_buffer_pool_dump_now dumps on demand. After docker restart:

SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status

WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_load_status', 'Innodb_buffer_pool_pages_data');

SET GLOBAL query_cache_size = 0;

Listing 3.117: Warm-up status after a restart, and the query cache

+--------------------------------+--------------------------------------------------+

| VARIABLE_NAME | VARIABLE_VALUE |

+--------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 260923 8:12:47 |

| Innodb_buffer_pool_pages_data | 12324 |

+--------------------------------+--------------------------------------------------+

ERROR 1193 (HY000) at line 3: Unknown system variable 'query_cache_size'

Output of Listing 3.117 The query cache stored result sets keyed by exact SQL text behind one global mutex, and any write discarded the table's entries, so busy servers ran slower with it. It was removed in 8.0. Only have_query_cache (always NO) remains, and an option file that still sets query_cache_type stops mysqld with unknown variable. Cache results in Redis or Memcached (Sections 4.22.11 and 5.16.7), in ProxySQL, or in a summary table (Section 3.15.6).

3.13.3 Redo Log Capacity and Flush Behavior

InnoDB records each change in the sequential redo log and writes dirty pages later; recovery replays it from the last checkpoint. Too small a log forces early flushing in write bursts; a large one lengthens recovery. Since 8.0.30 the dynamic innodb_redo_log_capacity (default 100 MB) sizes it across 32 files in #innodb_redo/; in 9.7 innodb_log_file_size and innodb_log_files_in_group are gone (ERROR 1193). A SET GLOBAL to 4 GB took effect without a restart (Innodb_redo_log_resize_status read OK). A common target is one peak hour of redo: sample Innodb_redo_log_current_lsn an hour apart.How often the redo log and the binary log (on by default) reach disk is a durability choice. Eight mysqlslap clients ran 16,000 single-row autocommit inserts under each pair: trx_commit / sync_binlog Flushing A crash can lose Run time 1 / 1 (default) fsync redo and binlog at every commit Nothing committed 33.4 s 2 / 1 Redo written at commit, fsynced each second ~1 s if the OS fails 17.4 s 2 / 0 Binlog fsync left to the OS ~1 s plus binlog events 1.5 s 0 / 0 Redo written and fsynced each second ~1 s even if mysqld dies 1.1 s Table 3.6: Commit flushing settings, their risk, and 16,000 inserts on WSL2 Relaxing both was 30 times faster; the binlog fsync was half the cost. WSL2's virtual disk has slow fsync, so measure your hardware. Keep 1/1 for orders and payments (Section 3.14.1); use 2/0 only for data you can reload. Fewer, larger transactions also cut fsyncs.

3.13.4 Connections, Threads, and the Thread Cache

Community MySQL runs one OS thread per connection. max_connections (default 151) caps them, and one extra slot is kept for an account with CONNECTION_ADMIN. With the limit at 20, 30 mysqlslap clients connecting as a plain app user got 11 errors 1040 Too many connections, and Connection_errors_max_connections counted 111 refused attempts. Raising the limit is rarely the fix, since each connection has its own buffers; pool connections in the application instead (Section 4.16.16).A disconnecting client's thread waits in the thread cache (up to thread_cache_size) for reuse. The default is 8 + max_connections / 100, so 9 here. Counters read around mysqlslap --concurrency=50 --iterations=20 --query="SELECT 1": thread_cache_size Connections Threads_created Reused 0 1,003 1,003 0% 9 (default) 1,003 829 17% 64 1,003 42 96% Table 3.7: Thread cache reuse under 50 concurrent short connections Run time stayed at 0.05 s per iteration, since Linux creates threads cheaply; the cache matters at hundreds of connections per second. If Threads_created climbs in step with Connections, raise it.

3.13.5 Temporary Tables and Sort Buffers

GROUP BY, DISTINCT and UNION often need an internal temporary table (Using temporary, Section 3.12.4). It starts in the in-memory TempTable engine and moves to InnoDB on disk when it outgrows tmp_table_size (default 16 MB, per table) or TempTable as a whole passes temptable_max_ram (3% of RAM within 1-4 GB; 1 GB here). max_heap_table_size limits only the older MEMORY engine. A sort that no index provides (Using filesort) fills sort_buffer_size (256 KB), then merges sorted runs from disk (Sort_merge_passes).Two page_views queries each ran in a fresh session, read back from performance_schema.session_status: a GROUP BY referrer producing 3 million groups, and an ORDER BY viewed_at, id LIMIT 1 OFFSET 2000000: Query Session setting Time Disk temp tables Merge passes GROUP BY tmp_table_size 16M 107.61 s 1 0 GROUP BY tmp_table_size 1G 8.64 s 0 0 ORDER BY sort_buffer_size 256K 5.44 s 0 458 ORDER BY sort_buffer_size 64M 6.27 s 0 1 Table 3.8: A spilled temporary table and two sort buffer sizes The aggregate ran 12 times slower once its temporary table went to disk. The sort ran faster with the small buffer, despite 458 merge passes; the manual warns that Linux allocation slows above 256 KB and 2 MB. Watch Created_tmp_disk_tables against Created_tmp_tables, fix the query or index first (Section 3.11.4), and raise a limit only per statement, with a hint such as /*+ SET_VAR(tmp_table_size = 1073741824) */.

3.13.6 Enabling and Querying the Performance Schema

The Performance Schema instruments statements, stages, waits, memory and I/O. It is on by default (performance_schema is startup-only), and two tables steer it at runtime: setup_instruments (here 1,277 instruments, 796 enabled, 285 timed) and setup_consumers, which has statement history and digests on but the events_waits_* tables off. To study mutex waits, UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'wait/synch/mutex/innodb/%' and enable the events_waits_current consumer; option-file lines such as performance-schema-instrument make that permanent.events_statements_summary_by_digest groups statements by normalized text (literals become ?) and times them in picoseconds. After a TRUNCATE, eight mysqlslap clients cycled 480 times through five shop queries, one of them a count on the unindexed page_views.customer_id:

SELECT LEFT(DIGEST_TEXT, 38) AS digest, COUNT_STAR AS calls,

ROUND(SUM_TIMER_WAIT / 1e12, 1) AS total_s, ROUND(AVG_TIMER_WAIT / 1e9, 1) AS avg_ms,

SUM_ROWS_EXAMINED DIV COUNT_STAR AS rows_avg, SUM_NO_INDEX_USED AS no_idx

FROM performance_schema.events_statements_summary_by_digest

WHERE SCHEMA_NAME = 'shop' ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;

Listing 3.118: The costliest statement shapes in the shop database

+----------------------------------------+-------+---------+--------+----------+--------+

| digest | calls | total_s | avg_ms | rows_avg | no_idx |

+----------------------------------------+-------+---------+--------+----------+--------+

| SELECT COUNT ( * ) FROM `page_views` W | 96 | 112.1 | 1167.8 | 3000000 | 96 |

| SELECT `id` , `viewed_at` FROM `page_v | 96 | 2.6 | 26.6 | 10 | 0 |

| SELECT `o` . `id` , `o` . `status` , S | 96 | 0 | 0.5 | 25 | 96 |

| SELECT NAME , `email` FROM `customers` | 96 | 0 | 0.3 | 1 | 0 |

| SELECT `title` , `price` FROM `product | 96 | 0 | 0.2 | 13 | 96 |

+----------------------------------------+-------+---------+--------+----------+--------+

Output of Listing 3.118 One shape took 97% of the time, reading 3 million rows per call to return one number: the query to EXPLAIN (Section 3.12.2) and index (Section 3.11.4). Rank by total time, not by the slowest call.

3.13.7 The sys Schema and INFORMATION_SCHEMA

INFORMATION_SCHEMA describes objects and engine state: TABLES sizes (723 MB of data and 157 MB of indexes for page_views), INNODB_METRICS, INNODB_BUFFER_POOL_STATS (Section 3.17.1). The sys schema turns it and the Performance Schema into readable views. After the Section 3.13.6 load:

SELECT query, exec_count, avg_latency, rows_examined_avg, full_scan

FROM sys.statements_with_runtimes_in_95th_percentile WHERE db = 'shop' LIMIT 1\G

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';

Listing 3.119: Slowest statement shapes and indexes nobody used

*************************** 1. row ***************************

query: SELECT COUNT ( * ) FROM `page_ ... r_id` = ? AND `viewed_at` >= ?

exec_count: 96

avg_latency: 1.17 s

rows_examined_avg: 3000000

full_scan: *

+---------------+-------------+--------------+

| object_schema | object_name | index_name |

+---------------+-------------+--------------+

| shop | order_items | product_id |

| shop | orders | customer_id |

| shop | page_views | idx_referrer |

| shop | products | category_id |

+---------------+-------------+--------------+

Output of Listing 3.119 Three of the unused indexes back foreign keys, and counting starts at server startup, which may miss a monthly report. Make a candidate invisible first (Section 3.11.7). sys.waits_global_by_latency ranked innodb_temp_file I/O second: the on-disk GROUP BY of Section 3.13.5.MySQLTuner (github.com/major/MySQLTuner-perl (https://github.com/major/MySQLTuner-perl), GPL-3.0) reads the same counters and prints advice. Version 2.9.2 (19 August 2026) is newer than Ubuntu 26.04's 2.8.29, so download mysqltuner.pl from the repository. bench.cnf holds [client] credentials; the grep keeps four of its warnings:

perl mysqltuner.pl --defaults-file bench.cnf --noprettyicon --nogood --noinfo 2>/dev/null \

| grep -E 'Slow queries|Sorts requiring|TLS/SSL is|pool instances:'

Listing 3.120: Selected MySQLTuner 2.9.2 findings

[!!] Slow queries: 51% (1K/2K)

[!!] Sorts requiring temporary tables: 387% (1K temp sorts / 355 sorts)

[!!] InnoDB buffer pool instances: 1

[!!] TLS/SSL is disabled. Connections are unencrypted.

Output of Listing 3.120 The slow share reflects the long_query_time = 0 capture of Section 3.13.8. The full report also advised a larger sort_buffer_size, which Section 3.13.5 found slower, and it printed Current connection is encrypted above the TLS line. One pool instance is the 9.7 default on four CPUs. Treat its tuning advice as hypotheses to test.

3.13.8 The Slow Query Log and pt-query-digest

The slow query log (Section 3.17.4) writes each statement slower than long_query_time (seconds, fractions allowed, default 10) to slow_query_log_file once slow_query_log is on. log_slow_extra adds rows read, sorts and temporary tables per entry, and log_queries_not_using_indexes adds unindexed statements of any speed. The Section 3.13.6 load ran with long_query_time = 0, which logs everything: 490 entries in 15 seconds, so keep that to short windows.pt-query-digest from Percona Toolkit (github.com/percona/percona-toolkit (https://github.com/percona/percona-toolkit), GPL-2.0; sudo apt install percona-toolkit gives 3.7.1) groups entries by fingerprint and ranks them. The cut only shortens query IDs to fit the page: pt-query-digest --limit=100%:5 --report-format=profile slow.log | cut -c 1-24,43- Listing 3.121: Ranking the captured statements

# Profile

# Rank Query ID Response time Calls R/Call V/M

# ==== ================= ============== ===== ====== ===

# 1 0x6D35C40A4CEB43A 112.1023 97.7% 96 1.1677 0.08 SELECT page_views

# 2 0x0EEBB85A118EEC5 2.5522 2.2% 96 0.0266 0.00 SELECT page_views

# 3 0xD777DBF59B6F1E9 0.0411 0.0% 96 0.0004 0.00 SELECT orders order_items

# 4 0xE3B3C0F9E64EFD2 0.0216 0.0% 96 0.0002 0.00 SELECT customers

# 5 0x5CA6F8547AE7552 0.0172 0.0% 96 0.0002 0.00 SELECT products

# MISC 0xMISC 0.0001 0.0% 10 0.0000 0.0 <2 ITEMS>

Output of Listing 3.121 The ranking matches Section 3.13.6. V/M (variance to mean) flags erratic shapes. The full report adds per-query percentiles and a sample statement to paste into EXPLAIN. It also reads general logs and tcpdump captures (--type).