Než se začtete do článku, je potřeba vědět, že nejsem žádný databázový expert, proto pokud o této problematice víte více, budu jedině rád, když se s ostatními v diskuzi pod článkem o své znalosti podělíte.
Nedávno jsem řešil problém s MySQL serverem, který mi neustále zatěžoval procesor, i když to bylo zcela zbytečné. Můj problém byl v tom, že po restartu serveru vše chvíli fungovalo, ale po několika hodinách vyletělo vytížení MySQL na 90% a nechtělo klesnout. Jak jsem později zjistil, bylo to chybným defaultním nastavením serveru. Popravdě, celkem mě zaskočilo, že v základu není nastavení nastaveno tak, aby vyhovělo většině běžných případů (já vím, jsem najivní). Respektive, že nemá MySQL nějakou formu auto optimalizace, kterou by stačilo zapnout. Místo toho, musíte pracně zjišťovat, jestli databázi nezatěžuje nějaký nelegitimní provoz, v podobě třeba nějakého útoku apod. K prvotní analýze celkem postačí všudypřítomný PHPMyAdmin, který má v horním menu mnou nedávno znovu objevené tlačítko s názvem „Stav“, pod kterým se skrývá základní monitoring databáze. Spolu s jednoduchým poradcem, který vám pomůže s optimalizací. Bohužel moc na něj spoléhat nelze. Ani při nastavení, které mi doporučil, se stav vytížení moc nezlepšil. Nicméně jako dobrý základ to zcela postačí. Dále mi při analýze pomohl přítomný online grafický monitoring dotazů, procesů, vytížení procesoru, paměti aj, který mi trochu poodhalil co se děje vnitru databáze.
Po prvotní analýze jsem zjistil, že databázi velmi přetěžují WordPressové weby, které jsou velmi náročné na výkon serveru. Nasadil jsem tedy na weby cachovací pluginy, které znatelně serveru odlehčily, nicméně nezabránily přetěžování procesoru. Hledal jsem na webu dál a po pár dnech jsem narazil na zahraniční diskuzní fórum, kde nějaký zkušenější správce doporučil nastavit následující parametry MySQL v souboru my.conf.
thread_cache_size=8 sort_buffer_size=256K concurrent_insert=2
Tyto tři hodnoty jsem doplnil do konfiguračního souboru. Hned bylo poznat, že je server mnohem méně vytížený a že zvládá běžný provoz mnohem lépe. Pojďme si v jednoduchosti tyto tři nastavení přiblížit.
thread_cache_size – v defaultním nastavení je hodnota 0. To znamená, že je vypnutá cache pro spojení. Ty se pak musí znovu navazovat a to je zbytečné.
sort_buffer_size – čím více třídíte (ORDER BY a GROUP BY) data, tím více je potřeba tuto hodnotu nastavit
concurrent_insert – nastavení souběžného čtení a zápisu do tabulky.
Mezi další důležitá nastavení patří:
key_buffer_size – cache paměť pro ukládání indexů. Měla by být nastavena tak, aby hodnoty key_reads / key_read_request byly alespoň < 0.03, ideálně pak < 0.01. Tato hodnota je používána pro tabulky typu MyISAM.
innodb_buffer – stejný význam jako předchozí parametr, jen platí pro InnoDB tabulky.
max_connections – maximální počet najednou otevřených spojení. Hodnota se navyšuje s přibývajícím počtem klientů.
Optimalizace serveru je vždy tak trochu magie. Na internetu jsem nenašel jednotný názor na to, jak by se měl server ladit, takže si holt musíte trochu zaexperimentovat.