Programování

7 klíčů pro lepší výkon MySQL

Peter Zaitsev je spoluzakladatel a generální ředitel společnostiPercona.

Jedním ze způsobů, jak měříme aplikace, je výkon. Jednou z metrik výkonu aplikace je uživatelská zkušenost, která se obecně promítá do „musel uživatel čekat déle, než je rozumné množství času, aby získal to, co chtěl.“

Tato metrika může v různých scénářích znamenat různé věci. U aplikace pro mobilní nakupování nesmí být doba odezvy delší než pár sekund. U HR stránky zaměstnance může trvat několik sekund.

Máme spoustu výzkumů o tom, jak výkon ovlivňuje chování uživatelů:

  • U 79 procent zákazníků je méně pravděpodobné, že se vrátí na pomalý web
  • 47 procent spotřebitelů očekává, že se webová stránka načte za 2 sekundy nebo méně
  • 40 procent uživatelů opustí web, pokud jeho načítání trvá déle než tři sekundy
  • Jednosekundové zpoždění v době načítání stránky může způsobit 7% ztrátu při převodu a o 11% méně zobrazení stránky

Ať je standard jakýkoli, je nezbytné udržovat dobrý výkon aplikací. Jinak si uživatelé stěžují (nebo v horším případě přejdou do jiné aplikace). Jedním z faktorů, který ovlivňuje výkon aplikace, je výkon databáze. Interakce mezi aplikacemi, webovými stránkami a databázemi je zásadní pro stanovení úrovně výkonu aplikace.

Ústřední součástí této interakce je to, jak aplikace dotazují databázi a jak databáze reaguje na požadavky. V každém případě je MySQL jedním z nejpopulárnějších systémů pro správu databází. Další podniky přecházejí na MySQL (a další databáze s otevřeným zdrojovým kódem) jako databázové řešení ve svých produkčních prostředích.

Existuje mnoho metod konfigurace MySQL, které vám pomohou zajistit, aby vaše databáze reagovala na dotazy rychle as minimálním snížením výkonu aplikace.

Následuje několik základních tipů, které vám pomohou optimalizovat výkon databáze MySQL.

Klíč optimalizace MySQL č. 1: Naučte se používat VYSVĚTLIT

Dvě nejdůležitější rozhodnutí, která učiníte s libovolnou databází, jsou návrh způsobu mapování vztahů mezi aplikačními entitami na tabulky (schéma databáze) a návrh, jak aplikace získají potřebná data ve formátu, který potřebují (dotazy).

Komplikované aplikace mohou mít komplikovaná schémata a dotazy. Pokud se chystáte dosáhnout výkonu a rozsahu, který vaše aplikace vyžadují, nemůžete spoléhat jen na intuici, abyste pochopili, jak budou dotazy prováděny.

Místo hádání a naděje byste se měli naučit používat VYSVĚTLIT příkaz. Tento příkaz ukazuje, jak bude dotaz proveden, a poskytne vám přehled o tom, jaký výkon můžete očekávat, a o tom, jak se bude měřítko dotazu měnit při změně velikosti dat.

Existuje řada nástrojů - například MySQL Workbench -, které umožňují vizualizaci VYSVĚTLIT výstup pro vás, ale stále musíte rozumět základům, abyste to pochopili.

Existují dva různé formáty, ve kterých VYSVĚTLIT příkaz poskytuje výstup: staromódní formát tabulky a modernější strukturovaný dokument JSON, který poskytuje podstatně více podrobností (viz níže):

mysql> vysvětlit formát = json vybrat avg (k) ze sbtest1 kde id mezi 1000 a 2000 \ G

**************************** 1. řádek ******************** *******

