Gas geven met MySQL - deel 2

Par: Edco Wallet 2-7-2021

Catégories:Beheer, BLOG, DBMS, MariaDB, MySQL, Technisch, Tips,

In deel 1 van deze blog – Help! MySQL groeit uit mijn jasje – schreven we over performance-uitdagingen waar je mee te maken kan krijgen als je database groeit en met welke valkuilen je rekening moet houden als je goed voorbereid wil zijn. Kortom: waar je vooraf al over na moet denken om flexibel mee te groeien met je database. In deze blog willen we je de 6 belangrijkste onderdelen meegeven waar je op kunt letten en welke parameters van invloed zijn.

1 Meten is weten

Voordat je überhaupt aan knoppen gaat draaien is het goed om inzicht te krijgen hoe je database functioneert op de verschillende onderwerpen. Monitoring is key! Monitoring is in feite niets anders dan data verzamelen die je wat meer diepgaande informatie geven over bijvoorbeeld queries, deadlocks, transactions, buffers etc.

Grafana en Prometheus zijn bouwstenen waarmee monitoring kan worden opgezet. Door exporters te scrapen in Prometheus en dashboards toe te voegen aan Grafana ontstaat een monitoringoplossing. Die exporters en dashboards zijn vaak op GitHub terug te vinden, evenals diverse plugins. Er zijn ook kant-en-klare (open-source) monitoringoplossingen, zoals Percona Management en Monitoring (PMM), beschikbaar in de markt voor verschillende type database-engines, waaronder MySQL-varianten en MariaDB, maar ook voor DBaaS als AWS Aurora, RDS en Azure SQL-database.

2 Query-analyse

Een manier om te beoordelen hoe je server taken uitvoert, is door de belasting van de server te monitoren. Zo kun je de zwaarste queries identificeren voor verdere tuning. Hier is ‘tijd’ de belangrijkste factor, want als je een query uitvoert op de server, is vooral van belang hoe snel het voltooid is. Belasting monitoren kun je eenvoudig inregelen met tools zoals MySQL Enterprise Monitor's query analyzer of het pt-query-digest van de Percona Toolkit. Deze tools leggen queries vast die de server uitvoert, waarbij de zwaarste en meest tijdrovende taken direct naar boven komen. Dat geeft inzicht in waar je op zou moeten focussen.

3 Hardware

Als je database niet doet wat jij wil, is je primaire reactie om daar direct in te duiken. Toch is het raadzaam om eerst je hardware na te lopen en te beoordelen of daar wellicht zaken niet goed functioneren of niet in balans zijn. Vier basisingrediënten zijn van belang: CPU, RAM, schijven en netwerk. Als één van deze vier onderdelen niet goed functioneert of niet voldoende kan ‘geven’ zal je databaseserver slecht functioneren.

Zorg ervoor dat deze onderdelen goed in balans zijn. Een snelle processor is mooi, zeker omdat MySQL queries niet parallel over meerdere CPU’s uitgevoerd kunnen worden, maar te weinig geheugen zorgt er bijvoorbeeld ook voor dat de database op schijven gaat cachen en dat is killing voor je performance. En niet geheel onbelangrijk, je zal in de databasesettings wel dit geheugen moeten toewijzen zodat het ook daadwerkelijk benut gaat worden. En andersom, als je in de databasesettings veel geheugen toewijst, moet het wel beschikbaar zijn.

4 Operating system

Operating-system-tuning is ook heel belangrijk. Denk aan swappiness, IO scheduler, NUMA technology, huge pages, etc. In deze blog van Percona kun je daar meer over lezen.

5 Inefficiënt datamodel

We hebben er al eerder een blog over geschreven: de invloed van datamodellering wordt vaak onderschat. Zaken als indexeringsstrategie, datatypen en volgorde van kolommen dienen goed aan te sluiten bij de actuele belasting van de database. Veel databases worden nog vaak opgeleverd zonder gedegen indexeringsstrategie of met een verouderde indexering. Het is aan te raden om een indexeringsstrategie te implementeren of om de huidige indexering te reorganiseren. De keuze voor datatypen moet zorgvuldig worden gemaakt. Wanneer bijvoorbeeld huisnummers moeten worden opgeslagen kan doorgaans worden volstaan met een smallint of int en zorgt een bigint voor verspilling van ruimte.

Ook de volgorde van kolommen of velden kan van wezenlijke invloed zijn op de uiteindelijke prestaties van de database. Vaak wordt er voorbijgegaan aan het feit dat de specifieke kenmerken van kolommen (verplicht/niet-verplicht) en het datatype (fixed/variable width) de daadwerkelijke opslag op de data-pages beïnvloeden.

