MySQL performance tuning for game servers
By Datafex Engineering TeamPublished Updated 3 min read
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.
The values in the table are read against the RAM of the plan you already have. If the memory needed to leave room for both the buffer pool and the game processes is larger than that, moving RAM up one step is a shorter route than fiddling with settings; you can pick each resource and see the monthly total in the server configurator.
[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. Which plan carries the database and the game process together at a given player count is set out on the game page: FiveM server plans
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 (what is an NVMe SSD?). 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; moving the database onto a second virtual server in the same location gives a better result than growing the resources of one machine. If you have filled the single-core limit too, the next step is a dedicated server: the whole disk and all of the memory stay with one workload.