Configure MySQL: InnoDB, memory, connections and logging

MySQL's default configuration is fine for development but unsuited to production: 128 MB of InnoDB buffer pool, a maximum of 151 connections, no slow query log enabled. A server with these settings under-uses the available RAM, captures no slow query, and can surprise you when the load increases.

This guide shows you how to adapt the configuration to your server, starting with the most impactful parameters. Each change is tested on a real lab (MySQL 8.4 LTS, Debian 12, 2 GB of RAM) and comes with the application method that fits, SET GLOBAL for an immediate change, SET PERSIST for a persistent change, or a restart when it is mandatory.

Version used

This guide is based on MySQL 8.4 LTS on Debian 12. The version installable from the Oracle repository on 2026-10-20 is 8.4.11, and this repository also serves Debian 13 (trixie): the examples in this guide were captured on Debian 12 (bookworm), whereas the associated lab runs on Debian 13. MySQL 8.4 noticeably changed the InnoDB default values compared with MySQL 8.0, and these changes are detailed in a dedicated section.

What you will learn

• Locate the configuration files and understand the my.cnf → mysqld.cnf → mysqld-auto.cnf hierarchy
• Size InnoDB memory: innodb_buffer_pool_size, innodb_log_buffer_size
• Configure connections, the binary log, the slow query log and the network
• Apply changes live with SET GLOBAL and SET PERSIST
• Understand the new InnoDB defaults of MySQL 8.4 and when to adjust them

In what context?

You need to configure MySQL in these situations:

• You have just installed MySQL and the 128 MB buffer pool does not fit your server with 8 GB of RAM
• You are putting a server into production and must guarantee decent performance and useful logging
• You must choose which interfaces MySQL listens on: restrict it to the local machine, or open it to a remote application
• You notice slow queries and need to identify them
• You are preparing MySQL for replication and must configure the binary log and GTID

What this guide does not cover

The scope stops at the server parameters: memory, connections, journaling, network listening. Everything about accounts, privileges and TLS encryption belongs to security, and everything about replication requires its own settings that go beyond the configuration file. The mysql client is presented in Getting started with the mysql client, accounts, privileges and TLS in Security and replication in Replication. Backup and monitoring also have their own guides: Backup and restore and Monitoring and maintenance.

Prerequisites

• MySQL installed and the service running (see the Installation guide)
• Access to the root account or an account with the SYSTEM_VARIABLES_ADMIN privilege (for SET GLOBAL / SET PERSIST) and PERSIST_RO_VARIABLES_ADMIN (for SET PERSIST_ONLY on static variables)
• Know the basics of the mysql client (see the Getting started with the mysql client guide)

Where is the configuration located?

MySQL does not have one configuration file but several, read in a fixed order, and when the same parameter is defined twice the last one read wins. On top of that comes mysqld-auto.cnf, written by the server itself and overriding everything else. Knowing how to read this chain saves you an hour of wondering why a parameter changed in the right file has no effect at startup.

File hierarchy

MySQL reads its configuration files in a precise order. Check this order on your system:

mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

On Debian/Ubuntu, the typical layout after installing from the Oracle repository:

/etc/mysql/
├── my.cnf                          → Main file (includes conf.d/ and mysql.conf.d/)
├── conf.d/
│   └── mysql.cnf                   → mysql client options
└── mysql.conf.d/
    └── mysqld.cnf                  → mysqld server configuration

File	Role
/etc/mysql/my.cnf	Entry point, includes conf.d/ and mysql.conf.d/ through !includedir
/etc/mysql/mysql.conf.d/mysqld.cnf	Main server configuration, this is where you add your parameters
/var/lib/mysql/mysqld-auto.cnf	Written by SET PERSIST, takes precedence over the manual files

Swipe to see more

On RHEL/Rocky, everything is in a single /etc/my.cnf file.

[mysqld], [client], [mysql] sections

MySQL configuration files use sections in square brackets:

Section	Applies to	Example parameters
[mysqld]	The server	innodb_buffer_pool_size, max_connections, bind_address
[client]	All clients (mysql, mysqldump, mysqlimport)	port, socket, default-character-set
[mysql]	The mysql client only	prompt, pager, auto-rehash

Swipe to see more

The most important section is [mysqld], it is the one that configures the server.

mysqld-auto.cnf: where SET PERSIST writes

When you run SET PERSIST innodb_buffer_pool_size = 1073741824;, MySQL writes the value to /var/lib/mysql/mysqld-auto.cnf, a JSON file (not the classic INI format).

cat /var/lib/mysql/mysqld-auto.cnf | python3 -m json.tool | head -20

{
    "Version": 2,
    "mysql_dynamic_parse_early_variables": {
        "innodb_buffer_pool_size": {
            "Value": "1073741824",
            "Metadata": {
                "Host": "",
                "User": "root",
                "Timestamp": 1744530000
            }
        }
    }
}

Priority rule: mysqld-auto.cnf is read last and its values take precedence over mysqld.cnf and my.cnf.

Never edit mysqld-auto.cnf by hand

This file is managed by MySQL itself. If you edit it manually and break the JSON syntax, MySQL will refuse to start. Use SET PERSIST to add values and RESET PERSIST variable_name to remove them.

Applying changes

MySQL offers three methods to modify the configuration:

SET GLOBAL: hot change, not persistent

The change takes effect immediately for new sessions but will be lost at the next restart:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

SET PERSIST: hot AND persistent change

The change takes effect immediately and is written to mysqld-auto.cnf, so it will survive a restart:

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;

A command that succeeds does not prove that the value will survive: on a running server, the variables_info table does not distinguish a SET PERSIST from a SET GLOBAL, whereas performance_schema.persisted_variables does. What each check proves, and what only a restart proves, is set out in "Checking the configuration".

SET PERSIST_ONLY: for static variables

Some variables cannot be changed live (static context). SET PERSIST_ONLY writes the value to mysqld-auto.cnf without trying to apply it immediately, and it will take effect at the next restart:

SET PERSIST_ONLY innodb_buffer_pool_instances = 4;
-- Takes effect at the next restart

RESET PERSIST: removing a persisted value

RESET PERSIST removes the line from mysqld-auto.cnf but does not restore the previous value: the variable keeps its current value until the next restart, when it will take the value from the .cnf files or the compiled default. Measured on MySQL 8.4.10: performance_schema.persisted_variables becomes empty, but @@max_connections is still 300. The form without an argument erases all persisted variables at once, without confirmation.

RESET PERSIST slow_query_log;        -- removes this variable
RESET PERSIST;                        -- removes ALL persisted variables

Dynamic vs static variables

The choice between the three previous commands does not depend on you but on the variable's context, set by MySQL. A static variable will refuse SET GLOBAL whatever your privileges.

Type	Changeable live?	Method
Dynamic	Yes	SET GLOBAL or SET PERSIST
Static	No	Edit mysqld.cnf or SET PERSIST_ONLY, then restart

Swipe to see more

To find out whether a variable is dynamic, consult the server system variables reference, where each variable carries the Dynamic: Yes or Dynamic: No flag. In practice, if a SET GLOBAL fails with ERROR 1238 (HY000): Variable 'xxx' is a read only variable, the variable is static.

performance_schema.variables_info

The performance_schema.variables_info table shows the source of the current value (COMPILED, GLOBAL, PERSISTED, DYNAMIC), the origin file and the user who changed it, but it does not say whether a variable is dynamic or static.

It does not say either whether a value is persisted. Measured on MySQL 8.4: after a SET GLOBAL as after a SET PERSIST, the reported source is DYNAMIC, in both cases. It only becomes PERSISTED after a restart, when the value is read back from mysqld-auto.cnf. The performance_schema.persisted_variables table distinguishes the two cases, but without proving the return at startup: the "Checking the configuration" section draws the method from this.

