Jak na úpravy v MySQL?

Spousta lidí má tendenci trávit obrovské množství času laděním konfigurací. Většinou žádného zlepšení nedosáhnete a spíše serveru uškodíte.

Není výjimka, že u takto “optimalizovaných” serverů se často konfigurace rozpadaly, došla jim paměť a když se zátěž trochu intenzivně navýšila, server to neustál.

Ve většině případů můžete získat 95% špičkového výkonu serveru nastavením asi 10 možností správně. Několika situací se to netýká kvůli své specifikaci. Pro jednoduché aplikace, blogy nebo firemní stránky se to netýká vůbec.

Kde úpravy provádět?

Úpravy provádějte pouze v konfiguračním souboru na:

VPS Centrum

Vyzkoušejte zdarma naši aplikaci pro správu serveru a domén. Budete si připadat jako zkušený administrátor.

/etc/mysql/mariadb.conf.d/60-local.cnf

Můžete je provádět na více místech, ale pouze tady zajistíte hladký chod celého serveru. Aktualizace MySQL mohou některé soubory přepsat, ale na tomto místě jsou změny v bezpečí.

Ladění výkonu SQL je proces maximalizace rychlosti dotazů v relační databázi. Tento úkol obvykle zahrnuje více nástrojů a technik.

Tyto metody zahrnují:

• Vyladění konfiguračních souborů MySQL.
• Psaní efektivnějších databázových dotazů.
• Strukturování databáze za účelem efektivnějšího načítání dat.

Ještě než začneme…

I zkušení administrátoři se mohou dopustit chyb, které způsobí mnoho problémů. Než tedy začnete slepě aplikovat doporučení z tohoto příspěvku, mějte na paměti následující body:

• Měňte vždy jen jedno nastavení! Jen tak lze odhadnout, zda je změna přínosná.
• Většinu nastavení lze změnit za běhu pomocí SET GLOBAL. Je to velmi praktické a umožňuje to rychle vrátit změnu zpět, pokud způsobí nějaký problém. Nakonec však chcete, aby bylo nastavení v konfiguračním souboru upraveno trvale.
• Změna v konfigurace není vidět ani po restartu MySQL? Použili jste správný konfigurační soubor? Umístili jste nastavení do správné sekce? (nebojte ukážeme si níže)
• Server se po změně odmítá spustit: Použili jste správnou jednotku? Například innodb_buffer_pool_size by měla být nastavena v bajtech, zatímco max_connection je bezrozměrná.
• V konfiguračním souboru nepovolujte duplicitní nastavení. Pokud chcete mít přehled o změnách, použijte správu verzí.
• Neprovádějte naivní výpočty typu „můj nový server má 2x RAM, tak prostě nastavím všechny hodnoty 2x jako ty předchozí“.

Základní nastavení

Zde jsou 3 nastavení pro ladění výkonu MySQL, na která byste se měli vždy zaměřit. Pokud tak neučiníte, je velmi pravděpodobné, že se velmi rychle dostanete do problémů.

innodb_buffer_pool_size: Je nastavení číslo jedna, na které se zaměřte u každé instalace používající InnoDB. Ve vyrovnávací paměti se ukládají data a indexy do mezipaměti, pokud ji budete mít co největší, zajistíte, že pro většinu operací čtení budete používat paměť, a ne disky. Typické hodnoty jsou 5-6 GB (8 GB RAM), 20-25 GB (32 GB RAM), 100-120 GB (128 GB RAM).

innodb_log_file_size: Jedná se o velikost protokolů opakovaných záznamů. Protokoly redo se používají k zajištění rychlého a trvalého zápisu a také při obnově po havárii. Až do verze MySQL 5.1 bylo obtížné ji nastavit, protože jste chtěli jak redo logy pro dobrý výkon, tak malé redo logy pro rychlou obnovu po havárii. Naštěstí se od verze MySQL 5.5 výkonnost obnovy po havárii výrazně zlepšila, takže nyní můžete mít dobrý výkon zápisu i rychlou obnovu.

