Přejít k navigační liště

Zdroják » Databáze » Zvyšujeme výkon MySQL změnou konfigurace

Zvyšujeme výkon MySQL změnou konfigurace

Články Databáze

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.

Nálepky:

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.

Komentáře

Subscribe
Upozornit na
guest
18 Komentářů
Nejstarší
Nejnovější Most Voted
Inline Feedbacks
View all comments
kuba

„naváže další díl, který pojedná o horizontá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í“
Buď si autor plete vertikalni vs horizontální škálování, nebo to bude hodně zajímavé :)

Blaxi

Samozřejmě jedná se o záměnu těchto výrazů, má býti:
„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.“
Má omluva.

konzultant v oboru ICT

O cem muze byt clanek o vertialnim skalovani? Proste jen potunime server po fyzicke strance a je to. Clanek na jednu vetu :-)

Martin Malý

… anebo se autor přepsal! :) Opraveno, díky za upozornění.

AnDY

Inac na tuning mysql posluzi celkom sikovny skript:
http://mysqltuner.pl/mysqltuner.pl

fos4

Některé také přidám:
mysqlreport: http://hackmysql.com/mysqlreport
mysqlprimer: http://www.day32.com/MySQL/

Jakub Vrána

Kromě několika překlepů je článek podle mě poměrně zdařilý. Možná bych alespoň nějaký čas věnoval specifikům InnoDB (obzvlášť primárnímu klíči, podle kterého se data organizují a který se přímo zapisuje do listů ostatních indexů, takže jeho volba má zásadní důležitost). No a pak by taky neškodilo lépe vysvětlit, jak funguje query cache (že jakákoliv změna tabulky smaže cache všech dotazů, které tabulku používají, nejen dotčených záznamů).
Rád bych se nicméně ohradil proti dvěma výrokům:
1. „tabulky typu MyISAM jsou daleko výkonnější“ – to zásadně záleží na charakteru provozu. V jednouživatelském režimu to obvykle platí, ale ve víceuživatelském (který je zcela typický pro webové aplikace) to může být přesně naopak. Obzvlášť kombinace UPDATE a SELECT (i nesouvisejících záznamů v jedné tabulce) může být pro MyISAM smrtelná.
2. U query cache „čím větší je počet paralelně přistupujících klientů k dané databázi“ – jakou souvislost s query cache má to, jestli klienti přistupují paralelně nebo sériově? Pokud vím, tak žádnou.

Radim Smička

Bylo by užitečné trochu více v článku zdůraznit Query cache. Přijde mi, že převládá názor zapnutí query cache = zvýšení výkonu. Čím častější aktualizace a čím unikátnější dotazy tím méně se cache vyplatí. Nemá smysl cachovat select, který se provádí v průměru jednou za minutu, když podkladová tabulka se mění každých 10 vteřin.
>Efektivitu cache lze měřit pomocí stavových proměnných, které zobrazíme například položením dotazu SHOW STATUS LIKE ‚Qcache%‘.
Podle mě je to téměř k ničemu, často jsou v aplikaci dotazy na malé pomocné tabulky, které jsou rychlé a tuhle statistiku úplně rozhodí. Je lepší se vždy zamýšlet opravdu nad tím kolikrát se po update, insert nebo delete provede select – tedy 2×, 1000×? Není univerzální odpověď kdy cache a no a kdy ne.
I pokud se tabulka nemění často může query cache dělat problémy – pokud na tabulku míří velká spousta neopakujících se dotazů. Taková změna tabulky po 1 000 000 unikátních dotazů, může trvat relativně dlouho (kromě změny tabulky se bude měnit i cache).

Blaxi

Samozřejmě, že článek nemohl obsáhnout všechny možnosti a nastavení, už takto jsem se musel značně omezovat, abych se vešel do rozumného maxima délky článku. Od toho jsou tu dobré ty diskuze, kde je možné některé důležité věci zdůraznit.
Mno už aby vyšla verze MySQL 5.5, jež slibuje velmi pěkné navýšení výkonu zejmnéma pro tabulky InnoDB a také semisynchronní replikace. Doufám, že se vývojářům povede vydat GA verzi o prázdninách.

mat

MySQL je opravdový děs, pokud to myslíte z databázemi opravdu vážně, použijte PostgreSQL …

František Kučera

+/- souhlas, ale na druhou stranu nad MySQL existuje řada fajn aplikací, takže se hodí umět poladit i MySQL databázi – ne všechno můžeš zmigrovat do PostgreSQL (i když ta aplikace o sobě tvrdí, že to jde, tak se nakonec může ukázat, že jí nikdo s jinou databází netestoval a odladěná je jen pro MySQL).

tonda

souhlas mysql je des …

sup

Vsade citam, ze postgreSQL je lepsia ako MySQL, ale nikde nie su dovody. Nie je niekde clanok s porovnanim tychto dvoch DB?

Jakub Vrána

Já jsem před rokem srovnal funkce MySQL a PostgreSQL: http://php.vrana.cz/srovnani-funkci-mysql-a-postgresql.php
Je to čistě srovnání funkcí, nezohledňoval jsem výkonnost, konfigurovatelnost, využitelnost (rozšířenost) a tak dále.

Blaxi

Na googlu se dá najít celkme dost článků včetně benchmarků
http://www.google.cz/search?hl=&q=mysql+vs+postgresql+performance&sourceid=navclient-ff&rlz=1B5GGGL_csCZ301CZ303&ie=UTF-8&aq=3&oq=mysql+vs+p
celkem pěkné porovnání včetně dalších odkazů
http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

mat

v cem je rozdil? reknu to ze sveho pohledu. DB pouzivam temer vyhradne pro analyticke ucely.. tzn mam v nich ulozeny data a vytvarim nad nima reporty, slozite selecty, analyzy … Mysql stale nepodporuje celou radu sql prikazu, ktere jsou v tzv SQL standardu a musi se to slozite obchazet .. Je to takovy voser, ze by jeden skakal z okna. Bohuzel nekteri klienti maji data v tomto systemu :-( Pokud to alespon trochu jde, delam import do rozumejsich systemu jako je postgre, oracle apod …
MySQL podle me nema zadne opodstatneni. Dnes uz neni ani rychla, ani „light“ … vsechny drivejsi vyhody konkurence dohnala, tak proc pouzivat nedodelek?

logik

Všechny? Nezpochybňuju, že mysql je děs, ale která free databáze nabízí dobře vyřešenou replikaci?

Martin

A na tohle jste prisel kde? Podle me je to prave naopak…

Enum a statická analýza kódu

Mám jednu univerzální radu pro začínající programátorty. V učení sice neexistují rychlé zkratky, ovšem tuhle radu můžete snadno začít používat a zrychlit tak tempo učení. Tou tajemnou ingrediencí je statická analýza kódu. Ukážeme si to na příkladu enum.

Pocta C64

Za prvopočátek své programátorské kariéry vděčím počítači Commodore 64. Tehdy jsem genialitu návrhu nemohl docenit. Dnes dokážu lehce nahlédnout pod pokličku. Chtěl bych se o to s vámi podělit a vzdát mu hold.