Řešení potíží s nedostatkem paměti na flexibilním serveru Azure Database for MySQL
Aby bylo možné zajistit optimální výkon instance flexibilního serveru Azure Database for MySQL, je velmi důležité mít odpovídající přidělení a využití paměti. Když ve výchozím nastavení vytvoříte instanci flexibilního serveru Azure Database for MySQL, bude dostupná fyzická paměť závislá na úrovni a velikosti, kterou pro svou úlohu vyberete. Kromě toho se paměť přiděluje vyrovnávacím pamětím a mezipamětí, aby se zlepšily databázové operace. Další informace naleznete v tématu Jak MySQL používá paměť.
Flexibilní server Azure Database for MySQL spotřebovává paměť, aby dosáhl co největšího dosažení mezipaměti. Díky tomu může využití paměti často najet mezi 80 až 90 % dostupné fyzické paměti instance. Pokud není problém s průběhem úlohy dotazu, není to problém. Můžete ale narazit na problémy s nedostatkem paměti z důvodů, jako je například:
- Nakonfigurovali jsme příliš velké vyrovnávací paměti.
- Podoptimální dotazy spuštěné.
- Dotazy provádějící spojení a řazení velkých datových sad
- Nastavte maximální počet připojení na databázovém serveru, který je příliš vysoký.
Většinu paměti serveru používají globální vyrovnávací paměti a mezipaměti InnoDB, které zahrnují komponenty, jako jsou innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size a query_cache_size.
Hodnota parametru innodb_buffer_pool_size určuje oblast paměti, ve které InnoDB ukládá tabulky databáze do mezipaměti a data související s indexem. MySQL se snaží co nejvíce pojmout data související s tabulkou a indexy ve fondu vyrovnávací paměti. Větší fond vyrovnávací paměti vyžaduje méně vstupně-výstupních operací, které se přesouvají na disk.
Monitorování využití paměti
Flexibilní server Azure Database for MySQL poskytuje řadu metrik pro měření výkonu vaší instance databáze. Pokud chcete lépe porozumět využití paměti pro databázový server, podívejte se na metriky Procent paměti hostitele nebo Procento paměti.
Pokud si všimnete, že využití paměti se náhle zvýšilo a že dostupná paměť rychle klesá, monitorujte další metriky, jako je například procento procesoru hostitele, celková připojení a vstupně-výstupní procento, a zjistěte, jestli je zdrojem problému náhlé zvýšení zatížení.
Je důležité si uvědomit, že každé připojení vytvořené s databázovým serverem vyžaduje přidělení určité velikosti paměti. V důsledku toho může nárůst počtu připojení k databázi způsobit nedostatek paměti.
Příčiny vysokého využití paměti
Pojďme se podívat na některé další příčiny vysokého využití paměti v MySQL. Tyto příčiny jsou závislé na charakteristikách úlohy.
Zvýšení dočasných tabulek
MySQL používá "dočasné tabulky", což je speciální typ tabulky navržené k uložení dočasné sady výsledků. Dočasné tabulky je možné během relace opakovaně používat. Vzhledem k tomu, že všechny dočasné tabulky vytvořené v relaci jsou místní, můžou mít různé dočasné tabulky. V produkčních systémech s mnoha relacemi provádějícími kompilace velkých dočasných sad výsledků byste měli pravidelně kontrolovat globální čítač stavu created_tmp_tables, který sleduje počet dočasných tabulek vytvořených během špičky. Velký počet dočasných tabulek v paměti může rychle vést k nedostatku dostupné paměti v instanci flexibilního serveru Azure Database for MySQL.
U MySQL je dočasná velikost tabulky určená hodnotami dvou parametrů, jak je popsáno v následující tabulce.
| Parametr | Popis |
|---|---|
| tmp_table_size | Určuje maximální velikost interních dočasných tabulek v paměti. |
| max_heap_table_size | Určuje maximální velikost, na kterou může uživatel vytvořit tabulky MEMORY. |
Poznámka:
Při určování maximální velikosti interní dočasné tabulky v paměti považuje MySQL nižší hodnoty nastavené pro tmp_table_size a max_heap_table_size parametry.
Doporučení
Při řešení problémů s nedostatkem paměti souvisejících s dočasnými tabulkami zvažte následující doporučení.
- Před zvýšením hodnoty tmp_table_size ověřte, že je databáze správně indexována, zejména pro sloupce zapojené do spojení a seskupování podle operací. Použití příslušných indexů v podkladových tabulkách omezuje počet vytvořených dočasných tabulek. Zvýšení hodnoty tohoto parametru a parametru max_heap_table_size bez ověření indexů může umožnit neefektivní dotazy spouštět bez indexů a vytvářet více dočasných tabulek, než je potřeba.
- Vylaďte hodnoty parametrů max_heap_table_size a tmp_table_size tak, aby vyhovovaly potřebám vaší úlohy.
- Pokud jsou hodnoty nastavené pro parametry max_heap_table_size a tmp_table_size příliš nízké, dočasné tabulky se můžou pravidelně přetévat do úložiště a přidávat latenci dotazům. Dočasné tabulky přetékající na disk můžete sledovat pomocí globálního čítače stavu created_tmp_disk_tables. Porovnáním hodnot created_tmp_disk_tables a created_tmp_tables proměnných zobrazíte počet interních dočasných tabulek na disku vytvořených na celkový počet interních dočasných tabulek.
Mezipaměť tabulek
MySQL jako vícevláknový systém udržuje mezipaměť popisovačů souborů tabulky, aby bylo možné tabulky souběžně otevírat nezávisle na několika relacích. MySQL používá k údržbě této mezipaměti tabulky určitou velikost paměti a popisovačů souborů operačního systému. Proměnná table_open_cache definuje velikost mezipaměti tabulky.
Doporučení
Při řešení problémů s nedostatkem paměti souvisejících s mezipamětí tabulky zvažte následující doporučení.
- Parametr table_open_cache určuje počet otevřených tabulek pro všechna vlákna. Zvýšením této hodnoty se zvýší počet popisovačů souborů, které mysqld vyžaduje. Pokud chcete zkontrolovat, jestli potřebujete zvýšit mezipaměť tabulky, zkontrolujte proměnnou stavu opened_tables v zobrazení čítače globálního stavu. Zvyšte hodnotu tohoto parametru v přírůstcích, aby vyhovovala vašemu zatížení.
- Nastavení table_open_cache příliš nízké může způsobit, že flexibilní server Azure Database for MySQL stráví více času při otevírání a zavírání tabulek potřebných ke zpracování dotazů.
- Nastavení příliš vysoké hodnoty může způsobit použití více paměti a operační systém spuštěných popisovačů souborů, což vede k odmítnutí připojení nebo selhání zpracování dotazů.
Jiné vyrovnávací paměti a mezipaměť dotazů
Při řešení potíží souvisejících s nedostatkem paměti můžete pracovat s několika dalšími vyrovnávacími paměťmi a mezipamětí, které vám pomůžou s řešením.
Čistá vyrovnávací paměť (net_buffer_length)
Vyrovnávací paměť sítě je velikost pro vyrovnávací paměť připojení a vlákna pro každé vlákno klienta a může růst na hodnotu určenou pro max_allowed_packet. Pokud je příkaz dotazu velký, například všechny vložení/aktualizace mají velmi velkou hodnotu, zvýšení hodnoty parametru net_buffer_length pomůže zlepšit výkon.
Vyrovnávací paměť spojení (join_buffer_size)
Vyrovnávací paměť spojení je přidělena k ukládání řádků tabulky do mezipaměti, když spojení nemůže použít index. Pokud má vaše databáze mnoho spojení provedených bez indexů, zvažte přidání indexů pro rychlejší spojení. Pokud nemůžete přidat indexy, zvažte zvýšení hodnoty parametru join_buffer_size, který určuje velikost paměti přidělenou na připojení.
Vyrovnávací paměť řazení (sort_buffer_size)
Vyrovnávací paměť řazení se používá k provádění řazení pro některé dotazy ORDER BY a GROUP BY. Pokud se ve výstupu SHOW GLOBAL STATUS zobrazí mnoho Sort_merge_passes za sekundu, zvažte zvýšení sort_buffer_size hodnoty pro urychlení operací ORDER BY nebo GROUP BY, které není možné zlepšit pomocí optimalizace dotazů nebo lepšího indexování.
Vyhněte se libovolnému zvýšení hodnoty sort_buffer_size, pokud nemáte související informace, které označují jinak. Paměť pro tuto vyrovnávací paměť je přiřazena pro každé připojení. V dokumentaci k MySQL článek systémové proměnné serveru uvádí, že v Linuxu existují dvě prahové hodnoty: 256 kB a 2 MB a že použití větších hodnot může výrazně zpomalit přidělení paměti. V důsledku toho se vyhnete zvýšení sort_buffer_size hodnoty nad 2M, protože snížení výkonu převáží nad všemi výhodami.
Mezipaměť dotazů (query_cache_size)
Mezipaměť dotazů je oblast paměti, která se používá k ukládání sad výsledků dotazu do mezipaměti. Parametr query_cache_size určuje velikost paměti, která je přidělena pro ukládání výsledků dotazu do mezipaměti. Ve výchozím nastavení je mezipaměť dotazů zakázaná. Mezipaměť dotazů je navíc v MySQL verze 5.7.20 zastaralá a v MySQL verze 8.0 se odebere. Pokud je mezipaměť dotazů ve vašem řešení aktuálně povolená, před zakázáním ověřte, že se na ní nespoléhá žádné dotazy.
Výpočet poměru přístupů do mezipaměti vyrovnávací paměti
Poměr přístupů do mezipaměti vyrovnávací paměti je důležitý v prostředí flexibilního serveru Azure Database for MySQL, abyste pochopili, jestli fond vyrovnávacích pamětí dokáže vyhovět požadavkům úloh nebo ne, a obecně platí, že obecně platí, že je vhodné mít vždy více než 99% poměr dosažení mezipaměti fondu vyrovnávací paměti.
Pokud chcete vypočítat poměr přístupů fondu vyrovnávacích pamětí InnoDB pro požadavky na čtení, můžete spustit příkaz SHOW GLOBAL STATUS a načíst čítače "Innodb_buffer_pool_read_requests" a "Innodb_buffer_pool_reads" a pak vypočítat hodnotu pomocí následujícího vzorce.
InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100
Podívejte se na následující příklady.
show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name | Value |
| +--------------------------+-------+ |
| Innodb_buffer_pool_reads | 197 |
| +--------------------------+-------+ |
| 1 row in set (0.00 sec) |
show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name | Value |
| +----------------------------------+----------+ |
| Innodb_buffer_pool_read_requests | 22479167 |
| +----------------------------------+----------+ |
| 1 row in set (0.00 sec) |
Když použijete výše uvedené hodnoty, výpočet poměru dosažení fondu vyrovnávací paměti InnoDB pro požadavky na čtení získá následující výsledek:
InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100
Buffer hit ratio = 99.99%
Kromě poměru přístupů do vyrovnávací paměti příkazů select pro všechny příkazy DML se zapisuje do fondu vyrovnávací paměti InnoDB na pozadí. Pokud je ale potřeba stránku přečíst nebo vytvořit a nejsou k dispozici žádné čisté stránky, je také nutné počkat, až se stránky vyprázdní.
Čítač Innodb_buffer_pool_wait_free spočítá, kolikrát k tomu došlo. Innodb_buffer_pool_wait_free větší než 0 je silným indikátorem, že fond vyrovnávací paměti InnoDB je příliš malý a pro přizpůsobení zápisů přicházejících do databáze se vyžaduje zvětšení velikosti fondu vyrovnávací paměti nebo velikosti instance.
Doporučení
- Ujistěte se, že má vaše databáze přidělených dostatek prostředků ke spouštění dotazů. V některých případech možná budete muset vertikálně navýšit kapacitu instance, abyste získali větší fyzickou paměť, aby vyrovnávací paměti a mezipaměti vyhovovaly vašim úlohám.
- Vyhněte se velkým nebo dlouhotrvajícím transakcím tím, že je rozdělíte na menší transakce.
- Používejte upozornění "Procento paměti hostitele", abyste dostávali oznámení, pokud systém překročí některou ze zadaných prahových hodnot.
- Pomocí Nástroje Query Performance Insights nebo Azure Workbooks identifikujte problematické nebo pomalé dotazy a pak je optimalizujte.
- V případě produkčních databázových serverů shromážděte diagnostiku v pravidelných intervalech, abyste měli jistotu, že vše funguje hladce. Pokud ne, vyřešte případné problémy, které identifikujete, a vyřešte je.