MySQL InnoDB tuning for 1-8 GB servers

On a MySQL server with 1 to 8 GB of memory, three settings decide most of the outcome: innodb_buffer_pool_size, max_connections and innodb_redo_log_capacity. On MySQL 8.4 there are two more that small servers trip over and big ones never notice: temptable_max_ram, whose new default is never less than 1 GB, and the binary log, which is on by default and keeps 30 days of history. Set the buffer pool to roughly half the memory on a 1-2 GB server and 60-70% above that, keep connections in the tens rather than the hundreds, size the redo log in the hundreds of megabytes, cap in-memory temporary tables, and shorten binary log retention. Everything else is either fine at its default or worth less than one missing index.

The reason this matters more on a small machine is the failure mode. A large, slightly misconfigured server is slower than it could be. A small one that is misconfigured runs out of memory, and running out of memory is not a slowdown: the kernel stops the process. Tuning a small MySQL server is mostly the job of spending a fixed memory budget deliberately.

What the defaults assume#

MySQL's defaults are chosen to start anywhere, not to perform anywhere. Several changed in 8.4, which is worth knowing if you are following advice written for 8.0.

The buffer pool default of 128 MB is the clearest example of a value nobody should keep. On a 4 GB server it leaves 90% of the memory to the operating system cache, which InnoDB bypasses anyway because 8.4 defaults to innodb_flush_method=O_DIRECT on Linux. Data that does not fit in the buffer pool gets read from disk, and the memory you are paying for sits idle.

The query cache, the subject of a great deal of old tuning advice, was removed in MySQL 8.0. If a guide tells you to set query_cache_size, it was written for 5.7, and the server will refuse to start with that line in its configuration.

The memory budget#

Think of MySQL's memory in three parts: what is allocated once (the buffer pool, the log buffer, performance schema), what each connection can take while it runs a query, and what the operating system needs. The worst case is roughly:

buffer pool+ log buffer (64 MB in 8.4)+ performance_schema (often 100-250 MB)+ max_connections x (per-thread buffers + thread stack)+ in-memory temporary tables+ a margin for the OS and everything else

The per-connection buffers are small by default - sort_buffer_size 256 KB, join_buffer_size 256 KB, read_buffer_size 128 KB, read_rnd_buffer_size 256 KB, thread_stack 1 MB - and are allocated only when a query needs them. That makes an idle connection cheap and a busy one a few megabytes. The danger is raising them globally: sort_buffer_size = 64M looks harmless until fifty connections sort at once. Raise them per session for the query that needs it:

SET SESSION sort_buffer_size = 16 * 1024 * 1024;SELECT ... ORDER BY ...;   -- the one report that sorts a lot

To see where memory actually goes on a running server, the sys schema summarises the performance schema's memory instruments:

SELECT event_name, current_allocFROM sys.memory_global_by_current_bytesLIMIT 10;SELECT * FROM sys.memory_global_total;

innodb_buffer_pool_size#

The buffer pool caches table and index pages. A hit is a memory read; a miss is a disk read. On a dedicated database server it is the single biggest use of memory, and the usual advice of "70-80% of RAM" assumes a large machine where the remaining 20% is several gigabytes. On a small one, the fixed costs above eat a larger share, so the percentage comes down.

These are starting points, not truths. A database smaller than the buffer pool does not need a bigger one - check how much data you actually have:

SELECT table_schema,       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mbFROM information_schema.tablesGROUP BY table_schemaORDER BY size_mb DESC;

If the whole database is 300 MB, a 1 GB buffer pool holds all of it and the rest is wasted. If it is 20 GB on a 4 GB server, what matters is whether the working set - the rows touched in a normal hour - fits. The miss rate tells you:

SELECT  (SELECT variable_value FROM performance_schema.global_status   WHERE variable_name = 'Innodb_buffer_pool_reads') AS disk_reads,  (SELECT variable_value FROM performance_schema.global_status   WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS logical_reads;

disk_reads divided by logical_reads should be well under 1% on a warmed-up server. Sample it twice an hour apart and compare the differences, not the totals since start-up. A persistently high ratio on a busy system is the honest signal that the working set does not fit, and either an index is missing or the plan is too small.

The buffer pool is resizable online in 8.x. The size is rounded up to a multiple of innodb_buffer_pool_chunk_size (128 MB) times innodb_buffer_pool_instances, which in 8.4 is 1 for pools of 1 GB or less and calculated from size and CPU count above that:

SET PERSIST innodb_buffer_pool_size = 2560 * 1024 * 1024;SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

Resizing takes a little while and briefly blocks access to pages being moved; do it at a quiet time.

innodb_dedicated_server=ON tells MySQL to size the buffer pool (50% of memory between 1 and 4 GB, 75% above) and redo log itself. It sizes from the memory it detects, and the documentation does not promise that detection honours a container's limit, so in a container set the values explicitly instead.

innodb_redo_log_capacity#

Every change to InnoDB data is written to the redo log first and flushed to the data files later, at checkpoints. A redo log that is too small forces frequent checkpoints, which means bursts of writes and stalls when a write-heavy moment fills it. Too large wastes disk and lengthens crash recovery.

Since MySQL 8.0.30 the redo log is sized by one variable, innodb_redo_log_capacity, and it is dynamic. The older pair innodb_log_file_size and innodb_log_files_in_group are deprecated; if they are set and innodb_redo_log_capacity is not, the server derives the capacity from them, but new configuration should use the single setting. The redo files live in #innodb_redo inside the data directory.

SET PERSIST innodb_redo_log_capacity = 512 * 1024 * 1024;

The default 100 MB is too small for anything but light write loads. A useful way to size it is to measure how much redo your peak hour writes:

SELECT variable_value INTO @a FROM performance_schema.global_statusWHERE variable_name = 'Innodb_redo_log_current_lsn';DO SLEEP(60);SELECT ROUND((variable_value - @a) / 1024 / 1024, 1) AS redo_mb_per_minuteFROM performance_schema.global_statusWHERE variable_name = 'Innodb_redo_log_current_lsn';

Run that at a busy time. A capacity that holds 30 to 60 minutes of peak writes is generous. On a small disk, cap it: 2 GB of redo on a 10 GB volume is a fifth of your space. The table above stays inside that.

Connections, threads and timeouts#

Every connection is a thread with its own stack and buffers. A server with one or two vCPUs cannot run more than a handful of queries at the same moment; connections beyond that wait, holding memory while they do. max_connections = 151 on a 1 GB server is a promise the memory cannot keep if all of them become busy at once.

• Size application pools first. Ten connections per application process covers most web workloads.
• Add up every process, worker and cron job that connects. That total, plus a margin, is max_connections.
• MySQL reserves one extra connection beyond the limit for an account with CONNECTION_ADMIN, so root can still log in to investigate a "Too many connections" incident.
• thread_cache_size (auto-sized by default) keeps threads for reuse; leave it.

SET PERSIST max_connections = 60;SHOW GLOBAL STATUS LIKE 'Max_used_connections';SHOW GLOBAL STATUS LIKE 'Threads_connected';

Max_used_connections is the high-water mark since start-up, which tells you whether the limit is anywhere near being reached. wait_timeout (28800 seconds) closes idle connections after eight hours; lowering it to 600 or so reclaims connections leaked by applications that open and abandon them, but make sure your pool recycles connections faster than that. The full argument for small limits and pools is in MySQL connection limits and pooling.

Temporary tables and the 8.4 temptable default#

Queries with GROUP BY, DISTINCT, UNION, derived tables or certain sorts build internal temporary tables. MySQL 8 holds them in memory with the TempTable engine up to temptable_max_ram in total, and spills to on-disk InnoDB temporary tables beyond that.

In 8.4 the default temptable_max_ram became 3% of total memory, but never less than 1 GB and never more than 4 GB. On a 1 GB or 2 GB server, that floor means temporary tables are allowed to use as much memory as the whole plan or half of it. One bad report query can then push the server over its limit. Cap it to something your budget can absorb:

SET PERSIST temptable_max_ram = 64 * 1024 * 1024;   -- 1 GB serverSET PERSIST tmp_table_size = 32 * 1024 * 1024;

Spilling to disk is slower but survivable; running out of memory is neither. The queries that build big temporary tables are marked Using temporary in EXPLAIN, and they tend to be the same ones that turn up in the slow query log. SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable%' shows how much the engine holds right now.

Durability, flushing and the binary log#

innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_flush_method = O_DIRECTinnodb_io_capacity = 10000

innodb_flush_log_at_trx_commit = 1 flushes the redo log to disk at every commit, so a committed transaction survives a crash. Setting it to 2 writes at commit but flushes once a second, which is a real speed-up for write-heavy loads and means a crash of the machine (not just of MySQL) can lose about a second of commits. It cannot corrupt the database. That makes it reasonable for logging and analytics, and wrong for orders and payments. sync_binlog is the same trade for the binary log.

innodb_io_capacity tells InnoDB how fast it can flush in the background. 8.0's default of 200 was written for spinning disks and is far too low for NVMe; if you run 8.0, raise it to around 2000. 8.4's default of 10000 assumes fast flash, which is what NVMe is.

The binary log needs a sentence of its own on a small disk. It is enabled by default since 8.0, records every change for replication and point-in-time recovery, and keeps 30 days. On a write-heavy database with a 10 or 20 GB volume, the binary logs can grow larger than the data. If you do not replicate and do not do point-in-time recovery, shorten retention:

SET PERSIST binlog_expire_logs_seconds = 259200;   -- 3 daysSHOW BINARY LOGS;PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

Disabling binary logging entirely (skip-log-bin or disable-log-bin) needs a start-up option in the configuration file, not SET PERSIST. expire_logs_days, the old setting in days, was removed in 8.4.

Applying settings: SET PERSIST and where values come from#

In MySQL 8 you rarely need to edit my.cnf to tune a server. SET PERSIST changes a dynamic variable now and records it in mysqld-auto.cnf in the data directory, which is read at start-up after the regular option files, so the change survives restarts. It needs SYSTEM_VARIABLES_ADMIN, which the root account has.

-- Where does each value come from?SELECT v.variable_name, v.variable_source, v.set_time, g.variable_valueFROM performance_schema.variables_info vJOIN performance_schema.global_variables g USING (variable_name)WHERE v.variable_name IN ('innodb_buffer_pool_size', 'max_connections',                          'innodb_redo_log_capacity', 'temptable_max_ram');-- Everything persisted so farSELECT * FROM performance_schema.persisted_variables;-- Undo a persisted setting (takes effect at next restart)RESET PERSIST temptable_max_ram;

variable_source reads COMPILED (the built-in default), GLOBAL or SERVER (an option file), PERSISTED or DYNAMIC (set at runtime). Variables that cannot change at runtime, such as performance_schema or innodb_buffer_pool_chunk_size, accept SET PERSIST_ONLY, which records the value for the next start without applying it; that needs the extra PERSIST_RO_VARIABLES_ADMIN privilege.

On RE:NODE you get the root password for your MySQL server, so SET PERSIST is available for everything above. Change one setting at a time, and take a backup before any restart - restoring is a button, and backup slots come with every MySQL plan.

Measure, then tune#

Configuration recovers percentages. A missing index costs orders of magnitude. Before changing anything in this post, turn on the slow query log for a day and look at what it catches, and read the plan of the worst query with EXPLAIN and EXPLAIN ANALYZE. Watch the memory graph rather than the averages - reading a server load graph explains why the peak is the number that kills you. And if the measurements say the working set simply does not fit, the honest answer is a bigger plan: when to upgrade your plan. PostgreSQL users will find the same reasoning applied there in PostgreSQL tuning for small servers.

FAQ#

How big should innodb_buffer_pool_size be on a 2 GB server?

About 1 GB, leaving room for connections, temporary tables, performance schema and the operating system. If your data is smaller than that, match the buffer pool to the data plus some growth and keep the memory free.

Do I still need innodb_log_file_size?

No. Since MySQL 8.0.30 the redo log is sized with innodb_redo_log_capacity, which can be changed at runtime. innodb_log_file_size and innodb_log_files_in_group are deprecated and only used if the new variable is not set.

Should I turn off performance_schema to save memory?

On a 1 GB server it can save a meaningful slice, and it is a start-up setting, so it needs a restart. The cost is losing the sys schema views, statement digests and memory accounting that make the rest of this post measurable. Keep it on unless memory is the only thing standing between you and a smaller plan.

Why is MySQL using more memory than the buffer pool?

Because the buffer pool is only the largest piece. Connections, temporary tables, the log buffer, performance schema, the table cache and the server's own code all add to it. sys.memory_global_total shows the instrumented total; the container's memory graph shows the real one.

Is innodb_dedicated_server a good idea on a small plan?

Not inside a container. It sizes the buffer pool from the memory it detects, which may not be the limit the container enforces. Set the buffer pool and redo log capacity explicitly.