Een van de belangrijkste instellingen voor de prestaties van MySQL en MariaDB is innodb_buffer_pool_size. Die bepaalt hoeveel RAM beschikbaar is om databasegegevens en indexen te cachen. Wanneer de bufferpool te klein wordt, moet de databaseserver steeds vaker gegevens van disk lezen. Dat kan de responstijden van alle applicaties op de server beïnvloeden.
Hoe ontdek je of de bufferpool groot genoeg is? Hoeveel geheugen heeft je database nodig? En hoe voorkom je dat je applicatie steeds trager wordt naarmate de database groeit?
Wat doet de InnoDB-bufferpool?
InnoDB is de standaard storage engine van MySQL en MariaDB. Deze bewaart tabellen en indexen op disk, opgedeeld in kleine blokken die pages worden genoemd. Standaard zijn die 16 KiB groot.
Wanneer een query gegevens nodig heeft, moet InnoDB de bijbehorende pages raadplegen. Om te voorkomen dat dezelfde gegevens telkens opnieuw van disk moeten worden gelezen, houdt InnoDB veelgebruikte pages in het RAM bij. Dat geheugen heet de bufferpool.
Stel dat je een webshop hebt met 100.000 producten. Bezoekers vragen voortdurend dezelfde productgegevens op, zoals de naam, prijs en voorraad. Wanneer die gegevens al in de bufferpool staan, kan InnoDB ze rechtstreeks uit het geheugen lezen. Staat de benodigde page er nog niet in, dan moet InnoDB deze eerst van disk ophalen.
Disks zijn altijd trager dan RAM. Zelfs de snelle NVMe-SSD's die we bij Cyberfusion gebruiken, hebben een aanzienlijk hogere toegangslatentie dan werkgeheugen.
Wat gebeurt er als de bufferpool te klein is?
De bufferpool heeft een beperkte capaciteit. Zodra deze vol raakt en InnoDB nieuwe pages moet laden, moet er ruimte worden vrijgemaakt. Daarvoor verwijdert InnoDB bestaande pages uit de bufferpool. Dit heet eviction.
Stel dat je applicatie regelmatig 6 GiB aan gegevens en indexen gebruikt, terwijl de bufferpool slechts 2 GiB groot is. InnoDB moet dan voortdurend pages uit het geheugen verwijderen om andere pages te kunnen laden. Wanneer een query later gegevens nodig heeft die inmiddels uit de bufferpool zijn verwijderd, moet InnoDB deze opnieuw van disk lezen.
Dat heeft verschillende gevolgen:
- Queries die normaal enkele milliseconden duren, worden onvoorspelbaar traag.
- Dezelfde query kan de ene keer snel zijn en de volgende keer veel langer duren.
- De disk-I/O neemt toe doordat gegevens steeds opnieuw worden ingelezen.
- Tijdens verkeerspieken kunnen de problemen groter worden, omdat meer queries dezelfde beperkte bufferpool gebruiken.
Dit kan ontstaan zonder dat je iets aan de applicatie hebt veranderd. De database groeit simpelweg door, totdat de bufferpool onvoldoende ruimte biedt.
Eén bufferpool voor alle databases
InnoDB gebruikt één bufferpool voor de volledige databaseserver. Alle databases en tabellen op dezelfde MariaDB-server delen dus hetzelfde geheugen.
Dat betekent dat een grote of drukke database ook de prestaties van andere databases kan beïnvloeden. Wanneer de ene applicatie veel nieuwe pages in het geheugen laadt, kunnen daarbij pages van een andere applicatie worden verwijderd. Hierdoor kan één database de responstijden van meerdere websites en applicaties beïnvloeden.
Je kunt de bufferpool bovendien niet rechtstreeks reserveren voor bepaalde tabellen. InnoDB bepaalt zelf welke pages in het geheugen blijven.
Een grote auditlog kan de hele applicatie vertragen
Neem een SaaS-applicatie met de volgende tabellen:
| Tabel | Grootte | Gebruik |
|---|---|---|
users | 500 MiB | Voortdurend |
subscriptions | 500 MiB | Voortdurend |
orders | 1 GiB | Voortdurend |
audit_log | 60 GiB | Incidenteel |
De bufferpool is 8 GiB groot. Tijdens normaal gebruik werkt de applicatie uitstekend: de veelgebruikte tabellen passen gemakkelijk in het geheugen en de historische auditlogs worden nauwelijks geraadpleegd.
Totdat iemand een rapportage over de afgelopen maanden opvraagt. MariaDB moet daarvoor mogelijk grote hoeveelheden historische gegevens lezen. De bijbehorende pages worden in de bufferpool geladen, waardoor andere pages plaats moeten maken.
InnoDB heeft hiervoor een beschermingsmechanisme: midpoint insertion. Nieuwe pages komen eerst in het minder beschermde deel van de LRU-lijst terecht. Pas wanneer ze opnieuw worden geraadpleegd en aan bepaalde voorwaarden voldoen, krijgen ze een positie in het beter beschermde deel. Dit beperkt de impact van grote scans, maar voorkomt niet dat zulke queries waardevolle cache-inhoud verdringen.
Wanneer de rapportage is afgerond, kan een deel van de gegevens die de applicatie normaal voortdurend gebruikt uit het geheugen verdwenen zijn. De volgende bezoekers moeten wachten totdat MariaDB die gegevens opnieuw van disk heeft geladen.
Hoe voorkom je dit?
Welke oplossing geschikt is, hangt af van de applicatie. Je kunt historische gegevens bijvoorbeeld archiveren, grote auditlogs op een afzonderlijke databaseserver onderbrengen of de betreffende queries optimaliseren.
Ook kan het verstandig zijn om grote hoeveelheden loggegevens buiten de relationele database op te slaan, bijvoorbeeld in Elasticsearch. Voor specifieke zoektoepassingen kan Meilisearch eveneens interessant zijn. Beide worden door Cyberfusion Core ondersteund.
Hoe groot is je huidige bufferpool?
Met een eenvoudige SQL-query achterhaal je hoeveel RAM MySQL of MariaDB momenteel voor de bufferpool reserveert:
SELECT
ROUND(@@innodb_buffer_pool_size / 1024 / 1024)
AS buffer_pool_size_mb;De uitkomst geeft de ingestelde grootte in MiB weer. Je kunt de configuratievariabele ook rechtstreeks opvragen:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';Een bufferpool van 128 MiB kan voor een kleine database voldoende zijn. Voor een drukbezochte webshop of een grotere SaaS-applicatie kan dat veel te weinig zijn.
Om te bepalen of je bufferpool groot genoeg is, moet je ook weten hoeveel ruimte je database inneemt. Met onderstaande query kun je de totale grootte van je InnoDB-tabellen en indexen per database opvragen:
SELECT
table_schema AS database_name,
ROUND(
SUM(data_length + index_length)
/ 1024 / 1024,
2
) AS size_mb
FROM information_schema.tables
WHERE engine = 'InnoDB'
AND table_type = 'BASE TABLE'
AND table_schema NOT IN (
'mysql',
'information_schema',
'performance_schema',
'sys'
)
GROUP BY table_schema
ORDER BY size_mb DESC;De uitkomst geeft een indicatie van de omvang van je gegevens en indexen. Stel dat je database 12 GiB groot is, terwijl de bufferpool slechts 4 GiB bedraagt. Dan weet je dat de volledige database onmogelijk in de bufferpool past.
Dat hoeft nog niet direct problemen te veroorzaken.
De working set bepaalt hoeveel geheugen je daadwerkelijk nodig hebt
Stel dat je SaaS-applicatie een database van 100 GiB heeft. Daarvan bestaat 90 GiB uit historische gegevens die gebruikers zelden raadplegen. De overige 10 GiB bevat actieve gebruikers, configuraties, recente transacties en andere gegevens die de applicatie voortdurend nodig heeft.
Die regelmatig gebruikte gegevens noemen we de working set. Wanneer de working set in de bufferpool past, kan de database uitstekend presteren, ook als de totale database veel groter is.
Er zit wel een risico aan deze benadering. De working set verandert gedurende de dag. Een grote export, een rapportage of een crawler kan gegevens raadplegen die normaal nauwelijks worden gebruikt. Daardoor kunnen veelgebruikte pages alsnog uit de bufferpool verdwijnen.
Het volledig in RAM laten passen van alle tabelgegevens en indexen is daarom een robuuste richtlijn, zeker bij bedrijfskritische applicaties waarbij voorspelbare responstijden belangrijk zijn.
Een database met een veel kleinere working set kan ook met minder geheugen prima functioneren. Maar hoe groter het verschil tussen de totale databasegrootte en de bufferpool, hoe belangrijker het wordt om het werkelijke gebruik te onderzoeken.
Hoe effectief gebruikt InnoDB het beschikbare geheugen?
De bufferpool-cachehitrate geeft aan welk deel van de logische leesverzoeken rechtstreeks vanuit de bufferpool wordt afgehandeld. Een hitrate van 99% betekent dat InnoDB 99% van die leesverzoeken vanuit het geheugen kan beantwoorden.
Je kunt de statistieken opvragen met:
SELECT
POOL_SIZE,
FREE_BUFFERS,
DATABASE_PAGES,
HIT_RATE
FROM information_schema.INNODB_BUFFER_POOL_STATS;De kolom HIT_RATE gebruikt een schaal van 0 tot 1000. Een waarde van 995 betekent dus een cachehitrate van 99,5%.
Je kunt de hitrate ook berekenen met twee statusvariabelen:
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Innodb_buffer_pool_reads',
'Innodb_buffer_pool_read_requests'
);Innodb_buffer_pool_read_requests telt de logische leesverzoeken. Innodb_buffer_pool_reads telt de verzoeken waarvoor InnoDB een fysieke read moest uitvoeren.
De cachehitrate bereken je door het aantal fysieke reads te delen door het aantal logische leesverzoeken, de uitkomst van 1 af te trekken en met 100 te vermenigvuldigen. Omdat deze tellers cumulatief zijn, kun je het beste twee metingen vergelijken. Zo zie je hoe de cache gedurende een bepaalde periode presteert, in plaats van alleen het gemiddelde sinds de laatste herstart.
Wat is een goede cachehitrate?
Bij een typische webapplicatie wil je doorgaans een cachehitrate die zo dicht mogelijk bij 100% ligt. Een vaste ondergrens is echter lastig te bepalen.
Bij een extreem drukbezochte applicatie kan zelfs 1% fysieke reads veel disk-I/O veroorzaken. Andersom hoeft een lagere hitrate bij een nauwelijks gebruikte database geen merkbare problemen op te leveren. Kijk daarom ook naar de hoeveelheid fysieke reads, de disk-I/O en de daadwerkelijke querytijden.
Bovendien zegt een hoge cachehitrate weinig over andere mogelijke knelpunten, zoals inefficiënte queries, lock contention of een hoge CPU-belasting.
Wat kun je doen als de bufferpool te klein is?
Er zijn twee belangrijke mogelijkheden: de hoeveelheid data verminderen of de bufferpool vergroten. Bij grotere applicaties kan het bovendien verstandig zijn om databases over meerdere servers te verdelen.
1. Verklein je database
Een database bevat vaak veel meer gegevens dan de applicatie daadwerkelijk nodig heeft. Denk aan logtabellen die jarenlang blijven groeien, historische gegevens waarvan de bewaartermijn al is verlopen, achtergebleven tabellen van verwijderde plugins, ongebruikte indexen en tijdelijke gegevens die nooit worden opgeschoond.
Vooral bij WordPress kunnen revisies, verlopen transients en oude metadata ervoor zorgen dat een database ongemerkt blijft groeien. Het verwijderen van overbodige gegevens kan de hoeveelheid opslagruimte die InnoDB nodig heeft aanzienlijk verminderen.
Let op: het verwijderen van records zorgt er niet automatisch voor dat de fysieke tabelbestanden kleiner worden. Daarvoor kan het nodig zijn de tabellen opnieuw op te bouwen, bijvoorbeeld met OPTIMIZE TABLE. Op Cyberfusion Core kun je dit periodiek laten uitvoeren.
Zo'n operatie kan belastend zijn en moet zorgvuldig worden gepland.
2. Vergroot de bufferpool
Als de gegevens die je applicatie nodig heeft niet in het beschikbare geheugen passen, kun je de bufferpool vergroten. De instelling daarvoor is innodb_buffer_pool_size.
Bijvoorbeeld:
[mysqld]
innodb_buffer_pool_size = 8GDe exacte mogelijkheden om deze instelling aan te passen verschillen per databaseversie. Houd er bovendien rekening mee dat de bufferpool slechts een deel van het geheugenverbruik van de databaseserver vertegenwoordigt.
MySQL en MariaDB hebben ook RAM nodig voor verbindingen, sorteeroperaties, tijdelijke tabellen en interne buffers. Wanneer PHP, nginx en de database op dezelfde server draaien, moet je ook rekening houden met hun gezamenlijke geheugengebruik.
Een te groot ingestelde bufferpool kan daardoor juist nieuwe problemen veroorzaken, bijvoorbeeld wanneer de server onvoldoende RAM overhoudt voor de overige processen.
3. Geef de database een eigen server
Bij grotere applicaties kan het verstandig zijn om de database, of bepaalde onderdelen daarvan, op een afzonderlijke server te draaien. Denk bijvoorbeeld aan grote auditlogs die veel ruimte innemen, maar waarvoor de responstijd minder belangrijk is.
Zo voorkom je dat een incidentele zware query op historische gegevens de bufferpool van je belangrijkste applicatiedatabase onder druk zet.
En wie houdt dit allemaal in de gaten?
De bufferpool één keer correct instellen is relatief eenvoudig. De uitdaging zit in het bijhouden van de databasegroei, het herkennen van problemen en het tijdig aanpassen van de configuratie.
Daarvoor moet je regelmatig controleren hoeveel geheugen beschikbaar is, hoe groot de databases zijn geworden en of de huidige configuratie nog aansluit bij het gebruik. Zeker bij applicaties die voortdurend groeien, vraagt dat om blijvende aandacht.
Daar heb je als developer waarschijnlijk betere dingen voor te doen.
Cyberfusion Core controleert de bufferpool voor je
Op Cyberfusion Core controleren we automatisch of de InnoDB-bufferpool nog groot genoeg is om je databases goed te laten presteren. Dreigt de bufferpool te klein te worden, dan trekken we aan de bel. Zo kunnen we tijdig ingrijpen, voordat je applicatie trager wordt.
Core geeft bovendien inzicht in de grootte van je databases en de beschikbare bufferpool. Je kunt iedere database afzonderlijk bekijken, inclusief de omvang van de onderliggende tabellen en indexen. Zo zie je direct welke tabellen de meeste ruimte gebruiken.
Iedere 24 uur controleert Core of de totale omvang van de databasegegevens en indexen nog binnen de bufferpool past. Is dat niet het geval, dan verschijnt er een aanbeveling bij de health checks. Dat betekent niet automatisch dat er al performanceproblemen zijn, maar het is wel aanleiding om de geheugenconfiguratie en het databasegebruik te beoordelen.
Ook databaseoptimalisatie kun je automatiseren
Core biedt daarnaast de mogelijkheid om databases periodiek te optimaliseren. Wanneer je de optie Optimising voor een database inschakelt, voert Core wekelijks OPTIMIZE TABLE uit.
Hiermee worden tabellen opnieuw opgebouwd, waardoor ongebruikte ruimte kan worden vrijgemaakt en de fysieke omvang van de tabellen kan afnemen. Dat is onder andere nuttig voor databases waarin regelmatig grote hoeveelheden records worden verwijderd.
Omdat deze operatie impact kan hebben op de databasebelasting, moet je bij grote tabellen rekening houden met de benodigde resources en de uitvoeringsduur.
De MariaDB-configuratie beheren wij
De InnoDB-bufferpool maakt onderdeel uit van de MariaDB-configuratie die Cyberfusion beheert. Wanneer de bufferpool groter moet worden, beoordelen onze engineers de geheugenbehoefte van de database en de beschikbare capaciteit op de node. Indien nodig kijken we ook of het verstandig is om MariaDB, of bepaalde databases, naar een afzonderlijke node te verplaatsen.
Je hoeft je dus zelf niet bezig te houden met de serverconfiguratie, geheugenverdeling en bijbehorende infrastructuurwijzigingen.
Besteed je tijd aan je applicatie
Goed geschreven code en efficiënte queries zijn belangrijk voor de prestaties van je applicatie. De configuratie van de hostingomgeving heeft minstens zoveel invloed. Een te kleine InnoDB-bufferpool kan zelfs een uitstekend geoptimaliseerde applicatie flink vertragen.
Met Cyberfusion Core beheren we de serverconfiguratie, controleren we proactief de technische gezondheid van je hostingomgeving en helpen we bij het opschalen wanneer dat nodig is. Zo kunnen jullie developers zich richten op het bouwen en verbeteren van applicaties voor klanten.
