monitorování metrik výkonu MySQL

tento příspěvek je součástí 1 3dílné série o monitorování MySQL. Část 2 je o shromažďování metrik z MySQL a část 3 vysvětluje, jak monitorovat MySQL pomocí Datadogu.

co je MySQL?

MySQL je nejpopulárnější open source relační databázový server na světě. Vlastněný Oracle, MySQL je k dispozici ve volně ke stažení Community Edition, stejně jako v komerčních vydáních s přidanými funkcemi a podporou. MySQL, původně vydané v roce 1995, od té doby vytvořilo vysoce postavené vidlice pro konkurenční technologie, jako jsou MariaDB a Percona.

klíčové statistiky MySQL

pokud vaše databáze běží pomalu nebo z jakéhokoli důvodu nevyhovuje dotazům, každá část vašeho zásobníku, která závisí na této databázi, bude mít také problémy s výkonem. Aby vaše databáze fungovala hladce, můžete aktivně sledovat metriky pokrývající čtyři oblasti výkonu a využití zdrojů:

• propustnost dotazu
• výkon provádění dotazu
• připojení
• využití vyrovnávací paměti

Uživatelé MySQL mají přístup ke stovkám metrik z databáze, takže v tomto článku se zaměříme na několik klíčových metrik, které vám umožní získat v reálném čase přehled o zdraví a výkonu vaší databáze. Ve druhé části této série vám ukážeme, jak přistupovat a shromažďovat všechny tyto metriky.

tento článek odkazuje na metrickou terminologii zavedenou v naší sérii Monitoring 101, která poskytuje rámec pro sběr a upozornění metrik.

kompatibilita mezi verzemi a technologiemi

některé monitorovací strategie diskutované v této sérii jsou specifické pro MySQL verze 5.6 a 5.7. Rozdíly mezi těmito verzemi budou zdůrazněny na cestě.

většina zde popsaných metrik a monitorovacích strategií se vztahuje také na technologie kompatibilní s MySQL, jako jsou MariaDB a Percona Server, s některými významnými rozdíly. Například některé funkce v pracovní ploše MySQL, která je podrobně popsána v části 2 této série, nejsou kompatibilní s aktuálně dostupnými verzemi MariaDB.

Uživatelé Amazon RDS by se měli podívat na naše specializované monitorovací příručky pro MySQL na RDS a pro Amazon Aurora kompatibilní s MySQL.

propustnost dotazu

vaším hlavním zájmem při sledování jakéhokoli systému je zajistit, aby jeho práce byla prováděna efektivně. Práce databáze běží dotazy, takže vaše první priorita monitorování by měla být ujistit se, že MySQL provádí dotazy podle očekávání.

MySQL má interní čítač („proměnná stavu serveru“, v jazyce MySQL) s názvem Questions, který je zvýšen pro všechny příkazy odeslané klientskými aplikacemi. Zobrazení zaměřené na klienta poskytované metrikou Questions často usnadňuje interpretaci než související čítač Queries, který také počítá příkazy provedené jako součást uložených programů, stejně jako příkazy jako PREPARE a DEALLOCATE PREPARE spuštěné jako součást připravených příkazů na straně serveru.

pro dotaz na proměnnou stavu serveru, například Questions nebo Com_select:

SHOW GLOBAL STATUS LIKE "Questions";+---------------+--------+| Variable_name | Value |+---------------+--------+| Questions | 254408 |+---------------+--------+

můžete také sledovat rozpis příkazů pro čtení a zápis, abyste lépe porozuměli pracovní zátěži vaší databáze a identifikovali potenciální úzká místa. Přečtené dotazy jsou obecně zachyceny metrikou Com_select. Zapíše přírůstek jedné ze tří stavových proměnných, v závislosti na příkazu:

zapíše = Com_insert + Com_update + Com_delete

metrika, na kterou je třeba upozornit: otázky

