Performance Tuning Interview Questions

Temporary tables spill to disk when tmp_table_size / max_heap_table_size are too small or the query needs a MEMORY-incompatible type (TEXT, wide VARCHAR in older versions). Extra: Using temporary; Using filesort is the EXPLAIN hint. Temptable engine in 8.0 changes the spill path. Per-session sort_buffer_size exploding under connection storms is the related RAM trap.

thread_pool (Enterprise) or a proxy that multiplexes connections stops 10k sleeping app connections from creating 10k OS threads. thread_cache_size only reuses ended threads, it does not cap concurrent ones. max_connections too high with tiny innodb_buffer_pool causes thrashing. Connection pooling in the app plus a sane wait_timeout is the open-source default.

Doublewrite buffer protects against torn pages; disabling it for raw devices or when the filesystem provides atomic 16K writes (innodb_doublewrite=OFF / DETECT_ONLY) is a specialist move. On cloud GPUs/disks with 4K atomicity you still want it. A torn page after crash recovery is silent corruption until CHECK TABLE. Clone and XtraBackup assume doublewrite semantics.

Read-your-writes plus innodb_flush_neighbors=0 on SSD avoids wasted sequential flushes of nearby pages. innodb_flush_method=O_DIRECT skips the OS cache double buffering with the buffer pool. On cloud network disks, fsync latency dominates innodb_flush_log_at_trx_commit=1. Measure redo fsync time in InnoDB status, not disk %util from a noisy neighbor.

innodb_adaptive_hash_index_parts and buffer pool instances (innodb_buffer_pool_instances) split latches so many CPUs do not serialize on one LRU. Undersizing instances on a 64GB pool is a 5.7-era contention classic. Too many instances on a small pool wastes memory. PERFORMANCE_SCHEMA wait/synch/mutex/innodb/buf_pool_mutex tells you if you guessed wrong.

Group replication / semi-sync ack wait shows up as a commit stall; tuning rpl_semi_sync_source_timeout or group_replication_flow_control_mode is a throughput knob, not an InnoDB buffer-pool knob. Threads_running high with CPU idle is the tell. Losing the ACK replica silently falls back to async and your p99 magically improves—that is a durability incident, not a win.

innodb_buffer_pool_size should be 50–70% of RAM on a dedicated server so the clustered working set stays in memory. Too large causes OS swapping, which is worse than a slightly smaller pool. Multiple innodb_buffer_pool_instances reduce mutex contention on big pools. Warm the pool (dump/load or buffer pool dump) after restart before judging QPS.

innodb_log_file_size (or redo log capacity in 8.0.30+) controls how much dirty buffer-pool data can sit before a sharp checkpoint stall. Tiny redo forces aggressive flushing and kills write QPS. Huge redo delays crash recovery. Watch log wait / checkpoint age in SHOW ENGINE INNODB STATUS, not just CPU.

The slow query log plus long_query_time captures statements that miss the buffer pool or filesort, including admin SQL. log_slow_extra (8.0) adds Rows_examined. PERFORMANCE_SCHEMA events_statements_summary_by_digest aggregates without a file. pt-query-digest on the slow log is still the practical interview answer when P_S is off.

PERFORMANCE_SCHEMA waits (file_io, innodb_row_lock, stages) show why a thread is blocked, not just the plan it would pick. Wait/io/table/sql/handler versus wait/synch/mutex/innodb splits IO from contention. Enabling every consumer has overhead—start with waits and statements. sys.innodb_lock_waits is the readable layer on top.

innodb_flush_log_at_trx_commit=1 fsyncs redo on every commit (durable); 2 flushes to OS cache once per second; 0 is even weaker. Combined with sync_binlog=1 you get full crash-safe replication source behavior. Group commit batches fsyncs so 1 is often affordable. Semi-sync ACK wait can dwarf the fsync cost.

innodb_io_capacity and innodb_io_capacity_max tell the background page cleaner how hard it may hit the device. Set them from measured IOPS, not from a blog’s SSD number. Too low delays checkpointing; too high starves foreground reads. Combine with innodb_flush_neighbors=0 on SSD. The cleaner cannot outrun a tiny redo log.

sys.schema (views over PERFORMANCE_SCHEMA) such as statements_with_full_table_scans and io_global_by_file_by_bytes beat raw P_S SQL in an interview. host_summary_by_statement_latency finds a noisy neighbor. innodb_buffer_stats_by_table shows which table occupies the pool. Enable the required P_S instruments or the views are empty.

Adaptive hash index (AHI) caches B-tree lookups in a hash table; it helps point queries on a hot working set and hurts under many range scans (latch contention). SHOW ENGINE INNODB STATUS has hash searches versus non-hash. Disabling AHI is a common OLAP-on-primary fix. It is not a replacement for a covering secondary index.

sort_buffer_size is per-session; raising it globally for filesort can explode RAM under a connection spike. Prefer an index that removes Using filesort. Join_buffer_size has the same per-thread trap. MEASURE with PERFORMANCE_SCHEMA memory/sql/sort_sort_buffer before copying a my.cnf from a blog.

table_open_cache and table_definition_cache too small cause mutex storms on .frm/.ibd opens, visible as wait/synch/mutex/sql/LOCK_open or high Opened_tables versus Open_tables. Partitioned tables multiply file handles. Raising the cache without raising the OS nofile limit just fails opens. This is not an innodb_buffer_pool problem.

Redo log wait (log wait in SHOW ENGINE INNODB STATUS) means the buffer pool is dirty faster than checkpointing can advance, so writes stall on redo space. Enlarge redo capacity or raise io_capacity, then find the write query with the slow log. A long transaction also delays checkpoint. This is not replica lag—it is local durability backpressure.

Histogram-driven estimates plus eq_range_index_dive_limit change whether the optimizer dives into a B-tree for equality lists (IN (...)). Too many dives on a huge IN list stall parse; too few pick a bad index. Histograms on skewed columns reduce dive need. Pinning an index with a hint hides a stats problem you should ANALYZE instead.

Query rewrite plugin or a proxy can inject hints without changing app SQL; overusing it hides schema debt. It is valid for a vendor query you cannot patch. Rewrites must be versioned like schema. EXPLAIN the rewritten text, not the original digest. It will not fix a cold innodb_buffer_pool after failover.

Memory instrumentation in PERFORMANCE_SCHEMA (memory/innodb/buf_buf_pool) confirms what InnoDB allocated versus innodb_buffer_pool_size. OS reclaim (transparent huge pages, NUMA) can still add latency. innodb_buffer_pool_dump_at_shutdown speeds warmup but is not extra RAM. If swapped, shrink the pool—do not raise it because hit rate is 99%.