Het MySQL’s SHOW TABLE STATUS commando en phpMyAdmin geven een overzicht van het gebruik van elke tabel. Dit verschilt dikwijls (vrij veel) van wat er effectief in gebruik is. Hiervoor zijn verschillende redenen mogelijk (en vaak gaat het om een combinatie). We bespreken ze en kijken wat je eraan kan doen.
Oorzaken van deze verschillen
Indices
Wanneer je een tabel aanmaakt, kan je één of meerdere indices definiëren. Een index bevat een deel van de data van een specifieke kolom die gebruikt wordt om resultaten te filteren met een link naar de volledige data. Daardoor kan de data deels dubbel op de disk staan.
“Lege” plekken in de data-bestanden op disk
MySQL gaat data niet echt verwijderen nadat je ze in het midden van een tabel weg doet. MySQL gaat het enkel als “verwijderd” markeren zodat nieuwe data deze lege plek kan hergebruiken. Wordt die lege plek niet ingenomen door nieuwe data, dan blijft die toch ruimte innemen, waardoor je tabel meer ruimte inneemt dan MySQL toont.
Na verloop van tijd kunnen bepaalde tabellen (meestal cache tabellen) erg veel plaats innemen. Soms zelfs tot 50% of >100% van de effectieve data.
MySQL analyseert niet alle data (lazy counting)
Omdat zo’n volledig data-bestand uitlezen erg intensief is, probeert MySQL resources te besparen door bepaalde waardes te gaan schatten. MySQL doet dit omdat anders je MySQL queries niet meer zouden presteren zoals het moet, waardoor je website performantie verlies heeft.
De data die voor de steekproef wordt gebruikt, kan na verloop van tijd niet meer realistisch zijn voor het effectieve gebruik van je database. Wat het verschil verklaart tussen MySQL en de effectieve waarde op disk.
Teveel tabellen
Ook de structuur van je database is een belangrijke oorzaak om de verschillen te verklaren. Daarom raden we aan om enkel die tabellen toe te voegen die je echt nodig hebt, omdat elke tabel extra overhead veroorzaakt. Hoe meer tabellen, hoe groter de afwijking.
Oplossingen
Her-analyseer of optimaliseer de tabellen waarvan je vermoedt dat ze meer ruimte gebruiken dan MySQL rapporteert. Er zijn hiervoor verschillende manieren:
- Om ervoor te zorgen dat MySQL alle data van een tabel analyseert ipv. enkel een steekproef, gebruik je volgend commando:
ANALYZE TABLE <tablename>; - Om de lege plekken in je data-bestand op te lossen (die ook in je indices voorkomen) kan je een rebuild initiëren:
- Voor alle soorten tabellen:
OPTIMIZE TABLE <tablename>; - Voor InnoDB tabellen:
ALTER TABLE <tablename> ENGINE=InnoDB;(enkel gebruiken voor InnoDB tabellen, de eerste optie werkt voor alle soorten tabellen)
- Voor alle soorten tabellen:
Voordelen van een geoptimaliseerde database
Naast het feit dat je minder opslagruimte gebruikt op disk, zijn er nog voordelen van een geoptimaliseerde database:
- Grote databases vereisen veel memory en CPU van je systeem. Resources die je liever voor andere, vaak belangrijkere processen gebruikt. Met een geoptimaliseerde database verspil je die resources niet meer.
- Een back-up maken van je geoptimaliseerde database gaat sneller, waardoor de performantie van je server ook tijdens het maken van back-ups op niveau blijft.
- Queries uitvoeren op een database die niet geoptimaliseerd is, kan je website vertragen. Een geoptimaliseerde tabel gaat veel sneller resultaten aanleveren.