Someone else’s server settings come with someone else’s assumptions. Before editing your MySQL config file, read the running values and define the problem. These five parameters affect memory, connection capacity, caching, I/O behavior, and transaction durability in different ways.
Read the Running Server Before the Config File
Use the MySQL client on Windows for the examples in this article. These are MySQL statements, not T-SQL. Start by identifying the version and reading current global values. A value in a saved option file does not prove that the running service loaded that file or that no later change overrode it.
-- MySQL
SELECT VERSION() AS server_version;
SHOW GLOBAL VARIABLES
WHERE Variable_name IN ('innodb_buffer_pool_size', 'max_connections',
'query_cache_size', 'query_cache_type',
'innodb_flush_method', 'innodb_flush_log_at_trx_commit');I check the Windows service’s executable path before editing my.ini. Its startup arguments identify an explicit –defaults-file when one is used. Inspect that actual path through Services rather than assuming every installation uses the same directory. Back up the file and preserve the existing option groups before making a narrow change.
Read the error log too. An obsolete variable can prevent startup, while a typo can defeat the intended setting. MySQL 8.0 removed the query cache, so those variables do not appear there. On a fresh MySQL 8.0.46 test instance on Windows, the query above returned four rows and no query_cache rows. An absent row is useful version information. It is not an invitation to add the missing parameter to a modern configuration.
Size innodb_buffer_pool_size for the Whole Host
The InnoDB buffer pool caches data and index pages. A small pool increases the need to read pages from storage when the workload revisits data that does not remain cached. A larger pool is useful only when the Windows host has memory available after accounting for the rest of its work.
Budget for the operating system, other processes, connections, temporary work, and MySQL memory outside the pool. Do not assign all installed RAM to this one variable. Shared hosts require particular care. A database server that forces Windows to page heavily has turned a memory improvement into a storage problem.
-- MySQL
SHOW GLOBAL STATUS
WHERE Variable_name IN ('Innodb_buffer_pool_read_requests',
'Innodb_buffer_pool_reads',
'Innodb_buffer_pool_pages_data',
'Innodb_buffer_pool_pages_free',
'Uptime');Read the counters twice across a representative interval. Compare the change in physical buffer-pool reads with logical read requests, and inspect memory pressure and storage latency alongside them. Since-startup ratios mix quiet and busy periods. They are not a precise description of the interval that produced the complaint.
I size this pool from a host memory budget and workload evidence. A universal percentage ignores concurrency and shared resources. Increase in planned steps, observe the actual allocated size, and retain the previous setting. A cache is useful when it serves the workload, not when its number looks impressive.
Treat max_connections as a Capacity Limit
max_connections sets the normal connection limit. Raising it can allow more clients to connect, but it does not create CPU, memory, or storage throughput. Connections consume resources, and concurrent statements add further demand. A large limit is not a fix for an application that opens sessions and fails to release them.
-- MySQL
SHOW GLOBAL STATUS
WHERE Variable_name IN ('Threads_connected', 'Threads_running',
'Max_used_connections',
'Connection_errors_max_connections', 'Uptime');Compare current sessions, actively running threads, and peak use. The peak is tied to the relevant counter lifetime, so capture uptime and account for restarts or resets. Inspect application pooling, connection lifetime, and retry behavior. A retry storm can make a capacity problem worse by multiplying attempted work during a slowdown.
Set the limit from measured peak demand, planned headroom, and tested resource capacity. Budget for per-session and per-operation allocations. They are not all allocated at their maximum simultaneously, but concurrent work still needs room. Coordinate the database limit with the application’s pool limits so each layer has a clear boundary.
Treat query_cache_size in an Old Config File as Legacy
The query cache stored complete SELECT results and reused them for matching requests under its rules. Writes invalidated relevant cached results, and shared cache management introduced contention. It was not the same structure as the InnoDB buffer pool. One cached pages, while the other cached query results.
MySQL 8.0 removed query_cache_size and the query cache itself. Do not attempt to tune a feature that the installed engine does not have. On a legacy release that still supports it, inspect both query_cache_size and query_cache_type. Disabling it requires understanding both controls and the release’s supported configuration behavior.
For a legacy workload, test the effect of disabling the cache rather than claiming a universal improvement. Examine query plans, indexes, and application behavior. A query that needs excessive reads deserves query tuning regardless of result caching. The cache cannot negotiate with a missing index. It just postpones the conversation under favorable conditions.
Treat an old configuration containing this variable as something to review before an upgrade. Remove unsupported options through the upgrade plan and confirm startup in a test environment. Do not retain a zero-valued obsolete variable merely because zero sounds harmless. Unknown configuration options can still stop a server.
Match innodb_flush_method to Windows
innodb_flush_method controls the method InnoDB uses for file I/O and flushing behavior. Supported values are platform-specific. On Windows, the documented choices include unbuffered and normal, with unbuffered the default. O_DIRECT is not the Windows setting to copy into my.ini from a different platform’s example.
Read the current value and verify the supported choices for your release and storage. Nonbuffered and buffered I/O interact differently with the operating system’s cache. Neither value replaces the need for reliable storage that honors writes. Test against the actual hardware, filesystem, and mixed read-write workload before changing startup behavior.
This setting does not decide how frequently a committed transaction’s redo log is flushed. That is the next parameter’s job. Keeping the two concepts separate prevents a performance test from silently changing the durability agreement. A faster-looking write path is useful only when the recovery guarantees still match the requirement.
Preserve innodb_flush_log_at_trx_commit Guarantees
innodb_flush_log_at_trx_commit controls redo-log writing and flushing around commits. The default value 1 writes and flushes the redo log at each transaction commit. Value 2 writes at commit but flushes periodically. Value 0 performs writing and flushing periodically instead of for each commit.
The periodic interval is not a guaranteed loss boundary during a failure. Scheduling and system conditions affect it. Changing from 1 trades durability for a different write pattern. An operating system crash or power failure exposes work that was not durably flushed. Explain that trade before changing a production database.
Choose the value from the business’s durability requirement and the storage’s reliability. Keep related binary-log durability settings in the wider review when binary logging is used. Do not lower this parameter as an automatic response to slow commits. Inspect storage latency, transaction design, and workload batching first.
The five controls serve different purposes. More memory does not authorize weaker commits. More connections do not improve a congested storage path. Give each proposed change its own reason and acceptance check, including which failure behavior remains acceptable afterward.
Change One Config File Line and Confirm the Loaded Value
Plan the edit in the actual my.ini used by the service. Put server options in the appropriate server group and preserve unrelated entries. Check whether the chosen variable supports a runtime change or requires restart. A runtime change and a file edit have different persistence behavior, so document both deliberately.
Use a maintenance window when restart is required. After startup, read the error log and rerun SHOW GLOBAL VARIABLES. Confirm that the intended value loaded. Then repeat representative workload observations and compare memory pressure, connection use, storage behavior, and application results. Record the measurements from your own server.
Which symptom should this one edit improve, and what result would make you reverse it? Answer before touching the file. Keep the original value and rollback steps available. A disciplined config file review produces a small, explainable change that survives restart and preserves the database’s recovery commitments.
A config file is not a collection of magic values, it is a record of decisions your workload must support.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
