Ladenie výkonu MySQL, MariaDB a Percona servera pre systémových administrátorov

Správne ladenie výkonu databázového servera je kľúčové pre stabilitu a rýchlosť (nielen webových) aplikácií. Databázy MySQL (a jej klony MariaDB a Percona Server) v predvolenom nastavení nebývajú vyladené pre optimálny resp. maximálny výkon – skôr sú nastavené konzervatívne, aby fungovali na priemernom hardvéri. Bez optimalizácie môže dochádzať k pomalým odozvám, pomalým dopytom a neefektívnemu využitiu CPU, pamäte či diskového úložiska. Zmenou konfigurácie a ladením výkonu dokážeme zrýchliť spracovanie dopytov, zvládnuť viac súbežných používateľov a predísť zahlteniu systému.

Bežnou chybou administrátora je spoliehať sa na predvolené nastavenia alebo naopak nekontrolovane preberať nastavenia z internetu bez pochopenia. Každý server je iný – to, čo funguje inde, nemusí u vás. Preto je dôležité postupovať systematicky: najprv monitorovať aktuálny stav, identifikovať úzke miesta a potom postupne aplikovať zmeny. Vždy sledujte efekt každej zmeny na výkon. Neexistuje jedno magické nastavenie; ide o kombináciu viacerých optimalizácií prispôsobených konkrétnemu prostrediu.

Všeobecné odporúčania a zálohovanie

Upozornenie: Pred akýmkoľvek zásahom do konfigurácie databázy vždy zaistite aktuálnu zálohu databáz aj konfiguračných súborov a overte, že viete vykonať obnovu. Optimalizačné zásahy môžu viesť k neočakávaným problémom – napríklad zmena veľkosti logov InnoDB vyžaduje špeciálny postup pri reštarte servera. Majte preto pripravený plán návratu k pôvodnému stavu.

Pamätajte, že nižšie uvedené nastavenia a hodnoty sú informatívne odporúčania. Každý server a aplikácia má iné požiadavky, preto ladeniu pristupujte metódou pokus-omyl s priebežným testovaním. Odporúčané hodnoty treba doladiť podľa reálnej záťaže a výsledkov meraní.

Základné pravidlo výkonového ladenia je meniť vždy iba jednu vec naraz a pozorovať vplyv. Ak by ste upravili viac parametrov súčasne a zlepšil by sa (alebo zhoršil) výkon, ťažko zistíte, ktorá zmena mala aký efekt. Preto postupujte iteratívne a trpezlivo.

Rozdiely medzi MySQL, MariaDB a Percona

MySQL, MariaDB a Percona Server sú príbuzné databázové systémy (MariaDB a Percona sú forky MySQL) a z pohľadu výkonového ladenia sú vo veľkej miere podobné. Väčšina konfiguračných parametrov (najmä pre úložisko InnoDB) má rovnaké názvy a funkciu. Napriek tomu existujú niektoré rozdiely, na ktoré je dobré brať ohľad:

• Predvolené nastavenia: MariaDB aj Percona môžu mať odlišné predvolené hodnoty niektorých parametrov oproti Oracle MySQL. Napríklad MariaDB vypína niektoré bezpečnostné zápisy pre rýchlosť – predvolená hodnota sync_binlog je 0 (MySQL používa 1) a režim zápisu binárneho logu je MIXED namiesto plne transakčného ROW . To znamená, že MariaDB v základe dosahuje vyšší výkon zápisu na úkor vyššieho rizika straty posledných dát pri páde (MySQL volí konzervatívnejší prístup). Pri ladení teda skontrolujte tieto nastavenia a zvážte, či nepotrebujete v MariaDB zapnúť bezpečnejšie správanie alebo naopak v MySQL povoliť rýchlejšie (no menej odolné) nastavenie podľa potrieb.
  
• Performance Schema: MySQL od verzie 5.6 štandardne používa Performance Schema (interný monitorovací nástroj), ktorý je v Oracle MySQL zapnutý predvolene. MariaDB má Performance Schema tiež implementovanú, ale štandardne ju má vypnutú, aby sa znížila režijná záťaž . Ak teda používate MariaDB a chcete využívať detailné výkonové metriky cez Performance Schema, musíte ju explicitne zapnúť (performance_schema=ON v konfiguračnom súbore a reštart). Percona Server má Performance Schema rovnako ako MySQL zapnutú a často pridáva aj vlastné rozšírenia monitorovania.
  
• InnoDB vs XtraDB: Staršie verzie MariaDB (do 10.1) používali namiesto Oracle InnoDB vlastnú fork verziu XtraDB (prevzatú od Percony). V novších MariaDB už opäť figuruje InnoDB, avšak s rôznymi úpravami. Z praktického hľadiska sú však pre nás parametre rovnaké. Percona Server historicky používal vylepšené InnoDB (XtraDB), dnes už zosúlaďuje názvy s Oracle MySQL. Rozdiely sú minimálne – napr. MariaDB môže mať iné implikované správanie niektorých premenných alebo podporu ďalších storage engine (Aria, MyRocks atď.), čo však pre bežné ladenie InnoDB nie je podstatné.
  
• Funkcie navyše: MariaDB aj Percona pridávajú niektoré extra funkcie. Napríklad thread pool – v MySQL Community edícii nie je dostupný (iba v Enterprise verzii), zatiaľ čo MariaDB ho má zabudovaný a Percona ho podporuje ako voľbu (thread_handling=pool-of-threads). Thread pool umožňuje pri veľkom počte súbežných spojení efektívnejšie riadiť vlákna, aby nebol systém preplnený stovkami vlákien naraz. V praxi to znamená, že MariaDB/Percona zvládnu veľký počet pripojení lepšie vďaka zabudovanému thread poolu (napr. MariaDB dokáže obsluhovať stovky tisíc pripojení pomocou poolu ), kým MySQL používa pre každé pripojenie samostatné vlákno. Pri bežnej záťaži to nemusí byť rozdiel, ale pri tisícoch spojení už áno. Ak administrujete MariaDB/Percona, zvážte využitie thread poolu (je často predvolene zapnutý v MariaDB, v Percone treba povoliť) pre vysokú konkurenčnú záťaž.

Celkovo však platí, že princípy ladenia výkonu sú takmer totožné pre MySQL, MariaDB aj Percona. Ďalej v článku budeme hovoriť všeobecne o MySQL (resp. o MySQL/MariaDB), pričom spomenieme len tam, kde je konkrétny rozdiel. Vždy si však overte detaily v dokumentácii pre konkrétnu verziu, ktorú používate. Tiež odporúčame používať posledné stabilné verzie (napr. MySQL 8.0.x, MariaDB 10.6/10.11, Percona Server 8.0), keďže novšie verzie často prinášajú výkonové zlepšenia a lepšie predvolené nastavenia.

Monitorovanie výkonu

Prvým krokom ladenia je monitoring – potrebujete vedieť, čo vašu databázu spomaľuje. Môže to byť nedostatok CPU, málo pamäte, pomalé diskové I/O, nevhodné dopyty bez indexov, alebo kombinácia faktorov. Na monitoring použijeme jednak systémové nástroje, jednak nástroje na úrovni databázy.

Systémové nástroje (CPU, RAM, disk, sieť)

• htop: Pokročilá verzia klasického top. Umožňuje interaktívne sledovať využitie CPU a pamäte v reálnom čase, s rozpisom po vláknach/procesoch. Po spustení htop na serveri uvidíte, koľko CPU používa proces MySQL/MariaDB (mysqld), koľko pamäte zaberá a či náhodou systém neswapuje (indikácia nedostatku RAM). htop vie farebne zvýrazniť I/O wait (čakanie na disk) – ak vidíte, že CPU jadro je väčšinou v stave „IOwait“, znamená to, že proces čaká na disk (úložisko je pravdepodobne bottleneck). htop vám rýchlo napovie, či je databázový server CPU-bound (vysoké zaťaženie CPU), RAM-bound (dochádza pamäť, používa sa swap) alebo IO-bound (časté čakanie na disk).

Tip: Pre htop máme pokročilý návod.

• iotop: Nástroj podobný top, zameraný na diskové vstupy/výstupy. Príkaz iotop zobrazí procesy a množstvo dát, ktoré práve čítajú alebo zapisujú na disk. Ak je databáza pomalá a iotop ukáže, že mysqld trvalo intenzívne zapisuje alebo číta veľké objemy, viete, že úzke hrdlo je disk. Môžete tiež vidieť % času stráveného v I/O operáciách. Napríklad pri vysokom IO vyťažení uvidíte v iotope proces mysqld s vysokými hodnotami čítania/zápisu. V kombinácii s htopom zistíte, či CPU čaká na disk (IOwait). Iotop pomáha odhaliť, či náhodou nezapisuje MySQL príliš často na disk (napr. kvôli nevhodnému nastaveniu flushovania, o čom viac nižšie).
  