Indexen helpen je bij de performance, maar tegelijk kan het je ook enorm afremmen. We komen nog regelmatig databases tegen met 300+ tabellen en ook 300+ indexen waarvan er maar tien gebruikt worden. Die overige 290 vragen geheugen en vergen ook onderhoud vanwege mutaties in de tabellen. We komen nog steeds databases tegen met een index op zelfs elke kolom. Kijk waar indexen je query’s kunnen helpen maar overdrijf niet. Er zal ook regelmatig onderhoud moeten plaatsvinden op de indexering. Statistieken opfrissen, de indexen reorganiseren of zelfs verwijderen en waar nodig opnieuw aanmaken. Tabellen die vaak worden gemuteerd zullen hier veel baat bij hebben.

6 Database-settings

InnoDB is de standaard storage-engine sinds MySQL 5.5 en het wordt veel vaker gebruikt dan welke andere storage-engine dan ook. Het moet wel zorgvuldig worden geconfigureerd.

Let op: Er is onderscheid tussen settings voor de gehele MySQL instance, zoals innodb_buffer_pool_size, en settings per connectie, zoals tmp_table_size en max_heap_table_size. Door deze laatste kan een kleine wijziging (16 MB meer) grote gevolgen hebben (bij 1000 connecties potentieel 16 Gb meer memory gebruik).

Innodb_buffer_pool_size: De bufferpool is waar data en indexen worden gecached: door deze zo groot mogelijk te maken, gebruik je het RAM-geheugen en niet je disks voor de meeste leesoperaties. Typische waarden zijn 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB).

Innodb_log_file_size: De redo logs worden gebruikt om ervoor te zorgen dat de schrijfacties snel en duurzaam zijn, ook tijdens crash-recovery. Sinds MySQL 5.5 is crash-recovery-performance sterk verbeterd zodat je nu goede schrijfperformance en snelle crash-recovery kunt hebben. Tot MySQL 5.5 was de totale redo-loggrootte gelimiteerd tot 4GB (de standaard is om 2 log bestanden te hebben). Dit is opgeheven vanaf MySQL 5.6.

Max_connections: Als je vaak de foutmelding ‘Too many connections’ krijgt, dan is max_connections te laag. Het komt vaak voor dat, omdat de applicatie de verbindingen met de database niet goed afsluit, je veel meer nodig hebt dan de standaard 151 verbindingen. Het grootste nadeel van hoge waarden voor max_connections (zoals 1000 of meer) is dat de server niet meer zal reageren als hij om wat voor reden dan ook 1000 of meer actieve transacties moet uitvoeren. Het gebruik van een connection-pool op applicatieniveau of een thread-pool op MySQL-niveau kan hier helpen.

Query_cache_size: Specificeert de grootte van de cache van MySQL queries die wachten om uitgevoerd te worden. De aanbeveling is om te beginnen met kleine waarden rond de 10MB en dan te verhogen tot niet meer dan 100-200MB. Met te veel queries in de cache kan er ophoping van queries ‘Waiting for cache lock’ ontstaan. Als je queries blijven back-uppen, is het beter om EXPLAIN te gebruiken om queries te beoordelen en manieren te vinden om ze efficiënter te maken.

Andere specifieke InnoDB settings die niet per se altijd op default moeten blijven staan:

Bovenstaande is uitgebreid beschreven op de website van Percona.

Eén knop tegelijk

Zoals je wel kunt voorstellen uit al deze configuraties, opties en verbanden is je database niet statisch maar een dynamisch samenspel van parameters. Ons advies als je zelf aan de slag wilt: draai aan één knop tegelijk. Verander niet meerdere instellingen tegelijkertijd. Let ook goed op dat je de juiste configuratiefile gebruikt en vergeet niet aan versiebeheer te doen! Je zal niet de eerste zijn die niet meer terug kan…

Toch liever een expert inschakelen?

Vind je dit interessant en ligt dit in lijn met je hulpvraag maar wil je er toch liever een expert naar laten kijken? Je kan ons altijd vrijblijvend bellen of mailen, we zijn je graag van dienst!

Andere relevante blogs over dit onderwerp:

Help! MySQL groeit uit mijn jasje (deel 1)

Winst boeken met database performance

Zo krijg je je database weer fit en gezond

Invloed van datamodellering vaak onderschat

terug naar blogoverzicht