aktuální míra dotazů bude přirozeně stoupat a klesat, a jako taková to není vždy použitelná metrika založená na pevných prahových hodnotách. Ale stojí za to upozornit na náhlé změny objemu dotazu-zejména drastické poklesy propustnosti mohou znamenat vážný problém.

výkon dotazu

Uživatelé MySQL mají řadu možností pro sledování latence dotazů, a to jak využitím vestavěných metrik MySQL, tak dotazováním na schéma výkonu. Ve výchozím nastavení povoleno od MySQL 5.6.6, tabulky databáze performance_schema v MySQL ukládají statistiky nízké úrovně o událostech serveru a provádění dotazů.

performance schema statement digest

mnoho klíčových metrik je obsaženo v tabulce performance schema events_statements_summary_by_digest, která zachycuje informace o latenci, chybách a objemu dotazu pro každý normalizovaný příkaz. Ukázkový řádek z tabulky ukazuje příkaz, který byl spuštěn dvakrát a který v průměru trval 325 milisekund (všechna měření časovače jsou v pikosekundách):

*************************** 1. row *************************** SCHEMA_NAME: employees DIGEST: 0c6318da9de53353a3a1bacea70b4fce DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? COUNT_STAR: 2 SUM_TIMER_WAIT: 650358383000 MIN_TIMER_WAIT: 292045159000 AVG_TIMER_WAIT: 325179191000 MAX_TIMER_WAIT: 358313224000 SUM_LOCK_TIME: 520000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 520048 SUM_ROWS_EXAMINED: 520048... SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-03-24 14:25:32 LAST_SEEN: 2016-03-24 14:25:55

tabulka digest normalizuje všechny příkazy (jak je vidět v poli DIGEST_TEXT výše), ignoruje hodnoty dat a standardizuje mezery a velká písmena, takže následující dva dotazy budou považovány za stejné:

select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;

Chcete-li extrahovat průměrnou dobu běhu podle schématu v mikrosekundách, můžete se zeptat na schéma výkonu:

SELECT schema_name , SUM(count_star) count , ROUND( (SUM(sum_timer_wait) / SUM(count_star)) / 1000000) AS avg_microsec FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-------+--------------+| schema_name | count | avg_microsec |+--------------------+-------+--------------+| employees | 223 | 171940 || performance_schema | 37 | 20761 || sys | 4 | 748 |+--------------------+-------+--------------+

podobně, spočítat celkový počet příkazů na schéma, které generovalo chyby:

SELECT schema_name , SUM(sum_errors) err_count FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-----------+| schema_name | err_count |+--------------------+-----------+| employees | 8 || performance_schema | 1 || sys | 3 |+--------------------+-----------+

schéma sys

dotazování na schéma výkonu, jak je uvedeno výše, funguje skvěle pro programové načítání metrik z databáze. Pro ad hoc dotazy a vyšetřování je však obvykle snazší použít schéma MySQL sys. Schéma sys poskytuje organizovanou sadu metrik v čitelnějším formátu, což usnadňuje odpovídající dotazy. Například najít nejpomalejší příkazy (ty v 95. percentilu podle runtime):

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

nebo zjistit, které normalizované příkazy generovaly chyby:

SELECT * FROM sys.statements_with_errors_or_warnings;

mnoho dalších užitečných příkladů je podrobně popsáno v dokumentaci schématu sys. Schéma sys je součástí MySQL počínaje verzí 5.7.7, ale uživatelé MySQL 5.6 jej mohou nainstalovat pomocí několika příkazů. Pokyny viz část 2 této série.

pomalé dotazy

kromě množství údajů o výkonu dostupných ve schématu výkonu a schématu sys má MySQL čítač Slow_queries, který se zvyšuje pokaždé, když doba provedení dotazu překročí počet sekund specifikovaný parametrem long_query_time. Prahová hodnota je ve výchozím nastavení nastavena na 10 sekund:

SHOW VARIABLES LIKE 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

parametr long_query_time lze nastavit jedním příkazem. Chcete-li například nastavit prahovou hodnotu pomalého dotazu na pět sekund:

SET GLOBAL long_query_time = 5;

(Všimněte si, že možná budete muset ukončit relaci a znovu se připojit k databázi, aby se změna použila na úrovni relace.)

vyšetřování problémů s výkonem dotazu

pokud se vaše dotazy provádějí pomaleji, než se očekávalo, často je viníkem nedávno změněný dotaz. Pokud není žádný dotaz považován za nepřiměřeně pomalý, další věcí, kterou je třeba vyhodnotit, jsou metriky na úrovni systému, které hledají omezení v základních zdrojích (CPU, disk I/o, paměť a síť). Nasycení CPU a I / O úzká místa jsou běžnými viníky. Můžete také zkontrolovat metriku Innodb_row_lock_waits, která počítá, jak často musel InnoDB storage engine čekat na získání zámku v určitém řádku. InnoDB je výchozím úložným motorem od MySQL verze 5.5 a MySQL používá blokování na úrovni řádků pro tabulky InnoDB.

Chcete-li zvýšit rychlost operací čtení a zápisu, mnoho uživatelů bude chtít naladit velikost fondu vyrovnávací paměti používaného InnoDB do mezipaměti tabulky a indexových dat. Více o monitorování a změně velikosti fondu vyrovnávací paměti níže.

metriky pro upozornění na

• doba běhu dotazu: Správa latence pro klíčové databáze je kritická. Pokud průměrná doba běhu dotazů v produkční databázi začne stoupat, vyhledejte omezení zdrojů v instancích databáze, možné tvrzení o zámcích řádků nebo tabulek a změny ve vzorcích dotazů na straně klienta.
• chyby dotazu: Náhlý nárůst chyb dotazu může znamenat problém s klientskou aplikací nebo samotnou databází. Pomocí schématu sys můžete rychle zjistit, které dotazy mohou způsobovat problémy. Například seznam 10 normalizovaných příkazů, které vrátily nejvíce chyb:

    SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;  

• Slow_queries: jak definujete pomalý dotaz (a tedy jak nakonfigurujete parametr long_query_time) závisí na vašem případu použití. Ať už je vaše definice „pomalé“ jakákoli, pravděpodobně budete chtít prozkoumat, zda počet pomalých dotazů stoupá nad základní úroveň. Chcete-li identifikovat skutečné dotazy prováděné pomalu, můžete dotazovat schéma sys nebo se ponořit do volitelného protokolu pomalých dotazů MySQL, který je ve výchozím nastavení zakázán. Více informací o povolení a přístupu k pomalému protokolu dotazů je k dispozici v dokumentaci MySQL.

připojení

kontrola a nastavení limitu připojení

monitorování klientských připojení je zásadní, protože po vyčerpání dostupných připojení budou nová klientská připojení odmítnuta. Limit připojení MySQL je výchozí na 151, ale lze jej ověřit pomocí dotazu:

SHOW VARIABLES LIKE 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+

dokumentace MySQL naznačuje, že robustní servery by měly být schopny zpracovávat připojení ve vysokých stovkách nebo tisících:

“ Linux nebo Solaris by měly být schopny běžně podporovat 500 až 1000 současných připojení a až 10 000 připojení, pokud máte k dispozici mnoho gigabajtů paměti RAM a pracovní zátěž z každého z nich je nízká nebo cílová doba odezvy nenáročná. Systém Windows je omezen na (otevřené tabulky × 2 + otevřená připojení) < 2048 kvůli vrstvě kompatibility Posix použité na této platformě.“

mez připojení lze nastavit za běhu:

SET GLOBAL max_connections = 200;

toto nastavení se však po restartování serveru vrátí na výchozí hodnotu. Chcete-li trvale nastavit limit připojení, přidejte takový řádek do konfiguračního souboru my.cnf (viz tento příspěvek pro pomoc při hledání konfiguračního souboru):

max_connections = 200

Monitorování využití připojení

MySQL odhaluje Threads_connected metrické počítání připojovacích vláken-jedno vlákno na připojení. Sledováním této metriky vedle nakonfigurovaného limitu připojení můžete zajistit, že máte dostatečnou kapacitu pro zpracování nových připojení. MySQL také vystavuje metriku Threads_running, aby izoloval, které z těchto podprocesů aktivně zpracovávají dotazy v daném okamžiku, na rozdíl od otevřených připojení, ale v současné době jsou nečinná.

pokud váš server dosáhne limitu max_connections, začne odmítat připojení. V takovém případě bude metrika Connection_errors_max_connections zvýšena, stejně jako metrika Aborted_connects, která sleduje všechny neúspěšné pokusy o připojení.

MySQL vystavuje řadu dalších metrik o chybách připojení, které vám mohou pomoci vyšetřit problémy s připojením. Metrika Connection_errors_internal je dobré sledovat, protože se zvyšuje pouze tehdy, když chyba pochází ze samotného serveru. Interní chyby mohou odrážet stav mimo paměť nebo neschopnost serveru spustit nové vlákno.

metriky pro upozornění na

• Threads_connected: pokud se klient pokusí připojit k MySQL, když jsou používána všechna dostupná připojení, MySQL vrátí chybu „příliš mnoho připojení“ a přírůstek Connection_errors_max_connections. Chcete-li tomuto scénáři zabránit, měli byste sledovat počet otevřených připojení a ujistit se, že zůstává bezpečně pod nakonfigurovaným limitem max_connections.
• Aborted_connects: pokud se tento čítač zvyšuje, vaši klienti se snaží a nedaří se připojit k databázi. Prozkoumejte zdroj problému pomocí jemnozrnných metrik připojení, jako jsou Connection_errors_max_connections a Connection_errors_internal.

využití vyrovnávací paměti

výchozí modul úložiště MySQL, InnoDB, používá oblast paměti nazývanou fond vyrovnávací paměti k ukládání dat do mezipaměti tabulek a indexů. Metriky fondu vyrovnávací paměti jsou metriky zdrojů na rozdíl od metrik práce a jako takové jsou primárně užitečné pro vyšetřování (spíše než detekci) problémů s výkonem. Pokud výkon databáze začne klouzat, zatímco disk I / O roste, rozšíření fondu vyrovnávací paměti může často přinést výhody.

velikost fondu vyrovnávací paměti

fond vyrovnávací paměti je ve výchozím nastavení relativně malý 128 mebibajtů, ale MySQL doporučuje, abyste jej mohli zvýšit až na 80 procent fyzické paměti na vyhrazeném databázovém serveru. MySQL také přidává několik poznámek opatrnosti, nicméně, protože režie paměti InnoDB může zvýšit stopu paměti asi o 10 procent nad přidělenou velikost fondu vyrovnávací paměti. A pokud vám dojde fyzická paměť, váš systém se uchýlí k stránkování a výkon bude výrazně trpět.

fond vyrovnávací paměti lze také rozdělit do samostatných oblastí, známých jako instance. Použití více instancí může zlepšit souběžnost pro vyrovnávací paměti v rozsahu multi-GiB.

operace změny velikosti fondu vyrovnávací paměti se provádějí v blocích a velikost fondu vyrovnávací paměti musí být nastavena na násobek velikosti bloku krát počet instancí:

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

Velikost bloku je výchozí na 128 MiB, ale je konfigurovatelná od MySQL 5.7.5. Hodnota obou parametrů lze zkontrolovat následovně:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

pokud dotaz innodb_buffer_pool_chunk_size nevrátí žádné výsledky, parametr není laditelný ve vaší verzi MySQL a lze předpokládat, že je 128 MiB.

nastavení velikosti vyrovnávací paměti a počtu instancí při spuštění serveru:

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

