MySQL Performance Tuning: Slow Queries, InnoDB & Config

Our MySQL performance tuning support team works cases in a fixed order: measure, read the plan, fix the index, then adjust configuration. This page documents that order with the variable names as they exist in MySQL 8.4. Most slow MySQL servers are fixed by an index or a query rewrite, not by a my.cnf change; the configuration section covers the small set of settings that actually move throughput.

Measure first: the slow query log

The slow query log records statements that take longer than long_query_time seconds to execute. It is disabled by default. Enable it at runtime:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- optional: also log queries that use no index
SET GLOBAL log_queries_not_using_indexes = 'ON';

Persist the same settings in my.cnf under [mysqld] so they survive a restart, or use SET PERSIST instead of SET GLOBAL. Notes on the variables:

• long_query_time defaults to 10 seconds, which hides almost everything worth finding. 1 second is a normal working threshold; 0 logs every statement and is only for short capture windows because of the write volume.
• log_queries_not_using_indexes is noisy on schemas with small lookup tables; min_examined_row_limit filters out statements that examined fewer rows than the limit.
• log_output chooses FILE, TABLE (mysql.slow_log), or both.

Raw slow logs repeat the same statement thousands of times with different literals. Aggregate before reading:

# ships with MySQL
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# Percona Toolkit; groups by fingerprint, ranks by total time
pt-query-digest /var/log/mysql/mysql-slow.log

mysqldumpslow sorts by total time with -s t; pt-query-digest additionally reports per-query row-examined versus row-sent ratios, which is the fastest indicator of a missing index. Tuning without this step is guessing: the top two or three fingerprints in the digest usually account for most of the server's load.

EXPLAIN basics

Run EXPLAIN on each statement the digest surfaces. Three columns settle most cases:

• type — the join access method, from best to worst: system, const, eq_ref, ref, range, index, ALL. ALL is a full table scan; index is a full index scan, which is smaller but still a scan. On any table beyond a few thousand rows, ALL in a frequently-run query is the problem.
• rows — the optimizer's estimate of rows examined per table. Multiply across joined tables for the rough work per execution. A query returning 20 rows while examining 2 million is an index problem, not a hardware problem.
• key — the index actually chosen. NULL means no index was usable; compare with possible_keys to see whether an index existed but was skipped (commonly because a function wraps the column, the datatype forces a cast, or the leftmost prefix rule below is not met).

EXPLAIN ANALYZE executes the statement and reports actual row counts and timing per plan node, which exposes bad estimates that plain EXPLAIN hides.

Indexing rules

• Leftmost prefix. A composite index on (a, b, c) serves queries filtering on a; on a and b; or on a, b, and c. It does not serve a query filtering only on b or only on c. Column order in the index is determined by the queries, not by the table definition.
• Equality columns before range columns. In a composite index, put columns compared with = first; the index stops narrowing after the first range condition.
• Covering indexes. If the index contains every column the query reads, InnoDB answers from the index alone without visiting the row. EXPLAIN shows Using index in the Extra column. For hot queries that select two or three columns, extending an existing index to cover them is often the cheapest large win.
• Do not index everything. Every secondary index is maintained on every INSERT, UPDATE, and DELETE, consumes buffer pool space, and widens the optimizer's choice surface. Unused indexes are pure cost; the monitoring section below shows how to find them.
• Keep expressions off indexed columns. WHERE DATE(created_at) = '2026-08-01' cannot use an index on created_at; a range predicate on the raw column can. The same applies to implicit casts from mismatched datatypes or collations in join conditions.

InnoDB configuration

InnoDB is the default storage engine, and its buffer pool is the single most important memory setting on the server.

Buffer pool size

innodb_buffer_pool_size sets the cache for table and index data. The MySQL documentation notes that on dedicated database servers the buffer pool is often set to 50 to 75 percent of system memory, and common field guidance runs to roughly 70 to 80 percent on hosts that do nothing but run MySQL. The default of 128MB is a development setting, not a production one. Leave headroom for per-connection buffers, the operating system, and any other processes on the host; a buffer pool large enough to push the server into swap is worse than a smaller one. The variable is dynamic since 5.7 and can be resized without a restart. Check effectiveness with the buffer pool hit ratio: Innodb_buffer_pool_reads (disk reads) climbing fast relative to Innodb_buffer_pool_read_requests means the working set does not fit.

