MySQL my.cnf-tuning: gids voor prestatie-instellingen

Op een gameserver of een drukke webapplicatie is het knelpunt vaak niet MySQL zelf, maar het feit dat het nog steeds met de standaardinstellingen draait. Het afstellen van je MySQL my.cnf-bestand is de goedkoopste prestatiewinst die je kunt halen zonder nieuwe hardware te kopen. In deze gids loop ik de parameters langs die echt het verschil maken — van de InnoDB buffer pool tot verbindingslimieten, loginstellingen en query caching — en leg ik uit waarom elk ervan belangrijk is.

Waar staat my.cnf en hoe werkt het?

MySQL (en MariaDB) lezen hun configuratie bij het opstarten uit een reeks bestanden. Op Linux zijn de meest voorkomende paden:

• /etc/my.cnf
• /etc/mysql/my.cnf
• /etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu)
• /etc/my.cnf.d/*.cnf (CentOS/Alma/Rocky)

Om te zien welke bestanden worden gelezen en in welke volgorde, voer je uit:

mysqld --verbose --help | grep -A1 "Default options"

Instellingen komen onder de sectie [mysqld]. Na elke wijziging moet je de service herstarten:

sudo systemctl restart mysql   # of mariadb

InnoDB buffer pool: de meest kritieke instelling

Als je maar één parameter mocht afstellen, zou dat innodb_buffer_pool_size zijn. InnoDB houdt data en indexen in deze geheugenpool; hoe groter die is, hoe meer leesacties uit het RAM komen in plaats van van schijf. De vuistregel is 50 tot 70% van het totale RAM op een dedicated databaseserver. Als een gameserver, PHP of andere services dezelfde machine delen, wees dan voorzichtiger.

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

Voor grote pools (boven 8 GB) kun je de pool over meerdere instances verdelen om interne lock-contentie te verminderen; elke instance zou idealiter minstens 1 GB moeten zijn. Om te controleren of de pool groot genoeg is, kijk je naar de hit rate:

SHOW ENGINE INNODB STATUS\G

Als de Buffer pool hit rate in de uitvoer dicht bij 1000/1000 ligt, komen vrijwel alle leesacties uit het geheugen. Is die laag, overweeg dan de pool te vergroten.

InnoDB-logs en schrijfgedrag naar schijf

Bij schrijfintensieve workloads zijn de grootte van de redo log en het flush-beleid doorslaggevend. Als innodb_log_file_size (in MySQL 8 ook te beheren via innodb_redo_log_capacity) te klein is, maakt MySQL voortdurend checkpoints en zakt de schrijfprestatie in.

innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

• innodb_flush_log_at_trx_commit = 1 geeft volledige ACID-veiligheid; elke commit wordt naar schijf geschreven. Houd dit aan voor financiële data.
• De waarde 2 schrijft de log één keer per seconde naar schijf; je wint flink aan schrijfdoorvoer met het risico de laatste ~1 seconde te verliezen bij een plotse stroomuitval. Voor niet-kritieke data zoals game-inventaris is dit een redelijke afweging.
• O_DIRECT omzeilt de cache van het besturingssysteem en voorkomt dubbele buffering.

Verbindingsinstellingen: max_connections en de thread cache

Wanneer veel gelijktijdige spelers of webverzoeken binnenkomen en max_connections te laag staat, krijg je de fout "Too many connections". Maar dit blindelings verhogen is ook gevaarlijk: elke verbinding verbruikt geheugen.

max_connections = 200
thread_cache_size = 32
wait_timeout = 120
interactive_timeout = 120

thread_cache_size hergebruikt de threads van gesloten verbindingen, wat de kosten van een nieuwe verbinding verlaagt. wait_timeout sluit inactieve verbindingen zodat vastgelopen clients niet eindeloos een slot bezet houden. Gebruikt je applicatie een connection pool (bijvoorbeeld de persistente verbindingen van Laravel), stem de timeout daar dan op af.

Query cache en versieverschillen

Let op query_cache_size, dat in veel oude tutorials voorkomt: de query cache stond standaard uit in MySQL 5.7 en is volledig verwijderd in MySQL 8.0 omdat hij lock-contentie veroorzaakte bij hoge gelijktijdigheid. Gebruik je MySQL 8 en voeg je die regels toe aan my.cnf, dan start de service niet. De moderne aanpak is cachen in de applicatielaag (Redis, Memcached) of vertrouwen op de InnoDB buffer pool.

Blaas ook de buffers per verbinding niet op; deze worden voor elke verbinding apart toegewezen:

sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

Je wijzigingen verifiëren en meten

Waarden instellen is de helft van het werk; het effect meten is de andere helft. Controleer de actieve waarden:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Om trage queries op te sporen, schakel je de slow query log in en vind je de echte knelpunten:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Van de kant-en-klare tools analyseert mysqltuner.pl een draaiende server en geeft het concrete aanbevelingen; pas ze echter niet blindelings toe — pas elke alleen toe nadat je begrijpt waarom hij wordt voorgesteld. Onthoud: zelfs de beste my.cnf redt geen slecht geschreven query of een ontbrekende index. Verbeter eerst je queries en indexen met EXPLAIN en stem daarna de configuratie fijn af.

Veelgestelde vragen

Moet ik MySQL herstarten voor wijzigingen in my.cnf?

Voor de meeste InnoDB-instellingen (buffer pool, logbestandsgrootte) wel. Veel variabelen kun je echter dynamisch tijdens runtime wijzigen met SET GLOBAL — bijvoorbeeld SET GLOBAL max_connections = 300;. Dynamische wijzigingen gaan bij een herstart verloren; om ze permanent te maken moet je ze alsnog in my.cnf zetten.

Wat gebeurt er als ik innodb_buffer_pool_size te groot maak?

Je kunt het RAM uitputten en het besturingssysteem of andere services naar swap duwen; op dat moment zijn de prestaties slechter dan met een kleine pool. Onthoud dat naast de buffer pool ook elke verbinding geheugen gebruikt, dus laat de machine genoeg totale ruimte over.

Gebruik ik MySQL of MariaDB, en zijn de instellingen hetzelfde?

De kern-InnoDB- en verbindingsinstellingen zijn grotendeels gedeeld. Toch zijn er verschillen in de query cache, thread pool en sommige standaardwaarden. Gebruik de uitvoer van SHOW VARIABLES als referentie om de instellingsnamen voor jouw versie te bevestigen.

Vertraagt je database je server? Heb je hulp nodig bij MySQL/MariaDB-tuning, indexontwerp of query-optimalisatie voor een gameserver of webproject, neem dan contact met me op — ik bekijk je huidige configuratie en stel een concreet verbeterplan op.