Rozwiązywanie problemów z małą ilością pamięci w usłudze Azure Database for MySQL — serwer elastyczny

DOTYCZY: Azure Database for MySQL — pojedynczy serwer usługi Azure Database for MySQL — serwer elastyczny

Ważne

Pojedynczy serwer usługi Azure Database for MySQL znajduje się na ścieżce wycofania. Zdecydowanie zalecamy uaktualnienie do serwera elastycznego usługi Azure Database for MySQL. Aby uzyskać więcej informacji na temat migracji do serwera elastycznego usługi Azure Database for MySQL, zobacz Co się dzieje z usługą Azure Database for MySQL — pojedynczy serwer?

Aby zapewnić optymalne działanie wystąpienia serwera elastycznego usługi Azure Database for MySQL, bardzo ważne jest, aby mieć odpowiednią alokację i wykorzystanie pamięci. Domyślnie podczas tworzenia wystąpienia serwera elastycznego usługi Azure Database for MySQL dostępna pamięć fizyczna zależy od warstwy i rozmiaru wybranego dla obciążenia. Ponadto pamięć jest przydzielana dla i pamięci podręcznych w celu poprawy operacji bazy danych. Aby uzyskać więcej informacji, zobacz How MySQL Uses Memory (Jak program MySQL używa pamięci).

Należy pamiętać, że elastyczny serwer usługi Azure Database for MySQL zużywa pamięć, aby osiągnąć jak najwięcej trafień pamięci podręcznej. W związku z tym wykorzystanie pamięci może często znajdować się w zakresie od 80 do 90% dostępnej pamięci fizycznej wystąpienia. Jeśli nie wystąpi problem z postępem obciążenia zapytania, nie jest to problem. Jednak mogą wystąpić problemy z pamięcią z powodów, takich jak:

• Skonfigurowano zbyt duże.
• Uruchomione zapytania podrzędne.
• Zapytania wykonujące sprzężenia i sortowanie dużych zestawów danych.
• Ustaw zbyt wysokie maksymalne połączenia na serwerze bazy danych.

Większość pamięci serwera jest używana przez globalne i pamięci podręczne innoDB, które obejmują składniki, takie jak innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size i query_cache_size.

Wartość parametru innodb_buffer_pool_size określa obszar pamięci, w którym innoDB buforuje tabele bazy danych i dane związane z indeksem. MySQL próbuje pomieścić jak najwięcej danych związanych z tabelami i indeksami w puli, jak to możliwe. Większa pula wymaga mniejszej liczby operacji we/wy przekierowanych na dysk.

Monitorowanie użycia pamięci

Usługa Azure Database for MySQL — elastyczny serwer udostępnia szereg metryk, które umożliwiają ocenę wydajności wystąpienia bazy danych. Aby lepiej zrozumieć wykorzystanie pamięci dla serwera bazy danych, wyświetl metryki Procent pamięci hosta lub Procent pamięci.

Jeśli zauważysz, że użycie pamięci nagle się zwiększyło i że ilość dostępnej pamięci szybko spada, monitoruj inne metryki, takie jak Procent procesora CPU hosta, Łączne połączenia i Procent operacji we/wy, aby ustalić, czy nagły wzrost obciążenia jest źródłem problemu.

Należy pamiętać, że każde połączenie nawiązane z serwerem bazy danych wymaga alokacji pewnej ilości pamięci. W związku z tym wzrost liczby połączeń z bazą danych może powodować braki pamięci.

Przyczyny wysokiego wykorzystania pamięci

Przyjrzyjmy się kilku przyczynom wysokiego wykorzystania pamięci w programie MySQL. Przyczyny te zależą od cech obciążenia.

Zwiększenie liczby tabel tymczasowych

MySQL używa "tabel tymczasowych", które są specjalnym typem tabeli przeznaczonej do przechowywania tymczasowego zestawu wyników. Tabele tymczasowe mogą być używane kilka razy podczas sesji. Ponieważ wszystkie utworzone tabele tymczasowe są lokalne w sesji, różne sesje mogą mieć różne tabele tymczasowe. W systemach produkcyjnych z wieloma sesjami wykonującymi kompilacje dużych tymczasowych zestawów wyników należy regularnie sprawdzać globalny licznik stanu created_tmp_tables, który śledzi liczbę tabel tymczasowych tworzonych w godzinach szczytu. Duża liczba tabel tymczasowych w pamięci może szybko prowadzić do niskiej ilości dostępnej pamięci w wystąpieniu serwera elastycznego usługi Azure Database for MySQL.

W przypadku programu MySQL rozmiar tabeli tymczasowej jest określany przez wartości dwóch parametrów, zgodnie z opisem w poniższej tabeli.

