Ladenie systémových premenných MySQL pre vysoký výkon

Pre väčšinu vývojárov aplikácií je táto databáza oltárom bohov démonov, ktorí by mali zostať nedotknutí. Ale nemusí to tak byť!


Ak sú veci rovnaké, úroveň pohodlia, ktorú má vývojár so základnou databázou, určuje ich úroveň odpracovaných rokov. Malá databáza a malé skúsenosti s kódovaním = vývojár junior; málo databázy a dobré skúsenosti s kódovaním = vývojár na strednej úrovni; dobrá databáza a dobré skúsenosti s kódovaním = vedúci vývojár.

Je to drsná realita, ktorá dokonca trvá 6 až 8 rokov v rámci ich opasku, aby vysvetlila zložitosti nástroja na optimalizáciu dopytov a na požiadanie sa radšej pozerala do neba. ladenie databázy.

prečo?

Je prekvapujúce, že dôvodom nie je lenivosť (aj keď v určitej časti je to tak).

Ide o to, že databázy sú silou, s ktorou sa musia vyrovnať. Dokonca už tradične, keď existovali len relačné typy databáz, ich zvládnutie bolo samo osebe zázrakom a kariérnou cestou; v dnešnej dobe máme toľko typov databáz, že je jednoducho nemožné očakávať, že jediná smrteľná duša zvládne všetko.

Je tu však veľká šanca, že ste stále spokojní s relačnými databázami alebo ste súčasťou tímu, ktorý má produkt prevádzkovaný na relačnej databáze uspokojivo dlhý a dlhý čas. A v deviatich prípadoch z desiatich ste na MySQL (alebo MariaDB). V týchto prípadoch poskytuje potápanie len o niečo hlbšie pod kapotou obrovské výhody pri zvyšovaní výkonu aplikácií a stojí za to sa naučiť..

Zvedaví? Poďme sa ponoriť!

Nie ste zvedaví? Každopádne sa ponorte, pretože od toho závisí vaša kariéra! ��

Optimalizujte vyrovnávaciu pamäť dotazov MySQL

Takmer všetka optimalizácia v oblasti počítačov prichádza do vyrovnávacej pamäte. Na jednej strane CPU udržiava niekoľko úrovní vyrovnávacej pamäte, aby sa urýchlili jeho výpočty, a na druhej strane webové aplikácie agresívne využívajú riešenia ukladania do vyrovnávacej pamäte, ako je napríklad Redis, aby serverom predkomprimovali výsledky, namiesto toho, aby zakaždým zasiahli databázu..

Ale hej, dokonca aj zlá databáza MySQL má svoju vlastnú vyrovnávaciu pamäť dotazov! To znamená, že zakaždým, keď niečo zadáte, a údaje sú stále neaktuálne, MySQL bude slúžiť skôr na tieto výsledky uložené v pamäti, než aby znova spustil dotaz, čím sa aplikácia smiešne zrýchli..

Spustením tohto dotazu v databázovej konzole môžete skontrolovať, či máte v databáze k dispozícii vyrovnávaciu pamäť dotazov (poznámka, dostupná, nie je povolená).

MariaDB [(žiadne)]> ZOBRAZIŤ PREMENNÉ PODĽA ‘have_query_cache’;
+——————+——-+
| Názov premennej | Hodnota
+——————+——-+
| have_query_cache | ÁNO |
+——————+——-+

Môžete teda vidieť, že prevádzkujem aplikáciu MariaDB a že mám zapnutú službu cache pre ukladanie dopytov. Je veľmi nepravdepodobné, že by ste ho vypli, ak používate štandardnú inštaláciu MySQL.

Teraz sa pozrime, či mám skutočne zapnutú vyrovnávaciu pamäť dopytov:

MariaDB [(žiadne)]> ZOBRAZIŤ PREMENNÉ PODOBNÉ ‘query_cache_type’;
+——————+——-+
| Názov premennej | Hodnota
+——————+——-+
| query_cache_type | ZAPNUTÉ |
+——————+——-+

Áno. Ale v prípade, že to neurobíte, môžete to zapnúť vyslovením:

MariaDB [(žiadne)]> SET GLOBAL query_cache_type = ON;