VYSVĚTLENÍ: {

„Query_block“: {

„Select_id“: 1,

„Cost_info“: {

   „Query_cost“: „762,40“

"stůl": {

“Table_name”: “sbtest1”,

“Access_type”: “range”,

„Possible_keys“: [

"HLAVNÍ"

      ],

„Key“: „PRIMARY“,

“Used_key_parts”: [

„Id“

      ],

“Key_length”: “4”,

„Lines_examined_per_scan“: 1874,

„Lines_produced_per_join“: 1874,

“Filtrováno”: “100,00”,

„Cost_info“: {

„Read_cost“: „387,60“,

„Eval_cost“: „374,80“,

„Prefix_cost“: „762,40“,

“Data_read_per_join”: “351K”

      },

“Used_columns”: [

„Id“,

„K“

      ],

„Attached_condition“: „(` sbtest`.`sbtest1`.`id` mezi 1000 a 2000) “

    }

  }

}

Jedna součást, na kterou byste se měli podívat, je „cena za dotaz“. Náklady na dotaz označují, jak drahý MySQL považuje tento konkrétní dotaz z hlediska celkových nákladů na provedení dotazu a je založen na mnoha různých faktorech.

Jednoduché dotazy obvykle mají náklady na dotaz nižší než 1 000. Dotazy s cenou mezi 1 000 a 100 000 jsou považovány za dotazy se středními náklady a jsou obecně rychlé, pokud zpracováváte pouze stovky takových dotazů za sekundu (ne desítky tisíc).

Dotazy s cenou vyšší než 100 000 jsou drahé dotazy. Tyto dotazy budou často běžet rychle, když jste v systému jediným uživatelem, ale měli byste pečlivě zvážit, jak často takové dotazy ve svých interaktivních aplikacích používáte (zejména s rostoucím počtem uživatelů).

Samozřejmě se jedná o výkonnostní čísla, ale ukazují obecný princip. Váš systém může zpracovávat vytížení dotazů lépe nebo horší, v závislosti na jeho architektuře a konfiguraci.

Mezi faktory, které určují cenu dotazu, patří zejména to, zda dotaz správně používá indexy. The VYSVĚTLIT příkaz vám řekne, jestli dotaz nepoužívá indexy (obvykle kvůli tomu, jak jsou indexy vytvořeny v databázi nebo jak je samotný dotaz vytvořen). Proto je tak důležité naučit se používat VYSVĚTLIT.

Klíč optimalizace MySQL č. 2: Vytvořte správné indexy

Index zlepšuje výkon dotazu snížením množství dat v databázi, které musí dotazy skenovat. Indexy v MySQL se používají k urychlení přístupu v databázi a pomáhají prosazovat databázová omezení (například UNIKÁTNÍ a CIZÍ KLÍČ).

Indexy databáze jsou podobné indexům knih. Jsou uchovávány na svém vlastním místě a obsahují informace již v hlavní databázi. Jedná se o referenční metodu nebo mapu, kde se nacházejí data. Rejstříky nemění žádná data v databázi. Jednoduše ukazují na umístění dat.

Neexistují žádné indexy, které jsou vždy správné pro jakékoli pracovní vytížení. Vždy byste se měli podívat na indexy v kontextu dotazů, které systém spouští.

Dobře indexované databáze nejen běží rychleji, ale i jediný chybějící index může zpomalit procházení databáze. Použití VYSVĚTLIT (jak bylo doporučeno dříve) najít chybějící indexy a přidat je. Ale pozor: Nepřidávejte indexy, které nepotřebujete! Zbytečné indexy zpomalují snižování databází (podívejte se na moji prezentaci o doporučených postupech indexování MySQL).

Klíč optimalizace MySQL č. 3: Žádné výchozí hodnoty!

Jako každý software má MySQL mnoho konfigurovatelných nastavení, která lze použít k úpravě chování (a nakonec i výkonu). A jako každý software, mnoho z těchto konfigurovatelných nastavení je správci ignorováno a nakonec je použito ve výchozím režimu.

Chcete-li získat nejlepší výkon z MySQL, je důležité porozumět konfigurovatelnému nastavení MySQL a - což je důležitější - nastavit je tak, aby fungovalo co nejlépe pro vaše databázové prostředí.

Ve výchozím nastavení je MySQL vyladěn pro malou vývojovou instalaci, nikoli pro produkční měřítko. Obvykle chcete nakonfigurovat MySQL tak, aby používalo všechny dostupné paměťové prostředky a aby umožňovalo počet připojení, která vaše aplikace vyžaduje.

Tady jsou tři nastavení ladění výkonu MySQL, která byste měli vždy pečlivě prozkoumat:

innodb_buffer_pool_size: Fond vyrovnávacích pamětí je místo, kde jsou data a indexy ukládány do mezipaměti. To je hlavní důvod pro použití systému s velkým množstvím RAM jako databázového serveru. Pokud spouštíte pouze úložný modul InnoDB, obvykle přidělujete přibližně 80 procent své paměti pro fond vyrovnávacích pamětí. Pokud spouštíte velmi komplikované dotazy nebo máte velmi velký počet souběžných připojení k databázi nebo máte velmi velký počet tabulek, možná budete muset tuto hodnotu snížit o úroveň níže, abyste přidělili více paměti pro jiné účely.

Když nastavujete velikost fondu vyrovnávacích pamětí InnoDB, musíte se ujistit, že jej nenastavíte příliš velký, jinak by došlo k výměně. To absolutně zabije výkon vaší databáze. Snadný způsob, jak to zkontrolovat, je podívat se na Swapping Activity v grafu Přehled systému v Percona Monitoring and Management:

Percona

Jak ukazuje tento graf, některé výměny jsou v pořádku tak často. Pokud však uvidíte trvalou aktivitu výměny 1 MB za sekundu nebo více, budete muset zmenšit velikost fondu vyrovnávací paměti (nebo jiné využití paměti).

Pokud nedostanete hodnotu pro innodb_buffer_pool_size správně na první cestu, nebojte se. Počínaje MySQL 5.7 můžete dynamicky měnit velikost fondu vyrovnávacích pamětí InnoDB, aniž byste restartovali databázový server.

innodb_log_file_size: Toto je velikost jednoho souboru protokolu InnoDB. Ve výchozím nastavení používá InnoDB dvě hodnoty, takže toto číslo můžete zdvojnásobit, abyste získali velikost kruhového prostoru pro opakované protokolování, který InnoDB používá, aby se ujistil, že vaše transakce jsou trvalé. To také optimalizuje použití změn v databázi. Nastavení innodb_log_file_size je otázkou kompromisů. Čím větší redo prostor přidělíte, tím lepší výkon dosáhnete pro pracovní zátěž náročnou na zápis, ale čím delší je doba zotavení z havárie, pokud váš systém utrpí ztrátu energie nebo jiné problémy.

Jak víte, že je váš výkon MySQL omezen vaší aktuální velikostí souboru protokolu InnoDB? Zjistíte to tak, že se podíváte na to, kolik použitelného prostoru pro opakované protokoly je skutečně využito. Nejjednodušší způsob je podívat se na řídicí panel IncoDB Metrics Percona Monitoring and Management. V níže uvedeném grafu není velikost souboru protokolu InnoDB dostatečně velká, protože použitý prostor tlačí velmi blízko k tomu, kolik použitelného prostoru pro opakované protokoly je k dispozici (označeno červenou čárou). Velikost souboru protokolu by měla být alespoň o 20 procent větší než velikost místa použitého k zajištění optimálního výkonu systému.

Percona

max_connections: Rozsáhlé aplikace často vyžadují mnohem více, než je výchozí počet připojení. Na rozdíl od jiných proměnných, pokud toto nenastavíte správně, nebudete mít problémy s výkonem (samy o sobě). Místo toho, pokud počet připojení není dostatečný pro potřeby vaší aplikace, vaše aplikace se jednoduše nebude moci připojit k databázi (což vašim uživatelům vypadá jako prostoj). Správné nastavení této proměnné je důležité.

Může být obtížné zjistit, kolik připojení potřebujete pro složité aplikace s mnoha komponentami spuštěnými na více serverech. Naštěstí MySQL velmi snadno zjistí, kolik připojení se používá ve špičkovém provozu. Obvykle chcete zajistit, aby mezi maximálním počtem připojení, které vaše aplikace používá, a maximálním počtem dostupných připojení, byla alespoň 30% mezera. Snadný způsob, jak zobrazit tato čísla, je použití grafu připojení MySQL na řídicím panelu Přehled MySQL v Monitorování a správě Percona. Níže uvedený graf ukazuje zdravý systém, kde je k dispozici velký počet dalších připojení.

Percona

Mějte na paměti jednu věc, že ​​pokud vaše databáze běží pomalu, aplikace často vytvářejí nadměrný počet připojení. V takových případech byste měli pracovat spíše na problému s výkonem databáze, než jednoduše povolit více připojení. Více připojení může zhoršit základní problém s výkonem.

(Poznámka: Když nastavíte max_connections Pokud je proměnná výrazně vyšší než výchozí hodnota, musíte často zvážit zvýšení dalších parametrů, jako je velikost mezipaměti tabulky a počet otevřených souborů, které MySQL umožňuje. To však přesahuje rámec tohoto článku.) 

Klíč optimalizace MySQL č. 4: Uchovávejte databázi v paměti

V posledních letech jsme zaznamenali přechod na jednotky SSD (SSD). I když jsou disky SSD mnohem rychlejší než otáčení pevných disků, stále se neshodují s dostupností dat v paměti RAM. Tento rozdíl pochází nejen ze samotného výkonu úložiště, ale také z další práce, kterou musí databáze udělat, když načte data z disku nebo úložiště SSD.

Díky nedávným vylepšením hardwaru je stále více možné dostat vaši databázi do paměti - ať už běžíte v cloudu nebo spravujete svůj vlastní hardware.

Ještě lepší zprávou je, že nemusíte využívat všechny své databáze do paměti, abyste získali většinu výhod výkonu v paměti. Musíte pouze vložit pracovní sadu dat do paměti - data, která jsou přístupná nejčastěji.

Možná jste viděli některé články poskytující určitá konkrétní čísla o tom, jakou část databáze byste měli uchovávat v paměti, a to v rozmezí od 10 procent do 33 procent. Ve skutečnosti neexistuje žádné číslo „jedna velikost pro všechny“. Množství dat, které se vejde do paměti pro dosažení nejlepší výhody výkonu, souvisí s pracovním vytížením. Místo hledání konkrétního „magického“ čísla byste měli zkontrolovat, kolik I / O databáze běží v ustáleném stavu (obvykle několik hodin po spuštění). Podívejte se na čtení, protože čtení lze zcela vyloučit, pokud je vaše databáze v paměti. K zápisům bude vždy třeba dojít bez ohledu na to, kolik paměti máte k dispozici.

Níže vidíte I / O, které se dějí v grafu I / O InnoDB na řídicím panelu InnoDB Metrics v Percona Monitoring and Management.

Percona

V grafu výše vidíte špičky vysoké až 2 000 I / O operací za sekundu, což ukazuje, že (alespoň pro některé části pracovního vytížení) pracovní sada databáze dobře nezapadá do paměti.

Klíč pro optimalizaci MySQL č. 5: Použijte úložiště SSD

Pokud se vaše databáze nevejde do paměti (ai když ano), stále potřebujete rychlé úložiště pro zpracování zápisů a vyhnutí se problémům s výkonem při zahřívání databáze (hned po restartu). V dnešní době rychlé úložiště znamená SSD.

$config[zx-auto] not found$config[zx-overlay] not found