od MySQL 5.7.5 můžete také změnit velikost fondu vyrovnávací paměti za běhu pomocí příkazu SET určujícího požadovanou velikost v bajtech. Například, se dvěma instancemi fondu vyrovnávací paměti, můžete nastavit každý na 4 Velikost GiB nastavením celkové velikosti na 8 GiB:

SET GLOBAL innodb_buffer_pool_size=8589934592;

klíčové metriky fondu vyrovnávací paměti InnoDB

MySQL vystavuje několik metrik na fondu vyrovnávací paměti a jeho využití. Některé z nejužitečnějších jsou metriky sledování celkové velikosti fondu vyrovnávací paměti, kolik je v provozu, a jak efektivně fond vyrovnávací paměti slouží čte.

metriky Innodb_buffer_pool_read_requests a Innodb_buffer_pool_reads jsou klíčem k pochopení využití vyrovnávací paměti. Innodb_buffer_pool_read_requests sleduje počet logických požadavků na čtení, zatímco Innodb_buffer_pool_reads sleduje počet požadavků, které fond vyrovnávací paměti nemohl uspokojit, a proto musel být čten z disku. Vzhledem k tomu, že čtení z paměti je obecně řádově rychlejší než čtení z disku, výkon bude trpět, pokud Innodb_buffer_pool_reads začne stoupat.

využití fondu vyrovnávací paměti je užitečná metrika pro kontrolu před zvážením změny velikosti fondu vyrovnávací paměti. Metrika využití není k dispozici po vybalení z krabice, ale lze ji snadno vypočítat následovně:

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total

pokud vaše databáze slouží velkému počtu čtení z disku, ale fond vyrovnávací paměti není zdaleka plný, může se stát, že vaše mezipaměť byla nedávno vymazána a stále se zahřívá. Pokud se váš fond vyrovnávací paměti nevyplní, ale účinně slouží ke čtení, vaše pracovní sada dat se pravděpodobně pohodlně vejde do paměti.

vysoké využití vyrovnávací paměti na druhé straně nemusí být nutně špatná věc izolovaně, protože stará nebo nepoužívaná data jsou automaticky stárnuta z mezipaměti pomocí zásad LRU. Pokud však fond vyrovnávací paměti účinně neslouží vaší pracovní zátěži při čtení,může být čas rozšířit mezipaměť.

převod metrik fondu vyrovnávací paměti na bajty

většina metrik fondu vyrovnávací paměti je hlášena jako počet stránek paměti, ale tyto metriky lze převést na bajty, což usnadňuje propojení těchto metrik se skutečnou velikostí fondu vyrovnávací paměti. Chcete-li například zjistit celkovou velikost fondu vyrovnávací paměti v bajtech pomocí proměnné stavu serveru, která sleduje celkový počet stránek ve fondu vyrovnávací paměti:

Innodb_buffer_pool_pages_total * innodb_page_size

Velikost stránky InnoDB je nastavitelná, ale výchozí hodnota je 16 KiB nebo 16 384 bajtů. Jeho aktuální hodnotu lze zkontrolovat pomocí dotazu SHOW VARIABLES :

SHOW VARIABLES LIKE "innodb_page_size";

závěr

V tomto příspěvku jsme prozkoumali několik nejdůležitějších metrik, které byste měli sledovat, abyste měli přehled o aktivitě a výkonu MySQL. Pokud budujete monitorování MySQL, zachycení níže uvedených metrik vás postaví na cestu k pochopení vzorců využití vaší databáze a potenciálních omezení. Pomohou vám také určit, kdy je nutné škálovat nebo přesunout instance databáze na výkonnější hostitele, aby se udržel dobrý výkon aplikací.

• propustnost dotazu
• latence dotazu a chyby
• připojení a chyby klienta
• využití vyrovnávací paměti

Část 2 této řady obsahuje pokyny pro shromažďování a sledování všech metrik, které potřebujete od MySQL.

poděkování

Děkujeme Dave Stokesovi z Oracle a Ewen Fortune z VividCortex za poskytnutí cenné zpětné vazby k tomuto článku před zveřejněním.