• atop: Ďalší pokročilý monitorovací nástroj, ktorý zobrazuje historické štatistiky o využití CPU, pamäte, diskov, sietí atď. Môže byť užitočný, ak potrebujete pozrieť, čo sa dialo v určitom čase (ak atop beží ako daemon a loguje dáta). Pre rýchle interaktívne sledovanie ale väčšinou postačí htop + iotop.
  
• netdata: Netdata je moderný monitorovací démon s webovým rozhraním. Dá sa jednoducho nasadiť (jedným skriptom) a poskytuje priehľadné grafy CPU, pamäte, diskových operácií, sietí a dokonca má integráciu aj pre MySQL. Cez netdata v prehliadači vidíte v reálnom čase grafy – napríklad vyťaženie CPU jadier, množstvo I/O operácií na disk, počet pripojení k MySQL, počet dopytov za sekundu, dĺžku transakčných logov a množstvo ďalších metrík. Výhodou je, že rýchlo spozorujete výkyvy (spiky) – napríklad ak každú hodinu beží zálohovací skript a spôsobuje prudký nárast diskovej záťaže, v grafe to uvidíte. Netdata je teda skvelý pomocník na vizualizáciu trendov výkonu a odhalenie anomálií.

Pro tip: Ak využívate službu Websupport Monitoring, tak systémové informácie o vašej databáze vám vieme pridať do vašich dashboardov.

Stručne povedané, systémové nástroje vám pomôžu určiť, ktorá časť zdrojov je limitujúca. Zistíte, či databáze dochádza RAM (a začne swapovať), či nestíha CPU (plné využitie jadier), alebo či disky nestíhajú (vysoké I/O čakacie doby). Tým nasmerujete vaše ďalšie kroky správnym smerom.

Databázové nástroje (dopyty a interné metriky)

• Slow Query Log: MySQL/MariaDB má vstavanú funkcionalitu logovania dopytov, ktorých vykonanie presiahne určitý čas. Tento slow query log je štandardne vypnutý – veľmi odporúčame ho zapnúť na ladenej databáze. V konfigurácii (my.cnf) nastavte napríklad:

slow_query_log = ON

slow_query_log_file = /var/log/mysql-slow.log

long_query_time = 2

log_queries_not_using_indexes = ON

• Tým zabezpečíte, že všetky dopyty trvajúce dlhšie než 2 sekundy (hodnotu upravte podľa potreby) sa zapíšu do uvedeného log súboru. Voľba log_queries_not_using_indexes spôsobí, že sa zalogujú aj dopyty, ktoré síce možno netrvajú 2s, ale nevyužili index (čo môže indikovať neoptimálny dopyt). Analýzou slow logu potom zistíte, ktoré SQL dopyty sú najpomalšie alebo najčastejšie spomaľujú systém. Log môžete prechádzať ručne, alebo lepšie použiť nástroj ako mysqldumpslow (ktorý zhrnie podobné dopyty) či pokročilý pt-query-digest z Percona Toolkitu (o ňom viac neskôr). Výstupom bude zoznam dopytov zoradený napríklad podľa celkového času, koľkokrát sa vykonali, priemerného času atď. To sú cenné informácie – často zistíte, že napríklad jeden zabudnutý dopyt bez indexu spôsobuje značné zaťaženie.
  
• SHOW PROCESSLIST / PERFORMANCE SCHEMA: Pri bežiacej databáze môžete v MySQL príkazom SHOW FULL PROCESSLIST; získať prehľad momentálne bežiacich dopytov a stavov. Uvidíte napríklad či nejaké dopyty „visia“ na lockoch, alebo trvajú dlho (vo výstupe je čas). To je užitočné pre aktuálne problémy. Pre dlhodobejšie sledovanie však poslúži práve slow log. Moderné MySQL (8.0) má tiež Performance Schema, ktoré zbiera detailné štatistiky o výkone (časy čakania na lock, I/O, CPU čas dopytov atď.). Je to však pomerne komplexný nástroj – dáta sú dostupné v špeciálnych tabuľkách (schema performance_schema a pohľady v sys schéme). Pre systematickú analýzu môžete použiť napr. MySQL Workbench Performance Reports alebo príkazy v sys schéme, ktoré ťažia z performance schema. Pre začiatok si vystačíte so zapnutím slow logu a základnými statusmi.
  
• SHOW GLOBAL STATUS a SHOW VARIABLES: Tieto príkazy zobrazia množstvo interných číselných metrík (počet vykonaných dopytov, počet otvorení tabuliek, hit-rate cache a pod.) a hodnoty aktuálnych nastavení. Ideálne je nasadiť si nástroj ako MySQL/MariaDB monitor (napr. Netdata, Percona PMM, Zabbix templaty a pod.), ktorý bude tieto metriky periodicky čítať a grafovať. Ak taký nástroj nemáte, môžete ručne z času na čas pozrieť vybrané hodnoty. Napríklad hodnota Threads_connected povie počet aktuálnych pripojení, Innodb_buffer_pool_pages_free a podobné zase využitie InnoDB buffer poolu, Created_tmp_disk_tables počet dočasných tabuliek na disku (vysoké číslo naznačí, že DB často vytvára dočasné tabuľky na disku, možno treba zvýšiť tmp_table_size), atď. Manuál MySQL popisuje stovky takých metrík. Je nad rámec tohto článku prechádzať všetky – skôr odporúčame použiť na to nástroje (mysqltuner, pt-mysql-summary, netdata), ktoré tieto čísla zinterpretujú za vás.

Na záver k monitoringu – monitorujte DB priebežne a aj po aplikovaní zmien. Optimalizácia je neustály proces. Uistite sa, že máte nastavené nejaké alerty (či už vlastné skripty, Zabbix, Netdata alarmy, alebo aspoň pravidelné kontroly), ktoré vás upozornia na nezvyčajné stavy (napr. výpadok MySQL, extrémne vysoké load, dochádzajúce miesto na disku s databázami a pod.). Tieto veci síce priamo nezlepšia výkon, ale pomôžu vám udržať databázu v kondícii a rýchlo reagovať, keď sa niečo deje.

Analýza konfigurácie

Keď už viete, kde sú slabiny (z monitoringu), prejdite k analýze súčasnej konfigurácie MySQL/MariaDB. Konfigurácia sa typicky nachádza v súbore my.cnf alebo v adresári /etc/mysql/mysql.conf.d/ (záleží od distribúcie). Už vieme, že predvolené nastavenia servera často nie sú ideálne pre výkon – napríklad MySQL 8.0 už má lepšie defaulty než staršie verzie, ale stále môže byť potrebné ich doladiť pre váš server.

Postupujte takto:

• Zistite aktuálne nastavenia: Pomocou príkazu SHOW VARIABLES; v MySQL klientovi (alebo mysqladmin variables) získate zoznam všetkých systémových premenných a ich hodnôt. Tieto zahŕňajú aj implicitné defaulty aj hodnoty načítané z config súborov. Užitočné je filtrovať podľa kľúčového slova, napríklad:

SHOW VARIABLES LIKE 'innodb%';

SHOW VARIABLES LIKE 'max_connections';

SHOW VARIABLES LIKE 'cache%';

• Týmto spôsobom viete rýchlo nájsť, aké hodnoty má konkrétny parameter, ktorý idete ladiť, prípadne či nie je vypnutý (OFF) atď.
  
• Identifikujte hlavné parametre na zmenu: Nižšie sa venujeme konkrétnym dôležitým nastaveniam. Pri analýze si všimnite napríklad hodnotu innodb_buffer_pool_size (či je primeraná veľkosti RAM), hodnoty týkajúce sa cache (napr. table_open_cache, query_cache_size – ak existuje), nastavenie flushovania (innodb_flush_log_at_trx_commit, sync_binlog), počet povolených pripojení (max_connections), atď. Porovnajte ich s odporúčaniami.
  
