Ladění systémových proměnných MySQL pro vysoký výkon

Pro většinu vývojářů aplikací je databáze oltář démonských bohů, který je nejlépe ponechán bez přístupu. Ale nemusí to tak být!


Pokud jsou věci stejné, úroveň pohodlí, kterou má vývojář s podkladovou databází, určuje jejich úroveň seniority. Malá databáze a malé zkušenosti s kódováním = vývojář juniorů; málo databáze a dobré zkušenosti s kódováním = vývojář na střední úrovni; dobrá databáze a dobrá zkušenost s kódováním = starší vývojář.

Je to drsná realita, která dokonce vydrží 6-8 let pod pásem boje, aby vysvětlila složitosti optimalizátoru dotazů a raději hledala nebe, když se zeptá na ladění databáze.

Proč?

Důvodem není překvapení lenivost (i když v určité části je to).

Jde o to, že databáze jsou silou, se kterou se mohou potýkat. Dokonce i tradičně, když existovaly pouze relační typy databází, jejich zvládnutí bylo zázrakem a kariérní cestou samo o sobě; v těchto dnech máme tolik typů databází, že je prostě nemožné očekávat, že jediná smrtelná duše zvládne všechno.

To znamená, že existuje dobrá šance, že jste stále spokojeni s relačními databázemi nebo jste součástí týmu, který má produkt spuštěný v relační databázi uspokojivě dlouhou, dlouhou dobu. A v devíti případech z deseti jste na MySQL (nebo MariaDB). V těchto případech poskytuje potápění o něco hlouběji pod kapotou obrovské výhody při zvyšování výkonu aplikací a stojí za to se naučit.

Zvědavý? Pojďme se ponořit!

Není zvědavý? Každopádně se ponořte, protože na tom závisí vaše kariéra! ��

Optimalizujte mezipaměť dotazů MySQL

Téměř veškerá optimalizace v oblasti počítačů přichází do mezipaměti. Na jedné straně CPU udržuje několik úrovní mezipaměti, aby zrychlil své výpočty, a na druhé straně webové aplikace agresivně využívají řešení pro ukládání do mezipaměti, jako je Redis, aby serverům předkompilovaly výsledky, spíše než aby zasáhly databázi pokaždé.

Ale hej, i chudá databáze MySQL má svou vlastní mezipaměť dotazů! To znamená, že pokaždé, když něco požádáte, a data jsou stále zastaralá, MySQL bude tyto výsledky v mezipaměti zobrazovat spíše než znovu spustit dotaz, čímž se aplikace směšně zrychlí.

Chcete-li zkontrolovat, zda máte v databázi k dispozici mezipaměť dotazů (poznámka, dostupná, není povolena), spusťte tento dotaz v databázové konzoli:

MariaDB [(žádný)]> ZOBRAZIT PRŮMĚNY JAKO ‘have_query_cache’;
+——————+——-+
| Název proměnné | Hodnota |
+——————+——-+
| have_query_cache | ANO |
+——————+——-+

Takže vidíte, že běží MariaDB a že mám zapnutou mezipaměť dotazů. Je velmi nepravděpodobné, že byste jej vypnuli, pokud používáte standardní instalaci MySQL.

Nyní se podívejme, jestli mám skutečně zapnutou mezipaměť dotazů:

MariaDB [(žádný)]> ZOBRAZIT PRŮMĚNY JAKO ‘query_cache_type’;
+——————+——-+
| Název proměnné | Hodnota |
+——————+——-+
| query_cache_type | ZAPNUTO |
+——————+——-+

Ano. V případě, že tak neučiníte, můžete to zapnout vyslovením:

MariaDB [(žádný)]> SET GLOBAL query_cache_type = ON;

Zajímavé je, že tato proměnná také přijímá třetí hodnotu, která označuje „na vyžádání“, což znamená, že MySQL bude ukládat do mezipaměti pouze ty dotazy, které jí řekneme, ale nebudeme se do toho zabývat zde.

S tím máte dotazování do mezipaměti a udělali jste první krok k robustnějšímu nastavení MySQL! Říkám první krok, protože při jeho zapnutí je hlavní vylepšení, ale musíme vyladit ukládání dotazů do mezipaměti tak, aby vyhovovalo našemu nastavení. Naučme se to dělat.

Další sledovanou proměnnou je query_cache_size, jejíž funkce je samovysvětlující:

MariaDB [(žádný)]> ZOBRAZIT PRŮMĚNY JAKO ‘query_cache_size’;
+——————+———-+
| Název proměnné | Hodnota |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Mám tedy mezipaměť dotazů přibližně 16 MB. Všimněte si, že i když je zapnuto ukládání dotazů do mezipaměti, ale tato velikost je nula, je ukládání do mezipaměti skutečně vypnuto. Proto nestačí pouze kontrola jedné proměnné. Nyní byste měli nastavit velikost mezipaměti dotazu, ale kolik by to mělo být? Nejprve si prosím všimněte, že funkce ukládání do mezipaměti dotazu bude sama potřebovat k uložení svých metadat 4 KB, takže cokoli, co vyberete, by mělo být nad.

Řekněme, že jste nastavili velikost mezipaměti dotazů na 500 kB:

MariaDB [(žádný)]> SET GLOBAL query_cache_size = 500000;

Dělá to dost? Ne, protože způsob, jakým bude dotazovací stroj skutečně fungovat, závisí na několika dalších věcech:

  • Nejprve musí být proměnná query_cache_size dostatečně velká, aby uchovala výsledek vašich dotazů. Pokud je příliš malý, nebude nic v mezipaměti.
  • Zadruhé, pokud je parametr query_cache_size nastaven na příliš vysoké číslo, nastanou dva typy problémů: 1) Stroj bude muset provést další práci s ukládáním a lokalizováním výsledků dotazu v této masivní oblasti paměti. 2) Pokud většina dotazů vyústí v mnohem menší velikosti, mezipaměť se rozdrobí a výhody používání mezipaměti se ztratí..

Jak víte, že mezipaměť se fragmentuje? Zkontrolujte celkový počet bloků v mezipaměti takto:

MariaDB [(žádný)]> zobrazit stav jako ‘Qcache_total_blocks’;
+———————+——-+
| Název proměnné | Hodnota |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Pokud je číslo velmi vysoké, mezipaměť je fragmentovaná a musí být vyprázdněna.

Abyste se těmto problémům vyhnuli, ujistěte se, že velikost dotazu_cache_size je vybrána rozumně. Pokud se cítíte frustrovaní tím, že jsem vám tu nenechal konkrétní číslo, obávám se, že to je způsob, jak se věci posunou po vývoji a vstoupíte do strojírenství. Musíte se podívat do spuštěné aplikace a zjistit, jaké jsou velikosti dotazů pro důležité výsledky dotazů, a poté nastavit toto číslo. A i pak byste mohli nakonec udělat chybu. ��

Vlákna, fondy vláken, čekání a timeouty

Toto je pravděpodobně nejzajímavější část toho, jak MySQL funguje a jak to správně napravit, znamená, že aplikace bude několikrát rychlejší!

Vlákno

MySQL je server s více vlákny. To znamená, že pokaždé, když dojde k novému připojení k serveru MySQL, otevře se nové vlákno s daty připojení a předá popisovač klientovi (pouze v případě, že se zajímáte, co vlákno je, podívejte se na tento). Klient poté odešle všechny dotazy přes toto vlákno a obdrží výsledky. To nás vede k položení přirozené otázky: kolik vláken se může MySQL roztočit? Odpověď leží v následující části.

Nitě bazén

Žádný program v počítačovém systému nemůže otevřít tolik vláken, kolik chce. Důvod je dvojí: 1) Vlákna s nitěmi (RAM) a operační systém vám prostě nedovolí berserk a všechno to sníst. 2) Správa, řekněme, milion vláken je sama o sobě masivní úkol, a pokud by server MySQL mohl vytvořit tolik vláken, zemřelo by se pokusilo vyřešit režii.

Abychom těmto problémům předešli, přichází MySQL s fondem vláken – pevným počtem vláken, které jsou na začátku součástí fondu. Nové požadavky na připojení způsobí, že MySQL vybere jeden z těchto vláken a vrátí data připojení, a pokud jsou všechna vlákna použita, nová připojení jsou přirozeně odmítnuta. Uvidíme, jak velký je fond vláken:

ariaDB [(žádný)]> zobrazit proměnné jako ‘thread_pool_size’;
+——————+——-+
| Název proměnné | Hodnota |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Můj počítač tak umožňuje maximálně čtyři připojení současně. Je zajímavé, že číslo 4 pochází ze skutečnosti, že mám čtyřjádrový procesor, což znamená, že můj počítač může současně spouštět pouze 4 paralelní úkoly (zde mluvím o skutečně paralelních úlohách, nikoli o souběžných). V ideálním případě by to měl být limit, který by měl řídit hodnotu thread_pool_size, ale u hovězích strojů zvyšuje jeho přínos do určitého bodu. Pokud nechcete, aby všechna nová připojení čekala a jste v pořádku, abyste zasáhli nějaký výkon (znovu, toto je oblast, kterou můžete posoudit nejlépe na základě výkonu vaší aplikace při zatížení), může být dobrý nápad rozmístit až na 8..

Avšak nastavení nad 16 je hrozný nápad, pokud nemáte 32jádrový stroj, protože výkon se výrazně snižuje. Králičí díra podprocesů v MySQL jde hluboko, ale pokud vás to zajímá, tady je podrobnější diskuse.

Čekání a vypršení časového limitu

Jakmile bude vlákno vytvořeno a připojeno ke klientovi, bylo by plýtvání zdroji, pokud by klient během příštích několika sekund (nebo minut) neposlal žádné dotazy. Výsledkem je, že MySQL ukončí připojení po období nečinnosti. To je řízeno proměnnou wait_timeout:

MariaDB [(žádný)]> zobrazit proměnné jako ‘wait%’;
+—————+——-+
| Název proměnné | Hodnota |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Výsledná hodnota je v sekundách. Takže ano, ve výchozím nastavení je MySQL nastaveno tak, aby vyčkávalo 8 a více hodin, než oddělí kabel! To může být dobré, pokud máte dlouhodobé dotazy a skutečně na ně chcete čekat (ale i tak je osm hodin absurdní!), Ale ve většině případů je to hrozné. Při spuštění dotazu je tato hodnota nastavena na 0 (což znamená navždy), ale obecně by to mělo být nastaveno na velmi nízkou hodnotu (například 5 sekund nebo možná i méně), aby se uvolnilo připojení pro jiné procesy..

Ladění dočasných tabulek

Začněme s dočasnými tabulkami v MySQL.

Předpokládejme, že máme MySQL, které strukturálně vypadá takto: TABULKA A UNIE (TABULKA B VNITŘNÍ PŘIPOJENÍ C). To znamená, že máme zájem připojit se k tabulkám B a C a poté provést spojení výsledku s tabulkou A. Nyní by MySQL nejprve přistoupilo k tabulkám B a C, ale než bude moci provést spojení, potřebuje pro uložení těchto dat někde. Zde přicházejí dočasné tabulky – MySQL je používá k dočasnému ukládání dat ve středních fázích ve složitých dotazech a jakmile je dotaz ukončen, dočasná tabulka se zahodí.

Nyní je otázkou: proč bychom se tím vším měli zabývat?

Jednoduše proto, že dočasná tabulka, pouze výsledek dotazu, jsou data, která MySQL používá při výpočtu, a rychlost jejího přístupu (mimo jiné omezení) určí, jak rychle se dotaz provede. Například uložení dočasné tabulky do paměti RAM bude několikrát rychlejší než uložení na disk.

Toto chování řídí dvě proměnné:

MariaDB [(žádný)]> zobrazit proměnné jako ‘MariaDB [(žádné)]> zobrazit proměnné jako ‘tmp_table_size’;
+—————-+———-+

| Název proměnné | Hodnota |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Název proměnné | Hodnota |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(žádný)]> zobrazit proměnné jako ‘tmp_table_size’;
+—————-+———-+
| Název proměnné | Hodnota |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

První z nich, max_heap_table_size, nám říká, kolik paměti RAM lze využít v tabulce MySQL („halda“ zde odkazuje na datovou strukturu používanou při přidělování a správě RAM – čtěte více tady), zatímco druhý, tmp_table_size, ukazuje, jaká je maximální velikost dočasné tabulky. V mém případě jsou oba nastaveny na 16 MB, i když se snažím zajistit, aby zvýšení pouze tmp_table_size nebude fungovat tak celkově, MySQL by stále bylo omezeno max_table_heap_size.

Nyní přichází smysl: pokud jsou vytvářené dočasné tabulky větší než limit povolený těmito proměnnými, MySQL by byla nucena je zapsat na pevný disk, což by mělo za následek extrémně nízký výkon. Naše práce je nyní jednoduchá: udělejte maximum, abyste odhadli nejpřesnější velikost dat pro dočasné tabulky a vyladěte tyto proměnné na tento limit. Chtěl bych však varovat před absurditou: nastavení tohoto limitu na 16 GB (za předpokladu, že máte tolik paměti RAM), když většina vašich dočasných tabulek má velikost menší než 24 MB, je pošetilost – jednoduše plýtváte RAM, která by mohla ‘ už byly použity jinými dotazy nebo částmi systému (například mezipaměť).

Závěr

Není možné pokrýt všechny systémové proměnné v jednom článku nebo dokonce všechny důležité proměnné v jednom článku, když samotná dokumentace MySQL zahrnuje několik tisíc slov. Přestože jsme zde popsali některé univerzální proměnné, doporučujeme vám prozkoumat systémové proměnné pro motor, který používáte (InnoDB nebo MyISAM).

Mým nejžádanějším výsledkem pro psaní tohoto článku je, abyste si vzali tři věci:

  1. MySQL je typický kus softwaru, který pracuje v mezích stanovených operačním systémem. Není to nějaký záhadný program, který by Bůh věděl, co je nemožné skrotit. Naštěstí také není tak těžké pochopit, jak je nastavena a ovládána svými systémovými proměnnými.
  2.  Neexistuje žádné jediné nastavení, které způsobí, že se vaše instalace MySQL přiblíží. Nemáte na výběr, ale podívat se do běžících systémů (pamatujte, že optimalizace přichází poté, co je aplikace ve výrobě, ne dříve), dělat nejlepší odhady a měření a žít s realitou, že to nikdy nebude dokonalé.
  3. Ladění proměnných není jediným způsobem, jak optimalizovat MySQL – efektivní psaní dotazů je další velký problém, ale je to něco, na co se budu zabývat v jiném článku. Jde o to, že i když jste provedli božskou analýzu a vyladili tyto parametry co nejlépe, stále je možné, abyste vše zastavili.

Jaká je vaše oblíbená systémová proměnná pro ladění? ��

TAGY:

  • Databáze

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Комментариев нет, будьте первым кто его оставит

    Комментарии закрыты.

    Adblock
    detector