10 essentiële prestatietips voor MySQL

Zoals met alle relationele databases, kan MySQL een gecompliceerd beest blijken te zijn dat in een oogwenk tot stilstand kan komen, waardoor uw applicaties in de steek blijven en uw bedrijf op het spel staat.

De waarheid is dat veelgemaakte fouten ten grondslag liggen aan de meeste prestatieproblemen van MySQL. Om ervoor te zorgen dat uw MySQL-server op topsnelheid zoemt en stabiele en consistente prestaties levert, is het belangrijk om deze fouten te elimineren, die vaak worden verdoezeld door een of andere subtiliteit in uw werklast of een configuratietrap.

Gelukkig blijken veel prestatieproblemen met MySQL vergelijkbare oplossingen te hebben, waardoor het oplossen van problemen en het afstemmen van MySQL een beheersbare taak wordt.

Hier zijn 10 tips om geweldige prestaties uit MySQL te halen.

MySQL-prestatietip nr. 1: Maak een profiel van uw werklast

De beste manier om te begrijpen hoe uw server zijn tijd doorbrengt, is door de werklast van de server te profileren. Door uw werklast te profileren, kunt u de duurste vragen blootleggen voor verdere afstemming. Hier is tijd de belangrijkste statistiek, want wanneer u een query op de server uitbrengt, geeft u nergens om, behalve hoe snel deze wordt voltooid.

De beste manier om uw werklast te profileren, is met een tool zoals de queryanalysator van MySQL Enterprise Monitor of de pt-query-digest van de Percona Toolkit. Deze tools vangen queries op die de server uitvoert en retourneren een tabel met taken gesorteerd op afnemende responstijd, waardoor de duurste en meest tijdrovende taken direct naar boven komen, zodat je kunt zien waar je je op moet concentreren.

Tools voor het profileren van werkbelasting groeperen vergelijkbare query's, zodat u de query's kunt zien die langzaam zijn, evenals de query's die snel zijn maar vaak worden uitgevoerd.

MySQL-prestatietip nr. 2: Begrijp de vier fundamentele bronnen

Om te functioneren, heeft een databaseserver vier fundamentele bronnen nodig: CPU, geheugen, schijf en netwerk. Als een van deze zwak, onregelmatig of overbelast is, zal de databaseserver waarschijnlijk slecht presteren.

Het begrijpen van de fundamentele bronnen is belangrijk op twee specifieke gebieden: hardware kiezen en problemen oplossen.

Zorg bij het kiezen van hardware voor MySQL voor goed presterende componenten rondom. Even belangrijk, balanceer ze redelijk goed tegen elkaar. Organisaties zullen vaak servers selecteren met snelle CPU's en schijven, maar die te weinig geheugen hebben. In sommige gevallen is het toevoegen van geheugen een goedkope manier om de prestaties met ordes van grootte te verbeteren, vooral bij workloads die schijfgebonden zijn. Dit lijkt misschien contra-intuïtief, maar in veel gevallen worden schijven te veel gebruikt omdat er niet genoeg geheugen is om de werkende set gegevens van de server op te slaan.

Een ander goed voorbeeld van deze balans betreft CPU's. In de meeste gevallen zal MySQL goed presteren met snelle CPU's, omdat elke query in een enkele thread wordt uitgevoerd en niet parallel kan worden geschakeld tussen CPU's.

Als het gaat om het oplossen van problemen, controleer dan de prestaties en het gebruik van alle vier de bronnen en let er goed op of ze slecht presteren of dat er simpelweg wordt gevraagd om te veel werk te doen. Deze kennis kan helpen bij het snel oplossen van problemen.

MySQL-prestatietip nr. 3: gebruik MySQL niet als wachtrij

Wachtrijen en wachtrijachtige toegangspatronen kunnen uw applicatie binnensluipen zonder dat u het weet. Als u bijvoorbeeld de status van een item zo instelt dat een bepaald werkproces het kan claimen voordat er actie op wordt ondernomen, maakt u ongewild een wachtrij aan. E-mails markeren als niet-verzonden, ze verzenden en vervolgens markeren als verzonden is een bekend voorbeeld.

Wachtrijen veroorzaken problemen om twee belangrijke redenen: ze serialiseren uw werklast, waardoor wordt voorkomen dat taken parallel worden uitgevoerd, en ze resulteren vaak in een tabel met onderhanden werk en historische gegevens van taken die lang geleden zijn verwerkt. Beide voegen latentie toe aan de applicatie en laden naar MySQL.

MySQL-prestatietip nr. 4: Filter eerst de resultaten op goedkoopste

Een geweldige manier om MySQL te optimaliseren, is door eerst goedkoop, onnauwkeurig werk te doen en vervolgens hard en nauwkeurig te werken aan de kleinere, resulterende set gegevens.