• Kontrola konfiguračného súboru: Otvorte my.cnf a pozrite, čo je v ňom už nastavené. Neraz sa stáva, že distribúcia linuxu alebo hosting tam má nejaké default hodnoty (často zakomentované), alebo že sa uplatňujú viaceré súbory (napr. /etc/mysql/conf.d/ môže obsahovať ďalšie .cnf fragmenty). Uistite sa, že upravujete správny súbor a že po reštarte sa hodnoty naozaj menia (overíte opäť cez SHOW VARIABLES). Odporúča sa viesť si verzionovanie konfigurácie – t.j. zálohovať si pôvodný stav (napr. my.cnf.orig) a každú zmenu komentovať, aby ste vedeli, čo ste menili.
  
• Pochopte aktuálne využitie: Okrem statickej konfigurácie zistite aj, ako je systém využívaný. Napríklad ak Threads_connected (počet pripojení) bežne dosahuje 100 a max_connections máte 150, ste v bezpečnej zóne; ak by však Threads_connected často blížil k 150, znamená to, že aplikácia otvára veľa spojení a možno treba zvýšiť limit (alebo optimalizovať aplikáciu). Podobne Innodb_buffer_pool_pages_free vám povie, či buffer pool (cache InnoDB) má voľné miesto, alebo je plný (a teda by väčší pool možno pomohol). Tieto štatistiky získate cez SHOW GLOBAL STATUS a z Performance Schema. Na interpretáciu je dobré použiť nástroj MySQLTuner alebo Percona pt-variable-advisor, ktoré upozornia na očividné nedostatky (napr. že príliš veľa tabuliek sa otváralo – možno malý table_open_cache atď.).

Na základe analýzy konfigurácie a správania sa DB si spravte plán, ktoré parametre idete meniť. Ďalej si prejdeme najdôležitejšie okruhy nastavení a odporúčania.

Dôležité parametre InnoDB

Väčšina moderných inštalácií MySQL/MariaDB používa úložný engine InnoDB (defaultný od MySQL 5.5). InnoDB má množstvo konfigurácií, no z hľadiska výkonu vyniká niekoľko kľúčových parametrov, ktoré majú najväčší dopad. Nižšie uvádzame tie najdôležitejšie, ktoré by mal admin poznať a nastaviť:

• innodb_buffer_pool_size: Jedno z najdôležitejších nastavení vôbec. Určuje veľkosť buffer poolu – čo je pamäťová cache, ktorú InnoDB používa na ukladanie dátových a indexových stránok. V podstate platí, že čím väčší buffer pool, tým viac databázových dát sa zmestí do RAM a tým menej musí server čítať z disku pri dopytoch. Odporúčania hovoria alokovať približne 50–75% dostupnej RAM pre InnoDB buffer pool (ak databázový server beží sám na danom stroji a nie sú tam iné pamäťovo náročné služby). Na 16 GB RAM serveri teda napríklad 10–12 GB. MySQL 8.0 vie dynamicky meniť buffer pool (premenná je dynamická), ale i tak ju nastavte v konfigurácii napevno. MariaDB a Percona to majú rovnako.

Pozor: ak hostujete viac služieb na serveri (napr. webový server), nechajte im tiež pamäť – nedajte celých 75% ak by inak OS swapoval. Buffer pool radšej mierne nadhodnoťte než podhodnoťte – ak je príliš malý, systém bude neustále načítavať stránky z disku do RAM (cache miss), čo je pomalé. Naopak príliš veľký buffer pool (blížiaci sa 90%+ RAM) môže vytlačiť OS cache a iné procesy do swapu. Treba nájsť rozumný balans. Ak vaša databáza je menšia než RAM, môžete buffer_pool_size nastaviť približne na veľkosť DB (prípadne o niečo viac, ak pribúdajú dáta), nemá zmysel dávať násobne viac než veľkosť dát – nevyužilo by sa to.

• innodb_log_file_size (resp. v MySQL 8.0.30+ parameter innodb_redo_log_capacity): Toto nastavenie určuje veľkosť redo log súborov InnoDB (každý InnoDB má tzv. tranzakčný log, kam zapisuje zmeny pred ich aplikáciou do dátových súborov). Veľkosť logu ovplyvňuje výkon zápisu a tiež čas obnovy po páde. Väčší log umožňuje InnoDB zapisovať zmeny sekvenčne do logu a dáta flushovať na disk menej často, čo zlepšuje priepustnosť zápisov – príliš malý log môže brzdiť výkon pri intenzívnych zápisoch, pretože sa log často zaplní a InnoDB musí čakať na flush. Na druhej strane príliš veľký log znamená, že ak databáza spadne, bude trvať dlhšie prejsť celý log a aplikovať zmeny (recovery). Dobrá prax je nastaviť relatívne veľký log, ktorý pokryje napr. aspoň 1 hodinu zápisov (závisí od vašej záťaže). Konkrétne čísla: často sa odporúča okolo 512 MB až 2 GB na jeden log súbor (a zvyčajne sú 2 súbory – v MySQL 8+ ich môže byť viac). Všimnite si, že od MySQL 8.0.30 je parameter innodb_log_file_size označený ako zastaraný a nahradený innodb_redo_log_capacity , ktorý určuje celkovú kapacitu redo logov. Každopádne, odporúčanie je: zväčšiť výchozí malý log (ktorý býval napr. 50 MB) na stovky MB. Percona uvádza, že dostatočne veľké logy sú kľúčové pre stabilný výkon zápisu, avšak so zvyšujúcou veľkosťou rastie čas recovery . Ak máte 16GB RAM server, log v stovkách MB nebude problém.

Pozor: Zmena tohto parametra vyžaduje reštart MySQL a špeciálny postup – musíte vypnúť server, zálohovať/odstrániť staré log súbory (ib_logfile* v datadir), nastaviť novú hodnotu v my.cnf, potom zapnúť server, aby vytvoril nové logy. Bez tohto postupu by server nemusel naštartovať (ak zistí nesúlad veľkostí logu). Preto túto zmenu robte veľmi opatrne a ideálne v údržbovom okne.

• innodb_flush_log_at_trx_commit: Táto voľba riadi, ako často sa obsah tranzakčného logu flushuje (zápis na disk). Predvolená hodnota 1 znamená, že pri každom commite transakcie InnoDB flushne (zapíše) log na disk – to zaručuje maximálnu trvanlivosť (durability), ale generuje veľa sync operácií na disk. Nastavenie 0 alebo 2 zlepšuje výkon zápisov tým, že neflushuje pri každom commite (pri 2 zapisuje do logu na konci každej sekundy, ale nevolá fsync na disk pri každom commite; pri 0 dokonca nepíše na disk pri commitoch vôbec, loguje len do pamäte a raz za sekundu flushne). Rozdiel medzi 0 a 2 je v úrovni risku pri páde: 2 zaručí, že transakcie sa zapíšu do OS cache na disk (ale nevyvolá fsync), takže ak spadne OS, prídu sa o 1s transakcií; pri 0 sa môže stratiť aj celá 1s dávka. Mnoho nasadení, kde nevadí potenciálna strata posledných ~1 sekundy transakcií pri havárii, volí innodb_flush_log_at_trx_commit=2 pre výrazne vyšší výkon zápisu (najmä na pomalších diskoch alebo pri veľkom množstve malých transakcií). Oracle MySQL default je 1 (bezpečnosť), MariaDB default je tiež 1. Zvážte teda zmenu na 2, ak potrebujete výkon a ak aplikácia vydrží teoretickú stratu drobných dát pri páde. Hodnotu 0 neodporúčame v produkcii, tá je skôr na testy (znamená úplné vypnutie flush pri commitoch).
  
• innodb_flush_method: Metóda flushovania na disk. Pre klasické disky bývalo default fsync (alebo fdatasync). Pre SSD sa často odporúča použiť O_DIRECT, čím sa vynechá cache operačného systému pri I/O (InnoDB bude zapisovať priamo na disk, nebudú sa duplikovať stránky v OS cache). Výhoda O_DIRECT je, že nechá cache manažment na InnoDB (buffer pool) a nezahlcuje OS cache veľkými sekvenčnými I/O z flushovania. Moderné MySQL už má default často O_DIRECT pre InnoDB na Linuxe, ale radšej si to na vašom OS preverte. Na SSD určite používajte O_DIRECT (prípadne v kombinácii s O_DSYNC na logy). Parameter sa nastavuje v my.cnf, napr: innodb_flush_method = O_DIRECT. Výsledkom je predovšetkým zníženie zaťaženia OS cache a potenciálne o čosi lepšia konzistencia výkonu pri veľkých flush operáciách.
  
