Zvyšujeme výkon MySQL změnou konfigurace

MySQL

Databáze MySQL je velmi populárním a hojně využívaným zdrojem dat pro velké množství aplikací. Pojďme se podívat na možnosti této databáze z hlediska rozvoje aplikace do budoucnosti a potenciálního navyšování výkonu.

Seriál: Optimalizujeme výkon MySQL (2 díly)

  1. Zvyšujeme výkon MySQL změnou konfigurace 14.6.2010
  2. Upgradujeme MySQL server 28.6.2010

Většina moderních aplikací využívá jako úložiště dat relační databázi, která nabízí vysokou flexibilitu při práci s těmito daty. Databáze MySQL je jednou z nejpopulárnějších relačních databází dostupných na dnešním trhu. Nasazována je zejména pro rozličné webové aplikace, open source aplikace a také start-up projekty. Nespornými výhodami, z nichž tato databáze těží, jsou zejména její rozšířenost, cena, podpora velkého množství programovacích jazyků a bezesporu také obecné povědomí.

V tomto článku bychom se neměli zabývat popisem databáze samotné, nýbrž možnostmi MySQL z hlediska potenciální optimalizace a navyšování výkonu. Nemalá část nejen začínajících vývojářů je pouhými „konzumenty“ služeb, které databáze nabízí, a neumějí se vypořádat se situací, kdy jejich aplikace či start-up získá na popularitě a je třeba řešit otázky zvyšování výkonu. Ve většině případů je to právě databáze, která se stává úzkým hrdlem celé aplikace.

Na dnešní článek, v němž si rozebereme možnosti optimalizace výkonu databáze MySQL z hlediska změny konfigurace databázového serveru, naváže další díl, který pojedná o vertikálním škálování, a poslední díl pak o replikaci a možnostech jejího využití při vertikálním škálování.

Analýza aplikace

Dříve než se bezhlavě vrhneme do jakýchkoli úprav konfigurace a nastavení databáze MySQL či dokonce realizace replikace, je třeba dobře analyzovat celou aplikaci a zjistit, zda jsme nepodcenili fázi návrhu datového modelu.

Velmi často se totiž stává, že poměrně snadné navýšení výkonu aplikace jsme schopni získat optimalizací dotazů a doplněním chybějících indexů v tabulkách. Chybějící indexy lze doplnit pečlivou analýzou dotazů SELECT, jež jsou databází vykonávány, pomoci příkazu EXPLAIN. (Vlastní analýzu dotazů za pomoci příkazu EXPLAIN zde popisovat nebudu, neboť rozsahem překračuje tento článek, ale v případě čtenářského zájmu bychom se k tomuto tématu mohli vrátit a připravit samostatný článek včetně praktických příkladů. – pozn.aut.)

Pro následující řádky proto předpokládejme, že naše aplikace prošla kvalitním návrhem datového modelu a pokládané dotazy jsou již optimalizované.

Konfigurační soubor

Konfigurace MySQL databáze je uložená v souboru my.ini. Při nové instalaci MySQL databáze je tato databáze zpravidla nainstalována ve standardní konfiguraci (pokud nebylo při instalaci explicitně zvoleno jinak), kterou v žádném případě není možné označit jako výkonově optimální. Tuto standardní konfiguraci je bohužel velmi často možné nalézt i u některých poskytovatelů webhostingových služeb (navýšen bývá pouze maximální počet paralelních připojení).

Optimalizovat nastavení MySQL můžeme pouze v případě, že máme patřičná oprávnění na serveru. Pokud tedy aplikaci provozujeme na běžném webhostingu, pak tuto možnost mít v žádném případě nebudeme. Alternativou jsou v tomto případě virtuální servery, kdy si pronajímáme výpočetní výkon, diskový prostor a provozujeme vybraný operační systém. Virtuální hosting se hodí zejména pro aplikace, které do budoucna očekávají nutnost zvyšování výkonu a nechtějí zbytečně platit od začátku za výkon, který aktuálně nevyužijí.

