Tech Life

Ilustrační obrázek

Optimální nastavení join bufferu a sort bufferu v MySQL

09. 05. 2012 13:00    kategorie: Tech Life    autor: PMe    komentářů: 1

Najít optimální nastavení bufferů v MySQL není vždy snadné. Některé se alokují pro celý server, jiné pro daný thread a některé mají svoji specifickou logiku alokace. Díky tomu nelze vždy říct, že vetší buffer znamené větší výkon, i když to tak někdy z konfigračních souborů MySQL serveru může vypadat.
 

Často se v konfiguraci MySQL objevují například následující hodnoty:

sort_buffer_size=32M
join_buffer_size=64M

Je opravdu nutné alokovat takto velké buffery? Přínášejí opravdu kýžený výkonový zisk? A jak se vlastně doopravdy tyto dva buffery alokují?

Alokace paměti v Linux

Pro správné nastavení bufferů v MySQL je nejprve nutné se zamyslet jak s pamětí pracuje operační systém (v našem případe Linux). Při alokaci paměti MySQL používá standardní funkci malloc() (resp. obaluje jí ve funkci my_malloc()), která pro naalokování paměti používá dvě systémové volání - brk/sbrk a mmap. Jakou z nich Linux použije je dáno velikostí MMAP_THRESHOLD, kterou lze upravit pomocí funkce mallopt() (defaultně 128kB). Rozdíl mezi nimi je, že sbrk rozšíří stávající velikost datového segmentu oproti tomu mmap alokuje nezávislé oblasti virtuální paměti. Z hlediska výkonu je alokace pomocí systémového volání brk zhruba o 10% rychlejší než mmap, nicméně pro alokaci větších velikostí paměti již převládají její negativní vlastnosti např. fragmentace paměti (ne vždy je možné paměť naalokovanou přes sbrk vratit OS) a proto se používá mmap. Při použití bufferů pod velikost 128kB je možné získat drobný výkonový benefit i tímto. Dále může výrazně promluvit do délky alokace bufferu např. požadavek na jeho vynulování.

Nastavení sort bufferu

Velikost sort bufferu, nastavitelná pomocí proměné sort_buffer_size, pouze říká to, jak velká oblast může být použita pro řazení v paměti. Pro řazení záznamů používá MySQL techniku nazvanou filesort. Toto obecné pojmenování algoritmu řazení (ve skutečnosti rovnou dvou metod: původní a modifikované) ovšem nijak nespecifikuje, zda řazení probíhá v paměti, nebo v dočasném souboru na disku. To, zda se během řazení použil dočasný soubor a případně kolikrát, lze ziskat z proměné Sort_merge_passes. Nastavení sort bufferu by mělo vždy odpovídat požadavkům konkrétní aplikace. Lze nicméně říci, že nastavení výše než 8M výkonu už příliš neprospívá a pokud existuje nějaký dotaz v aplikaci, který nelze upravit, je lepší variantou nastavit sort_buffer_size v session při výkonávání dotazu. K nastavení sort bufferu je ještě vhodné nastavit proměnou read_rnd_buffer_size (velikost row bufferu) na velikost 8-16MB, která zvyšuje výkon, pokud optimalizátor zvolí původní verzi filesortu (obvykle když se řadí sloupce typu BLOB nebo TEXT).

Nastavení join bufferu

U join bufferu je situace mnohem zajímavější. Tento buffer se alokuje pouze v případě, že join je typu ALL nebo index, tedy není možné použít index (ref). Způsob alokace je takový, že pro každé spojení tabulek (kromě první tabulky) je alokován buffer minimálně o velikosti definované proměnou join_buffer_size a tedy výsledná paměťová náročnost pro joiny bez indexu muže být opravdu značná. Další nečekanou vlastností je i to, že na velikosti join bufferu až tak nezáleží, protože si jej ve skutečnosti MySQL volí v některých případech samo. Ve zdrojovém souboru sql/sql_select.cc je ve funkci join_init_cache vidět následující:


static int
join_init_cache(THD *thd,JOIN_TAB *tables,uint table_count)
{
...
cache->length=length+blobs*sizeof(char*);
cache->blobs=blobs;
*blob_ptr=0; /* End sequentel */
size=max(thd->variables.join_buff_size, cache->length);
if (!(cache->buff=(uchar*) my_malloc(size,MYF(0))))
DBUG_RETURN(1); /* Don't use cache */ /* purecov: inspected */
cache->end=cache->buff+size;
reset_cache_write(cache);
DBUG_RETURN(0);
}

Tedy MySQL ve skutečnosti volí velikost bufferů jako větší z hodnoty join_buffer_size a zjištěné potřebné velikosti bufferu. Toto chování potvrzuji i výkonové testy, které jsem provedl, kdy menší výkonový propad je znát u větších velikostí bufferu (32MB a více). Takto vysoké hodnoty by se v provozním prostředí neměly objevovat. Celková paměťová naročnost join dotazu bez použití indexů je následující - (n-1)*max(join_buffer_size,cache_length). Zásada pro hladký provoz serveru je tedy odstranit join dotazy bez indexu, jinak může v extrémním případě dojít i k vyčerpání paměti. Optimální nastavení proměnné join_buffer_size je obvykle mezi 1-8MB.

Závěr

Na join bufferu i sort bufferu jsme si ukázali, že méně někdy znamená více a tedy určitě není vhodné nastavovat zbytečně vysoké hodnoty při honbě za maximálním výkonem. Mnohem důležitější je odstranit dotazy, které nepoužívají indexy při joinech (ideálně samozřejmě všechny dotazy, které nepoužívají indexy), nebo dotazy, které provádějí řazení nad velkými tabulkami. Důležité je také se nad konfigurací MySQL více zamyslet, ne jen hloupě používat předpřipravené konfigurační šablony, které příliš nereflektují realitu konkrétní instance MySQL serveru.

Zdroje

O autorovi:
Petr Medonos vystudoval obor Inženýrská Informatika na VŠCHT. Od roku 2008 pracuje v Et neteře, kde se zabývá navrhováním robustních klientských architektur s důrazem na vysokou dostupnost, databázemi a bezpečností.

Sdílet odkaz:
tisk

Diskuze k článku

M.S., 23.6.2012 07:56

Děkuji za článek, který je jak po odborné tak i po čtivé stránce VYNIKAJÍCÍ.

Přidat příspěvek

 

Kontakt pro média


Máte zájem o další informace, odborný článek či přednášku na konferenci? Kontaktujte nás prosím na pr@etnetera.cz.

RSS - Tech life


RSS kanál Tech Life Blogu

Offlineblog

Offlineblog

Ljama


Komix z prostředí imaginární firmy.

ljama

Ještě jste ho nečetli? Tak tudy ...

 
Doporučujeme: Nabídka práce, volná pracovní místa - pracovní portál SPRÁVNÝKROK.CZ