• innodb_buffer_pool_instances: Ak má InnoDB buffer pool dostatočne veľkú veľkosť (napr. nad 1GB), odporúča sa ho rozdeliť na viaceré „instances“ – aby sa zmiernila konkurencia (locks) pri prístupe z viacerých vlákien. Default je 1 (celý pool ako jeden celok). MySQL aj MariaDB umožňujú nastaviť napr. 8 instancií. Prakticky ak máte buffer pool napr. 8GB, dajte innodb_buffer_pool_instances = 8 (čím vznikne 8 blokov po 1GB). Tým sa zlepšia paralelné prístupy. Pri menších veľkostiach (pod ~1GB) nemá zmysel deliť. Tento parameter stačí nastaviť a reštartnúť server (je to statická voľba).
  
• innodb_io_capacity a innodb_io_capacity_max: Tieto parametre ovplyvňujú, akou rýchlosťou vykonáva InnoDB pozadia činnosti ako flush dirty pages (zápis upravených stránok z buffer poolu na disk) a zlúčenie change bufferu. innodb_io_capacity definuje približný počet I/O operácií za sekundu, ktoré má InnoDB v pozadí využívať . Pôvodne bol default len 200 (čo zodpovedá cca bežnému HDD). Pre SSD disky by mal byť táto hodnota výrazne vyššia, pretože SSD zvládnu stovky až tisíce IOPS. Odporúča sa nastaviť približne na úroveň, akú disk zvládne – napr. pre bežné SSD môže byť niekoľko tisíc. Typicky pre SSD voľte stovky až nízke tisíce (napr. 1000). Avšak pozor: ak dáte zbytočne privysoké číslo, InnoDB môže až prehnane vyprázdňovať cache (flushne stránky skôr, než to je nutné) a tým znížiť efektivitu cache. Odporúča sa hodnotu držať čo najnižšiu, ale nie tak nízku, aby dochádzalo k periodicým výkyvom výkonu keď flush nestíha a naraz dobieha zameškané . Praktický postup: ak vidíte, že v SHOW ENGINE INNODB STATUS narastá unflushed modified pages a občas to spôsobí záťaž (checkpointy), môžete zvýšiť io_capacity. innodb_io_capacity_max je horný strop pri určitých situáciách (default ~2000 ak capacity 200). Môžete ho nastaviť napr. 2x capacity. Napríklad: pre náš 4vCPU/SSD server napr. innodb_io_capacity=1000 a innodb_io_capacity_max=2000 môže byť dobrý štart. 
• Ďalšie InnoDB parametre: Existujú aj ďalšie, ako napr. innodb_purge_threads (počet vlákien pre purge operácie, default 4 v novších verziách – zvyčajne netreba meniť), innodb_max_dirty_pages_pct (koľko % buffer poolu môže byť „špinavých“ – default okolo 90, ak chcete agresívnejšie flushovať, môžete znížiť na napr. 75, ale väčšinou netreba), innodb_flush_neighbors (či flushovať susediace stránky na disku – pri SSD vypnuté by default, keďže nemá zmysel flushovať sekvenčne, na HDD to bolo zapnuté – skontrolujte, malo by byť OFF). Tiež innodb_adaptive_hash_index (default ON – adaptívny hash index môže zrýchliť niektoré lookupy, vo vzácnych prípadoch však môže byť bottleneck pri extrémnej konkurecii – ak by ste videli v profile CPU, že veľa času trávi v futex kvôli adaptive hash latch, môžete zvážiť vypnutie). Tieto však presahujú úroveň bežného ladenia a spomíname ich len pre úplnosť. Väčšinou netreba zasahovať.

InnoDB by ste mali nastaviť tak, aby využila dostupnú pamäť (buffer pool), mala primerane veľké logy pre plynulý zápis a flushovacie parametre prispôsobené typu úložiska (SSD vs HDD) a požiadavkám na trvanlivosť dát. Tieto nastavenia spravidla prinesú najvýraznejšie zlepšenie výkonu databázy.

Optimalizácia I/O a SSD

Pre výkon databázy je diskové úložisko kritické, hlavne ak dáta nevojdú celé do pamäte. V dnešnej dobe sa už takmer vždy používa SSD (SATA alebo rýchlejšie NVMe) na databázové servery, keďže výrazne prekonávajú klasické HDD v I/O operáciách za sekundu. Avšak aj pri SSD existujú špecifiká:

• I/O scheduler a súborový systém: Na úrovni OS Linux sa uistite, že máte vhodný I/O scheduler. Pre SSD sa typicky odporúča noop alebo deadline namiesto staršieho cfq. Nové jadrá s BFQ/CFQ si aj tak poradia, ale ak viete nastaviť scheduler na noop (žiadne zbytočné radenie I/O – SSD to nepotrebuje), spravte tak. Súborový systém – ext4 alebo XFS sú oba v pohode. Ext4 je vo všeobecnosti dobrá voľba, XFS funguje tiež dobre, najmä pri väčších súboroch (InnoDB data). ZFS sa na Linuxe dá použiť ak potrebujete snapshoty, ale má vlastnú režiu – to je na zváženie a v prípade VPS asi nie veľmi dostupné riešenie.
  
• innodb_flush_method = O_DIRECT: Ako spomíname vyššie, toto nastavte pre SSD, aby ste zabránili dvojnásobnému kešovaniu a prípadným „write burstom“, keď OS cache zrazu flushne veľa dát naraz. S O_DIRECT InnoDB priebežne flushuje priamo na disk. To môže mierne zvýšiť latenciu jednotlivých zápisov, ale zníži sa kolísanie výkonu. U SSD to celkovo vedie k predvídateľnejšiemu správaniu.
  
• innodb_flush_neighbors = 0: Overte, že je vypnuté (MySQL 8 má default 0). Toto zabezpečí, že InnoDB pri flushnutí stránky nebude ešte prechádzať susedné stránky na disku – pri SSD to nemá význam (žiadne hlavičky diskov tu nehľadajú susedné sektory), takže ušetrí sa zbytočná I/O záťaž.
  
• Veľkosť stránky a alignement: InnoDB má default 16KB stránku. Väčšina SSD operuje s 4KB sektormi (interné page). To je v poriadku. Možno ste počuli o možnosti nastaviť menšiu InnoDB page size 4KB pre určité workloady. To je už veľmi pokročilé – bežne sa to nerobí, 16KB je osvedčený default. Podobne align partition na SSD na 1MB boundary – to všetko dnes OS inštalátory robia správne, nemusíte to extra riešiť.
  
• RAID a kontroléry: Ak používate RAID (napr. RAID10), overte, že radič nie je úzke hrdlo. Napríklad enterprise RAID radič by mal mať vyrovnávaciu pamäť (BBU – battery backed cache) a nemal by obmedzovať priepustnosť SSD. V prostredí VPS toto ovplyvniť neviete, to je na poskytovateľovi.
  
• Sledovanie I/O wait: Aj keď SSD sú rýchle, nekonečné nie sú. Môžu zvládnuť napr. 100k IOPS, no ak ich vyťažujete prudkými zápismi (napr. flush 1GB dát), na moment saturujú. To sa prejaví v htope ako I/O wait, prípadne v metrikách ako narastajúci Innodb_buffer_pool_wait_free (ak by buffer pool musel čakať na flush aby uvoľnil miesto). Sledujte tieto indikátory. Riešením môže byť zvýšenie innodb_io_capacity (nech flushuje viac priebežne), alebo navýšenie innodb_log_file_size (nech sa flushuje menej často), alebo proste zlepšenie dopytov, aby toľko nezapisovali.
  
• Trim (TRIM/Discard): Uistite sa, že SSD dostáva TRIM príkazy, aby mohol interne spravovať opotrebovanie. Ak máte filesystem s discard mount option, TRIMuje priebežne. Ak nie, spúšťajte fstrim (napr. raz týždenne cez cron) na diskové oddiely s DB. Toto nespôsobí priamo rýchlosť dopytov, ale dlhodobo udržuje SSD výkon (inak keď sa SSD zaplní, bez TRIMu môže spomaliť zápisy).
  
• Nebojte sa vyťažiť SSD: SSD sú stavané na pomerne vysoké opotrebenie (hlavne tie kvalitnejšie). Napr. parametre ako TBW (terabytes written) bývajú vysoké. Moderné NVMe disky zvládnu nonstop load. Čiže ak databáza potrebuje priebežne zapisovať stovky GB denne a disk je určený na stovky TBW, ste v pohode. Samozrejme netreba plytvať – ale netreba sa ani príliš báť využiť disk. Napríklad nastavenie innodb_io_capacity_max spomínané vyššie – Percona upozornila, že extrémne prehnané hodnoty môžu viesť k zbytočnému opotrebovaniu SSD bez prínosu (logické – ak nepotrebujeme flushnúť, radšej to nechajme v RAM a zapíšme neskôr). Čiže primeranosť je na mieste.

Optimalizácia I/O spočíva hlavne v prispôsobení nastavení charakteru vášho úložiska. Pre SSD vypnúť nepotrebné veci (neighbors), využiť O_DIRECT, adekvátne nastaviť flush politiky a dbať na TRIM. Výsledkom by mala byť databáza, ktorá konzistentne podáva výkon a nekolíše pri I/O bursts, s minimalizovanou latenciou diskových operácií.

Vyrovnávacie pamäte (cache) a buffery

Okrem veľkej InnoDB cache (buffer pool), ktorú sme už rozobrali, má MySQL niekoľko ďalších cache a vyrovnávacích pamätí, ktorých nastavenie ovplyvňuje výkon. Tu sú tie podstatné:

• Query cache (cache dopytov): Query cache je mechanizmus, ktorý si pamätal výsledky SELECT dopytov a pri rovnakom dopyte z cache vrátil výsledok namiesto znovuvýpočtu. MySQL 8.0 query cache úplne odstránil kvôli problémom so škálovateľnosťou. V starších MySQL (5.6/5.7) bola query cache dostupná, ale predvolene vypnutá (size=0), lebo na viacjadrových systémoch spôsobovala lock contention . MariaDB query cache stále obsahuje – v MariaDB 10.x je default query_cache_size=1M ale query_cache_type=OFF (tzn. vypnutá, aj keď alokovaná 1MB) . Odporúčanie: vo väčšine prípadov query cache nepoužívajte. Pokiaľ nemáte špecifický scenár (veľmi statické dáta, málo zápisov, veľa identických selectov), query cache spôsobí viac problémov (synchronizácia pri každom zápise, invalidácia). Moderné prístupy kešovania sa riešia skôr na aplikačnej vrstve (napr. výsledky dopytov do Redis/memcached, alebo aspoň proxy cache). Ak však viete, čo robíte a MariaDB query cache chcete, dajte jej rozumnú veľkosť (desiatky MB, nie viac) a sledujte metriku Qcache_hits vs Qcache_inserts atď., aby ste vedeli či má efekt. Pre nových resp. začínajucich adminov však skôr: nechajte query cache vypnutú, sústrediť sa treba na iné oblasti.
  
