De oorzaak kan fragmentatie zijn. Naarmate je applicatie records toevoegt, wijzigt en verwijdert, ontstaat er ongebruikte ruimte in de onderliggende databasetabellen. Die ruimte wordt niet altijd opnieuw benut, waardoor tabellen aanzienlijk groter kunnen worden dan nodig.
Met één SQL-query kun je daar iets aan doen:
OPTIMIZE TABLE orders;Waarom worden MySQL-tabellen steeds groter?
MySQL en MariaDB gebruiken standaard InnoDB als storage engine. InnoDB bewaart tabelgegevens en indexen in vaste blokken die pages worden genoemd. Standaard zijn die 16 KiB groot.
Een page bevat meerdere records. Wanneer een framework of CMS nieuwe records toevoegt, probeert InnoDB die zo efficiënt mogelijk over de beschikbare pages te verdelen.
Maar een database is voortdurend in beweging. Denk aan een webshop waarin bestellingen binnenkomen, orderstatussen veranderen en oude gegevens worden opgeschoond. Of een WordPress-website die regelmatig revisies, transients en tijdelijke gegevens aanmaakt en verwijdert.
Bij het verwijderen van records worden de bijbehorende bytes niet onmiddellijk uit het tabelbestand op disk verwijderd. InnoDB verwerkt verwijderingen en houdt de vrijgekomen ruimte beschikbaar voor toekomstige wijzigingen.
Dat klinkt efficiënt, maar leidt na verloop van tijd tot een probleem: de vrije ruimte raakt verspreid over verschillende pages.
Hoe ontstaat fragmentatie?
Stel dat een tabel oorspronkelijk 1 miljoen records bevat, verspreid over duizenden datapages. Vervolgens verwijder je de helft van de records.
Die verwijderde records liggen waarschijnlijk verspreid door de tabel. Sommige pages zijn nog grotendeels gevuld, terwijl andere nog maar een fractie van hun oorspronkelijke inhoud bevatten.
InnoDB kan de vrijgekomen ruimte opnieuw gebruiken. Maar nieuwe records passen niet altijd in de beschikbare gaten, bijvoorbeeld omdat ze groter zijn dan de oorspronkelijke records.
Het gevolg is dat de database meer pages kan gebruiken dan nodig is om de overgebleven gegevens op te slaan.
Dit heet fragmentatie.
Waarom kan fragmentatie je database vertragen?
Een gefragmenteerde tabel is niet per definitie traag. InnoDB is ontworpen om voortdurend records te verwerken en kan prima omgaan met een zekere hoeveelheid ongebruikte ruimte.
Merkbare slechtere performance ontstaat wanneer tabellen aanzienlijk groter worden dan hun werkelijke inhoud vereist.
1. Queries moeten meer pages lezen
Stel dat je een tabel hebt met 5 miljoen records. Door langdurige fragmentatie zijn die verdeeld over veel meer pages dan nodig.
Wanneer MariaDB een groot deel van de tabel moet doorlopen, bijvoorbeeld voor een rapportage, moet het langs extra pages.
Hoe meer pages, hoe trager. Pages moeten eerst vanuit de InnoDB-bufferpool worden gelezen, of vanaf disk worden opgehaald als ze nog niet in het geheugen staan.
Door de tabel opnieuw op te bouwen, met exact de benodigde hoeveelheid pages, wordt de query dus altijd sneller.
2. Je InnoDB-bufferpool wordt minder efficiënt gebruikt
De de InnoDB-bufferpool is het gedeelte van het RAM waarin MySQL en MariaDB veelgebruikte tabelgegevens en indexen bewaren.
Stel dat een tabel 8 GiB aan datapages gebruikt, terwijl de overgebleven gegevens na het opschonen eigenlijk nog maar 4 GiB nodig hebben.
Zolang die gegevens verspreid blijven over de oorspronkelijke -overtollige- pages, heeft de database meer ruimte in de bufferpool nodig dan wanneer de tabel compact wordt opgeslagen.
3. Je gebruikt onnodig veel diskruimte
Naast de performance-verbetering is er een praktische reden om tabellen te optimaliseren: opslagruimte.
Wanneer je 10 GiB aan oude logs verwijdert, betekent dat niet automatisch dat het tabelbestand op disk 10 GiB kleiner wordt.
Bij InnoDB blijft vrijgekomen ruimte vaak beschikbaar binnen de bestaande tablespace. De database kan die ruimte opnieuw gebruiken, maar het besturingssysteem krijgt haar niet vanzelf terug.
Daardoor kan een tabel tientallen gigabytes op disk innemen, terwijl de actuele records veel minder ruimte nodig hebben.
Verwacht je dat een tabel niet meer zo groot groeit, geeft een 'optimize' die ruimte terug aan het OS, zodat de diskruimte voor andere doeleinden vrijkomt.
Hoe controleer je of een tabel geoptimaliseerd kan worden?
In MySQL en MariaDB kun je via information_schema.tables een indicatie krijgen van de omvang van tabellen en de gerapporteerde ongebruikte ruimte.
SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2)
AS data_mb,
ROUND(index_length / 1024 / 1024, 2)
AS index_mb,
ROUND(data_free / 1024 / 1024, 2)
AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND engine = 'InnoDB'
ORDER BY data_free DESC;De kolom DATA_FREE geeft een indicatie van beschikbare ruimte binnen de opslagstructuur. Een indicatie dus: bij InnoDB is die waarde geen betrouwbare maat voor alle fragmentatie binnen afzonderlijke pages. Ook kan de waarde betrekking hebben op een gedeelde tablespace, waardoor je voorzichtig moet zijn met de interpretatie.
Waarom zou je niet iedere dag alle tabellen optimaliseren?
Als OPTIMIZE TABLE ruimte terugwint en queries kan versnellen, waarom voer je het dan niet voortdurend uit?
Omdat het optimaliseren zelf een zware databaseoperatie kan zijn.
Bij een volledige InnoDB-tabelrebuild moet MariaDB de bestaande gegevens lezen, de tabel opnieuw opbouwen en de indexen verwerken. Dat kost CPU-capaciteit, geheugen, disk-I/O en mogelijk veel tijdelijke opslagruimte.
Hoe groter de tabel, hoe meer tijd en capaciteit dat kost. Terwijl de optimize draait, kan de tabel gelockt worden. Moderne MySQL- en MariaDB-versies bieden mogelijkheden om veel tabelrebuilds grotendeels 'online' uit te voeren, maar dat betekent niet dat ze zonder impact plaatsvinden. Onder andere bij het voorbereiden en afronden van de operatie kunnen metadata-locks nodig zijn. Andere queries moeten dan wachten, wat in het slechtste geval zorgt voor downtime van sites en applicaties.
Daarom is het verstandig om optimalisatieoperaties van -voornamelijk- grote tabellen tijdens rustige perioden uit te voeren.
Automatische databaseoptimalisatie met Cyberfusion Core
Het handmatig uitvoeren van OPTIMIZE TABLE is eenvoudig. Vaststellen welke tabellen er baat bij hebben, rekening houden met de belasting en zorgen dat databaseonderhoud regelmatig plaatsvindt, vraagt meer aandacht.
En de database blijft veranderen. Een tabel die vandaag compact is opgeslagen, kan over enkele maanden opnieuw veel ongebruikte ruimte bevatten.
Voor developers die vooral bezig willen zijn met het bouwen van applicaties, is dat terugkerend infrastructuurwerk.
Laat Core je MariaDB-tabellen wekelijks optimaliseren
Op Cyberfusion Core kun je voor MariaDB-databases de optie Optimising inschakelen. Core voert vervolgens iedere week automatisch OPTIMIZE TABLE uit op de tabellen van die database. Daardoor worden sites en applicaties sneller, en komt diskruimte vrij.
Core biedt daarnaast inzicht in de grootte van je databases, tabellen en indexen. Als échte managed-hoster controleren we automatisch of de InnoDB-bufferpool nog groot genoeg is voor goede databaseperformance. Dreigt de bufferpool te klein te worden, dan trekken we aan de bel. Zo kunnen jullie developers zich richten op de applicatie zelf.