Good news: innodb_buffer_pool_size has been a dynamic variable in MySQL since version 5.7. Unlike PostgreSQL, where shared_buffers requires a restart, you can resize the buffer pool live with SET GLOBAL, and MySQL will reallocate it progressively. Watch Innodb_buffer_pool_resize_status during the operation.

Configuring InnoDB memory

InnoDB is MySQL's default storage engine, and it is the one that uses most of the server's memory. Three parameters drive this consumption: the size of the page cache, the size of the redo log buffer, and the way writes reach the disk. The first two are set according to the available RAM, the third according to the type of storage.

innodb_buffer_pool_size: the 50-70% rule

The most important parameter. The buffer pool is InnoDB's central memory cache, it stores the data and index pages read from disk. The bigger it is, the less disk I/O MySQL does.

Default value: 128 MB, far too low for production.

Sizing rule: between 50 and 70% of total RAM on a server dedicated to MySQL. On our lab with 2 GB of RAM:

SET PERSIST innodb_buffer_pool_size = 1073741824;  -- 1 GB

The value must be expressed in bytes (no M or G suffix with SET). In a my.cnf file, suffixes are accepted:

[mysqld]
innodb_buffer_pool_size = 1G

Why 50-70% and not 90%? Because MySQL needs memory for other structures: the thread cache, the sort buffers, temporary tables, the binary log cache, and the operating system needs its own page cache.

Checking buffer pool usage:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

The key metrics:

Counter	Meaning
Innodb_buffer_pool_read_requests	Reads served from the buffer pool (cache hit)
Innodb_buffer_pool_reads	Reads requiring disk access (cache miss)
Innodb_buffer_pool_pages_total	Total pages in the buffer pool
Innodb_buffer_pool_pages_free	Free pages (if 0, the buffer pool is full)

Swipe to see more

The optimal hit ratio:

SELECT
  ROUND((1 - (Reads / Read_requests)) * 100, 2) AS buffer_pool_hit_ratio
FROM (
  SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS Reads,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS Read_requests
) AS stats;

A hit ratio above 99% is the goal. Below 95%, increase the buffer pool.

innodb_buffer_pool_instances

The buffer pool is split into instances to reduce contention between threads. The default values suit most servers: only change them if your measurements show a contention problem.

innodb_log_buffer_size

Memory buffer used by InnoDB before redo log entries are written to disk.

MySQL 8.4 default value: 64 MiB (versus 16 MiB in MySQL 8.0)

This value suits most workloads. Increase it if you run large transactions (LOAD DATA, bulk inserts, large batches), to limit intermediate flushes of the log buffer:

SET PERSIST innodb_log_buffer_size = 134217728;  -- 128 MB

innodb_flush_method

Controls how MySQL writes data and logs to disk.

MySQL 8.4 default value: O_DIRECT, data is written directly to disk without going through the operating system cache. It is the right choice for most Linux systems with fast storage.

Value	Behavior	Recommendation
O_DIRECT	Bypasses the OS cache (8.4 default)	Recommended, avoids double buffering
fsync	Goes through the OS cache then fsync	Former default, less efficient
O_DIRECT_NO_FSYNC	Like O_DIRECT but without fsync for the redo logs	Risk of loss if the storage has no battery-protected cache

Swipe to see more

The new InnoDB defaults of MySQL 8.4

MySQL 8.4 changed several InnoDB default values to better match modern workloads. If you migrate from MySQL 8.0, these changes can affect performance:

Parameter	Old default (8.0)	New default (8.4)	Impact
innodb_io_capacity	200	10,000	MySQL writes dirty pages more aggressively
innodb_io_capacity_max	2 × io_capacity	2 × io_capacity (= 20,000)	Higher I/O ceiling
innodb_change_buffering	all	none	The change buffer is disabled
innodb_adaptive_hash_index	ON	OFF	The adaptive hash index is disabled
innodb_flush_method	fsync	O_DIRECT	Bypasses the OS cache by default
innodb_log_buffer_size	16 MiB	64 MiB	Redo log buffer quadrupled
innodb_doublewrite_pages	64	128	More pages in the doublewrite buffer
innodb_redo_log_capacity	~100 MB	100 MB	Identical, but control is now unified