• table_open_cache: MySQL (resp. MariaDB) interné cache otvorených tabuliek. Databáza musí často otvárať súbory tabuliek (frm definície, alebo samotné dátové súbory MyISAM/Aria), a táto cache drží handle už otvorených tabuliek pre rýchlejší prístup. Ak máte veľa tabuliek alebo často spúšťate dopyty na rôzne tabuľky, väčšia cache pomôže, aby systém nemusel stále otvárať/zatvárať súbory. Stavová hodnota Opened_tables indikuje, koľkokrát musel server otvoriť tabuľku – ak rýchlo rastie, cache je malá. Odporúčame nastaviť aspoň niekoľko tisíc (predvolené bývalo okolo 200. Na serveri s 16GB RAM kľudne table_open_cache = 4000 alebo viac, ak máte tisíce tabuliek. Pamäte to veľa nezje a môže to znížiť latencie pri prístupe k novým tabuľkám.

Pozor: ak zvýšite table_open_cache, zároveň zvýšte limit počtu súborov v OS (ulimit -n alebo v /etc/security/limits.conf), pretože MySQL bude držať viac file descriptorov otvorených.

• table_definition_cache: Podobná cache ako open_cache, ale cache definícií tabuliek (najmä užitočné v InnoDB, kde sa definície inak ukladajú do jeho data dictionary a mohlo by dochádzať k reloadom). Zvyčajne ju MySQL nastaví automaticky podľa počtu tabuliek. Ak máte desiatky tisíc tabuliek (napr. multitenant aplikácia s veľa schémami), zvažujte navýšenie. Inak default býva OK. Toto nastavenie neovplyvňuje priamo výkon dopytov, skôr zabraňuje prieťahom pri otváraní nových tabuliek.
  
• max_connections a thread_cache_size: Síce to nie je cache výsledkov alebo dát, ale ide o cache vlákien (threads). MySQL obsluhuje každé pripojenie v samostatnom vlákne. Keď sa klient odpojí, vlákno sa buď ukončí alebo sa uloží do thread cache pre neskoršie použitie. Parameter thread_cache_size hovorí, koľko nepoužívaných vlákien držať „v rezerve“. Ak je 0, vždy sa vlákno zruší a nové pripojenie si vyžiada fork nového vlákna (čo má malú režiu, ale pri veľmi častom pripájaní sa to môže nasčítať). Preto je vhodné mať thread_cache_size aspoň niekoľko (desiatok). Napríklad 16 alebo 50 podľa toho, koľko typicky mŕtvych vlákien vzniká. Stavová hodnota Threads_created vám povie, koľko vlákien bolo vytvorených – ak vidíte, že je veľmi vysoká a Connections tiež, znamená to, že cache mohla byť väčšia. Moderné MySQL často thread cache autokonfiguruje. V MariaDB (ak nepoužívate thread pool) tiež funguje podobne. Nastavenie max_connections určuje, koľko súčasných pripojení maximálne DB akceptuje. Na výkon priamo nemá vplyv (okrem toho, že extrémne vysoké číslo môže skonzumovaž veľa pamäte ak by sa všetci naraz pripojili). V bežných prípadoch hodnota okolo 150–300 stačí. Ak potrebujete viac (napr. stovky), uistite sa, že na to máte pamäť a prípadne zvážte architektúru (napr. použitie connection poolu v aplikácii, ProxySQL, alebo thread pool ak MariaDB). Každé spojenie (vlákno) totiž nesie režijnú pamäť (stack ~256KB, plus prípadne alokované buffre pre dopyt atď.). Tisíc nečinných pripojení tak zaberie ~256MB pamäte a nič nerobia.
  
• query_cache_size (MariaDB): Ako spomínané, radšej vypnúť alebo ponechať malú. Ak by ste ju predsa využili, sledujte Qcache_free_blocks a fragmentáciu cache.
  
• tmp_table_size a max_heap_table_size: Tieto nastavenia ovplyvňujú, do akej veľkosti môže MySQL vytvoriť dočasnú tabuľku v pamäti. Ak dočasná tabuľka (napr. pre GROUP BY, DISTINCT, či na triedenie) presiahne túto veľkosť, automaticky sa zapíše na disk (čo je pochopiteľne pomalšie). Predvolene býva okolo 16–32MB. Na 16GB serveri si môžete dovoliť zvýšiť povedzme na 64MB alebo 128MB, ak máte dopyty, ktoré triedia veľké množstvo dát. Zvýši to šancu, že sa to celé zmestí do RAM a nezasiahne disk. Pozor však, že toto je na každú dočasnú tabuľku – ak by naraz stovka dopytov robila obrovské dočasné tabuľky, môže to dokopy zjesť značnú RAM. Čiže nastavovať s uvážením podľa povahy dopytov.
  
• join_buffer_size, sort_buffer_size, read_buffer_size, read_rnd_buffer_size: To sú buffre pre jednotlivé dopyty (resp. threado). Napríklad join_buffer sa používa pri spojeniach bez indexu (bad), sort_buffer pri triedení ktoré sa nevmestí do optimalizovaných štruktúr atď. MySQLTuner často navrhuje tieto hodnoty zvyšovať ak vidí veľa využití. Buďte opatrný – tieto buffre sa alokujú per thread per operation. Ak ich dáte napr. 64MB každý a spustí sa komplexný dopyt, môže jedno vlákno zabrať aj stovky MB len v dočasných buffroch. A ak takých dopytov paralelne beží viac, pamäť sa míňa rýchlo. Všeobecne defaulty (napr. join_buffer 256KB–1MB, sort_buffer pár MB) sú v poriadku pre väčšinu prípadov. Zvyšujte ich len ak viete, že to potrebujete (napr. špecifický dopyt na veľa dát) a radšej dočasne pomocou SET SESSION pre dané spojenie. Globálne zväčšovanie týchto bufferov je zradné. Často je lepšie optimalizovať dopyt (pridať index, upraviť plán) než liať viac pamäte do bufferov.

Najväčší význam má InnoDB buffer pool, ostatné cache (table cache, thread cache) doladiť podľa potreby. Query cache radšej off. Ostatné buffre a pamäte nechajte približne default, pokiaľ nemáte dôvod meniť – a ak meníte, buďte si vedomí efektu na pamäť pri mnohých vláknach. Vždy sledujte hit-ratio ak je k dispozícii – napr. Key_reads a Key_read_requests pre MyISAM key cache (dnes menej podstatné, ale ak by ste používali MyISAM/Aria, veľkosť key_buffer_size nastavte podľa veľkosti indexov týchto tabuliek). Pre InnoDB buffer pool je hit ratio väčšinou 99%+ ak je správne nastavený, inak by ste videli veľa Innodb_buffer_pool_reads z disku.

Paralelizácia a počet pripojení

Výkon moderného databázového servera závisí aj od toho, ako dokáže využiť viacjadrový procesor a obslúžiť mnoho súbežných dopytov. Tu je niekoľko aspektov:

• Maximálny počet spojení (max_connections): Ako už bolo spomenuté, toto číslo nerobí databázu rýchlejšou samo o sebe, ale nastavuje limit, koľko klientov môže naraz byť pripojených. Ak ho nastavíte príliš nízko, riskujete chybu “Too many connections” ak príde špička. Ak príliš vysoko, riskujete, že pri nejakej zbláznenej situácii začne tisíc vláken zápasiť o zdroje a server sa zahltí kontextovými prepínačmi alebo vyčerpá pamäť. Pre VPS 4 vCPU / 16 GB RAM typicky stačí okolo 200–300. Viac dáva zmysel len ak bežne máte stovky aktívnych spojení (napr. veľký web s mnohými worker procesmi). Mnohé pripojenia však môžu byť len nečinné (idle) – tie moc nevadia, len zaberú thread (to rieši thread pool, viď nižšie). Sledujte Threads_connected a Threads_running – prvé sú všetky pripojenia, druhé len tie aktuálne vykonávajúce prácu. Aj pri 200 pripojeniach môže byť len 5 aktívnych (ostatné čakajú na klienta). V takom prípade výkon sa riadi tými aktívnymi, ostatné len visia v pamäti.
  
• Thread Pool vs per-thread: Ako spomínané v sekcii rozdielov, Oracle MySQL Community používa model jedno vlákno na jedno pripojenie. To znamená, že ak aj máte 500 pripojení, je tam 500 vlákien, hoci väčšina nič nerobí. OS/Linux si s tým do istej miery poradí (vlákna ktoré sú idle nespotrebúvajú CPU, len pamäť). No ak začne naraz 500 dopytov, 500 vlákien sa rozbehne a to môže spôsobiť silné preťažovanie CPU (prepínanie medzi 500 vláknami na 4 jadrách = každý dostane malý kúsok času, overhead je veľký). Riešením je thread pool, ktorý počet skutočne bežiacich threadov obmedzuje a radí dopyty do front, ak je už jedno vlákno na jadro zaneprázdnené. Thread pool je dostupný v MariaDB out-of-the-box (premenná thread_handling=pool-of-threads je default). V Percona Server sa dá zapnúť nastavením rovnakej premennej.. V MySQL Community bohužiaľ nie je – tam by ste museli prejsť na MySQL Enterprise alebo použiť connection pool pred MySQL (napr. ProxySQL môže multiplexovať mnoho logických spojení do pár fyzických). Odporúčanie: Ak používate MariaDB alebo Perconu a máte 100ky+ pripojení, určite využite thread pool. Spravidla funguje bez nutnosti ďalšieho ladenia, default rozdelí pripojenia do thread group podľa jadier . Výsledkom je, že aktívnych vlákien beží približne toľko ako CPU jadier, čo zabráni zbytočnému kontextovému prepínaniu a často to zlepší latenciu dopytov pri vysokej záťaži (za cenu mierneho oneskorenia pre niektoré dopyty kvôli čakaniu vo fronte, ale to je stále lepšie ako znefunkčniť celý server preťažením). Ak ste na Oracle MySQL, zvážte ProxySQL alebo aspoň dostatočne vysoký thread_cache_size, nech aspoň recykluje vlákna.
  
• Paralelné spracovanie dopytu: Tradične MySQL dopyt (napr. jeden SELECT) beží v jednom vlákne, teda na jednom jadre. To znamenalo, že ak máte jeden veľmi ťažký dopyt, nevyužije viac jadier (na rozdiel od niektorých iných DB). MySQL 8.0 však priniesol parallel read pre InnoDB (čiastočne) a parallel index build atď. Napríklad ak robíte ALTER TABLE … ADD INDEX, môže použiť viac jadier. Tiež v replikácii existuje paralelné aplikovanie transakcií. Ale bežný SELECT stále ide v jednom vlákne. Preto pri tuningu myslite tak, že výkon sa dosahuje paralelnosťou mnohých dopytov, nie paralelizáciou jedného dopytu (ako by to vedel napr. Oracle DB či Postgres s určitými nastaveniami). Z pohľadu admina to znamená: rozloženie záťaže – ak potrebujete spracovať veľký report, radšej to rozbiť na viac menších dopytov paralelne ak to dá zmysel, inak ten jeden dopyt bude obmedzený výkonom jedného jadra. V praxi s tým veľa nenarobíme, len je dobré to vedieť.
  
• innodb_read_io_threads a innodb_write_io_threads: Ide o počet vlákien, ktoré InnoDB používa na pozadie I/O operácie (čítanie pre read ahead, zápisy na pozadí). Default býval 4 a 4. Na serveri s 4 jadrami to spravidla stačí. Ak by ste mali viac diskov alebo fakt extrémnu I/O záťaž, dalo by sa zvýšiť na 8. MySQL umožňuje až 64 nastaviť, ale reálne prínos nad 4–8 býva minimálny, keďže aj tak záleží na IOPS disku. Neodporúčame meniť, pokiaľ nemáte meranie, že I/O queue nie je dostatočne vyťažený s 4 vláknami (to je málo pravdepodobné na bežnom SSD). MariaDB tiež default 4. Čiže skôr to neriešte – spomíname pre kompletnosť informácií.
  
• innodb_thread_concurrency: Historický parameter, ktorým sa obmedzoval počet súbežných InnoDB threads. V nových verziach ak je 0 (default), tak je vlastne vypnutý a InnoDB si to riadi sama. Kedysi sa ladil v MySQL 5.1/5.5, dnes už nie. Nechajte default (0 alebo nechať tak).
  
• Asynchrónne I/O a prefetching: Ešte spomenieme, že InnoDB využíva asynchrónne I/O, takže keď jedno vlákno potrebuje čítať z disku, neblokuje tým nutne CPU – požiadavka sa spracuje asynchrónne pomocou spomínaných read_io_threads. To je len detail, ktorý vysvetľuje, prečo aj jedno vlákno dokáže zapojiť viac vlákien v pozadí. Ale maximum CPU pre výpočet nad dátami má stále jedno.
  
• Scale-up vs scale-out: Ak narazíte na limit, že 4 jadrá CPU proste nestačia (máte trvalo 100% CPU a dopyty nestíhajú), máte v zásade dve cesty – scale-up (výkonnejší stroj, viac jadier, viac GHz) alebo scale-out (rozložiť záťaž na viac serverov). Scale-up v prípade MySQL ide do istej miery – MySQL 8 zvládne využiť aj 32+ jadier pre mnoho súbežných spojení, takže ak máte možnosť, môžete zvýšiť počet vCPU z 4 na 8 či 16 a uvidíte zlepšenie pri paralelnej záťaži. Samozrejme, u VPS to závisí od vášho plánu. Scale-out znamená napr. rozdeliť databázu na viac serverov podľa funkcie (napr. jeden server na čítanie – replikácia, druhý na zápisy) alebo podľa dát (sharding). To je však už architektonická otázka nad rámec tohto článku. Pre väčšinu prípadov optimalizácia konfigurácie a dopytov na jednom servery postačuje.

Indexy a návrh schémy

Aj ten najvyladenejší databázový server nebude podávať dobrý výkon, ak dopyty a databázová schéma nie sú optimálne navrhnuté. Z pohľadu administrátora nemáme vždy dosah na úpravu kódu aplikácie, ale môžeme analyzovať dopyty (cez slow log, EXPLAIN) a identifikovať potenciálne problémy, ktoré vývojárom navrhneme na opravu. Niekoľko tipov:

• Indexy, indexy, indexy: Najčastejšou príčinou pomalých dopytov je chýbajúci alebo nevhodný index. Full table scan je rádovo pomalšie než použitie indexu na výber pár riadkov. Ako povedal jeden znalec: “Ak dopyt musí prejsť celú tabuľku, nepomôžu ti ani 10× rýchlejšie CPU, ale ak pridáš index a dopyt spracuje len 0,01% tabuľky, získaš obrovské zrýchlenie aj bez rýchlejšieho hardvéru.” . Toto presne vystihuje význam indexov. Admin by mal preto prejsť slow log a pre podozrivo pomalé dopyty si vyskúšať EXPLAIN dopyt; – uvidí sa, či používajú indexy (typicky v stĺpci key alebo possible_keys). Ak nie, treba navrhnúť pridať index na stĺpec, podľa ktorého sa vo WHERE filtuje alebo JOINuje. Napríklad dopyt WHERE email = ‚nieco‘ na tabuľku s miliónmi záznamov bude pomalý bez indexu na stĺpci email. Jednoduchým pridaním indexu môžete skrátiť beh dopytu z sekúnd na milisekundy, čo je najlepšia optimalizácia aká existuje (v porovnaní s tým je ladenie configu druhoradé).
  
• Kontrola používania indexov: Niektoré dopyty možno index majú, ale nepoužijú ho – môže byť napr. nevhodný. Toto je skôr na developerov, aby dopyt prepisali alebo vytvorili lepší index (napr. kompozitný index ak dopyt filtruje podľa viacerých stĺpcov súčasne). Ako admin môžete aspoň identifikovať, že “tento dopyt nerobí index scan ale full scan”. MySQL 8 umožňuje aj príkaz EXPLAIN ANALYZE ktorý skutočne vykoná dopyt a povie koľko riadkov prešlo – to je užitočné na potvrdenie.
  
• Návrh schémy: Okrem indexov sem patrí aj typy stĺpcov a normalizácia. Veľmi široké tabuľky (mnoho stĺpcov, zvlášť ak sú TEXT/BLOB nepotrebne) spomaľujú prácu. Obrovské VARCHAR(1000) ak nepotrebujete toľko znakov, atď. Tieto veci keď objavíte, môžete odporučiť nápravu. Ďalej, chýbajúce primárne kľúče – v InnoDB by každá tabuľka mala mať PRIMARY KEY (alebo aspoň unikátny not null index), inak InnoDB musí vytvárať skrytý rowid, čo sťažuje replikácie a môže mať výkonnostné dopady pri niektorých operáciách. Takže ak objavíte tabuľku bez primárneho kľúča, navrhnite ho doplniť.
  
• Údržba štatistík a fragmentácie: InnoDB si automaticky udržiava štatistiky pre optimalizátor (pokiaľ nemáte starú verziu s innodb_stats_on_metadata zapnutým, v nových to už nerobí pri každom selecte). Niekedy pomôže spustiť ANALYZE TABLE mytable; aby sa štatistiky obnovili, ak optimalizátor volí zlý plán. Tiež ak sa výrazne zmenila veľkosť tabuľky (veľa zmazaných záznamov), môžete uvažovať nad OPTIMIZE TABLE (čo je vlastne recreate+analyze pre InnoDB) – to zmenší .ibd súbor a defragmentuje indexy. Avšak s modernými verzami to robte len ak naozaj treba, InnoDB inak efektívne pracuje aj s fragmentation, a OPTIMIZE je zdĺhavá operácia.
  
• Percona Toolkit má nástroj pt-index-usage a pt-duplicate-key-checker, ktoré vedia pomôcť nájsť nepoužívané indexy alebo duplicity. Nadbytočné indexy zbytočne spomaľujú zápisy (každý INSERT/UPDATE ich musí udržiavať) a zaberajú pamäť aj disk. Takže občas je fajn aj odhaliť indexy, ktoré sa nikdy nepoužívajú a odstrániť ich. To ale robte len ak naozaj viete, že sa nepoužijú.

Z pohľadu admina teda monitorujte dopyty a spolupracujte s vývojármi. Môžete im poskytnúť zoznam najhorších dopytov z logu, odporučiť kde pridať index, alebo ktoré indexy by mohli zlepšiť výkon. Toto úsilie často prinesie radovo vyššie zrýchlenie než akékoľvek tunenie parametrov.

Automatické reštarty a logovanie

Stará administrátorská múdrosť hovorí: “Hope for the best, plan for the worst.” Aj dobre vyladená databáza môže zlyhať alebo spadnúť. Dôležité je, aby sa v takom prípade rýchlo zotavila a aby ste mali k dispozícii logy na diagnostiku.

• Automatické reštartovanie: Uistite sa, že databázu spúšťate pomocou mechanizmu, ktorý ju v prípade pádu znova nahodí. Ak používate systém so systemd, skontrolujte mysqld.service – mala by mať nastavené Restart=on-failure (väčšina distier to tak má). V minulosti sa používal wrapper mysqld_safe, ktorý spúšťa mysqld a ak skončí pádom, znovu ho spustí. Na moderných systémoch to prevzal systemd. Každopádne, nechcete ostať s vypnutou DB len preto, že ju nikto nereštartol. Automatický reštart nie je náhrada riešenia problémov, ale kúpi vám čas – výpadok je možno minúta namiesto hodín, ak by ste to nezistili.
  
• Watchdog a vysoká dostupnosť: Pre kritické služby sa oplatí nasadiť ešte watchdog proces, ktorý ak vidí, že DB nereaguje (zasekne sa), tak ju zabije alebo reštartne. To už je pokročilé – existujú na to nástroje ako MHA (Master High Availability) pre MySQL, alebo skripty s mysqladmin ping v crone. Na single serveri však väčšinou postačí spomínaný systemd restart.
  
• Logovanie chýb a výpadkov: MySQL/MariaDB má error log – tam zapisuje rôzne varovania, chyby, stack trace pri páde, informácie o obnove po výpadku atď. Skontrolujte, kam sa loguje (parametre log_error a log_error_verbosity). Zvyčajne to bude /var/log/mysql/error.log. Pri ladiacom režime môžete zvýšiť verbositu (ale nepotrebujete ak všetko beží OK). Dôležité je priebežne nazerať do error logu, či tam nie sú varovania typu „InnoDB: ### …“ alebo „[Warning] Aborted connection…“. Napríklad opakované „Aborted connection (Got an error reading communication packets)“ môžu indikovať problémy s aplikáciou alebo sieťou (klienti sa odpájajú nekorektne). Varovania o výkonových veciach tam moc nie sú, skôr o anomáliách (deadlocky v InnoDB sa logujú, to je užitočné vedieť, ak by aplikácia napr. často vytvárala deadlock situácie, log to ukáže).
  
• Slow query log pre trvalý monitoring: Môžete nechať slow log zapnutý dlhodobo, len zvoľte vhodný long_query_time aby log nerástol príliš rýchlo. Napr. ak bežne dopyty trvajú pod 0.1s a to považujete za OK, dajte long_query_time napr. 0.5 či 1. Nech loguje len naozaj pomalé. Log potom rotujte (pridajte do logrotate.d), aby sa súbor nezahltil. Pravidelným prezeraním slow logu (alebo generovaním prehľadov cez pt-query-digest) získate stále aktuálny prehľad o tom, kde sa v aplikácii objavujú pomalé dopyty – a tie viete následne riešiť.
  
• Ostatné logy: MySQL má aj general query log, ktorý loguje všetky dopyty. Ten určite nechajte vypnutý v produkcii (I/O overkill). Môžete ho použiť dočasne pri debuggu. Tiež binárne logy (ak používate replikáciu alebo aspoň zapnuté pre point-in-time recovery) si zaslúžia pozornosť – sledujte, aby nezahltili disk. Nastavte expire_logs_days (MySQL 8 má binlog_expire_logs_seconds) napríklad na 7 alebo 14 dní, nech sa staré binlogy mažú automaticky. Inak ak zabudnete, o pol roka môže disk zaplniť priečinok s binlogmi.
  
• Test crash recovery: Toto sa moc v praxi nerobí, ale ak viete (napr. v testovacom prostredí), skúste simulovať pád (kill -9 mysqld napríklad) a pustiť ho. Sledujte v error logu, ako dlho trvá obnovenie (InnoDB by mala prejsť logy a nahodiť DB). Malo by to byť relatívne rýchle (pár sekúnd či minút). Ak by ste mali extrémne dlhý recovery, to by indikovalo, že napr. innodb_log_file_size bol zbytočne obrovský a mohol by byť menší. Tiež to otestuje, či máte nastavený auto-restart.
  
• Opäť zálohy a obnova: Hoci to už presahuje tému výkonu, spomenieme, že mať aktuálnu zálohu je aj výkonové opatrenie – v zmysle ak sa niečo pokazí (napr. zlý ALTER Table, alebo nevydarené ladenie configu poškodil data file – hoci to by nemalo, ale pre istotu), aby ste vedeli rýchlo obnoviť prevádzku. V rámci výkonu sem patrí aj test obnovy – nielen že máte dump, ale viete ho nanečisto obnoviť a zmerať, koľko by to trvalo. Lebo ak obnova potrvá 5 hodín, viete, že výpadok by bol 5 hodín. Možno potom zainvestujete do repliky alebo inej HA metódy, aby ste to skrátili.

Starajte sa o logy (error log, slow log) a majte nastavené mechanizmy, aby DB bežala neustále (automatický štart po boote, reštart po páde). Monitoring výpadkov by mal byť samozrejmosť – cieľom je minimalizovať downtime a získať užitočné informácie z logov pre ďalšie ladenie.

Nástroje na ladenie a optimalizáciu

Prejdime si užitočné nástroje, ktoré vedia administrátorovi výrazne pomôcť pri identifikácii problémov a návrhu optimalizácií. Spomenuli sme ich už priebežne, tu ich zhrnieme a porovnáme:

MySQLTuner

Skript v Perli, ktorý analyzuje aktuálny stav MySQL (premenné a štatistiky) a vygeneruje odporúčania.

Rýchle zhodnotenie, upozorní na zjavné nedostatky (napr. malý buffer_pool, fragmentovaná query cache atď.). Nevykonáva zmeny automaticky, iba radí. Je potrebné brať odporúčania s rezervou – každý bod posúdiť, či dáva zmysel. Neodporúča sa slepo aplikovať všetko, cieľom nemá byť “zbaviť sa všetkých warningov” za cenu prestrelených hodnôt . MySQLTuner je dobrý sluha, ale zlý pán – využite ho na checklist, ale premýšľajte nad každou radou.

Tuning Primer

Jednoduchý shell skript (tuning-primer.sh) na podobný účel ako MySQLTuner.

Je to starší nástroj, vhodný najmä pre MySQL 5.x. V novších verziách už nemusí rozpoznať niektoré parametre. Poskytuje základné odporúčania (napr. “query cache disabled – ok”, “joins without index – X”). Ak používate MySQLTuner, tuning-primer netreba, keďže robia podobnú prácu.

Percona Toolkit

Balík pokročilých nástrojov pre MySQL a systém. Obsahuje množstvo utilít: pt-query-digest, pt-kill, pt-stalk, pt-online-schema-change, pt-table-checksum, pt-variable-advisor a ďalšie.

Ide o profesionálnu sadu pre hlbšiu analýzu a správu. Pre výkonové ladenie sú najdôležitejšie: pt-query-digest (analyzuje slow log alebo aj prúd dopytov a vytvorí podrobný report najpomalších dopytov, ich percenta záťaže atď.), pt-variable-advisor (prejde SHOW VARIABLES a upozorní na potenciálne zlé nastavenia – v podstate taký static code analyzer pre my.cnf, s pripravenými pravidlami ), pt-mysql-summary a pt-summary (zhromaždia množstvo info o konfigurácii systému a DB do prehľadnej správy), pt-stalk (stráži výskyt nejakej udalosti, napr. vysoké zaťaženie, a vtedy začne zbierať diagnostické dáta), pt-kill (automaticky ukončuje dopyty/spojenia podľa kritérií – napr. ak nejaký dopyt beží viac než X sekúnd, môže ho zabiť; opatrne s tým v produkcii). Ďalej obsahuje nástroje na údržbu: pt-online-schema-change (umožní meniť schému tabuliek bez výpadku – vytvorením dočasnej tabuľky a triggers, atď.), pt-table-checksum a pt-table-sync (na kontrolu konzistencie replík a nápravu rozdielov), pt-index-usage (skúma logy a povie, ktoré indexy sa nepoužili ), atď. Percona Toolkit je nesmierne užitočný, ale je to ako skalpel v rukách chirurga – treba vedieť čo robíte. Odporúčame ho skôr skúsenejším adminom. Pre začiatok sa zoznámte s pt-query-digest (na analýzu slow logu) a pt-variable-advisor (rýchla kontrola configu). Vždy používajte najnovšiu verziu PT kompatibilnú s vašou DB verziou.

Ako vidno, nástroje MySQLTuner a Tuning Primer sú skôr jednorazové skripty pre rýchlu radu, zatiaľ čo Percona Toolkit je komplexná sada pre kontinuálnu prácu a hlbší zásah. V praxi je dobré pustiť MySQLTuner po pár dňoch behu servera (upozorní vás napríklad na to, koľko % table cache je využitých, koľko dopytov išlo do slow logu, atď.) a následne pravidelne používať napr. pt-query-digest na analyzovanie pomalých dopytov každý týždeň/mesiac.

Ešte spomenieme nástroj Performance Schema + sys – to sú vlastne vstavané “nástroje”. Sys schema (u MySQL) ponúka množstvo užitočných pohľadov ako sys.statements_with_temp_tables alebo sys.indexes_with_seeks atď., ktoré vedia poskytnúť prehľad bez externých nástrojov. MariaDB sys nemá, ale má INFORMATION_SCHEMA a Performance Schema. Ich využitie je však na samostatný článok.

Pri používaní týchto nástrojov vždy dbajte na bezpečnosť (napr. pt-online-schema-change používa TRIGGERy – nezabudnite ich po akcii dropnuť ak by skript neukončil, a podobne). Tiež si všímajte odporúčania z viacerých zdrojov – ak napr. MySQLTuner niečo navrhne, overte to s oficiálnou dokumentáciou či Percona blogom.A hlavne – nezabúdajte na zdravý rozum. Niekedy nástroj povie “zvýšte X na Y”, no vy viete, že vaša pracovná záťaž je špecifická a že to by mohlo spôsobiť iný problém. Berte to ako tip, nie príkaz.

Záver a ďalšie kroky

Ladenie výkonu databáz MySQL/MariaDB/Percona je iteratívny proces. Začína sa pochopením problémov cez monitoring, pokračuje úpravou konfigurácie a optimalizáciou dopytov, a nekončí nikdy – s meniacou sa záťažou a rastúcimi dátami musíme stále dohliadať na to, či je potrebné niečo doladiť.

Hlavné body, ktoré si odtiaľto odniesť:

• Monitorujte: Bez dát naslepo netrafíte. Sledujte CPU, pamäť, I/O, využívajte slow query log. Zároveň monitorujte dostupnosť a zálohy.
  
• Optimalizujte konfiguráciu: Najmä InnoDB a cache parametre. Upravte buffer pool, logy, flush nastavenia podľa vašej RAM a typu disku. Každú zmenu otestujte.
  
• Optimalizujte dopyty a schému: Toto je často najväčšia výhra. Indexy dokážu zázraky. Spolupracujte s vývojármi, poskytujte im dáta o pomalých dopytoch.
  
• Overujte nástroje: Použite skripty a toolkity na identifikáciu slabých miest, ale závery implementujte s rozvahou. Vždy majte zálohu konfigurácie a dát pred väčšími zmenami.
  
• Postupné ladenie: Nečakajte, že jedným zásahom vyriešite všetko. Možno zvýšenie buffer poolu odhalí, že teraz je bottleneck inde (napr. CPU kvôli neoptimal dopytu).
  
• Upgrade softvéru: Držte sa podporovaných verzií – nové verzie prinášajú fixy. Novšie verzie môžu mať lepší výkon bez inej zmeny.
  
• Hardware vs optimalizácia: Ak po vyladení stále výkon nepostačuje, zvážte upgrade HW (viac RAM je vždy dobrá investícia, rýchlejšie CPU pomôže lineárne pri single-thread dopytoch, viac jadier pomôže paralelnej záťaži) alebo rozdelenie záťaže. Ale najprv si buďte istí, že ste softvérovo optimalizovali maximum – je zbytočné kupovať 2× drahší server, ak by stačilo pridať jeden index 😉.

Databáza sa vám odvďačí plynulým chodom a vaši používatelia rýchlymi odozvami aplikácie. Prajeme veľa úspechov pri ladení.