Vraťme se ale k vlastní úpravě konfigurace MySQL databáze. Úpravu můžeme provádět přímo editací souboru my.ini, anebo lze využít některých GUI, jako například MySQL Administrator (viz. obr. 1), který je výhodný pro méně zkušené uživatele.

 Obr. 1 – Prostředí  MySQL Administrator

Co je třeba zjistit před změnou konfigurace?

Bezprostředně před úpravou nastavení MySQL je žádoucí zjistit si několik informací, které mají zásadní vliv na případné úpravy konfigurace.

  1. Hardwarovou konfiguraci počítače – primárně nás zajímá množství operační paměti RAM, sekundárně pak procesor a počet jader.
  2. Zda MySQL běží jako dedikovaný server – pokud ne, pak jaké prostředky a paměť alokují ostatní služby.
  3. Počet na serveru běžících databází – důležitý je počet databází a velikost dat a indexů. Značný rozdíl je mezi jednou obrovskou statistickou databází anebo velkým počtem malých DB.
  4. Počet paralelně přistupujících klientů – obvyklý počet připojených klientů v exponovanou dobu běhu.
  5. Používaný typ databázových tabulek – typicky MyISAM či InnoDB, kdy tabulky typu MyISAM jsou daleko výkonnější, ale nepodporují transakční zpracování dat.
  6. Typ prováděných dotazů – zajímají nás zejména dotazy SELECT (lze vyčíst z výsledků dotazu SHOW STATUS LIKE 'select%') a poměr vůči dotazům měnícím obsah databáze.

Na základě zjištění těchto informací můžeme přistoupit k vlastní konfiguraci databázového serveru. Klíčovým faktorem pro optimalizaci je zejména velikost dostupné volné paměti pro různé buffery a cache a počet a výkon jader procesoru u nastavení umožňujících paralelizaci. U dedikovaných serverů je možné využít i více než 80% celkové operační paměti.

Cachujeme výsledky dotazů

Nemalé navýšení výkonu databáze je možné dosáhnout aktivací cache pro výsledky dotazů SELECT. Velká většina aplikací pokládá dotazy opakovaně, a proto je dobré mít výsledky již předpřipravené, pokud byly nedávno položeny.


Obr. 2 – Statistiky dotazů MySQL evidované programem Munin

Výhoda využívání této cache je tím vyšší, čím méně často v databázi probíhají změny a čím větší je počet paralelně přistupujících klientů k dané databázi. Vhodné je využití pro menší beztransakční databáze s jednoduchými dotazy.

Konfigurační proměnné:

  • Query_cache_size – při nenulovém nastavení udává velikost cache. Doporučené hodnoty pro nastavení jsou v řádech desítek MB, neboť stovky MB bývají již kontraproduktivní z důvodů nutnosti zneplatňování velkého množství záznamů při změnách. Konkrétní hodnota pro jednotlivé servery se může lišit a optimum je třeba stanovit měřením.
  • Query_cache_limit – maximální velikost výsledku dotazu, který má být ještě uložen do cache. Standardní hodnota je 1MB a je dostačující pro většinu potřeb. Pokud potřebujeme cachovat větší výsledky, můžeme tento limit zvýšit.
  • Query_cache_type – typ cache,
    • OFF = vypnuto,
    • ON = výchozí nastavení, zapnuto pro všechny výsledky dotazů kromě dotazů začínajících SELECT SQL_NO_CACHE,
    • DEMAND = zapnuto pouze pro výsledky dotazů začínajících SELECT SQL_CACHE.

Efektivitu cache lze měřit pomocí stavových proměnných, které zobrazíme například položením dotazu  SHOW STATUS
LIKE 'Qcache%'
.

Nakonec této části je třeba upozornit na to, že cache dotazů není všelékem pro všechny typy aplikací. Pro velmi zatížené aplikace je lepší cachovat již celé objekty či celky než pouhé výsledky dotazů. Navíc Query Cache MySQL není až tak rychlá jako specializované služby (jako například memcached či lokální sdílená paměť).

Akademie Root nabízí školení MySQL. Naučte se používat jednu z nejrozšířenějších databází – MySQL. Na našem školení se dozvíte vše potřebné od návrhu až po samotné využití MySQL ve vašich projektech. Školení je vhodné jak pro důkladné seznámení se základy databází, tak pro využití MySQL u náročnějších projektů.

Cachování indexů tabulek

Jestliže u cachování výsledků dotazů jsme nebyli schopni určit optimální nastavení, tak u cachování indexů tabulek máme situaci značně jednodušší. Paměť je vždy násobně rychlejší než pevný disk, a proto je vhodné mít v paměti nejlépe indexy všech tabulek.

Optimalizace je závislá na typu používaných tabulek. Pokud v databázích používáme většinu tabulek typu MyISAM, pak budeme chtít optimalizovat key_buffer. Naopak pokud většina tabulek je typu InnoDB, pak optimalizujeme innodb_buffer. Při podobném zastoupení pak je vhodné optimalizovat oba buffery s vědomím, že větší prioritu by měl mít vždy  innodb_buffer.

Konfigurační proměnné:

  • Key_buffer_size – velikost bufferu pro cachování indexů MyISAM tabulek. Typické hodnoty jsou v řádech GB. Není nutné nastavovat velikost o mnoho větší než je součet velikostí všech souborů *.myi. Pozor, že některé verze MySQL v sobě měly bug způsobující pád DB serveru v případě, kdy byla hodnota nastavena na hodnotu vyšší než 4GB a došlo k zaplnění bufferu na více než 4GB.
  • Innodb_buffer_po­ol_size – cache pro data a indexy tabulek typu InnoDB. Zde se vyplatí nešetřit a u dedikovaných serverů nastavit hodnoty i více než 70 % celkové operační paměti (samozřejmě nenastavujeme velikost o mnoho větší, než je součet velikostí  souborů idbdata).


Obr. 3 – Efektivita Query Cache a Key buffer v čase

Efektivitu nastavení key_buffer můžeme opět měřit a sledovat. Stavové proměnné zobrazíme položením dotazu SHOW
STATUS LIKE 'Key%'
. Problém s těmito countery je ovšem ten, že nezohledňují nikterak hledisko času, a proto je výhodnější využít jiné nástroje, které toto měřítko zohledňují (viz. obrázek 2).

Optimalizace InnoDB

Pokud používáme jako hlavní úložiště tabulek typu InnoDB, které podporují transakční zpracování, pak určitě budeme chtít optimalizovat parametry pro InnoDB.

Konfigurační proměnné:

  • Innodb_buffer_po­ol_size – cache pro data a indexy tabulek. Na dedikovaných serverech s výhodou nastavujeme v rozmezí 70–80% celkové paměti.
  • Innodb_additi­onal_mem_pool_si­ze – cache pro slovník a jiné interní informace. Pokud máme na serveru velké množství databází a tabulek v nich, je vhodné výchozí nastavení zvýšit. 20 MB je přiměřený začátek.
  • Innodb_log_fi­le_size – velikost logovacího souboru. Vyplatí se nám zvětšit zejména u aplikací, které často zapisují data do databáze. Čím větší hodnotu nastavíme, tím déle bude trvat následná obnova, pokud dojde k pádu DB serveru! V závislosti na velikosti serveru jsou hodnoty mezi 64 a 512 MB rozumným kompromisem mezi výkonem a dobou obnovy.
  • Innodb_log_buf­fer_size – velikost bufferu logu dříve než je zapsán do souboru, v rozmezí 1 až 8 MB. Jestliže využíváme velké a složité transakce, pak je výhodné výchozí hodnotu zvýšit. 4MB jsou zpravidla dostačující pro většinu transakcí.
  • Innodb_flush_log_­at_trx_commit – udává, kdy se mají transakce zapisovat z bufferu na pevný disk.
    • 1 = výchozí hodnota, všechny transakce jsou zapisovány na disk bezprostředně po jejich COMMITu. Toto nastavení zapříčiňuje velké množství diskových I/O operací a může být limitující. Výhodou je bezpečnost těchto transakcí.
    • 2 = transakce jsou po COMMITu zapisovány do cache OS. Tato cache je zapisována na disk v intervalu jedné sekundy. Jestliže je server zatěžován velkým množstvím zápisů do databáze, pak změnou na tuto hodnotu můžeme rapidně zvýšit výkon. Pozor, toto nastavení může být nebezpečné, neboť pokud operační systém zhavaruje, tak přijdeme pravděpodobně o transakce provedené v poslední sekundě!
    • 0 = výkonově ještě výhodnější než 2, ale může dojít ke ztrátě dat nejen při pádu OS, ale také při pádu MySQL serveru!
  • Innodb_thread_con­currency – konkurence jednotlivých vláken. Výchozí hodnota je odvislá od použité verze MySQL a je zpravidla optimální pro většinu aplikací. Pokud vyvíjíme vysoce konkurenční aplikace, může mít zvýšení pozitivní vliv na výkon.
  • Innodb_file_per_ta­ble – pro servery s menším počtem databází a tabulek v nich můžeme s výhodou aktivovat tuto proměnnou, kdy je každá tabulka ukládána jako samostatný soubor.
  • Innodb_open_files – souvisí s nastavením, kdy máme každou tabulku jako samostatný soubor a je výhodné mít všechny využívané tabulky otevřené. Nastavujeme ideálně dle počtu souborů typu *.ibd.

Optimalizace nastavení jednotlivých vláken

Všechna nastavení popsaná výše se vztahovala ke globální konfiguraci MySQL serveru a byla společná pro všechna běžící vlákna. Pojďme se nyní podívat na možnosti nastavení jednotlivých pracovních vláken.

Jestliže jsme pro key_buffer a inno_db_pool chtěli mít ideálně všechna data v paměti, tak pro buffery jednotlivých vláken musíme být opatrní, neboť bezhlavé navyšování těchto hodnot může mít velmi degradující vliv na výkon. Alokace nadbytečné paměti nás stojí nemalé výkonové prostředky a zpravidla je výhodnější tyto buffery naopak snižovat. Výchozí nastavení je výborným kompromisem, pokud si nechceme dělat vlastní experimentální testy přímo pro konkrétní serverové řešení.

  • Sort_buffer_size – využívají jednotlivá vlákna při operacích ORDER BY a GROUP BY. Buffer je využíván jak pro tabulky MyISAM, tak pro InnoDB.
  • Read_buffer_size – používá se pro sekvenční scanování MyISAM tabulek, výchozí hodnota je 128 KB a není třeba ji nikterak měnit, neboť podle empirických měření je to hodnota optimální pro velké i malé soubory dat.
  • Read_rnd_buffer_si­ze – využívá se pro některé způsoby řazení, kdy potřebujeme následně číst data podle nějakého klíče. Bufffer je použitelný jak pro MyISAM tak pro InnoDB tabulky.
  • Thread_stack – velikost zásobníku jednotlivých vláken. Využívá se například pro rekurzivní volání uložených procedur.

Další proměnné mající vliv na výkon MySQL

Závěrem si již jen v krátkosti uveďme ještě některé proměnné, které mají vliv na výkon, a jejichž nastavení je žádoucí v některých případech měnit.

  • Table_cache – počet otevřených pro všechna vlákna. Výchozí hodnotu je dobré patřičně zvětšit zejména pro servery s velkým množstvím databází a tabulek v nich, a také pro servery s velkým počtem paralelně přistupujících klientů.
  • Thread_cache – počet udržovaných pracovních vláken. Pro exponované servery, ke kterým paralelně přistupuje velké množství klientů v jednom čase, je výhodné nastavit tuto hodnotu s ohledem na průměrný počet aktivních spojení v době nejvyšší zátěže.
  • Tmp_table_size – udává maximální velikost dočasné tabulky, kterou je možné vytvořit v operační paměti. Po překročení tohoto limitu jsou dočasné tabulky jsou vytvářeny jako MyISAM tabulky na disku. Současně s navýšením hodnoty je dobré zvýšit také nastavení max_heap_table_si­ze.
  • Max_connections – maximální počet paralelně otevřených spojení. Hodnotu odvodíme od obvyklého počtu připojených klientů v exponovanou dobu běhu, kdy navíc připočteme 10 % jako rezervu.