Swipe to see more

innodb_io_capacity: 200 → 10,000

The jump is spectacular. MySQL 8.4 assumes that modern servers use SSD/NVMe drives capable of thousands of IOPS. If your server uses spinning disks (HDD), lower this value:

SET PERSIST innodb_io_capacity = 1000;       -- SATA SSD
SET PERSIST innodb_io_capacity = 200;        -- HDD

innodb_change_buffering: all → none

The change buffer used to buffer secondary index changes in order to apply them in batches. With the spread of SSDs and the growth of RAM, the gain had become negligible or even counterproductive. MySQL 8.4 disables it by default.

innodb_adaptive_hash_index: ON → OFF

The adaptive hash index built an in-memory index on top of the B-tree indexes to speed up frequent lookups. In practice, it used memory, caused contention on semaphores, and only brought a gain in specific cases. Keep it disabled unless benchmarks show a benefit.

innodb_dedicated_server: auto-sizing on a dedicated server

innodb_dedicated_server is not changeable live. It must be enabled at server startup, in my.cnf:

[mysqld]
innodb_dedicated_server = ON

In MySQL 8.4, this mode automatically computes:

• innodb_buffer_pool_size according to the detected RAM:

Detected RAM	Buffer pool
< 1 GB	128 MiB
1 to 4 GB	50% of RAM
> 4 GB	75% of RAM

Swipe to see more

• innodb_redo_log_capacity according to the number of logical processors: (logical processors / 2) GiB, capped at 16 GiB

Only enable it on a server dedicated to MySQL

As its name suggests, innodb_dedicated_server assumes that all the RAM is for MySQL, which explains its 75% tier above 4 GB, higher than the 50-70% rule seen above. If the server hosts other services (Nginx, Redis, an application), auto-sizing will consume too much memory. Size manually in that case.

Configuring connections

A MySQL connection is not free: it ties up a thread, a memory stack and session buffers as long as it stays open, even idle. The three parameters in this section act on the three moments of its life: how many connections are admitted, after how long an idle connection is closed, and whether the thread is recycled rather than destroyed.

max_connections

Default value: 151

Each MySQL connection consumes a thread (single process, multiple threads, unlike PostgreSQL which forks a process). 151 connections = 151 threads, each with its memory stack (thread_stack, 1 MB by default) and its session buffers.

SHOW VARIABLES LIKE 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

Best practices:

• For a development server or a lab: the default of 151 is enough
• For a production server: adjust to your real load, without exceeding 500-1000
• Beyond 300-500 connections: use ProxySQL or an application-side connection pool

SET PERSIST max_connections = 300;

wait_timeout and interactive_timeout

These parameters control the delay before an inactive connection is closed:

Parameter	Applies to	Default
wait_timeout	Non-interactive connections (application)	28,800 (8 hours)
interactive_timeout	Interactive connections (mysql client)	28,800 (8 hours)

Swipe to see more

8 hours of inactivity before closing is very long. In production, reduce them:

SET PERSIST wait_timeout = 600;           -- 10 minutes
SET PERSIST interactive_timeout = 3600;   -- 1 hour

thread_cache_size

When a connection closes, MySQL can keep the thread cached rather than destroying it, the next connection reuses it, saving the creation cost.

Default value: auto-sized (-1 internally) according to a formula based on max_connections, capped at 100.

Check the ratio of threads created to connections:

SHOW GLOBAL STATUS LIKE 'Threads%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 2     |
| Threads_connected | 1     |
| Threads_created   | 7     |
| Threads_running   | 1     |
+-------------------+-------+

If Threads_created increases quickly, increase thread_cache_size:

SET PERSIST thread_cache_size = 32;

Configuring the binary log

The binary log (binlog) records all data modifications. It is essential for replication and point-in-time recovery (PITR).

binlog_format: ROW by default

MySQL 8.4 uses the ROW format by default, the most reliable for replication:

Format	Content	Recommendation
ROW	The before/after image of each modified row	Recommended, deterministic
STATEMENT	The original SQL query	Risk of source/replica divergence
MIXED	STATEMENT when safe, ROW otherwise	Compromise rarely needed

Swipe to see more

binlog_expire_logs_seconds

Retention period of binlog files before automatic deletion:

MySQL 8.4 default value: 2,592,000 seconds (30 days)

For a lab or a server with limited storage:

SET PERSIST binlog_expire_logs_seconds = 604800;  -- 7 days

sync_binlog and innodb_flush_log_at_trx_commit: durability

These two parameters together control transaction durability:

sync_binlog	innodb_flush_log_at_trx_commit	Durability	Performance
1	1	Maximum (aims at zero loss on a crash, if the storage honors fsync)	Slower (2 disk writes per commit)
1	2	Good (possible loss of about 1 second of InnoDB transactions)	Better
0	0	Minimum (possible loss on a crash)	Maximum

Swipe to see more

Recommended configuration for production:

SET PERSIST sync_binlog = 1;
SET PERSIST innodb_flush_log_at_trx_commit = 1;

This is the double durability setting, MySQL and InnoDB both write to disk at every commit. It is the only one of the three settings in the table that aims at zero data loss on a crash, provided the storage really honors fsync (a write cache without battery or protection can lie about it). No SQL query proves it: only a hard shutdown of the machine, on a test server, shows it.

The cost of durability

The sync_binlog=1 + innodb_flush_log_at_trx_commit=1 setting doubles the number of disk writes per transaction. On an SSD/NVMe, the impact is moderate. On an HDD, write throughput can drop significantly. If your workload favors raw throughput and tolerates a possible loss of 1 second of data, set innodb_flush_log_at_trx_commit to 2.

Configuring logging

MySQL keeps three journals distinct from the binary log covered above: the error log, always active, the slow query log, disabled by default, and the general log, reserved for debugging. Only the second deserves to be enabled permanently; the third produces such a volume that it degrades the server if left open.

Error log

The error log is always active. Its location:

SHOW VARIABLES LIKE 'log_error';

+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_error     | /var/log/mysql/error.log  |
+---------------+---------------------------+

Slow query log: finding slow queries

The most useful parameter in operations. Disabled by default, enable it right away:

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;            -- in seconds (1 second)

Parameter	Default	Recommendation
slow_query_log	OFF	ON, always in production
long_query_time	10 (seconds!)	1 or even 0.5 to be responsive
log_queries_not_using_indexes	OFF	Investigation mode, see the note below

Swipe to see more

Use log_queries_not_using_indexes with care

Enabling log_queries_not_using_indexes can make the slow log grow very quickly on a database with many small tables. If you enable it for an investigation phase, add a throttle to limit the volume:

SET PERSIST log_queries_not_using_indexes = ON;
SET PERSIST log_throttle_queries_not_using_indexes = 60;  -- max 60 entries/minute

This setting is an investigation mode, not a universal production default.

The log file is located by default in the datadir:

ls -la /var/lib/mysql/*-slow.log

To analyze the slow queries:

mysqldumpslow -s t /var/lib/mysql/mysql-lab-slow.log | head -30

Start at 1 second, refine later

A threshold of long_query_time = 10 (the default) is far too permissive: many problematic queries last less than 10 seconds and are never traced. Start with a 1-second threshold (1) and go down to 0.5 if the logs are not too large.

General log (debug only)

The general log traces all queries, useful for debugging, dangerous in production (excessive log volume, performance impact):

SET GLOBAL general_log = ON;   -- do NOT persist
-- Debug your problem...
SET GLOBAL general_log = OFF;

log_output: FILE vs TABLE

MySQL can write logs to files or to system tables:

SET PERSIST log_output = 'FILE';     -- default, recommended for production
SET PERSIST log_output = 'TABLE';    -- writes to mysql.slow_log and mysql.general_log
SET PERSIST log_output = 'FILE,TABLE';  -- both

The TABLE mode is handy for running SQL queries on the logs, but uses more resources.

Configuring the network

Two parameters decide what crosses the network: on which interfaces MySQL listens, and how it identifies the clients that connect. Both are static and require a service restart, unlike most of the settings seen so far.

bind_address

MySQL 8.4 default value: *, that is all interfaces, IPv4 and IPv6 (MySQL 8.4 documentation, "Server System Variables" page).

The Oracle packages set no bind-address line: the .deb ships a mysqld.cnf that only contains pid-file, socket, datadir and log-error, and the EL9 .rpm ships an equally bare /etc/my.cnf. A fresh installation therefore listens on the network, on port 3306 as well as on the X protocol port 33060, and this is the default you need to fix. With a package from another origin, read the real value rather than assuming it:

SELECT @@bind_address, @@mysqlx_bind_address;

+----------------+-----------------------+
| @@bind_address | @@mysqlx_bind_address |
+----------------+-----------------------+
| *              | *                     |
+----------------+-----------------------+

To restrict listening to the local machine, for example when the application runs on the same server, edit the [mysqld] section of mysqld.cnf:

[mysqld]
bind_address        = 127.0.0.1
mysqlx_bind_address = 127.0.0.1

MySQL 8.4 also accepts several addresses separated by commas, for example bind_address = 127.0.0.1,192.168.122.70 to keep the local one and one specific network interface.

bind_address does not restrict port 33060 (X protocol), which has its own variable mysqlx_bind_address. Measured on MySQL 8.4: setting bind_address = 127.0.0.1 alone leaves 33060 open on all interfaces, and you wrongly believe you have closed the server. The "How do you restrict MySQL's network listening?" section of the Installation guide details the check with ss.

bind_address is static

bind_address and mysqlx_bind_address cannot be changed live, a restart is mandatory. Since MySQL already listens on all interfaces by default, restrict listening before opening network accounts, then set up accounts and permissions with the Security guide.

skip_name_resolve

By default, MySQL performs a reverse DNS lookup for each incoming connection. On an internal network without reliable DNS, this adds delay. Disable it:

SET PERSIST_ONLY skip_name_resolve = ON;   -- requires a restart

Once enabled, MySQL accounts must use IP addresses instead of host names in the GRANT.

Checking the configuration

After a series of changes, three questions arise: which value the server really applies, where it comes from, and whether it will survive a restart. SHOW VARIABLES answers the first and performance_schema.variables_info the second. The third is proven in two steps: performance_schema.persisted_variables shows the intent to persist, and only a restart proves the round trip, that is, that the value was written then read back. A last command validates a configuration file before you risk a restart that would fail.

SHOW VARIABLES and performance_schema.variables_info

Seeing a parameter's value:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Seeing a parameter's source (file, SET PERSIST, compiled):

SELECT variable_name, variable_source, set_time, set_user
FROM performance_schema.variables_info
WHERE variable_name LIKE 'innodb_buffer%';

+-------------------------------+-----------------+----------------------------+----------+
| variable_name                 | variable_source | set_time                   | set_user |
+-------------------------------+-----------------+----------------------------+----------+
| innodb_buffer_pool_chunk_size | COMPILED        | NULL                       | NULL     |
| innodb_buffer_pool_instances  | COMPILED        | NULL                       | NULL     |
| innodb_buffer_pool_size       | PERSISTED       | 2026-04-13 09:00:00.000000 | root     |
+-------------------------------+-----------------+----------------------------+----------+

variable_source tells you where the value comes from: COMPILED (compile-time default), GLOBAL (my.cnf/mysqld.cnf), PERSISTED (mysqld-auto.cnf), DYNAMIC (changed since startup, by SET GLOBAL or by SET PERSIST). A PERSISTED line like the one above is read after a restart: right after the SET PERSIST that set it, the same variable would have reported DYNAMIC.

How do you prove that a SET PERSIST will survive a restart?

On a running server, variables_info answers DYNAMIC after a SET PERSIST exactly as after a SET GLOBAL: it does not tell them apart, and this equality traps you in two ways. You believe your command failed because PERSISTED does not appear, or, more dangerously, you believe persistence is verified because a row shows up.

The performance_schema.persisted_variables table, for its part, does distinguish the two cases without a restart: it only lists the variables set by SET PERSIST, and a variable set by SET GLOBAL alone does not appear in it. It is a good first check: if the variable is not there, it will not be persisted. After a RESET PERSIST, the row disappears but the value stays in service until the restart.

This check has a limit: the table reflects the server's in-memory state, not the file. Measured on MySQL 8.4.10, the row stays displayed even though mysqld-auto.cnf has been replaced by a damaged JSON (or emptied), and that damaged file nevertheless prevents the next startup (Persisted config file is corrupt). The table therefore proves the intent to persist, not the round trip: reading the file shows what was written, not what the next startup will accept. Only a restart proves that the value is written then read back, and the configuration lab associated with this guide restarts the server for this reason.

The complete method takes four steps: set the value, query persisted_variables, note that variables_info stays silent, then restart and read the source again.

SET PERSIST max_connections = 300;

SELECT * FROM performance_schema.persisted_variables;
-- max_connections | 300 : MySQL has recorded the intent (in memory)

SELECT variable_source
FROM performance_schema.variables_info
WHERE variable_name = 'max_connections';
-- DYNAMIC : normal, neither a failure nor a proof

-- Restart the service (sudo systemctl restart mysql), then reconnect:

SELECT variable_source
FROM performance_schema.variables_info
WHERE variable_name = 'max_connections';
-- PERSISTED : the round trip is proven, the value was read back from mysqld-auto.cnf

This restart cuts all connections: on a production server, do it in a maintenance window, after validating your configuration files with the command that follows.

mysqld --validate-config: validate before restarting

MySQL 8.4 validates the configuration without starting the server:

sudo mysqld --validate-config

If a parameter in a .cnf file is invalid, the command prints the error and exits with a non-zero code. Always run this validation after editing mysqld.cnf by hand, before restarting the service.

It also reads mysqld-auto.cnf: measured on MySQL 8.4.10, a file replaced by { broken makes the command exit with code 1 and Persisted config file is corrupt, where the restart would have failed. A code 0 proves that the configuration is readable, not that the service will start (available memory, port already taken): only the restart proves that.

The validation has a blind spot on mysqld-auto.cnf, of which it only checks the JSON syntax. Measured on MySQL 8.4.10, an unknown variable name or a non-numeric value pass with code 0, without a word. The server then starts, the value is brought down to the minimum and max_connections falls to 1: only the error log warns (MY-000081). The silence comes from the validation tool, not from the server; after a restart, therefore, read that log.

Summary table: recommended values

These values are starting points for a server dedicated to MySQL, to be adjusted afterwards with the counters seen above: the buffer pool hit ratio for memory, Threads_created for the thread cache, the slow log volume for the threshold. If the server also hosts an application, reduce the buffer pool column accordingly.

Total RAM	innodb_buffer_pool_size	max_connections	innodb_log_buffer_size	long_query_time
2 GB	1 GB	100-200	64 MB	1 s
4 GB	2-2.5 GB	200-300	64 MB	1 s
8 GB	4-5 GB	200-500	64 MB	0.5 s
16 GB	8-11 GB	300-500	128 MB	0.5 s
32 GB	16-22 GB	300-1000	128 MB	0.5 s

Swipe to see more

Put it into practice

The lab asks you to raise the InnoDB buffer pool and the connection ceiling, to turn on the slow query log, and to make all of it survive a restart. The validation restarts the server and then requires the requested values: a setting applied to the current session does not count.

It restarts because nothing else proves the round trip, and this is measured. performance_schema.variables_info reports the same source, DYNAMIC, after a SET GLOBAL that will be lost and after a SET PERSIST that will survive: a test that read this source would conclude green on a volatile setting. performance_schema.persisted_variables does distinguish the two, but it reflects the memory and not the file: if you empty mysqld-auto.cnf behind the server's back, without restarting, the table keeps announcing the value. It therefore proves the intent to persist, and only the restart proves the read-back.

The slow log, for its part, is not graded on its variable but on a slow query that really lands in it: an enabled log whose file is not reachable remains an empty log.

Guided lab Practice

Learn how to do it

Configure memory, connections and the slow log, so it survives a restart

$ dsoxlab run mysql-03-configurer

Environment: VM·Duration: ~40 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start mysql-03-configurer checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run mysql-03-configurer
dsoxlab check mysql-03-configurer

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Troubleshooting

Two causes explain most configuration surprises: a mysqld-auto.cnf file that silently takes over from the hand-edited files, and a threshold left at its default value. The reflex is to read the source of the value with performance_schema.variables_info before changing anything: PERSISTED designates a mysqld-auto.cnf that overrides your files. A DYNAMIC source only signals a value changed since startup, without saying whether it is persisted: query performance_schema.persisted_variables then (does the variable appear in it?), and restart to prove that it will be read back.

Symptom	Likely cause	Fix
The server no longer restarts after a change in mysqld.cnf	Invalid parameter or incorrect syntax	mysqld --validate-config to identify the error. Fix the file or rename mysqld-auto.cnf
The error log shows Persisted config file is corrupt and the server does not start	mysqld-auto.cnf is no longer valid JSON (manual edit)	Fix the JSON, or move the file: the persisted values are then lost and must be set again. mysqld --validate-config detects it before the restart
SHOW VARIABLES returns a value different from mysqld.cnf	mysqld-auto.cnf has priority	SELECT * FROM performance_schema.variables_info WHERE variable_name = 'xxx' to find the source
After a SET PERSIST, variables_info reports DYNAMIC and not PERSISTED	Normal MySQL 8.4 behavior: the source only becomes PERSISTED at restart	Not a failure, but not a proof either: check that the variable is present in performance_schema.persisted_variables, then restart to confirm
Buffer pool hit ratio < 95%	Buffer pool too small	Increase innodb_buffer_pool_size
Threads_created increases quickly	Thread cache too small	Increase thread_cache_size
Slow query log empty despite slow_query_log = ON	long_query_time too high (default 10 s), or log file unreachable or not writable by the server	Lower it to 1 or 0.5 seconds; check slow_query_log_file and the mysql account's rights on its directory

Swipe to see more

Key takeaways

• The InnoDB buffer pool is the most impactful parameter, aim for 50 to 70% of RAM on a dedicated server.
• Unlike PostgreSQL, innodb_buffer_pool_size is dynamic, resizable live with SET GLOBAL.
• SET PERSIST writes to mysqld-auto.cnf (JSON), which has priority over all .cnf files, so choose a single management method. variables_info reports DYNAMIC after a SET PERSIST, as after a SET GLOBAL: performance_schema.persisted_variables tells them apart and shows the intent to persist, but only a restart proves the round trip, that is, that a persisted value is written then read back.
• MySQL 8.4 modernized the InnoDB defaults: innodb_io_capacity = 10,000, change buffer disabled, adaptive hash index disabled. Adjust innodb_io_capacity if you use HDDs.
• Enable the slow query log from day one (long_query_time = 1), it is the first tool for finding slow queries.
• The sync_binlog=1 + innodb_flush_log_at_trx_commit=1 setting aims at zero loss on a crash, provided the storage honors fsync: it is the reference setting for critical data.
• mysqld --validate-config checks the configuration, mysqld-auto.cnf included (its JSON syntax only), without starting the server: run it before every restart. A code 0 proves that the configuration is readable, not that the service will start.
• innodb_dedicated_server auto-sizes memory, but only on a server 100% dedicated to MySQL.
• Network listening: bind_address is * by default and the Oracle packages do not change that. Restrict it together with mysqlx_bind_address, because port 33060 has its own variable.

Knowledge check

Eight questions on what variables_info does not say, what persisted_variables proves exactly, the limits of mysqld --validate-config, the buffer pool left at its default, and the InnoDB defaults changed in 8.4.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →