Jak określić słaby punkt między wielkością puli a połączeniami z bazą danych w PostgreSQL

Mamy problemy z obsługą ruchu w godzinach szczytu do naszego serwera bazy danych. Szukamy ulepszenia sprzętu (patrz to pytanie o tę stronę ), ale chcemy też pracować nad konfiguracją puli i dostrajaniem serwerów.

Aplikacja, nad którą pracujemy, to turowa gra wieloosobowa na smartfony, w której backend składa się z Railsów z jednorożcem i PostgreSQL 9.1 jako bazy danych. Obecnie mamy 600 000 zarejestrowanych użytkowników, a ponieważ stan gry jest przechowywany w bazie danych, co kilka sekund wykonuje się kilka tysięcy zapisów. Przeanalizowaliśmy pliki dziennika z PostgreSQL za pomocą PgBadger i podczas krytycznych godzin dostajemy ich dużo

FATAL: remaining connection slots are reserved for non-replication superuser connections

Naiwnym rozwiązaniem tego problemu byłoby zwiększenie max_connections (które obecnie wynosi 100) w postgresql.conf . Przeczytałem http://wiki.postgresql.org/wiki/Number_Of_Database_Connections co wskazuje, że może to nie być właściwe. W wyżej wymienionym artykule jest mowa o znalezieniu optymalnego punktu pomiędzy max_connections a wielkością puli .

Co można zrobić, aby znaleźć to słodkie miejsce? Czy są jakieś dobre narzędzia do pomiaru wydajności We / Wy dla różnych wartości max_connections i wielkości puli ?

Nasza obecna konfiguracja to 4 serwery gier, każdy z 16 robotnikami jednorożca i wielkości puli 5.

Oto niestandardowe ustawienia postgres, których używamy:

version                      | PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu,compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
checkpoint_completion_target | 0.9
checkpoint_segments          | 60
checkpoint_timeout           | 6min
client_encoding              | UTF8
effective_cache_size         | 2GB
lc_collate                   | en_US.UTF-8
lc_ctype                     | en_US.UTF-8
log_destination              | csvlog
log_directory                | pg_log
log_filename                 | postgresql-%Y-%m-%d_%H%M%S.log
log_line_prefix              | %t
log_min_duration_statement   | 200ms
log_rotation_age             | 1d
log_rotation_size            | 10MB
logging_collector            | on
max_connections              | 100
max_stack_depth              | 2MB
server_encoding              | UTF8
shared_buffers               | 1GB
ssl                          | on
TimeZone                     | localtime
wal_buffers                  | 16MB
work_mem                     | 8MB

Krótka odpowiedź tutaj to „próby i błędy oparte na wskaźnikach monitorowania i wydajności”.

Istnieje kilka ogólnych zasad, które powinny pomóc ci znaleźć niejasny obszar, od którego powinieneś zacząć, ale są one bardzo ogólne. Często przytaczane są ogólne wytyczne „liczba procesorów plus liczba niezależnych dysków”, ale jest to tylko niewiarygodnie gruboziarnisty punkt początkowy.

To, co naprawdę musisz zrobić, to uzyskać solidne wskaźniki wydajności dla swojej aplikacji. Rozpocznij rejestrowanie statystyk.

Zintegrowane oprzyrządowanie nie ma wiele do tego. Są takie rzeczy, jak check_postgresskrypt nagios , rejestrowanie liczników wydajności systemu Cacti, moduł gromadzący statystyki PostgreSQL itp., Ale niewiele z nich łączy. Niestety musisz to zrobić sam. Po stronie PostgreSQL, patrz monitorowanie w instrukcji PostgreSQL. Istnieją pewne opcje stron trzecich, takie jak Postgres Enterprise Monitor EnterpriseDB .

W przypadku wspomnianych tutaj wskaźników na poziomie aplikacji należy je zapisać we wspólnych strukturach danych lub w zewnętrznej nietrwałej bazie danych, takiej jak Redis, i agregować je podczas ich zapisywania lub przed zapisaniem ich w bazie danych PostgreSQL. Próba zalogowania się bezpośrednio do Pg zniekształci twoje pomiary z narzutem utworzonym przez rejestrację pomiarów i pogorszy problem.

Najprostszą opcją jest prawdopodobnie singleton na każdym serwerze aplikacji, którego używasz do rejestrowania statystyk aplikacji. Prawdopodobnie chcesz stale aktualizować wartości min, max, n, total i mean; w ten sposób nie musisz przechowywać każdego punktu statystycznego, tylko agregaty. Ten singleton może zapisywać swoje zagregowane statystyki do Pg co x minut, na tyle niskim poziomie, że wpływ na wydajność będzie minimalny.

Zacząć od:

• Jakie jest opóźnienie żądania? Innymi słowy, ile czasu zajmuje aplikacja od otrzymania żądania od klienta, dopóki nie odpowie na niego. Nagrywaj to zbiorczo w czasie, a nie jako osobne rekordy. Pogrupuj według typu żądania; powiedzmy po stronie.

• Jakie jest opóźnienie dostępu do bazy danych dla każdego zapytania lub typu zapytania wykonywanego przez aplikację? Jak długo trzeba czekać na zapytanie DB o informacje / przechowywanie informacji, zanim to się skończy i będzie mógł przejść do następnego zadania? Ponownie agreguj te statystyki w aplikacji i zapisuj tylko dane zagregowane do bazy danych.

• Jaka jest twoja przepustowość? W jakimkolwiek x minutach, ile zapytań z każdej głównej klasy wykonywanej przez twoją aplikację jest obsługiwanych przez DB?