Parametr	Opis
tmp_table_size	Określa maksymalny rozmiar wewnętrznych tabel tymczasowych w pamięci.
max_heap_table_size	Określa maksymalny rozmiar, do którego mogą rosnąć tabele PAMIĘCI utworzone przez użytkownika.

Uwaga

Podczas określania maksymalnego rozmiaru wewnętrznej, tymczasowej tabeli w pamięci program MySQL uwzględnia niższe wartości ustawione dla parametrów tmp_table_size i max_heap_table_size.

Zalecenia

Aby rozwiązać problemy z małą ilością pamięci związane z tabelami tymczasowymi, rozważ poniższe zalecenia.

• Przed zwiększeniem wartości tmp_table_size sprawdź, czy baza danych jest prawidłowo indeksowana, szczególnie w przypadku kolumn zaangażowanych w sprzężenia i pogrupowanych według operacji. Użycie odpowiednich indeksów w tabelach bazowych ogranicza liczbę utworzonych tabel tymczasowych. Zwiększenie wartości tego parametru i parametru max_heap_table_size bez sprawdzania, czy indeksy mogą zezwalać na uruchamianie niewydajnych zapytań bez indeksów i tworzenie większej liczby tabel tymczasowych niż jest to konieczne.
• Dostosuj wartości parametrów max_heap_table_size i tmp_table_size, aby zaspokoić potrzeby obciążenia.
• Jeśli wartości ustawione dla parametrów max_heap_table_size i tmp_table_size są zbyt niskie, tabele tymczasowe mogą regularnie rozlać się do magazynu, dodając opóźnienie do zapytań. Tabele tymczasowe można śledzić rozlanie na dysk przy użyciu globalnego licznika stanu created_tmp_disk_tables. Porównując wartości zmiennych created_tmp_disk_tables i created_tmp_tables, można wyświetlić liczbę wewnętrznych tabel tymczasowych na dysku, które zostały utworzone do łącznej liczby utworzonych wewnętrznych tabel tymczasowych.

Pamięć podręczna tabel

Jako system wielowątkowy mySQL utrzymuje pamięć podręczną deskryptorów plików tabeli, dzięki czemu tabele mogą być otwierane niezależnie przez wiele sesji. Program MySQL używa niektórych deskryptorów pamięci i plików systemu operacyjnego do obsługi tej pamięci podręcznej tabel. Zmienna table_open_cache definiuje rozmiar pamięci podręcznej tabeli.

Zalecenia

Aby rozwiązać problemy z małą ilością pamięci związane z pamięcią podręczną tabel, należy wziąć pod uwagę następujące zalecenia.

• Parametr table_open_cache określa liczbę otwartych tabel dla wszystkich wątków. Zwiększenie tej wartości zwiększa liczbę deskryptorów plików, których wymaga mysqld. Możesz sprawdzić, czy chcesz zwiększyć pamięć podręczną tabel, sprawdzając zmienną stanu opened_tables w liczniku stanu wyświetlania globalnego. Zwiększ wartość tego parametru w przyrostach, aby uwzględnić obciążenie.
• Ustawienie table_open_cache zbyt małe może spowodować, że usługa Azure Database for MySQL — elastyczny serwer poświęca więcej czasu na otwieranie i zamykanie tabel potrzebnych do przetwarzania zapytań.
• Ustawienie tej wartości zbyt dużej może spowodować użycie większej ilości pamięci, a system operacyjny z uruchomionymi deskryptorami plików, co prowadzi do odmowy połączeń lub nie przetwarza zapytań.

Inne i pamięć podręczna zapytań

Podczas rozwiązywania problemów związanych z małą ilością pamięci można pracować z kilkoma i pamięcią podręczną, aby ułatwić rozwiązanie problemu.

Bufor netto (net_buffer_length)

Bufor netto jest rozmiarem połączeń i wątków dla każdego wątku klienta i może wzrosnąć do wartości określonej dla max_allowed_packet. Jeśli na przykład instrukcja zapytania jest duża, wszystkie wstawki/aktualizacje mają bardzo dużą wartość, zwiększenie wartości parametru net_buffer_length pomoże zwiększyć wydajność.

Bufor sprzężenia (join_buffer_size)

Bufor sprzężenia jest przydzielany do buforowania wierszy tabeli, gdy sprzężenie nie może używać indeksu. Jeśli baza danych ma wiele sprzężeń wykonanych bez indeksów, rozważ dodanie indeksów w celu szybszego sprzężenia. Jeśli nie możesz dodać indeksów, rozważ zwiększenie wartości parametru join_buffer_size, który określa ilość pamięci przydzielonej na połączenie.

Bufor sortowania (sort_buffer_size)

