Kalkulator strojenia pamięci bazy

Gdzie idzie pamięć bazy i parametr, który po cichu się mnoży

Zarządzane bazy przychodzą z parametrami pamięci wyprowadzonymi z rozmiaru instancji i większość z nich to rozsądne wartości domyślne, których lepiej nie ruszać. Jeden taki nie jest, bo nie zachowuje się jak reszta: work_mem przydzielany jest na każdy węzeł sortowania lub haszowania, więc jego realny koszt mnoży się przez liczbę połączeń i przez liczbę takich węzłów w zapytaniu. To narzędzie wyprowadza standardowe parametry z pamięci instancji, a potem pokazuje ten iloczyn wprost.

Jak to działa

• Wyprowadza shared_buffers, effective_cache_size i maintenance_work_mem z pamięci instancji, w proporcjach, których używa sam RDS.
• Po przełączeniu silnika pokazuje odpowiedniki MySQL — innodb_buffer_pool_size i bufory na połączenie.
• Mnoży work_mem przez liczbę połączeń i sortowań na zapytanie, bo to jest liczba, która naprawdę musi się zmieścić.
• Ostrzega, gdy alokacja bazowa plus ten najgorszy przypadek przekraczają pamięć instancji.

PostgreSQL, tak jak RDS ustawia domyślne
  shared_buffers        = {DBInstanceClassMemory/32768}  -> 25% RAM
  effective_cache_size  ~ 75% RAM   (podpowiedź dla planera, nie alokacja)
  maintenance_work_mem  ~ 5%, limit około 2 GiB

MySQL
  innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} -> 75% RAM

ten, który się mnoży
  najgorszy przypadek = work_mem x połączenia x sortowania_na_zapytanie

Przykład z liczbami

Instancja PostgreSQL 32 GiB na ustawieniach domyślnych i co się dzieje po podniesieniu jednego parametru.

1. shared_buffers = 25% z 32 GiB = 8 GiB
2. effective_cache_size = 75% = 24 GiB, ale to niczego nie rezerwuje — mówi tylko planerowi, ile prawdopodobnie cache'uje system
3. maintenance_work_mem = 5% = 1,6 GiB, poniżej limitu 2 GiB
4. work_mem = 16 MiB, przy 200 połączeniach i 2 węzłach sortowania na zapytanie
5. najgorszy przypadek = 16 MiB x 200 x 2 = 6,25 GiB
6. 8 GiB + 6,25 GiB = 14,25 GiB z 32 — komfortowo
7. teraz podnieś work_mem do 64 MiB, by przyspieszyć jeden raport, i urośnij do 1000 połączeń z 4 sortowaniami
8. najgorszy przypadek = 64 MiB x 1000 x 4 = 250 GiB na instancji 32 GiB

Ostatnie dwa kroki to typowa droga do zabicia przez OOM. Nic nie wyglądało lekkomyślnie: ktoś podniósł work_mem czterokrotnie, żeby przyspieszyć jedno zapytanie, a ruch urósł. Resztę zrobiło mnożenie — i dzieje się to pod obciążeniem, a nie w chwili zmiany parametru, dlatego rzadko łapie się to na przeglądzie.

Jak czytać wynik

• effective_cache_size nic nie alokuje. To twoja estymacja dla planera, ile pamięci system prawdopodobnie zużywa na cache twoich danych, i zmienia to, które plany wyglądają tanio. Ustawienie wysoko nie zużywa pamięci; ustawienie błędnie produkuje złe plany.
• shared_buffers na 25% to konwencjonalny punkt wyjścia i domyślna wartość RDS. Podnoszenie nie jest darmowe: PostgreSQL opiera się na cache stron systemu jako drugiej warstwie, więc pamięć przeniesiona do shared_buffers to pamięć zabrana temu cache'owi. Bardzo duże wartości potrafią spowolnić działanie.
• MySQL ma odwrotny kształt. innodb_buffer_pool_size domyślnie wynosi 75%, bo InnoDB nie opiera się na cache systemu tak jak PostgreSQL — chce mieć dane we własnej puli. Nie przenoś tu intuicji z PostgreSQL-a.
• Podnoś work_mem dla sesji, a nie dla serwera, gdy potrzebuje go jeden raport. SET work_mem w danym połączeniu daje plan, którego chcesz, bez przyznawania tego samego przydziału każdemu innemu połączeniu na instancji.
• Zapytanie z kilkoma węzłami sortowania lub haszowania potrafi wziąć work_mem kilkukrotnie, w obrębie jednego połączenia. Równoległe workery mnożą to ponownie. Suwak sortowań na zapytanie istnieje właśnie dlatego, że założenie jednego to najczęstszy powód, dla którego ta estymacja wychodzi za nisko.

Częste pytania

Czy najgorszy przypadek jest realistyczny, czy to tylko arytmetyka?

To sufit, nie prognoza — wszystkie połączenia musiałyby jednocześnie wykonywać pamięciożerne zapytanie. Ale to sufit, który ma znaczenie, bo bazy nie padają przy nim łagodnie: zabija je OOM killer, a skoki ruchu to dokładnie te momenty, gdy wszystkie połączenia pracują naraz.

Moja zarządzana baza nie pozwala zmienić shared_buffers. Dlaczego?

W RDS i Azure ustawia się to przez grupę parametrów jako wzór na DBInstanceClassMemory, a nie jako stałą liczbę, a zmiana wymaga restartu, bo segment pamięci współdzielonej alokowany jest przy starcie. Cloud SQL część flag blokuje całkiem. Domyślna wartość zwykle jest dobra; parametrem wartym twojej uwagi jest work_mem.

Skąd wiem, ile work_mem naprawdę potrzebuje zapytanie?

Uruchom EXPLAIN (ANALYZE, BUFFERS) i szukaj scalania zewnętrznego albo haszowania na dysku. Jeśli sortowanie zeszło na dysk, plan to pokaże wraz z rozmiarem. Ta liczba — nieco więcej niż zrzut — to wartość, której chce zapytanie, i mówi ci, czy warto podnosić ustawienie globalnie, czy raczej naprawić samo zapytanie.

A bufory na połączenie w MySQL?

Mnożą się tak samo. sort_buffer_size, join_buffer_size i read_rnd_buffer_size przydzielane są na połączenie, gdy są potrzebne, ponad pulą buforów. Obowiązuje ta sama dyscyplina: trzymaj je skromnie i podnoś dla tej sesji, która naprawdę ich potrzebuje.

Źródła

• PostgreSQL — parametry zużycia zasobów
• Amazon RDS — dobre praktyki
• MySQL — konfiguracja puli buforów InnoDB