Ibdata vreet mijn disk space op

Ibdata vreet mijn disk space op

In een vorig leven, toen ik begon met het gebruik van MySQL, hadden we alleen MyISAM table space in MySQL. Dit was niet ACID compliant, wat betekende dat het erg foutgevoelig was bij crashes enzovoort.

De zaken veranderden toen MySQL de InnoDB-opslagengine toevoegde. Maar eerlijk is eerlijk, InnoDB is een ingewikkeld beest. In het begin werd alle data opgeslagen in de buik van het ibdata1-bestand. Dit omvatte gedeelde tabelgegevens en bijvoorbeeld de Undo-log. Een groot nadeel van het ibdata-bestand is dat het alleen maar groeit en niet kan worden verkleind, zelfs niet als je gegevens uit een tabel verwijdert. De enige manier om schijfruimte terug te winnen is door een volledige export en import van je data uit te voeren.

De meest voor de hand liggende reden hiervoor is dat alle tabelgegevens erin worden opgeslagen. Gelukkig heeft MariaDB (tegen die tijd hadden we MySQL vervangen door MariaDB) de optie toegevoegd om data op te slaan in bestandsgebaseerde tabellenruimten, herkenbaar als .IBD-bestanden op je bestandssysteem. Voor de duidelijkheid: dit is de innodb_file_per_table-parameter. Hoewel dit een kleine prestatieboete met zich meebrengt, zorgden de voordelen ervoor dat we dit snel vergaten.

Het probleem

De bovenstaande oplossing werkte altijd zoals beloofd: geen groeiende ibdata-bestanden meer! Totdat we een paar weken geleden merkten dat een klant een constant groeiend ibdata-bestand had. Eerst wat achtergrondinformatie: deze workload voert veel schrijfbewerkingen (transacties) uit. Om eerlijk te zijn zouden het aantal transacties minder kunnen zijn als de klant wat efficiënter te werk ging, maar ze werken hier al aan. Laten we dat voor nu even vergeten.

Zoals ik hierboven al vermeldde, worden de Undo-logs opgeslagen in het ibdata-bestand. Elke uitgevoerde transactie wordt opgeslagen in de Undo-log, en alleen als de transactie succesvol is, wordt het als echte data gemarkeerd. Na een tijdje worden deze Undo-logs verwijderd. In deze workload bevatten transacties veel wijzigingen en kunnen ze potentieel groot worden. Belangrijk om te weten is dat de Undo-log niet de gewijzigde data zelf opslaat, maar eerder een verwijzing naar een specifieke "geschiedenisversie" van de data.

In dit geval verwachtten we enkele langlopende transacties. Dit controleerden we door het volgende uit te voeren:



Toen we het transactiegedeelte bekeken, zagen we niets vreemds. Maar toen herinnerde ik me dat MariaDB een handig hulpmiddel heeft genaamd innochecksum. Met deze tool kun je het ibdata-bestand analyseren, wat een goede indicatie kan geven van wat je schijfruimte opslokt. Je kunt het aanroepen met het volgende commando:

innochecksum --page-type-summary ibdata1

Houd er rekening mee dat dit bij grote bestanden enige tijd kan duren. Als je het uitvoert, toont het iets zoals dit:



(de getallen hierboven zijn fictief)

Een ding dat je meteen zou moeten opvallen, is de waarde van de Undo log-pagina's. Deze zijn veel te hoog! Dit wees me op de InnoDB history list length. Je kunt deze krijgen door: show status like ‘Innodb_history_list_%’.  Deze waarde geeft een goed idee van het aantal logpagina's die nog gepurged moeten worden. Er is geen officiële vuistregel, maar ik zou zeggen dat alles boven de 100.000 kritiek is. MariaDB heeft een purge-proces, maar dit kon duidelijk niet op tegen de hoeveelheid transacties.

De oplossing

Onze oplossing was vrij rigoureus, omdat we de schijfruimte moesten terugwinnen en de Undo-log (of eigenlijk de rollback-segmenten) uit het ibdata-bestand wilden halen. Dit betekende een export en import. Het eerste deel van de oplossing was het scheiden van de Undo-log. Dit deden we door de volgende configuratie toe te voegen:

innodb_undo_tablespaces=6
innodb_max_undo_log_size=5G
innodb_undo_log_truncate=ON

Dit vereist een lege database. Met andere woorden, dit kan niet worden gedaan op databases die al gegevens bevatten. Ook kun je deze instellingen achteraf niet wijzigen, dus kies ze zorgvuldig. Na het inschakelen van bovenstaande opties zie je zes undo00[1-6]-bestanden in je /var/lib/mysql-map. Je kunt deze bestanden zelfs op een andere schijf/partitie plaatsen als je wilt. Vanaf MariaDB 10.11 is innodb_undo_tablespaces standaard ingeschakeld (waarde: 2).

Het andere deel van onze oplossing was het verdubbelen van het aantal purge-threads, zodat het de transacties aankon. Dit deden we door het volgende toe te voegen:

innodb_purge_threads = 8

8 was voldoende in ons geval, maar dit is iets waar je mee moet experimenteren. Als je het te hoog instelt, kan dit prestatieverlies veroorzaken. Als de History Length nog steeds te hoog is, bijvoorbeeld door een eenmalige actie die je op je database uitvoert (zoals een grote purge), kun je proberen de innodb_purge_rseg_truncate_frequency tijdelijk te verlagen, bijvoorbeeld naar 32. Dit betekent dat InnoDB meer prioriteit geeft aan het purge-proces. Het nadeel hiervan is dat het je prestaties beïnvloedt, dus zorg ervoor dat je het terugzet naar de oorspronkelijke waarde zodra de History Length weer op het gewenste niveau is.

Conclusie

De InnoDB History Length is geen veelbekende metriek, maar het is een belangrijke indicator om in de gaten te houden. Hoewel de waarde veel fluctueert, mag deze op de lange termijn niet groeien. Dit kan namelijk een teken zijn dat het purge-proces de hoeveelheid schrijfbewerkingen niet kan bijhouden. Waarden boven de 100.000 moeten als kritiek worden beschouwd en kunnen de volgende problemen veroorzaken:
  1. Trage afsluitingen en opstarts van MariaDB; het hele Undo-bestand moet opnieuw worden verwerkt.
  2. Lagere prestaties.
  3. Een groeiend ibdata-bestand.
De stappen die we hebben genomen, hebben de problemen van deze specifieke klant opgelost.



    • Related Articles

    • Mijn verzonden e-mail wordt geweigerd

      Het kan helaas gebeuren dat e-mails niet aankomen bij de ontvanger. Er zijn meerdere mogelijke oorzaken, en hier vind je de meest voorkomende problemen en oplossingen! Als je na het lezen van deze handleiding nog steeds problemen ondervindt, kun je ...

    • DKIM: Instellen op Plesk

      E-mails die verzonden worden vanaf je Plesk server(s) worden betrouwbaar gezien wanneer deze voorzien is van DKIM. Wij vertellen je in deze blog: hoe je op je Plesk server DKIM aan kunt zetten dit op de gewenste Plesk pakketten kunt instellen de DKIM ...

    • E-mail werkt niet in Outlook 2016

      Heb je problemen met het instellen van je e-mail in Outlook 2016? Je bent niet de enige! We horen regelmatig van klanten dat hun e-mail niet goed werkt in deze versie van Outlook. Na wat onderzoek hebben we ontdekt dat het probleem vaak ligt bij een ...

    • Domein toevoegen aan spamfilter

      Sinds kort is het voor jou als klant van LinQhost mogelijk om zelf je spamfilter te beheren. Je kunt dus zelf domeinen aanmelden op het spamfilter, e-mailberichten uit quarantine halen en natuurlijk zelf een eigen whitelist/blacklist instellen. ...

    • Hosts file aanpassen in Windows 10

      Een vraag die wij regelmatig krijgen van onze klanten is: “Hoe kunnen wij onze website alvast testen; onze website draait nu nog bij een ander internetbedrijf”. Als jouw website op een server van Linqhost staat, maar waarvan de domeinnaam nog niet ...