Je zaujímavé, že táto premenná akceptuje aj tretiu hodnotu, ktorá označuje „na požiadanie“, čo znamená, že MySQL bude ukladať do vyrovnávacej pamäte iba tie otázky, ktoré jej povieme, ale nebudeme sa do toho zaoberať tu.

S týmto máte ukladanie dotazov a urobili ste prvý krok k robustnejšiemu nastaveniu MySQL! Hovorím prvý krok, pretože keď ho zapneme, je to zásadné zlepšenie, musíme vyladiť ukladanie do vyrovnávacej pamäte otázok, aby vyhovovalo nášmu nastaveniu. Naučme sa to robiť.

Ďalšou zaujímavou premennou, ktorá je tu uvedená, je query_cache_size, ktorej funkcia je samovysvetľujúca:

MariaDB [(žiadne)]> ZOBRAZIŤ PREMENNÉ PODOBNÉ ‘query_cache_size’;
+——————+———-+
| Názov premennej | Hodnota
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Mám teda vyrovnávaciu pamäť dopytov s veľkosťou približne 16 MB. Upozorňujeme, že aj keď je zapnuté ukladanie do vyrovnávacej pamäte dopytov, ale táto veľkosť je nula, ukladanie do vyrovnávacej pamäte je skutočne vypnuté. Preto nestačí skontrolovať iba jednu premennú. Teraz by ste mali nastaviť veľkosť vyrovnávacej pamäte dotazu, ale koľko by mala byť? Po prvé, nezabudnite, že funkcia ukladania údajov do vyrovnávacej pamäte bude sama o sebe potrebovať 4 KB na uloženie svojich metadát, takže všetko, čo vyberiete, by malo byť nad.

Povedzme, že ste nastavili veľkosť vyrovnávacej pamäte dopytov na 500 kB:

MariaDB [(žiadne)]> SET GLOBAL query_cache_size = 500000;

Robí to dosť? No, nie, pretože to, ako bude mať vyhľadávací stroj skutočne výkon, závisí od niekoľkých ďalších vecí:

  • Po prvé, premenná query_cache_size musí byť dostatočne veľká, aby vydržala výsledok vašich dopytov. Ak je príliš malý, nič sa nezachováva.
  • Po druhé, ak je parameter query_cache_size nastavený na príliš vysoké číslo, budú to dva typy problémov: 1) Motor bude musieť urobiť viac práce pri ukladaní a lokalizovaní výsledkov dotazov v tejto rozsiahlej oblasti pamäte. 2) Ak väčšina dopytov vedie k oveľa menším veľkostiam, vyrovnávacia pamäť sa fragmentuje a výhody používania vyrovnávacej pamäte sa stratia..

Ako viete, že vyrovnávacia pamäť sa fragmentuje? Skontrolujte celkový počet blokov v vyrovnávacej pamäti, ako je tento:

MariaDB [(žiadne)]> zobraziť stav ako ‘Qcache_total_blocks’;
+———————+——-+
| Názov premennej | Hodnota
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Ak je číslo veľmi vysoké, vyrovnávacia pamäť je fragmentovaná a musí sa prepláchnuť.

Aby ste sa týmto problémom vyhli, uistite sa, že veľkosť query_cache_size je zvolená rozumne. Ak sa cítite frustrovaní, že som vám tu nenechal konkrétne číslo, obávam sa, že takto sa veci vyvíjajú, keď sa pohnete po vývoji a vstúpite do strojárstva. Musíte sa pozrieť na spustenú aplikáciu, zistiť, aké sú veľkosti dotazov pre dôležité výsledky dotazov, a potom nastaviť toto číslo. A aj potom by ste mohli urobiť chybu. ��

Vlákna, oblasti vlákien, čakanie a vypršanie časového limitu

Toto je pravdepodobne najzaujímavejšia časť toho, ako MySQL funguje a ako to správne funguje, znamená, že vaša aplikácia je niekoľkokrát rýchlejšia!

Threading

MySQL je server s viacerými vláknami. To znamená, že zakaždým, keď dôjde k novému pripojeniu k serveru MySQL, otvorí sa nové vlákno s údajmi o pripojení a odovzdá sa mu popisovač pre klienta (len v prípade, že sa pýtate, čo je vlákno, pozrite si toto). Klient potom odošle všetky dotazy cez toto vlákno a dostane výsledky. To nás vedie k položeniu prirodzenej otázky: koľko vlákien sa môže MySQL roztočiť? Odpoveď je v nasledujúcej časti.

Vlákno vlákno

Žiadny program v počítačovom systéme nemôže otvoriť toľko vlákien, koľko chce. Dôvod je dvojaký: 1) Pamäť nákladov na vlákna (RAM) a operačný systém vám jednoducho nedovolí šokovať a všetko zjesť. 2) Správa, povedzme, milióna vlákien je sama osebe masívna úloha, a ak by server MySQL mohol vytvoriť toľko vlákien, zomrelo by sa tým, že sa bude zaoberať režijnými nákladmi..

Aby sa predišlo týmto problémom, MySQL sa dodáva s oblasťou vlákien – pevným počtom vlákien, ktoré sú na začiatku súčasťou fondu. Nové žiadosti o pripojenie spôsobujú, že MySQL vyberie jeden z týchto vlákien a vráti údaje o pripojení, a ak sa všetky vlákna použijú, nové pripojenia sa prirodzene odmietnu. Pozrime sa, aká veľká je oblasť vlákien:

ariaDB [(žiadne)]> zobraziť premenné ako ‘thread_pool_size’;
+——————+——-+
| Názov premennej | Hodnota
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Môj počítač tak umožňuje súčasne maximálne štyri pripojenia. Je zaujímavé, že číslo 4 pochádza zo skutočnosti, že mám štvorjadrový procesor, čo znamená, že môj počítač môže naraz spúšťať iba 4 paralelné úlohy (tu hovorím o skutočne paralelných úlohách, nie o súbežných úlohách). V ideálnom prípade by to mal byť limit, ktorý by mal riadiť hodnotu thread_pool_size, ale na strojoch na výrobu hovädzieho mäsa je zvýšenie hodnoty prínosom pre určitý bod. Ak nechcete, aby všetky nové pripojenia čakali a ste v poriadku, aby ste zasiahli určitý výkon (znova, toto je oblasť, ktorú môžete posúdiť najlepšie na základe výkonnosti vašej aplikácie pri načítaní), môže byť dobrý nápad rozmnožiť ju na 8..

Jeho nastavenie nad 16 je však hrozný nápad, pokiaľ nemáte 32jadrový stroj, pretože výkon výrazne klesá. Králičia diera bazénov vlákien v MySQL ide hlboko, ale ak vás to zaujíma, tady podrobnejšia diskusia.

Čakanie a vypršanie časového limitu

Po vytvorení vlákna a jeho pripojení ku klientovi by bolo zbytočným plytvaním prostriedkami, ak klient v priebehu niekoľkých sekúnd (alebo minút) nezaslal žiadne otázky. Výsledkom je, že MySQL ukončí pripojenie po určitej dobe nečinnosti. Toto je riadené premennou wait_timeout:

MariaDB [(žiadne)]> zobraziť premenné ako ‘wait%’;
+—————+——-+
| Názov premennej | Hodnota
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Výsledná hodnota je v sekundách. Takže áno, v predvolenom nastavení je MySQL nastavené tak, aby vyčkávalo 8 a viac hodín, kým odpojí kábel! To môže byť dobré, ak máte dlhodobé otázky a skutočne na ne chcete čakať (ale aj tak je osem hodín absurdné), ale vo väčšine prípadov je to hrozné. Keď je spustený dotaz, táto hodnota je nastavená na 0 (čo znamená navždy), ale spravidla by sa mala nastaviť na veľmi nízku hodnotu (napríklad 5 sekúnd alebo možno menej), aby sa uvoľnilo pripojenie pre iné procesy..

Ladenie dočasných tabuliek

Začnime s tým, čo sú dočasné tabuľky v MySQL.

Predpokladajme, že máme MySQL, ktoré štrukturálne vyzerá takto: TABUĽKA A ÚNIA (TABUĽKA B VNÚTORNÝ PRÍVOD C). To znamená, že máme záujem pripojiť sa k tabuľkám B a C a potom vykonať spojenie výsledku s tabuľkou A. Teraz by sa MySQL najskôr pripojilo k tabuľkám B a C, ale skôr ako môže vykonať spojenie, potrebuje na uloženie týchto údajov niekde. Tu prichádzajú dočasné tabuľky – MySQL ich používa na dočasné ukladanie údajov v stredných fázach v zložitých dotazoch a po ukončení dotazu sa dočasná tabuľka zahodí.

