Tłumaczenie raportu z 2015 roku Aleksego Lesowskiego "Głęboka analiza wewnętrznych statystyk PostgreSQL"
Zastrzeżenie od autora raportu: Zauważam, że ten raport jest datowany na listopad 2015 roku – minęło ponad 4 lata i wiele się zmieniło. Rozważana w raporcie wersja 9.4 nie jest już wspierana. W ciągu ostatnich 4 lat ukazało się 5 nowych wydań, w których wprowadzono szereg nowości, ulepszeń oraz zmian w zakresie statystyk, a część materiału stała się przestarzała i nieaktualna. Podczas przeglądania starałem się zaznaczyć te miejsca, aby nie wprowadzać Cię, Czytelniku, w błąd. Nie zdecydowałem się na przepisywanie tych fragmentów, jest ich zbyt wiele, przez co mógłby powstać zupełnie inny raport.
System zarządzania bazą danych PostgreSQL to ogromny mechanizm, składający się z wielu podsystemów, których zgrana praca bezpośrednio wpływa na wydajność bazy danych. W trakcie eksploatacji zbierane są statystyki i informacje o pracy komponentów, co pozwala ocenić efektywność PostgreSQL i podjąć działania mające na celu poprawę wydajności. Jednak tej informacji jest bardzo dużo, a przedstawiana jest w dość uproszczonej formie. Przetwarzanie tych informacji oraz ich interpretacja często stanowi dość nietrywialne zadanie, a "zoo" narzędzi i użyteczności łatwo zdezorientuje nawet zaawansowanego DBA.
Dzień dobry! Nazywam się Aleksy. Jak powiedział Ilia, będę opowiadać o statystyce PostgreSQL.
Statystyka aktywności PostgreSQL. PostgreSQL ma dwie statystyki. Statystykę aktywności, o której mowa, oraz statystykę planisty dotyczącą rozkładu danych. Będę mówić właśnie o statystyce aktywności PostgreSQL, która pozwala nam ocenić wydajność i w jakiś sposób ją poprawić.
Opowiem, jak skutecznie wykorzystać statystykę do rozwiązywania najróżniejszych problemów, które się pojawiają lub mogą się pojawić.
Czego nie ma w raporcie? W raporcie nie będę poruszać statystyki planisty, ponieważ to osobny temat na osobny wykład dotyczący tego, jak dane są przechowywane w bazie oraz jak planista zapytań uzyskuje materiał dotyczący jakościowych i ilościowych charakterystyk tych danych.
I nie będzie przeglądów narzędzi, nie będę porównywać jednego produktu z drugim. Nie będzie żadnej reklamy. Odrzućmy to.
Chcę wam pokazać, że korzystanie z statystyki jest przydatne. To konieczne. Używanie jej nie jest straszne. Potrzebujemy tylko zwykłego SQL-a i podstawowej wiedzy na temat SQL.
Porozmawiajmy o tym, jaką statystykę wybrać do rozwiązywania problemów.
Jeśli spojrzymy na PostgreSQL i w systemie operacyjnym uruchomimy polecenie do przeglądania procesów, zobaczymy "czarną skrzynkę". Zobaczymy jakieś procesy, które coś robią, i po nazwie możemy mniej więcej przypuszczać, co one tam robią, czym się zajmują. Ale w zasadzie to czarna skrzynka, do wnętrza nie możemy zajrzeć.
Możemy zobaczyć obciążenie procesora w top, możemy sprawdzić wykorzystanie pamięci różnymi narzędziami systemowymi, ale do wnętrza PostgreSQL nie zajrzymy. W tym celu potrzebne są nam inne narzędzia.
Kontynuując, opowiem, gdzie spędzane jest czas. Jeśli wyobrazimy sobie PostgreSQL w postaci takiego schematu, będziemy mogli odpowiedzieć, gdzie spędzane jest czas. To dwie rzeczy: przetwarzanie zapytań od klientów z aplikacji oraz zadania w tle, które wykonuje PostgreSQL, aby utrzymać swoją sprawność.
Jeśli zaczniemy rozpatrywać od lewego górnego rogu, to możemy śledzić, jak przetwarzane są zapytania klientów. Zapytanie pochodzi z aplikacji i do dalszej pracy otwierana jest sesja klienta. Zapytanie przekazywane jest do planera. Planer buduje plan zapytania. Wysyła go dalej do wykonania. Zachodzi jakiś blokowy input-output danych związany z tabelami i indeksami. Potrzebne dane są odczytywane z dysków do pamięci w specjalnym obszarze "shared buffers". Wyniki zapytania, jeśli to są aktualizacje, usunięcia, są rejestrowane w dzienniku transakcji w WAL. Niektóre informacje statystyczne trafiają do logu lub do kolektora statystyki. I wynik zapytania jest zwracany klientowi. Po czym klient może powtórzyć wszystko od nowa z nowym zapytaniem.
Co z zadaniami w tle i procesami w tle? Mamy kilka procesów, które zapewniają sprawność i utrzymują bazę danych w normalnym trybie pracy. Te procesy również będą omawiane w wystąpieniu: to autovacuum, checkpointer, procesy związane z replikacją, background writer. Każdego z nich będę omawiać w miarę postępu wystąpienia.
Jakie problemy są związane ze statystyką?
- Jest wiele informacji. PostgreSQL 9.4 oferuje 109 metryk do przeglądania danych statystycznych. Jednak jeśli w bazie danych przechowywana jest wiele tabel, schematów, baz, to wszystkie te metryki należy pomnożyć przez odpowiednią liczbę tabel i baz. Oznacza to, że informacji staje się jeszcze więcej. Łatwo można się w tym zagubić.
- Następnym problemem jest to, że statystyki są przedstawione jako liczniki. Jeśli spojrzymy na te statystyki, zobaczymy stale rosnące liczniki. Jeśli od momentu zresetowania statystyk minęło dużo czasu, zobaczymy wartości sięgające miliardów. I nic nam to nie mówi.
- Brak historii. Jeśli miałeś awarię, coś się wydarzyło 15-30 minut temu, nie jesteś w stanie skorzystać ze statystyk i sprawdzić, co działo się 15-30 minut temu. To jest problem.
- Brak wbudowanego narzędzia w PostgreSQL to problem. Programiści rdzenia nie dostarczają żadnej użytecznej aplikacji. Nie mają niczego takiego. Po prostu podają statystyki w bazie. Korzystaj, rób zapytania, co chcesz, to rób.
- Skoro brakuje wbudowanego narzędzia w PostgreSQL, staje się to przyczyną innego problemu. Wiele narzędzi zewnętrznych. Każda firma, która ma chociaż trochę umiejętności, próbuje stworzyć swój własny program. W efekcie w społeczności pojawia się wiele narzędzi, z których można korzystać do pracy ze statystyką. W niektórych narzędziach są jedne możliwości, w innych niektóre są niedostępne albo pojawiają się nowe opcje. Powstaje sytuacja, w której trzeba wykorzystać dwa, trzy, cztery narzędzia, które się pokrywają i posiadają różne funkcje. To jest bardzo nieprzyjemne.
Co z tego wynika? Ważne jest, aby potrafić bezpośrednio zbierać statystyki, aby nie być uzależnionym od programów, lub jakoś samodzielnie ulepszać te programy: dodać jakieś funkcje, aby uzyskać korzyści.
Potrzebna jest podstawowa wiedza z zakresu SQL. Aby uzyskać dane ze statystyk, musisz napisać zapytania SQL, tzn. musisz wiedzieć, jak tworzy się polecenia select i join.
Statystyka oferuje nam kilka rzeczy. Można je podzielić na kategorie.
- Pierwsza kategoria to zdarzenia zachodzące w bazie. To momenty, gdy w bazie dzieje się coś: zapytanie, odwołanie do tabeli, autowakuum, zmiany zatwierdzenia — to wszystko są zdarzenia. Związane z tymi zdarzeniami liczniki są inkrementowane. Możemy śledzić te zdarzenia.
- Druga kategoria to właściwości obiektów, takich jak tabele czy bazy. Mają one swoje właściwości. To rozmiar tabel. Możemy śledzić wzrost tabel, wzrost indeksów. Możemy również obserwować zmiany w dynamice.
- Trzecia kategoria to czas poświęcony na zdarzenie. Zapytanie to zdarzenie. Ma swoją konkretną miarę długości. Tutaj się rozpoczęło, tu się zakończyło. Możemy to śledzić. Może to być również czas odczytu bloku z dysku lub zapisu. Takie rzeczy również są monitorowane.
Źródła statystyki są przedstawione w następujący sposób:
- W pamięci współdzielonej (shared buffers) znajduje się segment przeznaczony na statyczne dane, są tam również te same liczniki, które są stale inkrementowane, gdy zachodzą różne zdarzenia lub pojawiają się jakieś problemy w pracy bazy.
- Wszystkie te liczniki nie są dostępne dla użytkownika, a nawet dla administratora. To rzeczy na niskim poziomie. Aby się do nich odwołać, PostgreSQL udostępnia interfejs w postaci funkcji SQL. Możemy wykonywać zapytania przy użyciu tych funkcji i uzyskać jakąś metrykę (lub zestaw metryk).
- Jednak korzystanie z tych funkcji nie zawsze jest wygodne, dlatego funkcje stanowią podstawę dla widoków (VIEW). To wirtualne tabele, które dostarczają statystykę dla konkretnego podsystemu lub zestawu zdarzeń w bazie danych.
- Te wbudowane widoki (VIEW) są podstawowym interfejsem użytkownika do pracy ze statystyką. Są dostępne domyślnie bez jakiejkolwiek dodatkowej konfiguracji, możesz od razu z nich korzystać, przeglądać, czerpać z nich informacje. A także są dostępne tarcze. Tarcze są oficjalne. Możesz zainstalować pakiet postgresql-contrib (na przykład postgresql94-contrib), załadować wymagany moduł w konfiguracji, określić dla niego parametry, zrestartować PostgreSQL i można korzystać. (Uwaga. W zależności od dystrybucji, w najnowszych wersjach pakiet contrib jest częścią pakietu głównego.).
- Istnieją także nieoficjalne contrib. Nie są dostarczane w standardowej wersji PostgreSQL. Trzeba je albo skompilować, albo zainstalować jako bibliotekę. Opcje mogą być różne, w zależności od tego, co wymyślił twórca tego nieoficjalnego contrib’a.
Na tym slajdzie przedstawione są wszystkie te widoki (VIEWs) i część funkcji dostępnych w PostgreSQL 9.4. Jak widzimy, jest ich bardzo dużo. Dość łatwo można się w tym pogubić, jeśli spotykamy się z tym po raz pierwszy.
Jednak jeśli weźmiemy poprzedni obrazek Jak spędzamy czas na PostgreSQL i porównamy to z tą listą, otrzymamy taki obrazek. Każdy widok (VIEWs) lub każda funkcja może być używana w różnych celach do uzyskania odpowiednich statystyk, gdy PostgreSQL jest aktywny. Już możemy uzyskać pewne informacje na temat działania podsystemu.
Pierwszą rzeczą, którą rozważymy, jest pg_stat_database. Jak widzimy, jest to widok. Zawiera bardzo dużo informacji. Różnorodne informacje. Dostarcza bardzo przydatnej wiedzy na temat tego, co dzieje się w bazie danych.
Co możemy z tego użytecznego wyciągnąć? Zacznijmy od najprostszych rzeczy.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Pierwszą rzeczą, którą możemy sprawdzić, jest procent trafień w pamięci podręcznej. Procent trafień w pamięci podręcznej to przydatna metryka. Pozwala ocenić, jaka część danych jest pobierana z pamięci podręcznej shared buffers, a jaka część odczytywana jest z dysku.
Oczywiście im więcej mamy trafień w pamięci podręcznej, tym lepiej. Oceniamy tę metrykę jako procent. Na przykład, jeśli stosunek tych trafień w pamięci podręcznej wynosi ponad 90 %, to jest to dobre. Jeśli spada poniżej 90 %, oznacza to, że mamy niewystarczającą pamięć do przechowywania „gorących” danych w pamięci. Aby z tych danych korzystać, PostgreSQL zmuszony jest odwoływać się do dysku, co jest wolniejsze niż odczyt danych z pamięci. Już trzeba myśleć o zwiększeniu pamięci: albo zwiększyć shared buffers, albo powiększyć pamięć operacyjną (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Co jeszcze można wyciągnąć z tego widoku? Można zobaczyć anomalie zachodzące w bazie. Co tutaj pokazano? Mamy commits, rollbacks, tworzenie plików tymczasowych, ich rozmiar, deadlocks oraz konflikty.
Możemy skorzystać z tego zapytania. Ten SQL jest dość prosty. I możemy zobaczyć te dane u siebie.
I tutaj od razu progi. Patrzymy na stosunek commits i rollbacks. Commits to udane zatwierdzenie transakcji. Rollbacks to wycofanie, tzn. transakcja wykonała jakieś operacje, obciążyła bazę, coś obliczyła, a potem wystąpił błąd i wyniki transakcji zostały odrzucone. Tzn. liczba rollbacks, która ciągle wzrasta, jest zła. I należy w jakiś sposób ich unikać i poprawiać kod, aby tak się nie działo.
Konflikty są związane z replikacją. I ich również należy unikać. Jeśli masz jakieś zapytania, które wykonywane są na replikach i pojawiają się konflikty, musisz te konflikty analizować, patrzeć, co się dzieje. Szczegóły można znaleźć w logach. I rozwiązywać sytuacje konfliktowe, aby zapytania aplikacji działały bez błędów.
Deadlocki to również zła sytuacja. Kiedy zapytania walczą o zasoby, jedno zapytanie uzyskuje blokadę na jednym zasobie, drugie zapytanie uzyskuje blokadę na drugim zasobie, a potem oba zapytania chcą uzyskać dostęp do zasobów wzajemnie się blokując. To również jest problematyczna sytuacja. Należy je rozwiązywać na poziomie przepisania aplikacji i serializacji dostępu do zasobów. I jeśli widzisz, że twoje deadlocki ciągle się zwiększają, musisz sprawdzić szczegóły w logach, analizować występujące sytuacje i zobaczyć, w czym tkwi problem.
Pliki tymczasowe to również zła sprawa. Kiedy zapytaniu użytkownika brakuje pamięci na przechowanie danych operacyjnych, tworzy plik na dysku. I wszystkie operacje, które mógłby wykonać w buforze tymczasowym w pamięci, zaczyna wykonywać już na dysku. To jest wolne. To wydłuża czas wykonania zapytania. I klient, który wysłał zapytanie do PostgreSQL, otrzyma odpowiedź nieco później. Jeśli te wszystkie operacje będą wykonywane w pamięci, Postgres odpowie znacznie szybciej i klient będzie mniej czekał.
Pg_stat_bgwriter to widok opisujący pracę dwóch podsystemów w tle PostgreSQL: to checkpointer i background writer.
Na początek omówimy punkty kontrolne, tzw. checkpoints. Czym są punkty kontrolne? Punkt kontrolny to pozycja w dzienniku transakcji, która informuje, że wszystkie zmiany danych zapisane w dzienniku zostały pomyślnie zsynchronizowane z danymi na dysku. Proces ten, w zależności od obciążenia i ustawień, może być czasochłonny i w dużej mierze polega na synchronizacji zanieczyszczonych stron w pamięci podręcznej z plikami danych na dysku. Po co to potrzebne? Gdyby PostgreSQL przez cały czas odwoływał się do dysku, aby pobierać i zapisywać dane za każdym razem, byłoby to wolne. Dlatego PostgreSQL ma segment pamięci, którego rozmiar zależy od parametrów w konfiguracji. Postgres umieszcza w tej pamięci operacyjne dane do późniejszego przetwarzania lub wystawiania na żądania. W przypadku żądań zmiany danych dochodzi do ich modyfikacji. I otrzymujemy dwie wersje danych. Jedna znajduje się w pamięci, druga na dysku. Okresowo musimy zsynchronizować te dane. Musimy to, co zmieniono w pamięci, zsynchronizować na dysk. W tym celu potrzebny jest punkt kontrolny.
Punkt kontrolny przechodzi przez pamięć podręczną, zaznacza zanieczyszczone strony, które potrzebne są do punktu kontrolnego. Następnie uruchamia drugi przebieg przez pamięć podręczną. I strony, które zostały zaznaczone do punktu kontrolnego, są już synchronizowane. W ten sposób odbywa się synchronizacja danych z dyskiem.
Istnieją dwa typy punktów kontrolnych. Jeden punkt kontrolny odbywa się na podstawie czasu. To punkt kontrolny, który jest użyteczny i dobry - checkpoint_timed. A punkty kontrolne na żądanie - checkpoint required. Taki punkt kontrolny występuje, gdy mamy do czynienia z bardzo dużą ilością zapisów danych. Zapisaliśmy bardzo dużo dzienników transakcji. I PostgreSQL uważa, że musi to wszystko jak najszybciej zsynchronizować, zrobić punkt kontrolny i kontynuować.
I jeśli spojrzysz na statystyki pg_stat_bgwriter i zobaczysz, że masz checkpoint_req znacznie większe niż checkpoint_timed, to źle. Dlaczego źle? To oznacza, że PostgreSQL znajduje się w ciągłej sytuacji stresowej, w której musi zapisywać dane na dysk. Punkt kontrolny na podstawie czasu jest mniej stresujący i odbywa się zgodnie z wewnętrznym harmonogramem, rozciągając się w czasie. PostgreSQL ma możliwość robienia przerw w działaniu i nie obciążania systemu dyskowego. To jest korzystne dla PostgreSQL. A żądania, które są wykonywane podczas punktu kontrolnego, nie będą odczuwać stresu z powodu zajętości systemu dyskowego.
Aby dostosować punkt kontrolny, istnieją trzy parametry:
checkpoint_segments.checkpoint_timeout.checkpoint_competion_target.
Pozwalają one kontrolować działanie punktów kontrolnych. Nie będę się na nich zatrzymywał. Ich wpływ to osobny temat.
Uwaga: Rozpatrywana w raporcie wersja 9.4 jest już nieaktualna. W nowoczesnych wersjach PostgreSQL parametr checkpoint_segments został zastąpiony przez parametry min_wal_size i max_wal_size.
Następny system to background writer — background writer. Co on robi? Działa non-stop w nieskończonej pętli. Skanuje strony w shared buffers i brudne strony, które znajdzie, zapisuje na dysk. W ten sposób pomaga checkpointerowi w wykonywaniu mniejszej ilości pracy podczas punktów kontrolnych.
Do czego jest jeszcze potrzebny? Zapewnia potrzebę czystych stron w shared buffers, jeśli nagle będą potrzebne (w dużej ilości i od razu) do przechowywania danych. Załóżmy, że w sytuacji, gdy do wykonania zapytania potrzebne były czyste strony i już są w shared buffers. Backend PostgreSQL backend po prostu je bierze i używa, nie musi niczego sam czyścić. Ale jeśli nagle takich stron nie ma, backend wstrzymuje swoją pracę i zaczyna szukać stron, które można zapisać na dysk i wykorzystać do swoich potrzeb — co negatywnie wpływa na czas wykonywanego w tym momencie zapytania. Jeśli widzisz, że twój parametr maxwritten_clean jest wysoki, to oznacza, że background writer nie radzi sobie ze swoją pracą i należy zwiększyć parametry bgwriter_lru_maxpages, aby mógł w jednym cyklu wykonać więcej pracy, więcej oczyścić stron.
Kolejny bardzo przydatny wskaźnik to buffers_backend_fsync. Backendy nie wykonują fsync, ponieważ jest to wolne. Przekazują fsync wyżej w stosie IO do checkpointera. Checkpointer ma swoją kolejkę, okresowo przetwarza fsync i synchronizuje strony w pamięci z plikami na dysku. Jeśli kolejka u checkpointera jest duża i zapełniona, to backend jest zmuszony samodzielnie wykonać fsync i to spowalnia pracę backendu, tj. klient otrzyma odpowiedź później, niż mógłby. Jeśli widzisz, że ta wartość jest większa od zera, to już jest problem i należy zwrócić uwagę na ustawienia background writera oraz ocenić wydajność systemu dyskowego.
Uwaga: _Następujący tekst opisuje reprezentacje statystyczne związane z replikacją. Większość nazw reprezentacji i funkcji została zmieniona w Postgres 10. Istota zmian polegała na zastąpieniu xlog na wal i lokacja na lsn w nazwach funkcji/reprezentacji itp. Przykładem jest funkcja pg_xlog_location_diff() która została przemianowana na pg_wal_lsn_diff()._
Tutaj mamy też dużo informacji. Ale potrzebujemy tylko punktów związanych z lokacją.
Jeśli widzimy, że wszystkie wartości są równe, to jest to idealny stan i replika nie opóźnia się w stosunku do mastera.
Ta heksadecymalna pozycja to pozycja w dzienniku transakcji. Nieustannie się zwiększa, jeśli w bazie są jakieś działania: dodania, usunięcia itd.
ile zapisano xlog w bajtach
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
opóźnienie replikacji w bajtach
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
opóźnienie replikacji w sekundach
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Jeśli te wartości się różnią, to znaczy, że jest jakieś opóźnienie. Opóźnienie to odstępstwo repliki od mastera, czyli dane różnią się między serwerami.
Są trzy przyczyny opóźnienia:
- To dyskowa substancja nie radzi sobie z synchronizacją zapisu plików.
- To mogą być problemy z siecią, lub przeciążenie sieci, gdy dane nie docierają na czas do repliki i nie mogą być odtworzone.
- I procesor. Procesor to bardzo rzadki przypadek. Widziałem to dwa lub trzy razy, ale to też się zdarza.
I oto trzy zapytania, które pozwalają nam wykorzystać statystykę. Możemy ocenić, ile zapisano w dzienniku transakcji. Istnieje taka funkcja pg_xlog_location_diff i możemy ocenić opóźnienie replikacji w bajtach i sekundach. Również do tego używamy wartości z tego widoku (VIEWs).
Uwaga: _Zamiast pg_xlog_locationdiff() funkcji można użyć operatora odejmowania i odjąć jedną lokację od drugiej. To wygodne.
Z opóźnieniem, które wyrażone jest w sekundach, jest jedna uwaga. Jeśli na masterze nie występuje żadna aktywność, transakcja miała miejsce gdzieś 15 minut temu i nie ma żadnej aktywności, a jeśli spojrzymy na to opóźnienie na replice, zobaczymy opóźnienie równe 15 minutom. Warto o tym pamiętać. I to może wprowadzać w zakłopotanie, gdy spojrzysz na to opóźnienie.
Pg_stat_all_tables – to kolejne przydatne widok. Pokazuje statystyki dotyczące tabel. Kiedy mamy tabele w bazie i zachodzi w nich jakaś aktywność lub działania, możemy uzyskać te informacje z tego widoku.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;Pierwszą rzeczą, którą możemy sprawdzić, jest liczba skanowań sekwencyjnych w tabeli. Sama liczba po tych skanowaniach niekoniecznie jest zła i nie jest wskazówką, że musimy już coś przedsięwziąć.
Jest jednak druga metryka – seq_tup_read. To liczba wierszy zwróconych w wyniku sekwencyjnego skanowania. Jeśli średnia liczba przekracza 1 000, 10 000, 50 000, 100 000, to już jest wskaźnik, że być może należy gdzieś utworzyć indeks, aby dostęp był realizowany przez indeks, lub może optymalizować zapytania, które wykorzystują takie skanowania sekwencyjne, aby ich unikać.
Prosty przykład – załóżmy, że zapytanie z dużym OFFSET i LIMIT jest wykonane. Na przykład skanowane jest 100 000 wierszy w tabeli, a potem pobierane jest 50 000 potrzebnych wierszy, podczas gdy poprzednio zeskanowane wiersze są odrzucane. To także zły przypadek. I takie zapytania należy optymalizować. I tutaj taki prosty SQL, na którym można to zobaczyć i ocenić uzyskane liczby.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Rozmiary tabel można również uzyskać za pomocą tej tabeli i dodatkowych funkcji. pg_total_relation_size(), pg_relation_size().
W ogóle istnieją metakomendy dt i di, które można wykorzystać w PSQL, aby również sprawdzić rozmiary tabel i indeksów.
Jednak korzystanie z funkcji pozwala nam na sprawdzenie rozmiarów tabel z uwzględnieniem indeksów lub bez uwzględnienia indeksów i podejmowanie ocen na podstawie wzrostu bazy danych, tj. jak ona rośnie, z jaką intensywnością oraz wyciąganie wniosków na temat optymalizacji rozmiarów.
Aktywność zapisu. Czym jest zapis? Przyjrzyjmy się operacji. UPDATE – operacji aktualizacji wierszy w tabeli. W zasadzie aktualizacja to dwie operacje (lub nawet więcej). To wstawienie nowej wersji wiersza i oznaczenie starej wersji wiersza jako przestarzałej. Następnie pojawi się automatyczne czyszczenie, które usunie te przestarzałe wersje wierszy i oznaczy to miejsce jako dostępne do ponownego wykorzystania.
Co więcej, aktualizacja to nie tylko aktualizacja tabeli. To również aktualizacja indeksów. Jeśli twoja tabela ma wiele indeksów, to przy aktualizacji wszystkie indeksy, w których uczestniczą pola aktualizowane w zapytaniu, muszą być również zaktualizowane. W tych indeksach znajdą się również przestarzałe wersje wierszy, które należy oczyścić.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;I przez swój projekt, aktualizacje to ciężkie operacje. Ale można je uprościć. Są hot updates. Pojawiły się w wersji PostgreSQL 8.3. I co to oznacza? To lekka aktualizacja, która nie powoduje przebudowy indeksów. To znaczy, że zaktualizowaliśmy rekord, ale aktualizowany jest tylko rekord na stronie (która należy do tabeli), a indeksy wciąż wskazują na ten sam rekord na stronie. Tam działa ciekawa logika, gdy przychodzi proces czyszczenia, przekształca te ciągi hot i wszystko działa dalej bez aktualizacji indeksów, a wszystko odbywa się z mniejszym zużyciem zasobów.
A gdy masz n_tup_hot_upd dużo, to bardzo dobrze. To oznacza, że lekkie aktualizacje dominują, a to wychodzi nam korzystniej pod względem zasobów i wszystko działa doskonale.
ALTER TABLE table_name SET (fillfactor = 70);Jak zwiększyć objętość hot updateów? Możemy skorzystać z fillfactor. Określa on rozmiar rezerwy wolnego miejsca podczas wypełniania strony w tabeli za pomocą INSERTs. Kiedy dane są wprowadzane do tabeli, w pełni wypełniają stronę, nie pozostawiając pustych miejsc. Następnie przypisywana jest nowa strona. Ponownie dane są wypełniane. I to zachowanie jest domyślne, fillfactor = 100 %.
Możemy ustawić fillfactor na 70%. To znaczy, że przy dodawaniu zostanie przydzielona nowa strona, ale wypełniona zostanie tylko 70% tej strony. 30% pozostaje w rezerwie. Kiedy zajdzie potrzeba aktualizacji, z dużym prawdopodobieństwem nastąpi ona na tej samej stronie, a nowa wersja wiersza znajdzie się na tej samej stronie. Zostanie przeprowadzone hot_update. W ten sposób ułatwiamy zapis w tabelach.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Kolejka autovakuum. Autovakuum to taka podsystem, w której statystyki w PostgreSQL są bardzo ubogie. Możemy w tabelach tylko w pg_stat_activity zobaczyć, ile mamy wakua w trakcie tej chwili. Jednak zrozumienie, ile tabel jest w kolejce dla niego, jest bardzo trudne.
Uwaga: _Od wersji Postgres 10 sytuacja z monitoringiem autovakuum znacznie się poprawiła — pojawił się widok pg_stat_progressvacuum, który znacznie upraszcza kwestię monitorowania autovakuum.
Możemy użyć takiego uproszczonego zapytania. I możemy zobaczyć, kiedy powinno zostać przeprowadzone wakum. Ale kiedy i jak powinno uruchomić się wakum? Te przestarzałe wersje wierszy, o których mówiłem wcześniej. Nastąpiła aktualizacja, nowa wersja wiersza została wstawiona. Pojawiła się przestarzała wersja wiersza. W tabeli pg_stat_user_tables jest taki parametr n_dead_tup. Pokazuje liczbę "martwych" wierszy. I gdy tylko liczba martwych wierszy wzrośnie ponad ustalony próg, do tabeli przystąpi autovakuum.
A jak ten próg jest obliczany? To dość konkretna procentowa proporcja w stosunku do całkowtej liczby wierszy w tabeli. Istnieje parametr autovacuum_vacuum_scale_factor. On określa proporcję procentową. Załóżmy, 10% + dodatkowy podstawowy próg 50 wierszy. I co się dzieje? Kiedy martwych wierszy jest więcej niż "10% + 50" wszystkich wierszy w tabeli, to ustawiamy tabelę na autovakuum.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Jednak jest jeden aspekt. Podstawowe progi dla parametrów av_base_thresh i av_scale_factor mogą być ustalane indywidualnie. Odpowiednio, próg będzie nie globalny, a indywidualny dla tabeli. Żeby to obliczyć, trzeba zastosować pewne sztuczki i triki. A jeśli jesteś ciekawy, możesz spojrzeć na doświadczenia naszych kolegów z Avito (link na slajdzie jest nieaktualny i został zaktualizowany w tekście).
Napisali dla , która uwzględnia te kwestie. To spory dokument na dwa arkusze. Ale oblicza poprawnie i dość efektywnie pozwala ocenić, gdzie mamy dużo, a gdzie mało wymagających tabel.
Co możemy z tym zrobić? Jeśli mamy dużą kolejkę i auto-vakuum nie radzi sobie, możemy zwiększyć liczbę pracowników vacuum, albo po prostu uczynić vacuum bardziej agresywnym, żeby wywoływał się wcześniej, przetwarzał tabelę małymi kawałkami. I w ten sposób kolejka będzie się zmniejszać. — Najważniejsze jest monitorowanie obciążenia dysków, ponieważ vacuum nie jest darmowe, chociaż z pojawieniem się urządzeń SSD/NVMe problem stał się mniej zauważalny.
Pg_stat_all_indexes – to statystyki dotyczące indeksów. To niewielkie dane. Na ich podstawie możemy uzyskać informacje o wykorzystaniu indeksów. I na przykład możemy określić, które indeksy są dla nas zbędne.
Jak już mówiłem, update – to nie tylko aktualizacja tabel, ale także aktualizacja indeksów. Odpowiednio, jeśli mamy dużo indeksów w tabeli, to podczas aktualizacji wierszy w tabeli indeksy zweryfikowanych pól także muszą zostać zaktualizowane, a jeśli mamy nieużywane indeksy, dla których nie ma skanowań indeksowych, to są one dla nas ciężarem. Należy się ich pozbyć. W tym celu potrzebujemy pola idx_scan. Po prostu sprawdzamy liczbę skanowań indeksów. Jeśli indeksy mają zero skanowań przez dość długi okres przechowywania statystyk (nie krótszy niż 2-3 tygodnie), to prawdopodobnie są to złe indeksy, powinniśmy się ich pozbyć.
Uwaga: Podczas szukania nieużywanych indeksów w przypadku klastrów replikacji strumieniowej należy sprawdzić wszystkie węzły klastra, ponieważ statystyki nie są globalne. Jeśli indeks nie jest używany na masterze, może być używany na replikach (jeśli tam jest obciążenie).
Dwa linki:
To bardziej zaawansowane przykłady zapytań dotyczących tego, jak szukać nieużywanych indeksów.
Drugi link to dość interesujące zapytanie. Jest w nim bardzo nietrywialna logika. Polecam się z nim zapoznać.
Co jeszcze warto podsumować w kwestii indeksów?
Nieużywane indeksy to zły pomysł.
Zajmują miejsce.
Spowalniają operacje aktualizacji.
To dodatkowa praca dla vacuuma.
Jeśli usuniemy nieużywane indeksy, nasza baza stanie się tylko lepsza.
Następna prezentacja to pg_stat_activity. To odpowiednik narzędzia ps, tylko w PostgreSQL. Jeśli pswidzisz procesy w systemie operacyjnym, to pg_stat_activity pokaże ci aktywność wewnątrz PostgreSQL.
Co możemy stamtąd użytecznego wziąć?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Możemy zobaczyć ogólną aktywność, co dzieje się w bazie. Możemy przeprowadzić nowy wdrożenie. Jeśli wszystko się zawaliło, nowe połączenia nie są przyjmowane, w aplikacji wyświetlają się błędy.
select
client_addr, usename, datname, count(*);
from pg_stat_activity group by 1, 2, 3 order by 4 desc;Możemy wykonać takie zapytanie i zobaczyć ogólny procent połączeń w odniesieniu do maksymalnego limitu połączeń i zobaczyć, kto zajmuje najwięcej połączeń. W przedstawionym przypadku widzimy, że użytkownik cron_role otworzył 508 połączeń. Coś się z nim wydarzyło. Musimy się z tym zająć i przyjrzeć. I możliwe, że to jakieś anormalne liczby połączeń.
Jeśli mamy obciążenie OLTP, zapytania powinny być wykonywane szybko, bardzo szybko i nie powinno być długich zapytań. Jednak jeśli pojawiają się długie zapytania, to w krótkim okresie nie ma to wielkiego znaczenia, ale w dłuższym okresie długie zapytania szkodzą bazie, zwiększają efekt bloat tabel, gdy następuje fragmentacja tabel. Zarówno od bloat, jak i od długich zapytań należy się uwolnić.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Zwróć uwagę: tym zapytaniem możemy zidentyfikować długie zapytania i transakcje. Używamy funkcji clock_timestamp() do określenia czasu działania. Długie zapytania, które znaleźliśmy, możemy zapamiętać, wykonać explain, zobaczyć plany i jakoś je zoptymalizować. Aktualne długie zapytania kończymy i żyjemy dalej.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Złe transakcje to te w stanie idle in transaction oraz idle in transaction (aborted).
Co to oznacza? Transakcje mają kilka stanów. I jeden z tych stanów mogą przyjmować w dowolnym momencie. Aby określić stany, mamy pole stan w tej prezentacji. I używamy go do określenia stanu.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';I, jak już wspomniałem wcześniej, te dwa stany idle in transaction i idle in transaction (aborted) – to źle. Co to oznacza? To sytuacja, kiedy aplikacja otworzyła transakcję, wykonała pewne działania i poszła swoimi sprawami. Transakcja pozostała otwarta. Zawiesza się, nic się w niej nie dzieje, zajmuje połączenie, blokuje zmienione wiersze i potencjalnie zwiększa bloat innych tabel, z powodu architektury silnika transakcyjnego Postgresa. Takie transakcje również powinny być eliminowane, ponieważ są szkodliwe w każdej sytuacji.
Jeśli widzisz, że masz ich w bazie więcej niż 5-10-20, to już powinieneś się zaniepokoić i zacząć coś z nimi robić.
Tutaj także do obliczeń czasowych używamy clock_timestamp(). Eliminujemy transakcje, optymalizujemy aplikację.
Jak już powiedziałem wcześniej, blokady – to sytuacja, gdy dwie lub więcej transakcji walczy o jeden lub grupę zasobów. W tym celu mamy pole czekanie z wartością boolean true lub false.
True – to oznacza, że proces czeka, trzeba coś zrobić. Kiedy proces czeka, oznacza to, że klient, który zainicjował ten proces, również czeka. Klient w przeglądarce siedzi i też czeka.
Uwaga: _Począwszy od wersji Postgres 9.6, pole czekanie zostało usunięte, a zamiast niego dodano dwa bardziej informacyjne pola typ_zdarzenia_oczekiwania i zdarzenie_oczekiwania._
Co robić? Jeśli widzisz true przez długi czas, to oznacza, że należy pozbyć się takich zapytań. Po prostu eliminujemy takie transakcje. Pisujemy do programistów, że trzeba w jakiś sposób optymalizować, aby nie było wyścigu o zasoby. A następnie programiści optymalizują aplikację, aby to się nie zdarzało.
I ostatni, aczkolwiek potencjalnie niefatalny przypadek – to pojawienie się deadlocków. Dwie transakcje zaktualizowały dwa zasoby, a potem znów się do nich odwołują, już do przeciwnych zasobów. W takim przypadku PostgreSQL sam eliminuje transakcję, aby druga mogła kontynuować pracę. To sytuacja martwego punktu, która sama się nie rozwiązuje. Dlatego PostgreSQL zmuszony jest do przyjęcia skrajnych środków.
Oto dwa zapytania, które pozwalają śledzić blokady. Używamy widoku pg_locks, która umożliwia śledzenie ciężkich blokad.
A pierwszy link to sam tekst zapytania. Jest dość długi.
A drugi link to artykuł o blokadach. Warto go przeczytać, jest bardzo interesujący.
Więc co widzimy? Widzimy dwa zapytania. Transakcja z ALTER TABLE – to blokująca transakcja. Została uruchomiona, ale nie zakończona, a aplikacja uruchamiająca tę transakcję gdzieś zajmuje się innymi sprawami. A drugie zapytanie – to aktualizacja. Czeka, aż zakończy się zmiana tabeli, aby móc kontynuować swoją pracę.
W ten sposób możemy ustalić, kto kogo zablokował, i możemy się tym dalej zająć.
Następny moduł to pg_stat_statements. Jak już wspomniałem, to moduł. Aby go użyć, należy załadować jego bibliotekę w konfiguracji, zrestartować PostgreSQL, zainstalować moduł (jedną komendą) i wtedy uzyskamy nowe widok.
Średni czas zapytania w milisekundach
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Najbardziej aktywne piszące (w shared_buffers) zapytania
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Co możemy z tego wziąć? Mówiąc o prostych rzeczach, możemy wziąć średni czas wykonania zapytania. Czas rośnie, to znaczy, że PostgreSQL odpowiada wolno i musimy coś zrobić.
Możemy sprawdzić najbardziej aktywne piszące transakcje w bazie danych, które zmieniają dane w shared buffers. Zobaczyć, kto aktualizuje lub usuwa dane.
I możemy po prostu spojrzeć na różne statystyki tych zapytań.
My pg_stat_statements używamy do generowania raportów. Co 24 godziny zresetowujemy statystyki. Akumulujemy je. Przed kolejnym resetem statystyk budujemy raport. Oto link do raportu. Możesz go zobaczyć.
Co robimy? Liczymy łączną statystykę dla wszystkich zapytań. Następnie dla każdego zapytania obliczamy jego indywidualny wkład w tę ogólną statystykę.
I co możemy zobaczyć? Możemy zobaczyć całkowity czas wykonania wszystkich zapytań danego typu w odniesieniu do wszystkich innych zapytań. Możemy zobaczyć wykorzystanie zasobów procesora i operacji wejścia/wyjścia w kontekście całego obrazu. I już optymalizować te zapytania. Budujemy ranking zapytań na podstawie tego raportu, a następnie otrzymujemy materiał do przemyśleń, co optymalizować.
Co zostało w tle? Pozostało jeszcze kilka prezentacji, które postanowiłem pominąć, ponieważ czas jest ograniczony.
Tak pgstattuple – to dodatkowy moduł z pakietu standardowego contribs. Pozwala ocenić bloat tabel, tzw. fragmentację tabeli. I jeśli fragmentacja jest duża, należy ją usunąć, korzystając z różnych narzędzi. A funkcja pgstattuple działa długo. Im więcej tabel, tym dłużej to zajmie.
Następnym contribem jest pg_buffercache. Umożliwia inspekcję shared buffers: jak intensywnie i do jakich tabel wykorzystane są strony bufora. Po prostu pozwala zajrzeć do shared buffers i ocenić, co się tam dzieje.
Następny moduł to pgfincore. Umożliwia niskopoziomowe operacje na tabelach za pomocą wywołania systemowego mincore(), tj. pozwala na załadowanie tabeli do shared buffers lub jej zrzucenie. Oprócz tego umożliwia inspekcję pamięci stronicowej systemu operacyjnego, tj. jaką przestrzeń zajmuje tabela w page cache, w shared buffers i po prostu pozwala ocenić obciążenie tabeli.
Następnym modułem jest pg_stat_kcache. Używa również wywołania systemowego getrusage(). Wykonuje je przed i po wykonaniu zapytania. W uzyskanej statystyce pozwala ocenić, ile czasu zapytanie zajęło na operacje wejścia/wyjścia z dysku, tj. operacje z systemem plików i monitoruje wykorzystanie procesora. Moduł jest jednak młody (ahem) i do działania wymaga PostgreSQL 9.4 oraz pg_stat_statements, o którym wspominałem wcześniej.
Umiejętność korzystania ze statystyki jest przydatna. Nie potrzebujesz zewnętrznych programów. Możesz samodzielnie zajrzeć, sprawdzić, coś zrobić, wykonać.
Korzystanie ze statystyki jest proste, to zwykły SQL. Zebranie zapytania, skonstruowanie, wysłanie, sprawdzenie.
Statystyka pomaga odpowiedzieć na pytania. Gdy masz pytania, zwracasz się ku statystyce – patrzysz, wyciągasz wnioski, analizujesz wyniki.
I eksperymentuj. Jest wiele zapytań, wiele danych. Zawsze można zoptymalizować jakieś istniejące zapytanie. Można stworzyć swoją wersję zapytania, która bardziej ci odpowiada niż oryginał i jej używać.
Linki
Ciekawe linki, które pojawiły się w artykule, na podstawie którego była prezentacja.
Autor pisz więcej
(eng)
Kolektor statystyk
Funkcje administracji systemem
Moduły Contrib
Narzędzia SQL i przykłady kodu SQL
Dziękuję wszystkim za uwagę!
Źródło: habr.com