Redo log capacity

From MySQL 8.0.30, redo log sizing is a single dynamic variable, innodb_redo_log_capacity, replacing innodb_log_file_size and innodb_log_files_in_group. The default is 100MB. Write-heavy workloads with an undersized redo log show aggressive flushing and stalls; the documented sizing check is to compare redo generated during peak hour (via Innodb_redo_log_current_lsn deltas or the innodb_redo_log_capacity_resized status) against capacity, keeping roughly an hour of redo. Larger capacity smooths write bursts at the cost of longer crash recovery.

Flush settings

innodb_flush_log_at_trx_commit trades durability for commit throughput:

Replicas rebuilt from the source, bulk-load windows, and workloads that tolerate a second of loss can run 2 or 0 for a large commit-rate gain. Systems of record stay at 1. innodb_flush_method = O_DIRECT is the usual companion on Linux to avoid double-buffering data files through the OS page cache.

Connection problems: too many connections

The error Too many connections means max_connections (default 151) is exhausted. mysqld reserves one extra connection for accounts with the CONNECTION_ADMIN privilege, which is how an administrator gets in to diagnose. The fix flow:

1. Connect as an administrative account and run SHOW PROCESSLIST. Identify what is holding connections: an application that leaks them, a stalled query pile-up, or genuine load.
2. If a query pile-up is the cause, kill the blocking statements and fix that query; raising the limit only delays the same failure.
3. If connection count is legitimately high, raise max_connections with SET PERSIST max_connections = 500; — but first check memory. Each connection allocates its own thread and per-session buffers (join, sort, read buffers), so worst-case memory is roughly buffer pool plus max_connections times per-session allocation. Raising the limit on a memory-tight host converts a connection error into OOM kills.
4. Reduce wait_timeout if idle application connections sit for hours holding slots.

The durable fix for high-connection-count applications is pooling — in the application framework, or in front of the server with ProxySQL or MySQL Router, which multiplex thousands of client connections onto a small backend pool.

Other high-impact settings

Variable names below are current in the 8.4 server system variables reference. The query cache does not appear here because it was removed in MySQL 8.0; query_cache_size advice found in older articles no longer applies.

Schema-level wins

• Datatypes. Smaller rows mean more rows per page, more of the table in the buffer pool, and smaller indexes. INT for values that fit SMALLINT, VARCHAR(255) as a reflex, and BIGINT primary keys on small tables all cost real memory at scale. Matching datatypes across join columns avoids casts that disable index use.
• Primary key choice. InnoDB clusters the table on the primary key and stores it in every secondary index; a compact, monotonic primary key keeps secondary indexes small and inserts sequential.
• Partitioning helps range-pruned queries and bulk deletion of old data by partition drop; it is not a substitute for correct indexing.

Monitoring: performance_schema and sys

The Performance Schema is enabled by default, and the sys schema presents it in readable form. The views used most in tuning work:

• SELECT * FROM sys.statements_with_full_table_scans LIMIT 10; — statements scanning without indexes, ranked.
• SELECT * FROM sys.statement_analysis LIMIT 10; — normalized statements ordered by total latency; the same view of load as pt-query-digest, without needing the log file.
• SELECT * FROM sys.schema_unused_indexes; — indexes with no reads since server start; candidates for removal after a full business cycle of uptime.
• SELECT * FROM sys.schema_tables_with_full_table_scans; — tables being scanned, with row counts.
• SELECT * FROM sys.memory_global_total; — instrumented memory use, for validating the buffer pool plus per-connection math above.

These views make the measure step continuous: the slow query log finds problems after the fact, sys shows them accumulating live.

When tuning is not enough

• Storage and memory limits are real. If the working set is far larger than RAM and the digest shows well-indexed queries waiting on I/O, the fix is memory or faster storage, not configuration.
• Mixed workloads fight each other. Analytics queries scanning large ranges evict the OLTP working set from the buffer pool. Move reporting to a replica or a separate system.
• Read scaling. Replication with read routing distributes read load; version and topology considerations are covered in MySQL versions.
• Operational coverage. Ongoing tuning, monitoring, and incident response for MySQL is a standard engagement for our remote DBA services team.