• W tym samym przedziale czasu x minut, ile było żądań klientów?

• Próbkowanie co kilka sekund i agregowanie w tych samych oknach x-minutowych w DB, ile było połączeń DB? Ile z nich było bezczynnych? Ile było aktywnych? We wkładkach? Aktualizacje wybiera? usuwa? Ile transakcji było w tym okresie? Zobacz dokumentację kolekcjonera statystyk

• Ponownie próbkowanie i agregowanie w tym samym przedziale czasu, jakie były parametry wydajności systemu hosta? Ile odczytów i ile operacji We / Wy zapisu na sekundę? Megabajty na sekundę dysku czyta i zapisuje? Wykorzystanie procesora? Średnie obciążenie? Wykorzystanie pamięci RAM?

Możesz teraz zacząć uczyć się o wydajności aplikacji poprzez korelowanie danych, tworzenie wykresów itp. Zaczniesz widzieć wzorce, zaczniesz znajdować wąskie gardła.

Możesz dowiedzieć się, że twój system ma wąską szyję INSERTi UPDATEma wysokie stawki transakcyjne, pomimo dość niskich operacji we / wy dysku w megabajtach na sekundę. Byłaby to wskazówka, że ​​musisz poprawić wydajność opróżniania dysku za pomocą kontrolera RAID z pamięcią podręczną z podtrzymaniem bateryjnym lub niektórych wysokiej jakości dysków SSD zabezpieczonych przed zużyciem energii. Możesz także użyć, synchronous_commit = offjeśli możesz zgubić kilka transakcji po awarii serwera i / lub acommit_delay , aby zmniejszyć obciążenie synchronizacji.

Kiedy zobrazujesz swoje transakcje na sekundę w stosunku do liczby równoczesnych połączeń i skorygujesz zmienną częstotliwość żądań, którą widzi aplikacja, będziesz w stanie lepiej zrozumieć, gdzie jest twoja najsłabsza przepustowość.

Jeśli nie masz magazynu z szybkim opróżnianiem (BBU RAID lub szybkie trwałe dyski SSD), nie będziesz potrzebować więcej niż dość małej liczby aktywnie zapisujących połączeń, może maksymalnie dwa razy więcej dysków, prawdopodobnie mniej w zależności od konfiguracji RAID , wydajność dysku itp. W tym przypadku nie warto nawet próbować i popsuć; po prostu uaktualnij swój podsystem pamięci masowej do jednego z szybkim opróżnianiem dysku .

Zobacz pg_test_fsyncnarzędzie, które pomoże ci określić, czy to może być problem dla Ciebie. Większość pakietów PostgreSQL instaluje to narzędzie jako część contrib, więc nie powinieneś go kompilować. Jeśli otrzymasz mniej niż kilka tysięcy operacji na sekundę pg_test_fsync, pilnie musisz zaktualizować swój system pamięci. Mój laptop wyposażony w SSD dostaje 5000-7000. Moja stacja robocza pracuje z 4-dyskową macierzą RAID 10 z dyskami SATA 7200 obr./min i zapisem (buforowanie bez zapisu) dostaje około 80 operacji na sekundę f_datasync, do 20 operacji na sekundę fsync(); jest setki razy wolniejszy . Porównaj: laptop z ssd vs stacja robocza z zapisem (bez buforowania) RAID 10. Dysk SSD tego laptopa jest tani i niekoniecznie ufam, że opróżni pamięć podręczną po utracie zasilania; Trzymam dobre kopie zapasowe i nie używam ich do danych, na których mi zależy. Dyski SSD dobrej jakości działają równie dobrze, jeśli nie lepiej, i są odporne na zapis.

W przypadku aplikacji zdecydowanie radzę przyjrzeć się:

• Dobry podsystem pamięci masowej z szybkimi opróżnieniami. Nie mogę tego wystarczająco podkreślić. Dobrej jakości dyski SSD odporne na awarie i / lub kontroler RAID z zabezpieczoną pamięcią podręczną pamięcią zwrotną.
• Za pomocą UNLOGGED tabel do danych, które możesz stracić. Okresowo agreguj go do rejestrowanych tabel. Na przykład utrzymuj trwające gry w niezalogowanych tabelach i zapisz wyniki w zwykłych trwałych tabelach.
• Używać commit_delay (mniej przydatne w przypadku szybkiego spłukiwania magazynu - wskazówka)
• Wyłączanie synchronous_commit dla transakcji, na które możesz sobie pozwolić stracić (mniej przydatne w przypadku magazynu o szybkim przepłukiwaniu - wskazówka)
• Tabele partycjonowania, zwłaszcza tabele, w których dane „starzeją się” i są czyszczone. Zamiast usuwać z podzielonej na partycje tabeli, upuść partycję.
• Częściowe indeksy
• Zmniejszenie liczby tworzonych indeksów. Każdy indeks ma koszt zapisu.
• Łączenie pracy w większe transakcje
• Korzystanie z replik w trybie gotowości „tylko do odczytu” w celu pobrania obciążenia odczytu z głównej bazy danych
• Używanie warstwy buforującej, takiej jak memcached lub redis dla danych, które zmieniają się rzadziej lub mogą stać się nieaktualne. Możesz użyć LISTENi NOTIFYwykonać unieważnienie pamięci podręcznej za pomocą wyzwalaczy w tabelach PostgreSQL.

W razie wątpliwości: http://www.postgresql.org/support/professional_support/