MySQL Performance – Five Configuration Topics with Version Limits

A MySQL Config File needs settings matched to its server version and workload. I measure pressure before changing a parameter.

Readers asked for more settings after my buffer-pool article. I listed four despite the title saying five. This discussion keeps those historical choices visible. Redo-log capacity provides the fifth consideration for supported modern versions.

1. innodb_buffer_pool_size

The InnoDB buffer pool caches data and indexes. I size it within the host’s overall memory budget. Some clients used 6 to 10 GB. Those observations weren’t a recommendation for every server.

2. max_connections

Raising the limit permits more connections; it does not add CPU or memory. Measure active work, idle connections, connection pooling, and peak demand. Fix leaks or pooling problems before treating a higher limit as the solution.

3. query_cache_size is historical

The query cache was removed in MySQL 8.0. Don’t add query_cache_size to a current MySQL configuration. MariaDB is a separate product with its own support and behavior.

4. innodb_flush_method

This setting depends on the operating system and storage. The original O_DIRECT advice described Unix systems. MySQL 8.4 on Windows uses unbuffered or normal; don’t copy a Unix value into a Windows configuration.

5. innodb_redo_log_capacity

MySQL 8.0.30 and later provide this redo-log capacity setting. Review redo pressure, disk use, checkpoint behavior, and recovery requirements before changing it. Older versions use different sizing settings.

Find the actual option file

On Windows, inspect the installed MySQL service and its configuration, including an explicit --defaults-file argument. Don’t assume a user-specific path. The earlier Unix filename was not a universal location.

I save the configuration and benchmark one change at a time. The test needs the actual MySQL version and representative workload. SQL Server execution doesn’t validate MySQL configuration.

Related reading

• MySQL Performance – Slow Query and innodb_buffer_pool_size
• SQL in Sixty Seconds

References: MySQL InnoDB variables, MySQL version changes.

A parameter value is not a universal tuning recipe, it is a choice to test within a resource budget.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.