Teraz je otázkou: prečo by sme sa mali tým všetkým zaoberať?

Jednoducho preto, že dočasná tabuľka, iba výsledok dotazu, sú dáta, ktoré MySQL používa pri výpočte, a rýchlosť prístupu (okrem iných obmedzení) určí, ako rýchlo sa dotaz vykoná. Napríklad ukladanie dočasnej tabuľky do pamäte RAM bude niekoľkokrát rýchlejšie ako ukladanie na disk.

Toto správanie riadi dve premenné:

MariaDB [(žiadne)]> zobraziť premenné ako ‘MariaDB [(žiadne)]> zobraziť premenné ako ‘tmp_table_size’;
+—————-+———-+

| Názov premennej | Hodnota

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

| tmp_table_size | 16777216 |

+—————-+———-+
,;
+———————+———-+
| Názov premennej | Hodnota
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(žiadne)]> zobraziť premenné ako ‘tmp_table_size’;
+—————-+———-+
| Názov premennej | Hodnota
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Prvá z nich, max_heap_table_size, nám hovorí, koľko pamäte RAM je možné využiť v tabuľke MySQL („halda“ tu odkazuje na dátovú štruktúru použitú pri prideľovaní a správe pamäte RAM – prečítajte si viac tu), zatiaľ čo druhá tabuľka, tmp_table_size, ukazuje, aká je maximálna veľkosť dočasnej tabuľky. V mojom prípade sú obidve nastavené na 16 MB, hoci sa snažím urobiť to, že zvýšenie iba tmp_table_size nebude fungovať tak všeobecne, MySQL by bolo stále obmedzené max_table_heap_size.

Teraz prichádza zmysel: ak sa vytvárajú dočasné tabuľky väčšie ako je limit povolený týmito premennými, MySQL by bola nútená zapísať ich na pevný disk, čo by malo za následok extrémne slabý výkon. Naša práca je teraz jednoduchá: urobte maximum pre to, aby ste odhadli najpresnejšiu veľkosť údajov pre dočasné tabuľky a vylaďte tieto premenné na tento limit. Chcel by som však varovať pred absurditou: nastavenie tohto limitu na 16 GB (za predpokladu, že máte toľko pamäte RAM), keď je väčšina vašich dočasných tabuliek s veľkosťou menšou ako 24 MB, je hlúposť – jednoducho strácate RAM, ktorá by mohla ‘ použili ma iné otázky alebo časti systému (napríklad vyrovnávacia pamäť).

záver

Nie je možné pokryť všetky systémové premenné v jednom článku alebo dokonca všetky dôležité premenné v jednom článku, keď samotná dokumentácia MySQL pokrýva niekoľko tisíc slov. Aj keď sme tu popísali niektoré univerzálne premenné, odporúčame vám pozrieť sa na systémové premenné pre motor, ktorý používate (InnoDB alebo MyISAM).

Mojím najžiadanejším výsledkom pri písaní tohto článku je odstránenie troch vecí:

  1. MySQL je typický softvér, ktorý pracuje v rámci limitov stanovených operačným systémom. Nie je to nejaký záhadný program, ktorý Boh vie, čo je nemožné skrotiť. Našťastie nie je také ťažké pochopiť, ako je nastavená a ako ju ovládajú jej systémové premenné.
  2.  Neexistuje žiadne jediné nastavenie, ktoré spôsobí, že vaša inštalácia MySQL bude zväčšená. Nemáte na výber, len sa pozrieť do svojich bežiacich systémov (nezabudnite, optimalizácia prichádza po tom, čo je aplikácia vo výrobe, nie skôr), urobte najlepšie odhady a merania a žite s realitou, že to nikdy nebude dokonalé..
  3. Vyladenie premenných nie je jediný spôsob, ako optimalizovať MySQL – efektívne písanie dopytov je ďalšia veľká vec, ale v inom článku sa budem venovať týmto otázkam. Ide o to, že aj keď ste vykonali analýzu podobnú Bohu a tieto parametre ste naladili čo najlepšie, stále je možné, aby ste všetko zastavili.

Aká je vaša obľúbená systémová premenná na ladenie? ��

Tagy:

  • databázy

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map