text: Optimální nastavení join bufferu a sort bufferu v MySQL / Etnetera
  Home > ETN Blog > Tech Life > Optimální nastavení join bufferu a sort bufferu v MySQL
  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:
  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í:
  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);
  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.
  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.