Bufor sortowania służy do przeprowadzania sortowania dla niektórych zapytań ORDER BY i GROUP BY. Jeśli w danych wyjściowych SHOW GLOBAL STATUS jest wyświetlanych wiele Sort_merge_passes na sekundę, rozważ zwiększenie wartości sort_buffer_size w celu przyspieszenia operacji ORDER BY lub GROUP BY, których nie można poprawić przy użyciu optymalizacji zapytań lub lepszego indeksowania.

Unikaj arbitralnego zwiększania wartości sort_buffer_size, chyba że masz powiązane informacje, które wskazują inaczej. Pamięć dla tego buforu jest przypisywana na połączenie. W dokumentacji bazy danych MySQL artykuł Zmienne systemowe serwera zwraca uwagę, że w systemie Linux istnieją dwa progi, 256 KB i 2 MB, a użycie większych wartości może znacznie spowolnić alokację pamięci. W rezultacie należy unikać zwiększania sort_buffer_size wartości przekraczającej 2 mln, ponieważ kara za wydajność przeważa nad wszelkimi korzyściami.

Pamięć podręczna zapytań (query_cache_size)

Pamięć podręczna zapytań to obszar pamięci używany do buforowania zestawów wyników zapytań. Parametr query_cache_size określa ilość pamięci przydzielonej do buforowania wyników zapytania. Domyślnie pamięć podręczna zapytań jest wyłączona. Ponadto pamięć podręczna zapytań jest przestarzała w programie MySQL w wersji 5.7.20 i została usunięta w programie MySQL w wersji 8.0. Jeśli pamięć podręczna zapytań jest obecnie włączona w rozwiązaniu, przed jego wyłączeniem sprawdź, czy nie ma na nim żadnych zapytań.

Obliczanie współczynnika trafień pamięci podręcznej buforu

Współczynnik trafień pamięci podręcznej buforu jest ważny w środowisku serwera elastycznego usługi Azure Database for MySQL, aby zrozumieć, czy pula może pomieścić żądania obciążenia, czy nie, a ogólnie rzecz biorąc, dobrym rozwiązaniem jest zawsze posiadanie współczynnika trafienia pamięci podręcznej puli ponad 99%.

Aby obliczyć współczynnik trafień puli InnoDB dla żądań odczytu, możesz uruchomić polecenie SHOW GLOBAL STATUS w celu pobrania liczników "Innodb_buffer_pool_read_requests" i "Innodb_buffer_pool_reads", a następnie obliczyć wartość przy użyciu formuły przedstawionej poniżej.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Rozważmy następujący przykład.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> 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)

Korzystając z powyższych wartości, obliczenie współczynnika trafień puli InnoDB dla żądań odczytu daje następujący wynik:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

Oprócz współczynnika trafień pamięci podręcznej buforu instrukcji select instrukcji DML operacje zapisu w puli InnoDB są wykonywane w tle. Jeśli jednak konieczne jest odczytanie lub utworzenie strony i nie są dostępne żadne czyste strony, konieczne jest również oczekiwanie na pierwsze opróżnienie stron.

Licznik Innodb_buffer_pool_wait_free zlicza liczbę wystąpień. Innodb_buffer_pool_wait_free większe niż 0 jest silnym wskaźnikiem, że pula InnoDB jest zbyt mała, a zwiększenie rozmiaru puli lub rozmiaru wystąpienia jest wymagane do uwzględnienia zapisów przychodzących do bazy danych.

Zalecenia

• Upewnij się, że baza danych ma wystarczającą ilość zasobów przydzielonych do uruchamiania zapytań. Czasami może być konieczne skalowanie w górę rozmiaru wystąpienia w celu uzyskania większej ilości pamięci fizycznej, aby i pamięci podręczne obsługiwały obciążenie.
• Unikaj dużych lub długotrwałych transakcji, dzieląc je na mniejsze transakcje.
• Użyj alertów "Procent pamięci hosta", aby otrzymywać powiadomienia, jeśli system przekroczy którykolwiek z określonych progów.
• Użyj szczegółowych informacji o wydajności zapytań lub skoroszytów platformy Azure, aby zidentyfikować wszelkie problematyczne lub powoli uruchomione zapytania, a następnie je zoptymalizować.
• W przypadku serwerów produkcyjnych baz danych zbierz diagnostykę w regularnych odstępach czasu, aby upewnić się, że wszystko działa bezproblemowo. Jeśli nie, rozwiąż wszelkie zidentyfikowane problemy i rozwiąż je.

Następne kroki

Aby znaleźć odpowiedzi na najważniejsze pytania lub opublikować lub odpowiedzieć na pytanie, odwiedź witrynę Stack Overflow.