Freelo - Nástroj na řízení úkolů a projektů

Přidej se, pozvi svůj tým a klienty, rozděl práci a sleduj, jak se úkoly dají do pohybu.

Pokud začnete s innodb_log_file_size = 512M (což dává 1GB logů opakovaného zápisu), měli byste mít dostatek místa pro zápis. Pokud víte, že vaše aplikace je náročná na zápis, a používáte MySQL 5.6, můžete začít s innodb_log_file_size = 4G.

max_connections: pokud se často setkáváte s chybou „Too many connections“, je hodnota max_connections příliš nízká. Velmi často se stává, že kvůli tomu aplikace neuzavírá správně spojení s databází. Potřebujete mnohem více než výchozích 151. Hlavní nevýhodou vysokých hodnot max_connections (jako 1000 a více) je, že server přestane reagovat, pokud z nějakého důvodu musí spustit 1000 a více aktivních spojení. 

Nastavení InnoDB

InnoDB je výchozím úložným systémem od verze 5.5 MySQL a je mnohem častěji používán než jakýkoliv jiný úložný systém. Proto by měl být pečlivě nakonfigurován.

innodb_file_per_table: Toto nastavení říká InnoDB, zda má ukládat data a indexy ve sdíleném prostoru tabulek (innodb_file_per_table = OFF) nebo v samostatném souboru .ibd pro každou tabulku (innodb_file_per_table= ON). Soubor pro každou tabulku umožňuje získat zpět místo při zrušení, zkrácení nebo obnovení tabulky. Je také potřebný pro některé pokročilé funkce, jako je komprese. Neposkytuje však žádný výkonnostní přínos. Hlavním scénářem, kdy soubor na tabulku NECHCETE, je když máte velmi vysoký počet tabulek (řekněme 10k+).

V MySQL 5.6 je výchozí hodnota ON, takže ve většině případů nemáte co řešit. U předchozích verzí byste měli před načítáním dat nastavit hodnotu ON, protože má vliv pouze na nově vytvořené tabulky.

innodb_flush_log_at_trx_commit: výchozí nastavení 1 znamená, že InnoDB je plně kompatibilní s ACID. Je to nejlepší hodnota, pokud je vaším hlavním zájmem bezpečnost dat, například na hlavní jednotce. Na systémech s pomalými disky však může znamenat značnou režii kvůli dodatečným synchronizacím. Ty jsou potřeba k propisováním každé změny do záznamů. Nastavení na hodnotu 2 je o něco méně spolehlivé, protože odevzdané transakce budou do záznamu propsány pouze jednou za sekundu, ale v některých situacích to může být přijatelné pro master a pro repliku je to rozhodně dobrá hodnota. Hodnota 0 je ještě rychlejší, ale je pravděpodobnější, že v případě havárie přijdete o některá data.

innodb_flush_method: toto nastavení řídí způsob zapisování dat a logů na disk. Oblíbené hodnoty jsou O_DIRECT, pokud máte hardwarový řadič RAID s cache pro zápis. FDATASYNC (výchozí hodnota) pro většinu ostatních scénářů. Sysbench je dobrý nástroj, který vám pomůže vybrat mezi těmito dvěma hodnotami.

innodb_log_buffer_size: jedná se o velikost vyrovnávací paměti pro transakce, které ještě nebyly odevzdány. Výchozí hodnota (1 MB) je obvykle v pořádku, ale jakmile máte transakce s velkými poli, vyrovnávací paměť se může velmi rychle zaplnit a vyvolat další I/O zátěž. Podívejte se na stavovou proměnnou innodb_log_waits a pokud není 0, zvyšte velikost innodb_log_buffer_size o 2 MB.

Další nastavení

