Optimalizace nastavení MySQL na VPS

Pro své projekty používám VPS u WEDOSu. Na jedné VPS jsou jednodušší weby, které nepotřebují tolik paměti. Na druhé VPS, s podstatně lepšími parametry (2 jádra CPU, 3GB RAM), běží můj zatím na zdroje nejnáročnější projekt – HamQTH. Databáze samotná na disku zabírá něco přes 8GB. V asi 30 tabulkách se nachází přes 10 milionů záznamů. S databází se hodně pracuje, neustále se do ní něco zapisuje. I takto vybavená VPS nestíhala, dusily ji požadavky na I/O.

Jak a co tedy optimalizovat?

Cache

Práci s databází velmi zrychlíte pokud bude mít v paměti co nejvíce dat. Jestliže používáte InnoDB engine, velmi doporučuji podívat se na nastavenou hodnotu pro tuto konfigurační proměnnou:

[cc lang=”bash”]
Innodb_buffer_po­ol_size = 1200
[/cc] kde
Innodb_buffer_po­ol_size – velikost paměti pro indexy a data v MB

Na svém serveru nastavuji tuto hodnotu asi na 40 až 50 procent dostupné RAM. Běží mi tam i Apache a další služby. Kdyby se jednalo pouze do databázový server, nastavte klidně 70% velikosti RAM. Aby se změny projevily je nutné restartovat MySQL server.

SQL dotazy

Dobře navržené dotazy a indexy v databázi jsou základ. V MySQL si můžete zapnout logování dotazů trvajících déle než je stanovený limit. V /etc/mysql/my.cnf přidejte resp. upravte tyto řádky:
[cc lang=”bash”]
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
[/cc] kde
log_slow_queries – cesta, kam logovat pomalé dotazy
long_query_time – logovat pouze dotazy trvající x sekund Po nastavení/změně hodnot je nutné restartovat MySQL server.

Zápisy na disk

I když máte na VPS vyhrazenou RAM, sdílíte procesor a co je hlavní, také disky. MySQL má ve výchozím nastavení zapnuto ukládání na disk po každém commitu. Pro bezpečnost dat je to velmi dobrá volba, bohužel v případě sdíleného I/O a velkého počtu zápisů, můžete stroj udusit. Pokud můžete přijmou malé riziko, kdy při pádu MySQL serveru přijdete o commity za poslední dvě sekundy, přidejte do my.cnf tento řádek:

[cc lang=”bash”]
innodb_flush_log_at_trx_commit = 2
[/cc]

Nyní po commitu zapíše MySQL data do cache operačního systému, která je na disk zapisována v pravidelných intervalech. Aby se změny projevily je nutné restartovat MySQL server.

Použitý engine

Pokud nepoužíváte fulltext nebo nemáte tabulky jen pro čtení, nemá smysl o MyISAM ani uvažovat. Tento engine při zápisu nebo změně kteréhokoliv řádku celou tabulku zamyká. Musí se tedy čekat až se operace provede a pak teprve je možné z tabulky číst. I malé procento zápisů způsobuje propad ve výkonu. Také nejsou podporovány transakce, cizí klíče a další funkce, které jsou pro InnoDB samozřejmostí.

A co dál?

Můžete ušetřit paměť nastavením mysql.allow_persistent na Off, použitím lehčí verze http serveru (lighttpdnginx), smazat nevyužívané indexy, pohrát si s nastavením innodb_log_fi­le_size atd.

komentáře 4

  1. Dobrý den četl sem Váš článek prosím chtěl sem se zeptat zda by jste mi jako odborník mohl poradit proč se můj web http://www.webus.cz tak pomalu načítá? Cca před měsícem došlo k rapidnímu zhoršení někdy na stránku čekám i 5 min 🙁 jako laik nejsem schopen posoudit proč ale wedos i programátor si mě přehazujou jako horký brambor. Hostuji u vedosu na virtuálním serveru VPS. Pokud mi dokážete dát radu byl bych Vám velmi vděčen. S přátelským pozdravem Petr Suchopár webus.cz

    1. Dobrý den,

      zkusil jsem použít profiler zabudovaný v Chrome a na první odpověd od serveru prohlížeč čekal 14 sekund. Načítání souvisejících obrázků a scriptů bylo pak již během vteřiny. Bez přístupu do VPS těžko soudit, ale je možné, že webový server čeká na databázi. Můžete zkusit do /etc/mysql/my.cnf do sekce [mysqld] doplnit:

      slow_query_log = 1
      slow_query_log_file = /var/log/mysql/slow.log
      long_query_time = 5
      log_queries_not_using_indexes = 1

      a restartujte mysql. V /var/log/mysql/slow.log najdete SQL příkazy, u kterých provádění trvá více než 5 sekund.

      Při načítání stránky můžete spustit htop a v reálném čase sledovat zatížení serveru.

      S pozdravem

      Petr Hložek

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *