MySQL systeem en database tuning

23-04-2004

Gert Dewit

Om te bepalen of je sowieso een hardware upgrade nodig hebt en om te weten te komen waar je dan best in investeert, is het een goed idee om wat performantie gegevens van je server te verzamelen.

De kans is namelijk groot dat je wel genoeg geheugen hebt maar dat het voor de verkeerde doeleinden wordt aangewend. Veel administrators gebruiken namelijk bij de installatie de waarden uit één van de voorbeeldconfiguraties die bij MySQL zaten (my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf). Dit zijn natuurlijk uitstekende voorbeelden om van te vertrekken maar elke MySQL server heeft een specifieke belasting en moet dus "bijgetuned" worden.

In veel, zoniet de meeste, gevallen is een slechte performantie van de database server zelfs geen zaak van systeem tuning maar wordt er gebrekking gebruik gemaakt van indexen of zijn de queries niet helemaal volgens de regels van de kunst geschreven. Ook voor database tuning is het van kapitaal belang dat de waarden van de MySQL variabelen verzameld worden.

Performantiegevens verzamelen

Het eerste wat er moet gebeuren is dus het verzamelen van performantiegegevens. Je kan de waarde van de statusvariabelen van MySQL steeds opvragen met het "SHOW STATUS" commando binnen een mysql shell of via het mysqladmin commando met de "extended-status" optie. Een bedreven perl of shell script programmeur lukt er waarschijnlijk wel in om dit met een zeker interval uit te voeren en naar een log file te schrijven.

Je kan natuurlijk ook gebruik maken van een bestaande tool. Kijk eens naar MySQL Activity Rapport, dit is een tool die SOS schreef naar aanleiding van een MySQL performantie tuning job en die gereleased is onder de GPL. Buiten het verzamelen van de data in een Round Robin Database zitten er in de package nog wat hulp scripts om de data in mooie grafieken te visualiseren. Als je wil weten hoe de output er uit ziet moet je even op deze demo pagina kijken.

Database tuning

Voor je de MySQL parameters begint te tunen is het zeer belangrijk om te weten wat de kwaliteit is van de databases die op je MySQL server draaien. Een applicatie die bijvoorbeeld weinig of geen gebruik maakt van indexen zal een belangrijke invloed hebben op de parameters die we zullen gebruiken om de MySQL server te tunen. Het heeft namelijk weinig of geen zin om de key cache te optimaliseren voor een applicatie die weinig of geen gebruik maakt van indexen.

Aan de hand van een goede tekst of grafische log kan je soms reeds een indicatie krijgen waar de probleemquery te zoeken valt. Als je bijvoorbeeld merkt dat er elke nacht om 4 uur een trage query loopt en dat is nu net het moment dat er een batch job gestart werd ...

Om na te gaan of je applicaties in orde zijn en ze dus correct gebruik maken van hun indexen heb je een aantal status variabelen ter beschikking.

select_full_join en select_range_check

Indien deze status variabelen niet nul zijn wil dit zeggen dat er minstens 1 query is die geen gebruik maakt van keys en indexen. Als dit in de tien- of honderdtallen loopt is een grondige doorlichting van de applicaties noodzakelijk.

handler_read_key en handler_read_rnd

Handler_read_key moet zo hoog mogelijk zijn, dit duidt op een correct gebruik van indexen en handler_read_rnd moet zo klein mogelijk zijn aangezien dit aangeeft dat er een sortering moest gebeuren van het queryresultaat.

Slow Queries

Om de meest problematische queries te identificeren biedt MySQL de mogelijkheid om deze te loggen naar een bestand. Standaard is een query slow als hij meer dan 10 seconden nodig heeft om uitgevoerd te worden, maar dit kan je wijzigen.

Door volgende regels aan je my.cnf bestand toe te voegen in de mysqld sectie, activeer je de slow query log en definieer je een query als slow indien hij meer dan 8 seconden duurt.

log-slow-queries=/var/log/mysql-slow.log
set-variable = long_query_time=8

Systeem tuning

Als je de applicaties en databases zo goed mogelijk hebt aangepast kan je het systeem gaan optimaliseren.

De eerste stap om MySQL optimaal laten te renderen is het optimaal compileren. Omdat dit reeds goed uitgelegd is in de [http://www.mysql.com/documentation/mysql/bychapter/index.html|MySQL handleiding] ga ik dat hier niet herhalen. Hercompileren behoort soms niet tot de opties omdat de admin zich er niet aan wil wagen of kan het zelfs helemaal niet omdat de bedrijfsregels stipuleren dat de door de leverancier aangeleverde binaries moeten gebruikt worden. Dat laatste meestal vanwege support redenen.

Voor je de parameters van MySQL begint te wijzigen kan je best je huidige configuratie even opslaan. Dit kan met : mysqladmin variables > mysql-vars-`date +%F`

Het date commando zorgt ervoor dat de bestandnaam ook een datum bevat. Ook je my.cnf bestand kan je best opslaan, bijvoorbeeld met : cp my.cnf my-`date +%F`.cnf

Key Cache

Aangezien een goed ontworpen database voor zijn queries extensief gebruik maakt van indexen is het dus zeer belangrijk dat deze zoveel mogelijk vanuit het werkgeheugen aangesproken kunnen worden. De key cache is bijgevolg een zeer belangrijke tuning parameter.

Als vuistregel wordt door MySQL vooropgesteld dat de verhouding tussen Key_reads en Key_read_request kleiner zou moeten zijn dan 1/100. Als je echter een applicatie hebt waar er zeer veel inserts, deletes en updates gebeuren kan een grotere verhouding perfect normaal zijn. Het is daarom belangrijk ook te loggen welk type van questions er gesteld worden. Indien 90 % van de questions uit selects bestaan wordt de key cache veel intensiever gebruikt dan wanneer er veel updates gebeuren.

De key_blocks_used status variabele kan ook helpen om na te gaan of je key cache groot genoeg is. Deze variabele geeft aan hoeveel van de key cache er nu werkelijk in gebruik is. Idealieter is de key cache nooit volledig bezet.

Als je de key_blocks_used status variabele vergelijkt met de key_buffer_size moet je er wel rekening mee houden dat je de blocks moet omrekenen naar bytes. Voor MySQL voor versie 4.1.1 was de block size 1024, vanaf versie 4.1.1 is dit de key_cache_block_size parameter.

versie < 4.1.1 : key buffer used = key_blocks_used x 1024
versie >= 4.1.1 : key buffer used = key_blocks_used x key_cache_block_size

Je kan de key cache aanpassen door je my.cnf bestand te wijzigen. set-variable = key_buffer_size=64MIndien je dit in de mysqld sectie van je my.cnf bestand zet reserveer je 64 MB voor je key cache

Table Cache

Standaard is MySQL geconfigureerd zodat de table cache 64 entries kan bevatten. In de meeste gevallen in dit echter ruim onvoldoende omdat voor elke connectie naar de databank de tabel bestanden geopend worden. Dit wil bijvoorbeeld zeggen dat indien je 32 gelijktijdige connecties hebt die een join uitvoeren over 3 tabellen je al minstens 96 open table entries hebt. Indien je MyISAM tabellen gebruikt verdubbelt dit getal omdat per open MyISAM tabel er 2 file descriptors gebruikt worden.

Omdat het constant openen en sluiten van bestanden een behoorlijke vertraging kan veroorzaken moet er voor gezorgd worden dat de table cache voldoende groot is.

Je table cache is volledig in gebruik als je open_tables status variable gelijk is aan je table_cache parameter. Dit is niet noodzakelijk een probleem maar meestal wel. De opened_tables status variabele kan een tweede indicatie leveren dat je table cache te klein is. Als de opened_tables variabele in verhouding tot de open_tables variabele heel hoog is, kan je best je table cache vergroten.

Hou er wel rekening mee dat indien je geregeld het FLUSH TABLES commando uitvoert, dit een impact heeft op de waarde van opened_tables. FLUSH TABLES sluit namelijk alle open tables en bijgevolg moeten deze bij de volgende query die deze tabellen gebruikt weer geopend worden.

set-variable = table_cache=256

Indien je dit in de mysqld sectie van je my.cnf bestand zet reserveer je 256 file descriptors voor je table cache.

Temporary Tables

Bij het sorteren van gegevens en ook bij sommige SELECT opdrachten maakt MySQL gebruik van tijdelijke tabellen. Om ervoor te zorgen dat het aanmaken van deze tijdelijke tabellen niet te veel tijd in beslag neemt door het gebruik van disk tabellen kan er geheugen gereserveerd worden in de temporary table cache.

Om na te gaan of je tmp_table_size parameter groot genoeg is kan je de created_tmp_tables en created_tmp_disk_tables status variabelen met elkaar vergelijken. Het spreekt voor zich dat het aantal created_tmp_disk_tables zo klein mogelijk moet zijn.

set-variable = tmp_table_size=64M

Indien je dit in de mysqld sectie van je my.cnf bestand zet reserveer je 64 MB voor je tijdelijke tabellen.

Overdrijf niet

Bij eender welke aanpassing van de MySQL variabelen moet je steeds in het achterhoofd houden dat een overschot niet per se een goede zaak is. Als je teveel geheugen gebruikt voor MySQL buffers blijft er niet genoeg over voor het systeem zelf. Het kan niet de bedoeling zijn dat de server begint te swappen.

Als je trouwens MyISAM tabellen gebruikt heb je er alle belang bij om zoveel mogelijk geheugen vrij te houden voor de buffer cache van het systeem. MySQL buffert bij MyISAM tabellen namelijk de MyISAM files niet zelf maar laat dit over aan het operating systeem.

Let ook op indien de server in kwestie naast MySQL database server ook nog andere taken moet uitvoeren. Als je bijvoorbeeld zowel de database als de http server op één machine lopende hebt zal je zeer goed moeten afwegen welke resources je vrij houdt voor elke service.

Blijf tunen

Het gebruik van een database server evolueert. Er komen databases bij, queries worden aangepast en/of toegevoegd, datasets groeien, ...

Database tuning is dus een continu process en een continue logging van de status variabelen is hierbij noodzakelijk. Zorg er wel voor dat je performantie logging de werking van je server niet verstoort.