Over de performance van MySQL

In de afgelopen weken liepen meerdere grote klanten aan tegen performanceproblemen op hun MySQL database. In alle gevallen was de bron van het probleem: schaalgrootte.

Voor een kleine site met weinig bezoekers hoef je weinig na te denken over de inrichting van je database en over de queries die je er op afvuurt. Het is eigenlijk altijd wel snel genoeg. Als je echter een productdatabase hebt van meer dan een miljoen artikelen, of wanneer je ruim 200 gelijktijdige bezoekers op je site hebt, dan is er wel wat planning nodig voor een goede performance.

Wat kan HostingXS doen?

Wij als provider kunnen heel wat tunen aan een MySQL server. Uiteraard is dat alleen mogelijk als je een eigen server afneemt.

In de standaard installatie is MySQL vrij klein ingericht. Vooral het geheugengebruik is laag. Daarmee is de standaard MySQL niet geschikt voor grote installaties. In grote installaties schrikken we helemaal niet van een MySQL-proces dat 2 GB geheugen in beslag neemt. Dit is één van de redenen waarom een grote drukke site een server met veel geheugen nodig heeft.

Gebruik je InnoDB tabellen? Laat het ons weten! Aan InnoDB valt heel veel te tunen voor grote installaties. Ook hier geldt: standaard is alles klein ingericht.

Met een paar relatief eenvoudige instellingen kunnen we MySQL veel beter geschikt maken voor grote installaties. De eerste bottleneck is hiermee weggenomen.

Wat kun je zelf doen?

Als provider kunnen we niet optimaliseren als de code van de klant niet goed werkt.

Een SQL-server is een erg ingewikkeld beest en er zijn heel veel zaken van invloed op snelheid.

We hebben een aantal voorbeelden van hoe het niet moet:

  • In een erg drukke webshop moest de hele artikeldatabase herschreven worden bij een mutatie op de voorraad. Dat gebeurt bij elke verkoop. Het herschrijven van die database kost meerdere seconden en gedurende die tijd kan niemand lezen: de site blokkeert. Bij een paar honderd gelijktijdige bezoekers en meerdere verkopen per minuut is dit funest.
  • Een andere webshop pakte een aantal willekeurige artikelen uit een database met ruim een miljoen records. Alleen werd dat niet aan MySQL overgelaten, maar werden de miljoen records ingeladen in PHP. Die maakte vervolgens de keuze.
  • Tenslotte een site met een enorme artikeldatabase en laadtijden van 30 seconden per pagina.

In alle gevallen konden we de database tunen om er wat meer snelheid uit te halen. Maar het was zeker niet genoeg.

InnoDB versus MyISAM

MySQL kan de data op meerdere manieren opslaan op de schijf. Standaard wordt er gekozen voor een MyISAM-indeling. Dit is een vrij eenvoudige manier van het beheren van gegevens. Door de eenvoud is het vaak ook een zeer snelle manier.

MyISAM heeft een aantal voordelen:

  • Het is zeer snel voor SELECT queries. Met andere woorden: lezen is erg snel.
  • Full text search is ondersteund. Dus stel dat je grote teksten opslaat (bijvoorbeeld een weblog) en je wilt kunnen zoeken op woorden in de tekst, dan is full text search van MyISAM erg snel.

Maar met de eenvoud komen ook nadelen:

  • Er kan slechts één proces schrijven in een tabel. Tijdens het schrijven blokkeren alle andere acties op die tabel. Gebruik dus vooral geen MyISAM op een order-database.
  • Schrijven in een tabel kan heel zwaar zijn. Een INSERT query is snel: die voegt gewoon data toe aan het einde van een bestand. Maar bij een UPDATE of DELETE loop je kans dat een aanzienlijk deel van de tabel opnieuw geschreven moet worden naar disk.
  • Er is geen manier om queries terug te draaien. Gaat een UPDATE op 1000 records halverwege mis? Dan is de UPDATE ook half uitgevoerd.
  • Foreign key checks bestaan niet. Verwijder je een record, dan blijven gekoppelde records gewoon staan met alle vervuiling van dien.

InnoDB is ontworpen om deze nadelen weg te nemen. Daardoor is InnoDB in de basis wel wat minder snel dan MyISAM. Het grote voordeel is echter: de snelheid neemt maar weinig af bij grote bezoekersaantallen:

  • Meerdere processen kunnen tegelijkertijd schrijven in een tabel. Er zijn geen blokkades.
  • Schrijven is niet zwaar. Elke schrijfactie voegt data toe aan een bestand.
  • Elke query is een transactie. Hierdoor wordt een query helemaal wél of helemaal níet uitgevoerd. Ook is het mogelijk om zelf een aantal queries te bundelen in een transactie.
  • Foreign keys worden afgedwongen. Als je een record verwijderd kan kun je als programmeur kiezen of alle gekoppelde records verwijderd worden, óf dat verwijderen helemaal niet mag als er nog gekoppelde records zijn.

Kortom: InnoDB is vaak een goede keus op grote, drukke sites. We willen zelfs zo ver gaan dat we aanraden altijd InnoDB te gebruiken, tenzij je een goede reden hebt om voor MyISAM te kiezen.

Queries optimaliseren

Het is erg belangrijk om queries zo optimaal mogelijk te schrijven. Er kunnen gigantische snelheidsverschillen zijn tussen goed geschreven queries en slecht geschreven queries.

Een aantal algemene tips:

  • Probeer het aantal queries te vermijden. Liever één ingewikkelde query die je meteen het juiste resultaat geeft, dan meerdere eenvoudige.
  • Laat MySQL je alle data geven die je nodig hebt, en vooral niet meer dan dat. Ga dus niet in programmacode nog verder filteren op resultaten.
  • Gebruik EXPLAIN. Hiermee geeft MySQL aan op welke manier een query uitgevoerd gaat worden. Gaan er indexen gebruikt worden of moeten er complete tabellen gescand worden?
  • Over indexen gesproken: indexen versnellen de database, maar teveel indexen zijn ook niet goed. Laat je leiden door EXPLAIN.
  • Gegenereerde queries zijn handig, maar vaak traag. Veel web frameworks kunnen automatisch queries maken zodat je als programmeur eigenlijk maar weinig met SQL te maken hebt. Maar die automatisch gegenereerde queries kunnen erg inefficiënt zijn.

Conclusie

Het is een hele kunst om de maximale snelheid uit MySQL te halen. Vertel ons wat je doet en waar je tegenaan loopt. Wij geven advies over de richting waarin je kunt optimaliseren.

Als je een grote, drukke site hebt, dan is gedegen kennis van MySQL onontbeerlijk. MySQL is de laatste jaren gegroeid tot een volwaardig databasesysteem dat vaak niet onder doet voor de grotere jongens zoals PostgresQL of zelfs Oracle.