Stel dat u iets zoekt binnen een bepaalde straal van een geografisch punt. Het eerste hulpmiddel in de toolbox van veel programmeurs is de grootcirkelformule (Haversine) voor het berekenen van afstand langs het oppervlak van een bol. Het probleem met deze techniek is dat de formule veel trigonometrische bewerkingen vereist, die erg CPU-intensief zijn. Berekeningen met grote cirkels lopen meestal langzaam en zorgen ervoor dat het CPU-gebruik van de machine omhoog schiet.

Voordat u de grootcirkelformule toepast, moet u uw records verkleinen tot een kleine subset van het totaal en de resulterende set bijsnijden tot een precieze cirkel. Een vierkant dat de cirkel bevat (precies of onnauwkeurig) is een gemakkelijke manier om dit te doen. Op die manier wordt de wereld buiten het plein nooit geraakt door al die kostbare trig-functies.

MySQL-prestatietip nr. 5: Ken de twee dodelijke vallen voor schaalbaarheid

Schaalbaarheid is niet zo vaag als u misschien denkt. In feite zijn er nauwkeurige wiskundige definities van schaalbaarheid die worden uitgedrukt als vergelijkingen. Deze vergelijkingen laten zien waarom systemen niet zo goed schalen als zou moeten.

Neem de Universal Scalability Law, een definitie die handig is bij het uitdrukken en kwantificeren van de schaalbaarheidskenmerken van een systeem. Het verklaart schaalproblemen in termen van twee fundamentele kosten: serialisatie en overspraak.

Parallelle processen die moeten stoppen om iets geserialiseerd te laten plaatsvinden, zijn inherent beperkt in hun schaalbaarheid. Evenzo, als de parallelle processen de hele tijd met elkaar moeten chatten om hun werk te coördineren, beperken ze elkaar.

Voorkom serialisering en overspraak, en uw toepassing wordt veel beter geschaald. Wat vertaalt dit zich naar de binnenkant van MySQL? Het varieert, maar sommige voorbeelden zijn het vermijden van exclusieve vergrendelingen op rijen. Wachtrijen, punt nr. 3 hierboven, hebben de neiging om om deze reden slecht te schalen.

MySQL-prestatietip nr. 6: Focus niet te veel op de configuratie

DBA's hebben de neiging om enorm veel tijd te besteden aan het aanpassen van configuraties. Het resultaat is meestal geen grote verbetering en kan soms zelfs zeer schadelijk zijn. Ik heb veel "geoptimaliseerde" servers gezien die constant crashten, onvoldoende geheugen hadden en slecht presteerden toen de werkdruk wat intenser werd.

De standaardinstellingen die bij MySQL worden geleverd, zijn one-size-fits-none en ernstig verouderd, maar u hoeft niet alles te configureren. Het is beter om de grondbeginselen goed te hebben en andere instellingen alleen te wijzigen als dat nodig is. In de meeste gevallen kunt u 95 procent van de topprestaties van de server krijgen door ongeveer 10 opties correct in te stellen. De weinige situaties waarin dit niet van toepassing is, zijn randgevallen die uniek zijn voor uw omstandigheden.

In de meeste gevallen worden hulpprogramma's voor het afstemmen van servers niet aanbevolen, omdat ze de neiging hebben om richtlijnen te geven die in specifieke gevallen niet kloppen. Sommigen hebben zelfs gevaarlijk, onnauwkeurig advies gecodeerd, zoals cache-hitratio's en formules voor geheugengebruik. Deze waren nooit juist, en ze zijn zelfs nog minder correct geworden naarmate de tijd verstreek.

MySQL-prestatietip nr. 7: Pas op voor pagineringsvragen

Toepassingen die pagineren, hebben de neiging de server op de knieën te krijgen. Door u een pagina met resultaten te tonen, met een link om naar de volgende pagina te gaan, groeperen en sorteren deze applicaties zich doorgaans op manieren die geen indexen kunnen gebruiken, en ze gebruiken een LIMITen offsetdie ervoor zorgen dat de server veel werk genereert, vervolgens rijen weggooien.

Optimalisaties zijn vaak te vinden in de gebruikersinterface zelf. In plaats van het exacte aantal pagina's in de resultaten en links naar elke pagina afzonderlijk weer te geven, kunt u gewoon een link naar de volgende pagina weergeven. U kunt ook voorkomen dat mensen pagina's te ver vanaf de eerste pagina bezoeken.

Aan de vraagzijde, in plaats van LIMITmet te gebruiken offset, kunt u nog een rij selecteren dan u nodig heeft, en wanneer de gebruiker op de link "volgende pagina" klikt, kunt u die laatste rij aanwijzen als het startpunt voor de volgende reeks resultaten. Als de gebruiker bijvoorbeeld een pagina met rijen 101 tot en met 120 heeft bekeken, selecteert u ook rij 121; om de volgende pagina weer te geven, zou je de server vragen naar rijen groter dan of gelijk aan 121, limiet 21.

MySQL-prestatietip nr. 8: Sla statistieken gretig op, waarschuw met tegenzin

Monitoring en alarmering zijn essentieel, maar wat gebeurt er met het typische monitoringsysteem? Het begint met het verzenden van valse positieven en systeembeheerders stellen e-mailfilterregels in om de ruis te stoppen. Al snel is uw monitoringsysteem volledig onbruikbaar.

Ik denk graag op twee manieren na over monitoring: meetgegevens vastleggen en alarmering. Het is erg belangrijk om alle mogelijke statistieken vast te leggen en op te slaan, omdat u ze graag zult hebben als u probeert te achterhalen wat er in het systeem is veranderd. Op een dag zal zich een vreemd probleem voordoen en u zult het geweldig vinden om naar een grafiek te wijzen en een verandering in de werklast van de server te laten zien.

Daarentegen is er een neiging om veel te alert te zijn. Mensen waarschuwen vaak voor zaken als de bufferhitratio of het aantal tijdelijke tabellen dat per seconde wordt gemaakt. Het probleem is dat er geen goede drempel is voor een dergelijke ratio. De juiste drempel verschilt niet alleen van server tot server, maar ook van uur tot uur als uw werklast verandert.

Wees daarom spaarzaam en alleen op omstandigheden die duiden op een duidelijk, uitvoerbaar probleem. Een hitratio met een lage buffer kan niet worden uitgevoerd, noch duidt het op een echt probleem, maar een server die niet reageert op een verbindingspoging is een echt probleem dat moet worden opgelost.

MySQL-prestatietip nr. 9: Leer de drie regels voor indexering

Indexeren is waarschijnlijk het meest verkeerd begrepen onderwerp in databases, omdat er zoveel manieren zijn om in de war te raken over hoe indexen werken en hoe de server ze gebruikt. Het kost veel moeite om echt te begrijpen wat er aan de hand is.

Indexen dienen, mits goed ontworpen, drie belangrijke doelen in een databaseserver:

  1. Met indexen kan de server groepen aangrenzende rijen vinden in plaats van enkele rijen. Veel mensen denken dat het doel van een index is om individuele rijen te vinden, maar het vinden van enkele rijen leidt tot willekeurige schijfbewerkingen, wat traag is. Het is veel beter om groepen rijen te vinden, die allemaal of de meeste interessant zijn, dan om rijen één voor één te zoeken.
  2. Met indexen kan de server sorteren vermijden door de rijen in de gewenste volgorde te lezen. Sorteren is kostbaar. Het lezen van rijen in de gewenste volgorde gaat veel sneller.
  3. Met indexen kan de server alleen volledige vragen van de index beantwoorden, waardoor het niet nodig is om de tabel te openen. Dit wordt ook wel een dekkende index of een alleen-indexquery genoemd.

Als u uw indexen en zoekopdrachten kunt ontwerpen om deze drie mogelijkheden te benutten, kunt u uw zoekopdrachten verschillende ordes van grootte sneller maken.

MySQL-prestatietip nr. 10: Maak gebruik van de expertise van uw collega's

Probeer het niet alleen te doen. Als je een probleem aan het puzzelen bent en doet wat je logisch en verstandig lijkt, is dat geweldig. Dit werkt ongeveer 19 van de 20 keer. De andere keer ga je door een konijnenhol dat erg duur en tijdrovend zal zijn, juist omdat de oplossing die je probeert heel logisch lijkt.

Bouw een netwerk van MySQL-gerelateerde bronnen - en dit gaat verder dan toolsets en handleidingen voor probleemoplossing. Er zijn een aantal uiterst deskundige mensen op de loer op mailinglijsten, forums, Q & A-websites, enzovoort. Conferenties, beurzen en evenementen voor lokale gebruikersgroepen bieden waardevolle kansen om inzichten te verwerven en relaties op te bouwen met collega's die u in een mum van tijd kunnen helpen.

Wie op zoek is naar tools die deze tips aanvullen, kan de Percona-configuratiewizard voor MySQL, Percona Query Advisor voor MySQL en Percona Monitoring Plugins raadplegen. (Opmerking: u moet een Percona-account aanmaken om toegang te krijgen tot die eerste twee links. Het is gratis.) De configuratiewizard kan u helpen bij het genereren van een my.cnf-basisbestand voor een nieuwe server dat superieur is aan de voorbeeldbestanden die bij de server. De Query Advisor analyseert uw SQL om mogelijk slechte patronen te detecteren, zoals pagineringquery's (nr. 7). Percona Monitoring Plug-ins zijn een set van monitoring- en grafische plug-ins om u te helpen statistieken gretig op te slaan en met tegenzin te waarschuwen (nr. 8). Al deze tools zijn gratis beschikbaar.