When people ask me how to tune MySQL, they usually mean, “Which variables should I change in my.cnf?” (the MySQL configuration file) That certainly is a reasonable place to start, because most day-to-day performance tuning really is driven by MySQL itself: memory allocation, redo flushing, connection handling, temporary tables, and InnoDB I/O behavior. But the operating system still matters a lot, especially when MySQL is under a sustained load. A poorly-sized buffer pool hurts performance, but so does an OS that swaps, uses poor filesystem settings, or caps file descriptors too low. MySQL’s own documentation frames performance as a combination of database-level design and configuration, which ultimately drives CPU and I/O behavior at the hardware level.
The practical answer is this: most tuning is related to MySQL, but the OS provides the floor and ceiling for MySQL performance. If the operating system is misconfigured, MySQL tuning alone will not save you. If the OS is healthy, then most of your gains usually come from tuning MySQL variables, schema design, indexing, and query patterns.
Start With the Right Principle
Before changing variables, remember that MySQL already includes measurement tools for tuning. The Performance Schema is enabled by default in MySQL 8.4 and is meant to help DBAs tune based on measurements rather than guesses.
That matters because there is no universal “best” config. A write-heavy OLTP system, an analytics box, and a mixed workload all want different settings.
The MySQL Variables That Usually Matter Most
This is the most important MySQL memory setting for InnoDB. The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.
If it is too small, MySQL reads from disk more often; if it is appropriately sized, many reads are served from memory instead. MySQL documents it as the primary memory area for caching table and index data for InnoDB.
Short summary: The main cache for InnoDB data and indexes.
This splits the buffer pool into multiple regions to reduce contention on busy systems. It is most useful when the buffer pool is large and concurrency is high. It should not be tuned in isolation; it is tied to the overall buffer pool layout. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool instance manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.
Short summary: Divides the buffer pool to improve concurrency on large-memory systems.
The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. During normal operations, the redo log encodes requests to change table data that result from SQL statements or low-level API calls. Modifications that did not finish updating data files before an unexpected shutdown are replayed automatically during initialization and before connections are accepted. If redo capacity is too small on a write-heavy system, checkpoints happen more aggressively and write pressure rises. Larger redo capacity can smooth writes, but recovery time after a crash can increase.
Short summary: Sets the total amount of redo log space available for write buffering.
The log buffer is the memory area that holds data to be written to the log files on disk. Log buffer size is defined by the innodb_log_buffer_size variable. The default size is 64MB. The contents of the log buffer are periodically flushed to disk. A large log buffer enables large transactions to run without the need to write redo log data to disk before the transactions commit. So, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer saves disk I/O.
Short summary: Memory buffer that holds redo records before they are written out.
innodb_flush_log_at_trx_commit
This is one of the most important durability-versus-performance switches. It controls how redo log buffer contents are written and flushed at commit time. Tighter flushing improves durability but can cost throughput; looser flushing can improve performance while accepting more risk during a crash. This controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.
Short summary: Governs how aggressively transaction commits force redo to disk.
If binary logging is enabled, this determines how often the binary log is synchronized to disk. Like innodb_flush_log_at_trx_commit, it is a durability/performance tradeoff. Stronger sync behavior protects committed transactions in crash scenarios, while weaker sync behavior may improve throughput.
Short summary: Controls binary log flush durability versus speed.
This tells InnoDB how much I/O the system can handle for background work. MySQL says it should be set approximately to the number of I/O operations the system can perform per second. Too low, and background work falls behind; too high, and InnoDB may become overly aggressive. The InnoDB master thread and other threads perform various tasks in the background, most of which are I/O related, such as flushing dirty pages from the buffer pool and writing changes from the change buffer to the appropriate secondary indexes. InnoDB attempts to perform these tasks in a way that does not adversely affect the normal working of the server. It tries to estimate the available I/O bandwidth and tune its activities to take advantage of available capacity.
Short summary: Tells InnoDB how much background I/O it may assume is available.
This acts as the upper burst limit for background I/O. It gives InnoDB room to work harder when it needs to catch up.
Short summary: Maximum burst I/O rate InnoDB can use for background tasks.
This controls how InnoDB interacts with the OS and filesystem when flushing data and logs. It can affect double caching and I/O behavior. The best choice depends on the platform and storage stack.
Short summary: Determines how InnoDB performs file I/O and flushes through the OS.
This sets the maximum number of simultaneous client connections. It is often misused. Raising it blindly can cause memory pressure and make overload worse rather than better, because each session can consume buffers and server resources.
Short summary: Limits concurrent client sessions.
This caches reusable threads so MySQL does not have to create and destroy them as often. It is useful for workloads with frequent short-lived connections.
Short summary: Reuses threads to reduce connection overhead.
This controls how many open table instances MySQL can keep cached. If it is too low, MySQL spends more time opening and closing tables. If it is too high without enough file descriptors at the OS level, you can run into resource limits.
Short summary: Caches opened tables to reduce reopen overhead.
This caches table metadata. It is especially important on servers with many tables, because repeated metadata loading becomes expensive. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum value is 400. The default value is based on the following formula, capped to a limit of 2000:
MIN(400 + table_open_cache / 2, 2000)
Short summary: Caches table definitions and metadata.
This controls the maximum size of in-memory internal temporary tables before they spill to disk, in combination with related limits. If it is too small, sorts and aggregations may create more disk-based temp tables. If an internal in-memory temporary table exceeds this size, it is automatically converted to an on-disk internal temporary table.
Short summary: Helps determine how large in-memory temp tables can grow.
This works with tmp_table_size for memory-based temp table limits. In practice, the smaller of the two limits often governs behavior.
Short summary: Caps MEMORY tables and influences temp table memory limits.
This is a per-session buffer used for sorts. It can help large sorts, but making it too large is dangerous because it is allocated per connection when needed.
Short summary: Per-session memory used for sort operations.
This is another per-session buffer, used when joins cannot use indexes efficiently. It can help some bad plans, but it is not a substitute for proper indexing and can become costly at scale. The value is the minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. This variable also controls the amount of memory used for hash joins. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Short summary: Per-session buffer for certain non-indexed join operations.
This is a per-session buffer used for sequential table scans. It can help scan-heavy workloads, but like other per-connection buffers, oversized values multiply quickly under concurrency. This does not apply to InnoDB tables.
Short summary: Per-session buffer for sequential reads.
This buffer helps with reading rows in sorted order after a sort operation. It can matter for some read-heavy patterns, but again, it is session-scoped and should not be inflated casually. This does not apply to InnoDB tables.
Short summary: Per-session buffer for post-sort row reads.
Which MySQL Variables Matter Less Than People Think?
A lot of tuning effort gets wasted on small per-thread buffers while the real bottleneck is an undersized buffer pool, poor indexing, inefficient queries, or mis-sized redo and flushing behavior. MySQL’s own optimization material places heavy emphasis on schema design, indexes, SQL, and understanding execution plans, not just variable tweaking.
That is why broad changes to sort_buffer_size, join_buffer_size, read_buffer_size, and similar variables should usually come after you confirm there is a measured benefit.
The Operating System Tuning You Actually Need
Now to the other half of the question: is OS tuning needed? Yes, but usually in a smaller, more focused set of areas.
Swapping and vm.swappiness
Linux exposes VM tuning under /proc/sys/vm, including controls for memory and cache behavior. For database servers, excessive swapping is usually destructive because it can stall the database badly. vm.swappiness controls the kernel’s tendency to swap memory pages. On a dedicated MySQL server, people commonly keep it low to reduce the chance of MySQL memory being swapped out. The exact value should be tested, but the principle is simple: do not let a database server drift into swap under normal load.
Short summary: Reduces the kernel’s urge to swap database memory to disk.
Transparent Huge Pages
Linux THP (Transparent Huge Pages) automatically promotes memory to huge pages. The kernel documentation describes how THP works, but for database workloads, many vendors recommend caution because automatic promotion and compaction can introduce latency. Oracle’s Linux guidance for Oracle Database recommends disabling Transparent HugePages and using standard huge pages instead for predictable performance. The same reasoning is often applied to other large database workloads that care about latency consistency.
Short summary: Avoids latency spikes from automatic huge-page management.
Standard Huge Pages / Large Pages
MySQL supports large page usage on platforms that provide it. Large pages can reduce page table overhead and help memory management for large database caches, especially when the server has a large InnoDB buffer pool.
Short summary: Improves memory efficiency for large database memory allocations.
Open file limits
MySQL can keep many tables, table definitions, logs, and sockets open. If the OS ulimit and service limits are too low, settings like table_open_cache become ineffective or the server hits hard ceilings.
Short summary: Ensures MySQL can actually keep enough files and tables open.
Filesystem and mount behavior
For database hosts, unnecessary metadata writes should be avoided. Administrators often use mount options such as noatime where appropriate so ordinary reads do not keep updating file access timestamps. The exact choice depends on the filesystem and environment, but minimizing avoidable I/O overhead is a sound OS-level practice. MySQL’s optimization guidance also stresses reducing unnecessary I/O at the hardware layer.
Short summary: Reduces wasted filesystem I/O around database files.
I/O scheduler and storage stack
MySQL specifically recommends benchmarking to determine which I/O scheduler works best for your workload and environment. That is a good reminder that storage tuning is not one-size-fits-all, especially on SSD, NVMe, SAN, and cloud block storage.
Short summary: Matches OS disk scheduling to the real storage device and workload.
Asynchronous I/O support
MySQL documents asynchronous I/O on Linux as part of InnoDB configuration. This matters because modern database performance depends heavily on efficient I/O submission and completion behavior.
Short summary: Lets InnoDB overlap and pipeline disk work more efficiently.
So, Where Should You Spend Your Time?
If I had to prioritize effort, I would do it in this order.
First, make sure the OS is not sabotaging the database: no swapping under load, THP handled appropriately, sufficient file limits, sane storage and filesystem behavior.
Second, tune the major MySQL variables: innodb_buffer_pool_size, redo settings, flush behavior, connection limits, table caches, and I/O capacity.
Third, use Performance Schema and execution plans to verify where time is actually going.
Fourth, tune schema and SQL. In real systems, a missing index often hurts more than any config mistake. MySQL’s optimization documentation emphasizes SQL, indexing, and execution plans for a reason.
A Sensible Rule of Thumb
For a dedicated MySQL server, you can think of tuning this way:
OS tuning is foundational. MySQL tuning is where most of the fine control lives. Query and schema tuning is where the biggest wins often hide.
That is why the answer is not “it’s all OS” or “it’s all MySQL.” The operating system must be configured so MySQL can use memory and storage predictably. After that, the most meaningful “knobs to turn” are usually in MySQL itself. And after that, the best improvements frequently come from better indexes, cleaner queries, and better workload visibility.