On a game server or a busy web application, the bottleneck is often not MySQL itself but the fact that it is still running on its default settings. Tuning your MySQL my.cnf file is the cheapest performance win you can get without buying new hardware. In this guide I walk through the parameters that actually make a difference — from the InnoDB buffer pool to connection limits, log settings and query caching — and explain why each one matters.
Where my.cnf lives and how it works
MySQL (and MariaDB) read their configuration from a set of files at startup. On Linux the most common paths are:
/etc/my.cnf/etc/mysql/my.cnf/etc/mysql/mysql.conf.d/mysqld.cnf(Debian/Ubuntu)/etc/my.cnf.d/*.cnf(CentOS/Alma/Rocky)
To see which files are read and in what order, run:
mysqld --verbose --help | grep -A1 "Default options"Settings go under the [mysqld] section. After every change you have to restart the service:
sudo systemctl restart mysql # or mariadbInnoDB buffer pool: the most critical setting
If you could only tune one parameter, it would be innodb_buffer_pool_size. InnoDB keeps data and indexes in this memory pool; the bigger it is, the more reads come from RAM instead of disk. The rule of thumb is 50–70% of total RAM on a dedicated database server. If a game server, PHP or other services share the same machine, be more conservative.
[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4For large pools (above 8 GB) you can split the pool into multiple instances to reduce internal lock contention; each instance should ideally be at least 1 GB. To check whether the pool is large enough, look at the hit rate:
SHOW ENGINE INNODB STATUS\GIf the Buffer pool hit rate in the output is close to 1000/1000, nearly all reads are served from memory. If it is low, consider growing the pool.
InnoDB logs and disk write behaviour
On write-heavy workloads, the redo log size and flush policy are decisive. If innodb_log_file_size (in MySQL 8 this can also be managed via innodb_redo_log_capacity) is too small, MySQL checkpoints constantly and write performance drops.
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECTinnodb_flush_log_at_trx_commit = 1gives full ACID safety; every commit is flushed to disk. Keep this for financial data.- Setting it to
2flushes the log to disk once per second; you gain serious write throughput at the risk of losing the last ~1 second on a sudden power loss. For non-critical data such as game inventory, this is a reasonable trade-off. O_DIRECTbypasses the operating system cache and prevents double buffering.
Connection settings: max_connections and the thread cache
When many concurrent players or web requests arrive and max_connections is too low, you get the "Too many connections" error. But raising it blindly is also dangerous: every connection consumes memory.
max_connections = 200
thread_cache_size = 32
wait_timeout = 120
interactive_timeout = 120thread_cache_size reuses the threads of closed connections, lowering the cost of opening a new one. wait_timeout closes idle connections so stuck clients do not hold a slot forever. If your application uses a connection pool (for example Laravel's persistent connections), balance the timeout accordingly.
Query cache and version differences
Watch out for query_cache_size, which appears in a lot of old tutorials: the query cache was disabled by default in MySQL 5.7 and removed entirely in MySQL 8.0 because it caused lock contention under high concurrency. If you run MySQL 8 and add those lines to my.cnf, the service will not start. The modern approach is to cache in the application layer (Redis, Memcached) or rely on the InnoDB buffer pool.
Do not overinflate per-connection buffers either; these are allocated separately for each connection:
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64MVerifying and measuring your changes
Setting values is half the job; measuring their effect is the other half. Check the live values:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';To catch slow queries, enable the slow query log and find the real bottlenecks:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1Among the ready-made tools, mysqltuner.pl analyses a running server and offers concrete suggestions; but do not apply them blindly — apply each only after you understand why it was suggested. Remember: even the best my.cnf cannot save a badly written query or a missing index. First fix your queries and indexes with EXPLAIN, then fine-tune the configuration.
Frequently Asked Questions
Do I have to restart MySQL for my.cnf changes?
For most InnoDB settings (buffer pool, log file size), yes. However, many variables can be changed dynamically at runtime with SET GLOBAL — for example SET GLOBAL max_connections = 300;. Dynamic changes are lost on restart, so to make them permanent you still have to write them into my.cnf.
What happens if I make innodb_buffer_pool_size too large?
You can exhaust RAM and push the OS or other services into swap; at that point performance is worse than with a small pool. Remember that, beyond the buffer pool, every connection also uses memory, so leave the machine enough total headroom.
Am I using MySQL or MariaDB, and are the settings the same?
The core InnoDB and connection settings are largely shared. Still, there are differences in the query cache, thread pool and some default values. To confirm setting names specific to your version, use the SHOW VARIABLES output as your reference.
Is your database slowing your server down? If you need help with MySQL/MariaDB tuning, index design or query optimization for a game server or web project, get in touch with me — I will review your current configuration and put together a concrete improvement plan.