Obr. 4 – Počet aktivních vláken MySQL v čase

Počítáme obsazenou paměť

Dříve než uložíme provedené změny a uvedeme je v platnost, je dobré si ověřit provedenou konfiguraci z hlediska paměťového zatížení. Obsazenou paměť spočítáme poměrně jednoduchou kalkulací, kdy sečteme globální nastavení všech bufferů a připočteme násobek paměti, kterou zabírají jednotlivá vlákna.

Mysql_max_memory_usage =
  Query_cache_size      +   Key_buffer_size         +
  Myisam_sort_buffer_size   +   Innodb_buffer_pool_size     +
  Innodb_log_buffer_size    +   Innodb_additional_mem_pool_size +
  Max_connections       *
  (
    Sort_buffer_size        +   Read_buffer_size        +
    Read_rnd_buffer_size    +   Join_buffer_size        +
    Thread_stack
  )

Nejedná se o naprosto přesné číslo, ale pro potřeby ověření nám postačí. U dedikovaných serverů můžeme využít většinu paměti serveru pro potřeby MySQL serveru. Pokud sdílíme prostředky s jinými službami, pak je potřeba vzít v úvahu jejich paměťové potřeby, abychom se vyhnuli swapování na disk. Pokud bychom v konfiguraci nastavili přehnané paměťové nároky, bylo by celé nastavení kontraproduktivní.

Závěr

V dnešním díle seriálu o zvyšování výkonu MySQL serveru jsme se seznámili s možnostmi změny konfigurace serveru. Změna je poměrně jednoduchou záležitostí, pokud víme co děláme a vycházíme z dobře analyzované situace, kdy máme odpovědi na důležité otázky. Navíc nás úprava nastavení nestojí žádné finanční prostředky (vyjma investovaného času). V žádném případě se nejedná o kompletní přehled možných změn konfigurace. Podrobnější informace včetně měření a benchmarků lze čerpat například z blogu www.mysqlperfor­manceblog.com.

V příštích dílech se podíváme na možnosti vertikálního škálování a také na replikaci a možnosti jejího využití při horizontálním škálování výkonu.

David Hübner pracuje na pozici vedoucího vývoje a projektového manažera ve společnosti eBRÁNA s.r.o.

Věděli jste, že nám můžete zasílat zprávičky? (Jen pro přihlášené.)

Komentáře: 18

Přehled komentářů

kuba Re: Zvyšujeme výkon MySQL změnou konfigurace
Blaxi Re: Zvyšujeme výkon MySQL změnou konfigurace
konzultant v oboru ICT Re: Zvyšujeme výkon MySQL změnou konfigurace
Martin Malý Re: Zvyšujeme výkon MySQL změnou konfigurace
AnDY Re: Zvyšujeme výkon MySQL změnou konfigurace
fos4 Re: Zvyšujeme výkon MySQL změnou konfigurace
Jakub Vrána Připomínky
Radim Smička Query cache
Blaxi Re: Query cache
mat MySQL je pro opravdové zoufalce
František Kučera Re: MySQL je pro opravdové zoufalce
tonda Re: MySQL je pro opravdové zoufalce
sup Re: MySQL je pro opravdové zoufalce
Jakub Vrána Re: MySQL je pro opravdové zoufalce
Blaxi Re: MySQL je pro opravdové zoufalce
mat Re: MySQL je pro opravdové zoufalce
logik Re: MySQL je pro opravdové zoufalce
Martin Re: MySQL je pro opravdové zoufalce
Zdroj: https://www.zdrojak.cz/?p=3247