Stejně jako u všech relačních databází se i MySQL může ukázat jako komplikovaná bestie, která se dokáže okamžitě plazit a zastavit, takže vaše aplikace zůstanou v klidu a vaše podnikání na lince.
Pravdou je, že běžné chyby jsou základem většiny problémů s výkonem MySQL. Chcete-li zajistit, aby váš server MySQL hučel při nejvyšší rychlosti a poskytoval stabilní a konzistentní výkon, je důležité eliminovat tyto chyby, které často zakrývá nějaká jemnost ve vašem pracovním zatížení nebo konfigurační past.
Naštěstí se ukázalo, že mnoho problémů s výkonem MySQL má podobná řešení, takže řešení problémů a vyladění MySQL je zvládnutelný úkol.
Tady je 10 tipů, jak získat skvělý výkon z MySQL.
Tip na výkon MySQL č. 1: Vytvořte si profilovou zátěž
Nejlepší způsob, jak pochopit, jak váš server tráví čas, je profilovat jeho pracovní zátěž. Díky profilování své pracovní zátěže můžete vystavit nejdražší dotazy pro další ladění. Zde je čas nejdůležitější metrikou, protože když zadáte dotaz proti serveru, nezáleží vám na ničem jiném než na tom, jak rychle se to dokončí.
Nejlepší způsob, jak profilovat své pracovní vytížení, je nástroj, jako je analyzátor dotazů MySQL Enterprise Monitor nebo pt-query-digest z Percona Toolkit. Tyto nástroje zachycují dotazy, které server spouští, a vracejí tabulku úkolů seřazených podle sestupného pořadí doby odezvy, přičemž okamžitě probublávají nejdražší a časově náročné úkoly, abyste viděli, kam zaměřit své úsilí.
Nástroje pro profilování pracovního vytížení seskupují podobné dotazy dohromady, což vám umožňuje zobrazit pomalé dotazy i rychlé, ale mnohokrát provedené dotazy.
Tip na výkon MySQL č. 2: Pochopte čtyři základní zdroje
Aby mohl databázový server fungovat, potřebuje čtyři základní zdroje: CPU, paměť, disk a síť. Pokud je některý z nich slabý, nevyzpytatelný nebo přetížený, je pravděpodobné, že databázový server bude fungovat špatně.
Pochopení základních zdrojů je důležité ve dvou konkrétních oblastech: výběr hardwaru a řešení problémů.
Při výběru hardwaru pro MySQL zajistěte všudypřítomné komponenty. Stejně důležité je vyvážit je navzájem přiměřeně dobře. Organizace často vybírají servery s rychlými procesory a disky, které však nemají dostatek paměti. V některých případech je přidání paměti levným způsobem, jak zvýšit výkon řádově, zejména u pracovních zátěží vázaných na disk. To se může zdát neintuitivní, ale v mnoha případech jsou disky nadměrně využívány, protože není dostatek paměti k uložení pracovní sady dat serveru.
Další dobrý příklad této rovnováhy se týká procesorů. Ve většině případů bude MySQL fungovat dobře s rychlými CPU, protože každý dotaz běží v jediném vlákně a nelze jej paralelizovat mezi CPU.
Pokud jde o řešení potíží, zkontrolujte výkon a využití všech čtyř zdrojů a pečlivě sledujte, zda mají špatný výkon nebo zda od nich vyžaduje příliš mnoho práce. Tyto znalosti mohou pomoci rychle vyřešit problémy.
Tip na výkon MySQL č. 3: Nepoužívejte MySQL jako frontu
Fronty a přístupové vzory podobné frontám se mohou vplížit do vaší aplikace, aniž byste o tom věděli. Pokud například nastavíte stav položky tak, aby si ji konkrétní pracovní proces mohl nárokovat, než s ní bude jednat, vytvoříte nevědomky frontu. Běžným příkladem je označování e-mailů jako neodeslaných, jejich odesílání a následné označování jako odeslaných.
Fronty způsobují problémy ze dvou hlavních důvodů: Serializují vaši pracovní zátěž, zabraňují paralelnímu provádění úkolů a často vedou k tabulce, která obsahuje rozpracovanou práci a také historická data z úloh, které byly zpracovány již dávno. Oba přidávají latenci do aplikace a načítají se do MySQL.
Tip na výkon MySQL č. 4: Nejprve filtrujte výsledky podle nejlevnějších
Skvělým způsobem, jak optimalizovat MySQL, je dělat nejprve levnou a nepřesnou práci, pak tvrdou a přesnou práci na menší výsledné sadě dat.
Předpokládejme například, že hledáte něco v daném okruhu geografického bodu. Prvním nástrojem v sadě nástrojů mnoha programátorů je vzorec velkého kruhu (Haversine) pro výpočet vzdálenosti podél povrchu koule. Problém této techniky spočívá v tom, že vzorec vyžaduje mnoho trigonometrických operací, které jsou velmi náročné na CPU. Výpočty ve velkém kruhu mají tendenci běžet pomalu a využití procesoru stroje rychle stoupá.
Než použijete vzorec velkého kruhu, připravte své záznamy na malou podmnožinu z celkového počtu a ořízněte výslednou sadu na přesný kruh. Snadný způsob, jak to udělat, je čtverec, který obsahuje kruh (přesně nebo nepřesně). Tímto způsobem svět mimo náměstí nikdy nezasáhne všechny tyto nákladné spouštěcí funkce.
MySQL performance tip No. 5: Know the two scalability death past
Škálovatelnost není tak vágní, jak si možná myslíte. Ve skutečnosti existují přesné matematické definice škálovatelnosti, které jsou vyjádřeny jako rovnice. Tyto rovnice zdůrazňují, proč se systémy nemění tak dobře, jak by měly.
Vezměte si zákon o univerzální škálovatelnosti, definici, která je užitečná při vyjádření a kvantifikaci charakteristik škálovatelnosti systému. Vysvětluje problémy se škálováním z hlediska dvou základních nákladů: serializace a přeslech.
Paralelní procesy, které se musí zastavit, aby se něco serializovalo, jsou ve své škálovatelnosti inherentně omezené. Podobně, pokud paralelní procesy potřebují neustále navzájem chatovat, aby koordinovaly svou práci, navzájem se omezují.
Vyhněte se serializaci a přeslechům a vaše aplikace se bude škálovat mnohem lépe. Co to znamená v MySQL? Liší se, ale některými příklady by bylo vyhnout se výlučným zámkům na řádcích. Fronty, bod č. 3 výše, mají z tohoto důvodu tendenci se špatně škálovat.
Tip na výkon MySQL č. 6: Nesoustřeďte se příliš na konfiguraci
DBA mají tendenci trávit obrovské množství času laděním konfigurací. Výsledek obvykle není velkým vylepšením a někdy může být dokonce velmi škodlivý. Viděl jsem spoustu „optimalizovaných“ serverů, které neustále havarovaly, došla jim paměť a špatně fungovaly, když se pracovní zátěž trochu zvýšila.
Výchozí nastavení dodávaná s MySQL jsou univerzální a špatně zastaralá, ale nemusíte konfigurovat vše. Je lepší dát základy do pořádku a změnit další nastavení, pouze pokud je to potřeba. Ve většině případů můžete získat 95 procent špičkového výkonu serveru správným nastavením asi 10 možností. Těch několik situací, kdy to neplatí, bude výjimečných případů, které jsou specifické pro vaše okolnosti.
Ve většině případů se nástroje pro ladění serverů nedoporučují, protože mají tendenci poskytovat pokyny, které pro konkrétní případy nedávají smysl. Někteří mají dokonce zakódované nebezpečné a nepřesné rady - například poměry zásahů do mezipaměti a vzorce spotřeby paměti. Nikdy neměli pravdu a postupem času se staly ještě méně správnými.
Tip na výkon MySQL č. 7: Dávejte pozor na dotazy na stránkování
Aplikace, které stránkují, mají sklon server položit na kolena. Při zobrazení stránky s výsledky a odkazem na další stránku se tyto aplikace obvykle seskupí a seřadí způsoby, které nemohou používat indexy, a používají OMEZIT a offset které způsobí, že server vygeneruje spoustu práce a poté zahodí řádky.
Optimalizace se často nacházejí v samotném uživatelském rozhraní. Místo toho, abyste ve výsledcích zobrazovali přesný počet stránek a odkazy na každou stránku zvlášť, můžete zobrazit pouze odkaz na další stránku. Můžete také zabránit lidem, aby šli na stránky příliš daleko od první stránky.
Na straně dotazu místo použití OMEZIT s offset, můžete vybrat ještě jeden řádek, než potřebujete, a když uživatel klikne na odkaz „další stránka“, můžete tento poslední řádek označit jako výchozí bod pro další sadu výsledků. Například pokud si uživatel prohlížel stránku s řádky 101 až 120, vybrali byste také řádek 121; k vykreslení další stránky byste na serveru zadali dotaz na řádky větší nebo rovné 121, limit 21.
Tip na výkon MySQL č. 8: Ukládejte statistiky dychtivě, varujte neochotně
Monitorování a výstrahy jsou nezbytné, ale co se stane s typickým monitorovacím systémem? Začíná odesílat falešné poplachy a správci systému nastavují pravidla filtrování e-mailů, aby hluk zastavili. Váš monitorovací systém je brzy zcela k ničemu.
Rád přemýšlím o monitorování dvěma způsoby: zachycením metrik a upozorněním. Je velmi důležité zachytit a uložit všechny metriky, které můžete, protože budete rádi, že je máte, když se snažíte zjistit, co se v systému změnilo. Jednoho dne se objeví podivný problém a budete rádi, když budete moci ukázat na graf a ukázat změnu v pracovní zátěži serveru.
Naproti tomu existuje tendence příliš upozorňovat. Lidé často upozorňují na věci, jako je poměr zásahů do vyrovnávací paměti nebo počet dočasných tabulek vytvořených za sekundu. Problém je v tom, že pro takový poměr neexistuje dobrá prahová hodnota. Správná prahová hodnota se liší nejen od serveru k serveru, ale od hodiny k hodině, jak se mění vaše pracovní vytížení.
Ve výsledku upozorňujte střídmě a pouze na podmínky, které naznačují jednoznačný a řešitelný problém. Nízký poměr zásahů do vyrovnávací paměti není použitelný, ani to neznamená skutečný problém, ale server, který nereaguje na pokus o připojení, je skutečným problémem, který je třeba vyřešit.
Tip na výkon MySQL č. 9: Naučte se tři pravidla indexování
Indexování je pravděpodobně nejvíce nepochopené téma v databázích, protože existuje tolik způsobů, jak se zmást, jak fungují indexy a jak je server používá. Skutečné pochopení toho, co se děje, vyžaduje hodně úsilí.
Pokud jsou rejstříky správně navrženy, slouží na databázovém serveru třem důležitým účelům:
- Indexy umožňují serveru najít skupiny sousedních řádků namísto jednotlivých řádků. Mnoho lidí si myslí, že účelem indexu je najít jednotlivé řádky, ale nalezení jednotlivých řádků vede k náhodným operacím s diskem, což je pomalé. Je mnohem lepší najít skupiny řádků, které jsou zajímavé všechny nebo většina z nich, než najít jednotlivé řádky.
- Indexy umožňují serveru vyhnout se řazení čtením řádků v požadovaném pořadí. Třídění je nákladné. Čtení řádků v požadovaném pořadí je mnohem rychlejší.
- Rejstříky umožňují serveru uspokojit celé dotazy pouze z indexu, takže není nutné vůbec přistupovat k tabulce. Toto je různě známé jako krycí index nebo dotaz pouze na index.
Pokud můžete navrhnout své indexy a dotazy tak, aby tyto tři příležitosti využily, můžete své dotazy zrychlit o několik řádů.
Tip na výkon MySQL č. 10: Využijte odborné znalosti svých kolegů
Nepokoušejte se jít sám. Pokud si lámete hlavu nad problémem a děláte to, co se vám zdá logické a rozumné, je to skvělé. To bude fungovat asi 19krát z 20. Jindy se dostanete dolů do králičí nory, která bude velmi nákladná a časově náročná, právě proto, že řešení, které zkoušíte, má zřejmě velký smysl.
Vytvořte síť zdrojů souvisejících s MySQL - a to jde nad rámec sad nástrojů a příruček pro řešení potíží. Na seznamy adresátů, fóra, weby s otázkami a odpověďmi atd. Číhají někteří extrémně znalí lidé. Konference, veletrhy a události místních skupin uživatelů poskytují cenné příležitosti pro získání přehledů a budování vztahů s kolegy, kteří vám mohou okamžitě pomoci.
Pro ty, kteří hledají nástroje k doplnění těchto tipů, můžete vyzkoušet Průvodce konfigurací Percona pro MySQL, Percona Query Advisor pro MySQL a Percona Monitoring Plugins. (Poznámka: Pro přístup k těmto prvním dvěma odkazům budete muset vytvořit účet Percona. Je to zdarma.) Průvodce konfigurací vám pomůže vygenerovat základní soubor my.cnf pro nový server, který je lepší než ukázkové soubory dodávané s serveru. Poradce dotazů bude analyzovat váš SQL, aby pomohl detekovat potenciálně špatné vzory, jako jsou stránkovací dotazy (č. 7). Monitorovací pluginy Percona jsou sada monitorovacích a grafických pluginů, které vám pomohou dychtivě ukládat statistiky a neochotně upozorňovat (č. 8). Všechny tyto nástroje jsou volně dostupné.