query_cache_size: mezipaměť dotazů je známé úzké místo, které se projevuje i při mírné souběžnosti. Nejlepší možností je vypnout ji hned od prvního dne nastavením query_cache_size = 0 (nyní výchozí nastavení v MySQL 5.6) a použít jiné způsoby, jak zrychlit čtení dotazů: dobré indexování, přidání replik pro rozložení čtecí zátěže nebo použití externí cache (například memcache nebo redis). Pokud jste již svou aplikaci MySQL vytvořili se zapnutou mezipamětí pro dotazy a pokud jste nikdy nezaznamenali žádný problém, může pro vás být mezipaměť pro dotazy přínosná. Proto byste měli být opatrní, pokud se rozhodnete ji zakázat.

log_bin: zapnutí binárního logování je povinné, pokud chcete, aby server fungoval jako replikační master. Pokud ano, nezapomeňte také nastavit server_id na jedinečnou hodnotu. Je to také užitečné pro jediný server, pokud chcete mít možnost provést obnovu point-in-time: obnovit poslední zálohu a použít binární protokoly. Jednou vytvořené soubory binárních protokolů se uchovávají navždy. Pokud tedy nechcete, aby vám došlo místo na disku, měli byste buď staré soubory vyčistit pomocí příkazu PURGE BINARY LOGS, nebo nastavením expire_logs_days určit, po kolika dnech budou protokoly automaticky vyčištěny.

Binární logování však není zdarma, takže pokud jej nepotřebujete například na replice, která není hlavní, doporučujeme jej nechat vypnuté.

skip_name_resolve: při připojení klienta provede server překlad názvu hostitele, a pokud je DNS pomalé, bude pomalé i navazování spojení. Proto se doporučuje spouštět server s funkcí skip-name-resolve, aby se zakázalo veškeré vyhledávání DNS. Jediným omezením je, že příkazy GRANT pak musí používat pouze IP adresy, proto buďte opatrní při přidávání tohoto nastavení do stávajícího systému.

PhpMyAdmin – Poradce pro začátečníky

Dokonce i náš oblíbený pomocník pro správu databází vám poradí či ukáže, kde je problém.

Stačí se přihlásit do oblíbeného správce a v horním menu navštívit sekci Stav > Poradce.

Schovaný poradce

V poradci uvidíte nejběžnější problémy s chybovou hláškou, která se zobrazuje a na pravé straně vidíte ihned doporučení, jak daný problém vyřešit. Po kliknutí na konkrétní problém se otevře modal s podrobnostmi.

Takto vypadá modal, který vyskočí

Další pomůcka, o které lidi moc neví je takový menší “monitoring”, který hlídá serverové proměnné a jejich hodnoty a pokud překročí normu, zobrazí se červeně.

Dokonalý pomocník při řešení problémů kolem databáze

Výhoda je, že hned vedle proměnných máte odkaz na dokumentaci, kde se dozvíte, co znamenají a v popisu krátký vysvětlení problému. (řešení zatím bohužel ne. 🙂

Slow query log

Jedná se o speciální log, který v sobě uchovává informace o pomalých dotazech. Odkdy se mají pomalé dotazy logovat si můžete nastavit v konfiguračním souboru k MySQL/MariaDB.

Na našich serverech s VPS centrem najdete config na /etc/mysql/mariadb.conf.d/60-local.cnf , kde musíte odkomentovat tyto řádky a restartovat MySQL.

Řádky pro odkomentování

Poté můžete na příkazové řádce spustit příkaz

mysqldumpslow – Případně můžete přidat [options] [log_file]

Po spuštění příkazu vám vyjedou veškeré dotazy, které přesahují limit, které jste v configu nastavil.

Závěr

Existují samozřejmě i další nastavení, která mohou mít význam v závislosti na pracovní zátěži nebo hardwaru: například málo paměti a rychlé disky, vysoká souběžnost, zátěž náročná na zápis jsou případy, kdy bude potřeba specifické ladění. Cílem nápovědy je poskytnout vám několik možností ladění výkonu MySQL, které vám umožní rychle získat rozumnou konfiguraci MySQL, aniž byste museli trávit příliš mnoho času změnou nepodstatných nastavení MySQL nebo čtením dokumentace, abyste pochopili, která nastavení jsou pro vás důležitá.