MySQL performance tuning for game servers
On roleplay servers most peak-hour stuttering comes not from a shortage of CPU or RAM but from the database. The good news is that this is the one bottleneck that can be fixed without buying hardware.
Measure first
Instead of changing settings by guesswork, turn on the slow query log:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';Every query that takes longer than half a second is logged. Running it for an hour and looking at the log shows, on most servers, that the problem is in a single query.
The highest-return setting: innodb_buffer_pool_size
InnoDB keeps the hot part of the data in memory. The default value (128 MB) is meaninglessly small for a game server.
| Server RAM | Recommended buffer pool |
|---|---|
| 8 GB | 2 GB |
| 16 GB | 4-6 GB |
| 32 GB | 8-12 GB |
The rule: 25-40% of the server's RAM. Giving it more takes memory away from the game server and produces a net loss — this is not a machine where MySQL runs on its own.
[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
max_connections = 200The innodb_flush_log_at_trx_commit trade-off
This setting calls for a deliberate choice:
- `1` (default): every transaction is written to disk. Safest, slowest.
- `2`: written to disk once a second. In a power cut the last ~1 second is lost.
On a game server 2 is usually the right choice: losing one second of player progress is more acceptable than continuous disk write latency. If you hold financial data, stay on 1.
Indexes: where the real problem is
This is the thing to look at before the settings. The database schemas of frameworks such as ESX/QBCore may not include indexes on frequently queried columns:
EXPLAIN SELECT * FROM players WHERE identifier = 'license:abc';If you see type: ALL in the output, the table is being scanned from start to finish. On a players table with 50,000 rows, that means reading 50,000 rows for every player who connects.
ALTER TABLE players ADD INDEX idx_identifier (identifier);A single index removes all of the peak-hour stuttering on some servers. This is definitely the place to look before growing the hardware.
Synchronous queries inside the game loop
The second most common cause: the query itself is fast but the game loop is waiting for it. In FiveM, using MySQL.Sync stops the main thread; MySQL.Async does not. That difference on the code side cannot be compensated for by any MySQL setting.
Storage
MySQL's workload is small random reads and writes, and here the gap between NVMe and SATA SSD is measured in multiples rather than percentages. If a game server runs on the same machine, the two share the same disk; if await in the iostat -x 1 output climbs into milliseconds, the bottleneck is storage.
When should you move it to a separate server?
The practical thresholds: 200-250 concurrent players on FiveM/RedM, 4 channels or 700-800 players on Metin2. Past that point MySQL and the game processes share the same memory and disk, so the two slow down together; taking a second server in the same location gives a better result than growing the resources of one machine.