Transkrypcja raportu Alexeya Lesovsky'ego z 2015 r. „Głębokie zanurzenie się w wewnętrznych statystykach PostgreSQL”
Zastrzeżenie od autora raportu: Zwracam uwagę, że ten raport jest datowany na listopad 2015 r. - minęły ponad 4 lata i minęło dużo czasu. Wersja 9.4 omówiona w raporcie nie jest już obsługiwana. W ciągu ostatnich 4 lat ukazało się 5 nowych wydań, w których pojawiło się wiele innowacji, ulepszeń i zmian dotyczących statystyk, a część materiałów jest nieaktualna i nieaktualna. Recenzując starałem się zaznaczyć te miejsca, aby nie wprowadzać czytelnika w błąd. Nie przepisałem tych miejsc, jest ich dużo, w wyniku czego wyjdzie zupełnie inny raport.
PostgreSQL DBMS to ogromny mechanizm, a mechanizm ten składa się z wielu podsystemów, których skoordynowana praca bezpośrednio wpływa na wydajność DBMS. W trakcie działania zbierane są statystyki i informacje o działaniu komponentów, co pozwala na ocenę skuteczności PostgreSQL i podjęcie działań w celu poprawy wydajności. Tych informacji jest jednak bardzo dużo i podane są w dość uproszczonej formie. Przetwarzanie tych informacji i ich interpretacja jest czasami zadaniem zupełnie nietrywialnym, a „zoo” narzędzi i programów narzędziowych może łatwo zdezorientować nawet zaawansowanego administratora danych.
Dzień dobry Mam na imię Aleksiej. Jak powiedział Ilya, opowiem o statystykach PostgreSQL.
Statystyki aktywności PostgreSQL. PostgreSQL ma dwie statystyki. Statystyki aktywności, które zostaną omówione. I statystyki harmonogramu dotyczące dystrybucji danych. W szczególności opowiem o statystykach aktywności PostgreSQL, które pozwalają nam ocenić wydajność i jakoś ją poprawić.
Powiem Ci, jak skutecznie wykorzystać statystyki do rozwiązywania różnych problemów, które masz lub możesz mieć.
Czego nie będzie w raporcie? W raporcie nie będę dotykał statystyk harmonogramu, ponieważ. jest to osobny temat na osobny raport o tym, jak dane są przechowywane w bazie danych i jak planista zapytań uzyskuje pojęcie o jakościowych i ilościowych cechach tych danych.
I nie będzie recenzji narzędzi, nie będę porównywał jednego produktu z drugim. Nie będzie reklamy. Rzućmy to.
Chcę Ci pokazać, że korzystanie ze statystyk jest przydatne. Czy to jest to konieczne. Używaj go bez strachu. Wszystko czego potrzebujemy to zwykły SQL i podstawowa znajomość SQL.
Porozmawiamy o tym, które statystyki wybrać, aby rozwiązać problemy.
Jeśli spojrzymy na PostgreSQL i uruchomimy polecenie w systemie operacyjnym, aby wyświetlić procesy, zobaczymy „czarną skrzynkę”. Zobaczymy procesy, które coś robią, a po nazwie możemy z grubsza wyobrazić sobie, co tam robią, co robią. Ale w rzeczywistości jest to czarna skrzynka, do której nie możemy zajrzeć.
Możemy spojrzeć na obciążenie procesora top, możemy zobaczyć wykorzystanie pamięci przez niektóre narzędzia systemowe, ale nie będziemy mogli zajrzeć do PostgreSQL. Do tego potrzebujemy innych narzędzi.
Idąc dalej, powiem ci, gdzie spędza się czas. Jeśli przedstawimy PostgreSQL w postaci takiego schematu, to będzie można odpowiedzieć, gdzie spędza się czas. Są to dwie rzeczy: przetwarzanie żądań klientów z aplikacji i zadania w tle, które wykonuje PostgreSQL, aby utrzymać jego działanie.
Jeśli zaczniemy patrzeć w lewy górny róg, zobaczymy, jak przetwarzane są żądania klientów. Żądanie pochodzi z aplikacji i otwierana jest sesja klienta do dalszej pracy. Żądanie jest przekazywane do programu planującego. Planista tworzy plan zapytań. Wysyła go dalej do wykonania. Istnieje pewien rodzaj blokowych danych we/wy związanych z tabelami i indeksami. Niezbędne dane są odczytywane z dysków do pamięci w specjalnym obszarze zwanym „buforami współdzielonymi”. Wyniki zapytania, jeśli są to aktualizacje, usunięcia, są rejestrowane w dzienniku transakcji w WAL. Niektóre informacje statystyczne trafiają do dziennika lub kolektora statystyk. A wynik żądania jest zwracany klientowi. Następnie klient może powtórzyć wszystko z nowym żądaniem.
Co mamy z zadaniami w tle i procesami w tle? Mamy kilka procesów, które utrzymują bazę danych w normalnym trybie operacyjnym. Te procesy również zostaną omówione w raporcie: są to autovacuum, checkpointer, procesy związane z replikacją, zapis w tle. Dotknę każdego z nich, gdy zdam relację.
Jakie są problemy ze statystykami?
- Mnóstwo informacji. PostgreSQL 9.4 zapewnia 109 metryk do przeglądania danych statystycznych. Jeśli jednak baza danych przechowuje wiele tabel, schematów, baz danych, to wszystkie te metryki trzeba będzie pomnożyć przez odpowiednią liczbę tabel, baz danych. Oznacza to, że jest jeszcze więcej informacji. I bardzo łatwo się w nim utopić.
- Kolejnym problemem jest to, że statystyki są reprezentowane przez liczniki. Jeśli spojrzymy na te statystyki, zobaczymy stale rosnące liczniki. A jeśli od wyzerowania statystyk minęło dużo czasu, zobaczymy miliardy wartości. I nic nam nie mówią.
- Nie ma historii. Jeśli masz jakąś awarię, coś spadło 15-30 minut temu, nie będziesz mógł skorzystać ze statystyk i zobaczyć, co się stało 15-30 minut temu. To jest problem.
- Problemem jest brak narzędzia wbudowanego w PostgreSQL. Twórcy jądra nie udostępniają żadnego narzędzia. Nie mają czegoś takiego. Podają tylko statystyki w bazie danych. Użyj go, złóż do niego prośbę, cokolwiek chcesz, a następnie zrób to.
- Ponieważ w PostgreSQL nie ma wbudowanego narzędzia, powoduje to kolejny problem. Wiele narzędzi stron trzecich. Każda firma, która ma mniej lub bardziej bezpośrednie ręce, próbuje napisać swój własny program. W rezultacie społeczność ma wiele narzędzi, których można użyć do pracy ze statystykami. A w niektórych narzędziach są pewne funkcje, w innych nie ma innych funkcji lub są nowe funkcje. I dochodzi do sytuacji, że trzeba użyć dwóch, trzech lub czterech narzędzi, które nakładają się na siebie i mają różne funkcje. To jest bardzo nieprzyjemne.
Co z tego wynika? Ważne jest, aby móc bezpośrednio pobierać statystyki, aby nie polegać na programach lub samodzielnie ulepszać te programy: dodaj kilka funkcji, aby uzyskać korzyści.
I potrzebujesz podstawowej znajomości języka SQL. Aby uzyskać dane ze statystyk, musisz wykonać zapytania SQL, czyli musisz wiedzieć, jak odbywa się selekcja, łączenie.
Statystyki mówią nam kilka rzeczy. Można je podzielić na kategorie.
- Pierwsza kategoria to zdarzenia mające miejsce w bazie danych. Dzieje się tak, gdy w bazie danych występuje jakieś zdarzenie: zapytanie, dostęp do tabeli, autopróżnia, zatwierdzenia, to wszystko są zdarzenia. Liczniki odpowiadające tym zdarzeniom są zwiększane. I możemy śledzić te wydarzenia.
- Druga kategoria to właściwości obiektów takich jak tabele, bazy danych. Mają właściwości. To jest rozmiar stołów. Możemy śledzić wzrost tabel, wzrost indeksów. Widzimy zmiany w dynamice.
- A trzecia kategoria to czas spędzony na wydarzeniu. Żądanie jest wydarzeniem. Ma swoją własną, specyficzną miarę trwania. Tu się zaczęło, tu się skończyło. Możemy to śledzić. Albo czas odczytu bloku z dysku, albo czas zapisu. Te rzeczy są również śledzone.
Źródła statystyk przedstawiają się następująco:
- W pamięci współdzielonej (buforach współdzielonych) znajduje się segment do umieszczania tam danych statycznych, są też takie liczniki, które na bieżąco inkrementują się, gdy wystąpią określone zdarzenia, lub pojawią się jakieś momenty w działaniu bazy danych.
- Wszystkie te liczniki nie są dostępne dla użytkownika i nie są nawet dostępne dla administratora. To są rzeczy na niskim poziomie. Aby uzyskać do nich dostęp, PostgreSQL zapewnia interfejs w postaci funkcji SQL. Możemy dokonać wyboru za pomocą tych funkcji i uzyskać jakiś rodzaj metryki (lub zestawu metryk).
- Jednak nie zawsze korzystanie z tych funkcji jest wygodne, dlatego podstawą widoków (WIDOKÓW) są funkcje. Są to wirtualne tabele, które udostępniają statystyki dotyczące określonego podsystemu lub zestawu zdarzeń w bazie danych.
- Te wbudowane widoki (WIDOKI) są głównym interfejsem użytkownika do pracy ze statystykami. Są dostępne domyślnie bez żadnych dodatkowych ustawień, możesz od razu z nich korzystać, oglądać, pobierać stamtąd informacje. Są też wkłady. Wkłady są oficjalne. Możesz zainstalować pakiet postgresql-contrib (na przykład postgresql94-contrib), załadować niezbędny moduł w konfiguracji, określić jego parametry, zrestartować PostgreSQL i możesz go używać. (Notatka. W zależności od dystrybucji, w ostatnich wersjach contrib pakiet jest częścią pakietu głównego).
- I są nieoficjalne wkłady. Nie są dostarczane ze standardową dystrybucją PostgreSQL. Muszą być skompilowane lub zainstalowane jako biblioteka. Opcje mogą być bardzo różne, w zależności od tego, co wymyślił twórca tego nieoficjalnego wkładu.
Ten slajd pokazuje wszystkie te widoki (WIDOKI) i niektóre z tych funkcji, które są dostępne w PostgreSQL 9.4. Jak widzimy, jest ich bardzo dużo. I dość łatwo się pomylić, jeśli doświadczasz tego po raz pierwszy.
Jeśli jednak weźmiemy poprzednie zdjęcie Как тратится время на PostgreSQL i zgodne z tą listą, otrzymujemy ten obraz. Każdy widok (WIDOK) lub każdą funkcję możemy wykorzystać w tym czy innym celu, aby uzyskać odpowiednie statystyki, gdy mamy uruchomiony PostgreSQL. I już możemy uzyskać pewne informacje na temat działania podsystemu.
Pierwszą rzeczą, na którą spojrzymy, jest pg_stat_database. Jak widzimy, jest to reprezentacja. Zawiera wiele informacji. Najbardziej zróżnicowane informacje. I daje bardzo użyteczną wiedzę o tym, co dzieje się w bazie danych.
Co możemy stamtąd zabrać? Zacznijmy od najprostszych rzeczy.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Pierwszą rzeczą, na którą możemy spojrzeć, jest procent trafień w pamięci podręcznej. Procent trafień w pamięci podręcznej to przydatna metryka. Pozwala oszacować, ile danych jest pobieranych z pamięci podręcznej buforów współdzielonych, a ile odczytywanych z dysku.
Jest oczywiste, że im więcej mamy trafień w pamięci podręcznej, tym lepiej. Miernik ten oceniamy w procentach. I na przykład, jeśli mamy odsetek tych trafień w pamięci podręcznej większy niż 90%, to jest dobrze. Jeśli spadnie poniżej 90%, oznacza to, że nie mamy wystarczającej ilości pamięci, aby utrzymać w pamięci gorącą ilość danych. Aby skorzystać z tych danych, PostgreSQL jest zmuszony uzyskać dostęp do dysku i jest to wolniejsze niż w przypadku odczytu danych z pamięci. I musisz pomyśleć o zwiększeniu pamięci: albo zwiększ wspólne bufory, albo zwiększ pamięć żelazną (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 wynieść z tej prezentacji? Możesz zobaczyć anomalie występujące w bazie danych. Co jest tutaj pokazane? Są zatwierdzenia, wycofania, tworzenie plików tymczasowych, ich rozmiar, zakleszczenia i konflikty.
Możemy skorzystać z tej prośby. Ten SQL jest całkiem prosty. I sami możemy zobaczyć te dane.
A oto wartości progowe. Patrzymy na stosunek zatwierdzeń i wycofań. Commits to pomyślne potwierdzenie transakcji. Rollbacks to rollback, czyli transakcja wykonała jakąś pracę, obciążyła bazę danych, coś rozważyła, a potem nastąpiła awaria, a wyniki transakcji są odrzucane. tj. stale rosnąca liczba wycofań jest zła. I powinieneś jakoś ich unikać i edytować kod, aby tak się nie stało.
Konflikty są związane z replikacją. I ich też należy unikać. Jeśli masz jakieś zapytania, które są wykonywane na replice i pojawiają się konflikty, musisz przeanalizować te konflikty i zobaczyć, co się stanie. Szczegóły można znaleźć w logach. I rozwiązuj konflikty, aby żądania aplikacji działały bez błędów.
Zakleszczenia to także zła sytuacja. Gdy żądania konkurują o zasoby, jedno żądanie uzyskało dostęp do jednego zasobu i przejęło blokadę, drugie żądanie uzyskało dostęp do drugiego zasobu i również przejęło blokadę, a następnie oba żądania uzyskały dostęp do swoich zasobów i zostały zablokowane, czekając, aż sąsiad zwolni blokadę. To też jest problematyczna sytuacja. Należy się nimi zająć na poziomie przepisywania aplikacji i serializacji dostępu do zasobów. A jeśli widzisz, że Twoje impasy stale rosną, musisz przyjrzeć się szczegółom w logach, przeanalizować zaistniałe sytuacje i zobaczyć, w czym tkwi problem.
Pliki tymczasowe (temp_files) również są złe. Gdy żądanie użytkownika nie ma wystarczającej ilości pamięci, aby pomieścić operacyjne, tymczasowe dane, tworzy plik na dysku. A wszystkie operacje, które mógł wykonać w tymczasowym buforze w pamięci, zaczyna wykonywać już na dysku. To jest powolne. Zwiększa to czas wykonywania zapytania. A klient, który wysłał żądanie do PostgreSQL, otrzyma odpowiedź nieco później. Jeśli wszystkie te operacje są wykonywane w pamięci, Postgres będzie reagował znacznie szybciej, a klient będzie krócej czekał.
pg_stat_bgwriter — ten widok opisuje działanie dwóch podsystemów tła PostgreSQL: checkpointer и background writer.
Na początek przeanalizujmy punkty kontrolne, tzw. checkpoints. Co to są punkty kontrolne? Punkt kontrolny to pozycja w dzienniku transakcji wskazująca, że wszystkie zmiany danych dokonane w dzienniku zostały pomyślnie zsynchronizowane z danymi na dysku. Proces, w zależności od obciążenia i ustawień, może być długotrwały i polega głównie na synchronizacji brudnych stron we współdzielonych buforach z plikami danych na dysku. Po co to jest? Gdyby PostgreSQL cały czas uzyskiwał dostęp do dysku i pobierał stamtąd dane oraz zapisywał dane przy każdym dostępie, byłby powolny. Dlatego PostgreSQL ma segment pamięci, którego rozmiar zależy od parametrów w konfiguracji. Postgres przydziela dane operacyjne w tej pamięci do dalszego przetwarzania lub zapytania. W przypadku żądań zmiany danych są one zmieniane. I otrzymujemy dwie wersje danych. Jeden jest w pamięci, drugi na dysku. I okresowo musisz synchronizować te dane. Potrzebujemy, aby to, co zostało zmienione w pamięci, zostało zsynchronizowane z dyskiem. To wymaga punktu kontrolnego.
Punkt kontrolny przechodzi przez wspólne bufory, zaznacza brudne strony, które są potrzebne do punktu kontrolnego. Następnie rozpoczyna drugie przejście przez wspólne bufory. A strony, które są zaznaczone do punktu kontrolnego, on już je synchronizuje. W ten sposób dane są już zsynchronizowane z dyskiem.
Istnieją dwa rodzaje punktów kontrolnych. Jeden punkt kontrolny jest wykonywany po przekroczeniu limitu czasu. Ten punkt kontrolny jest przydatny i dobry - checkpoint_timed. I są punkty kontrolne na żądanie - checkpoint required. Taki punkt kontrolny pojawia się, gdy mamy bardzo duży rekord danych. Zarejestrowaliśmy wiele dzienników transakcji. A PostgreSQL uważa, że musi to wszystko zsynchronizować tak szybko, jak to możliwe, zrobić punkt kontrolny i przejść dalej.
A gdyby spojrzeć na statystyki pg_stat_bgwriter i zobacz co masz checkpoint_req jest znacznie większy niż checkpoint_timed, to jest źle. Dlaczego źle? Oznacza to, że PostgreSQL jest pod ciągłym obciążeniem, gdy musi zapisać dane na dysku. Punkt kontrolny po przekroczeniu limitu czasu jest mniej stresujący i jest realizowany zgodnie z wewnętrznym harmonogramem i niejako rozciągnięty w czasie. PostgreSQL ma możliwość wstrzymywania pracy i nie obciążania podsystemu dyskowego. Jest to przydatne dla PostgreSQL. Żądania, które są wykonywane w punkcie kontrolnym, nie będą odczuwać stresu związanego z faktem, że podsystem dyskowy jest zajęty.
Istnieją trzy parametry do dostosowania punktu kontrolnego:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Pozwalają kontrolować działanie punktów kontrolnych. Ale nie będę się nad nimi rozwodzić. Osobną kwestią jest ich wpływ.
Uwaga: Wersja 9.4 rozpatrywana w raporcie nie jest już aktualna. W nowoczesnych wersjach PostgreSQL parametr checkpoint_segments zastąpione parametrami min_wal_size и max_wal_size.
Następnym podsystemem jest program piszący działający w tle − background writer. Co on robi? Działa nieustannie w nieskończonej pętli. Skanuje strony do współdzielonych buforów i opróżnia znalezione brudne strony na dysk. W ten sposób pomaga osobie kontrolującej wykonać mniej pracy podczas sprawdzania punktów kontrolnych.
Do czego jeszcze jest potrzebny? Zapewnia potrzebę czystych stron we współdzielonych buforach, jeśli są one nagle potrzebne (w dużych ilościach i natychmiast) do pomieszczenia danych. Załóżmy, że zaistniała sytuacja, gdy żądanie wymagało czystych stron, a one już znajdują się we współdzielonych buforach. Postgres backend po prostu je bierze i używa, niczego sam nie musi czyścić. Ale jeśli nagle takich stron nie ma, backend zatrzymuje się i zaczyna szukać stron, aby zrzucić je na dysk i wziąć na własne potrzeby - co negatywnie wpływa na czas aktualnie wykonywanego żądania. Jeśli widzisz, że masz parametr maxwritten_clean duży, oznacza to, że program piszący w tle nie wykonuje swojej pracy i musisz zwiększyć parametry bgwriter_lru_maxpagesaby mógł wykonać więcej pracy w jednym cyklu, wyczyścić więcej stron.
Kolejnym bardzo przydatnym wskaźnikiem jest buffers_backend_fsync. Backendy nie wykonują fsync, ponieważ jest wolny. Przekazują fsync w górę wskaźnika kontrolnego stosu IO. Checkpointer ma swoją kolejkę, okresowo przetwarza fsync i synchronizuje strony w pamięci z plikami na dysku. Jeśli kolejka punktów kontrolnych jest duża i pełna, backend jest zmuszony do wykonania fsync, co spowalnia backend, czyli klient otrzyma odpowiedź później niż mógłby. Jeśli widzisz, że masz tę wartość większą od zera, to już jest problem i należy zwrócić uwagę na ustawienia programu piszącego w tle, a także ocenić wydajność podsystemu dyskowego.
Uwaga: _Poniższy tekst opisuje widoki statystyczne związane z replikacją. Większość nazw widoków i funkcji została zmieniona w Postgres 10. Istotą zmian było zastąpienie xlog na wal и location na lsn w nazwach funkcji/widoków itp. Konkretny przykład, funkcja pg_xlog_location_diff() została zmieniona na pg_wal_lsn_diff()._
Tutaj też mamy dużo. Ale potrzebujemy tylko przedmiotów związanych z lokalizacją.
Jeśli widzimy, że wszystkie wartości są równe, to jest to idealne i replika nie pozostaje w tyle za wzorcem.
Ta pozycja szesnastkowa jest tutaj pozycją w dzienniku transakcji. Stale rośnie, jeśli w bazie danych jest jakaś aktywność: wstawia, usuwa itp.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Jeśli te rzeczy są inne, to jest jakieś opóźnienie. Lag to opóźnienie repliki względem mastera, tzn. dane różnią się między serwerami.
Są trzy powody opóźnienia:
- To podsystem dysku, który nie obsługuje zapisów synchronizacji plików.
- Są to możliwe błędy sieci, czyli przeciążenie sieci, gdy dane nie mają czasu dotrzeć do repliki i nie może jej odtworzyć.
- I procesor. Procesor to bardzo rzadki przypadek. I widziałem to dwa lub trzy razy, ale to też może się zdarzyć.
A oto trzy zapytania, które pozwalają nam korzystać ze statystyk. Możemy oszacować, ile jest zapisanych w naszym dzienniku transakcji. Jest taka funkcja pg_xlog_location_diff i możemy oszacować opóźnienie replikacji w bajtach i sekundach. Używamy do tego również wartości z tego widoku (WIDOKÓW).
Uwaga: _Zamiast pg_xlog_locationdiff(), możesz użyć operatora odejmowania i odjąć jedną lokalizację od drugiej. Wygodny.
Z opóźnieniem, które jest w sekundach, jest jeden moment. Jeśli na wzorcu nie ma żadnej aktywności, transakcja była tam około 15 minut temu i nie ma żadnej aktywności, a jeśli spojrzymy na to opóźnienie na replice, zobaczymy opóźnienie 15 minut. Warto o tym pamiętać. I może to prowadzić do oszołomienia, gdy oglądałeś to opóźnienie.
pg_stat_all_tables to kolejny przydatny widok. Pokazuje statystyki na tabelach. Kiedy mamy tabele w bazie danych, jest z nimi jakaś aktywność, jakieś akcje, 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ą, na którą możemy spojrzeć, są sekwencyjne skany tabel. Sama liczba po tych fragmentach niekoniecznie jest zła i nie wskazuje, że musimy już coś zrobić.
Istnieje jednak druga metryka - seq_tup_read. Jest to liczba wierszy zwróconych ze skanowania sekwencyjnego. Jeśli średnia liczba przekracza 1, 000 10, 000 50, 000 100, to już jest to wskaźnik, że być może trzeba gdzieś zbudować indeks, aby dostępy były według indeksu, lub można zoptymalizować zapytania wykorzystujące takie sekwencyjne skany, aby tak się nie dzieje.
Prosty przykład - powiedzmy, że żądanie z dużym PRZESUNIĘCIEM i LIMITEM jest tego warte. Na przykład skanowanych jest 100 000 wierszy w tabeli, a następnie pobieranych jest 50 000 wymaganych wierszy, a poprzednie zeskanowane wiersze są odrzucane. To też jest zły przypadek. I takie żądania muszą być optymalizowane. A oto takie proste zapytanie SQL, na którym można to zobaczyć i ocenić otrzymane 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().
Ogólnie rzecz biorąc, istnieją metapolecenia dt и di, którego możesz używać w PSQL, a także zobaczyć rozmiary tabel i indeksów.
Jednak użycie funkcji pomaga nam spojrzeć na rozmiary tabel, nawet z uwzględnieniem indeksów lub bez uwzględnienia indeksów, i już dokonać pewnych szacunków na podstawie wzrostu bazy, czyli tego, jak rośnie ona z nami, z z jaką intensywnością i już wyciągnąć pewne wnioski na temat optymalizacji rozmiaru.
Napisz aktywność. Co to jest rekord? Spójrzmy na operację UPDATE – operacja aktualizacji wierszy w tabeli. W rzeczywistości aktualizacja to dwie operacje (a nawet więcej). Powoduje to wstawienie nowej wersji wiersza i oznaczenie starej wersji wiersza jako przestarzałej. Później autovacuum przyjdzie i wyczyści te przestarzałe wersje linii, oznacz to miejsce jako dostępne do ponownego użycia.
Aktualizacja to nie tylko aktualizacja tabeli. To wciąż aktualizacja indeksu. Jeśli masz wiele indeksów w tabeli, to przy aktualizacji wszystkie indeksy, w których uczestniczą pola zaktualizowane w zapytaniu, również będą musiały zostać zaktualizowane. Indeksy te będą również miały przestarzałe wersje wierszy, które będą wymagały oczyszczenia.
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;A ze względu na swoją konstrukcję UPDATE jest operacją wagi ciężkiej. Ale można je ułatwić. Jeść hot updates. Pojawiły się one w PostgreSQL w wersji 8.3. I co to jest? Jest to lekka aktualizacja, która nie powoduje odbudowy indeksów. Oznacza to, że zaktualizowaliśmy rekord, ale tylko rekord na stronie (który należy do tabeli) został zaktualizowany, a indeksy nadal wskazują ten sam rekord na stronie. Jest trochę taka ciekawa logika pracy, jak przychodzi próżnia to ma te łańcuchy hot odbudowuje i wszystko nadal działa bez aktualizacji indeksów, a wszystko dzieje się przy mniejszym marnowaniu zasobów.
A kiedy masz n_tup_hot_upd duży, jest bardzo dobry. Oznacza to, że przeważają lekkie aktualizacje, a to jest dla nas tańsze pod względem zasobów i wszystko jest w porządku.
ALTER TABLE table_name SET (fillfactor = 70);Jak zwiększyć głośność hot updateow? Możemy użyć fillfactor. Określa rozmiar zarezerwowanego wolnego miejsca podczas wypełniania strony w tabeli przy użyciu INSERT-ów. Gdy inserty trafiają na stół, całkowicie wypełniają stronę, nie pozostawiając na niej pustego miejsca. Następnie podświetlana jest nowa strona. Dane zostaną ponownie wypełnione. I to jest zachowanie domyślne, współczynnik wypełnienia = 100%.
Możemy ustawić współczynnik wypełnienia na 70%. Oznacza to, że przy wstawkach przydzielono nową stronę, ale wypełniono tylko 70% strony. A my mamy 30% w rezerwie. Gdy będziesz musiał dokonać aktualizacji, najprawdopodobniej nastąpi to na tej samej stronie, a nowa wersja wiersza zmieści się na tej samej stronie. I hot_update zostanie wykonane. Ułatwia to pisanie po tablicach.
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 autopróżniowa. Autovacuum to taki podsystem, dla którego w PostgreSQL jest bardzo mało statystyk. Możemy zobaczyć tylko w tabelach w pg_stat_activity ile próżni mamy w tej chwili. Jednak bardzo trudno jest zrozumieć, ile stołów w kolejce ma w ruchu.
Uwaga: _Od Postgres 10 sytuacja ze śledzeniem próżni znacznie się poprawiła - pojawił się widok pg_stat_progresspróżni, co znacznie upraszcza kwestię monitorowania autopróżni.
Możemy użyć tego uproszczonego zapytania. I widzimy, kiedy należy wykonać próżnię. Ale jak i kiedy powinna rozpocząć się próżnia? To są stare wersje strun, o których mówiłem wcześniej. Nastąpiła aktualizacja, wstawiono nową wersję wiersza. Pojawiła się przestarzała wersja ciągu. Tabela pg_stat_user_tables jest taki parametr n_dead_tup. Pokazuje liczbę „martwych” wierszy. A gdy tylko liczba martwych rzędów przekroczy określony próg, do stołu pojawi się autopróżnia.
A jak oblicza się ten próg? Jest to bardzo określony procent całkowitej liczby wierszy w tabeli. Jest parametr autovacuum_vacuum_scale_factor. Określa procent. Powiedzmy 10% + jest dodatkowy próg bazowy 50 linii. I co się dzieje? Kiedy mamy więcej martwych wierszy niż „10% + 50” wszystkich wierszy w tabeli, ustawiamy tabelę na autovacuum.
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));Jest jednak jeden punkt. Podstawowe progi parametrów av_base_thresh и av_scale_factor można przypisać indywidualnie. W związku z tym próg nie będzie globalny, ale indywidualny dla stołu. Dlatego, aby obliczyć, musisz użyć sztuczek i sztuczek. A jeśli jesteś zainteresowany, możesz spojrzeć na doświadczenia naszych kolegów z Avito (link na slajdzie jest nieprawidłowy i został zaktualizowany w tekście).
Pisali dla który bierze pod uwagę te rzeczy. Na dwóch prześcieradłach znajduje się ściereczka do stóp. Ale on uważa poprawnie i dość skutecznie pozwala nam ocenić, gdzie potrzebujemy dużo podciśnienia dla stołów, gdzie jest mało.
Co możemy z tym zrobić? Jeśli mamy długą kolejkę i autopróżnia sobie nie radzi, to możemy zwiększyć liczbę robotników próżniowych lub po prostu sprawić, by próżnia była bardziej agresywnaaby uruchamiał się wcześniej, przetwarza tabelę w małych kawałkach. I tak kolejka się zmniejszy. - Najważniejsze tutaj jest monitorowanie obciążenia dysków, ponieważ. Odkurzanie nie jest darmowe, chociaż wraz z pojawieniem się urządzeń SSD / NVMe problem stał się mniej zauważalny.
pg_stat_all_indexes to statystyki dotyczące indeksów. Ona nie jest duża. I możemy z niego uzyskać informacje na temat wykorzystania indeksów. I na przykład możemy określić, które indeksy mamy dodatkowe.
Jak już powiedziałem, aktualizacja to nie tylko aktualizacja tabel, ale także aktualizacja indeksów. W związku z tym, jeśli mamy dużo indeksów w tabeli, to podczas aktualizacji wierszy w tabeli należy również zaktualizować indeksy indeksowanych pól, oraz jeśli mamy nieużywane indeksy, dla których nie ma skanów indeksów, to wiszą u nas jako balast. I musisz się ich pozbyć. Do tego potrzebujemy pola idx_scan. Patrzymy tylko na liczbę skanów indeksu. Jeśli indeksy mają zero skanów przez stosunkowo długi okres przechowywania statystyk (co najmniej 2-3 tygodnie), to najprawdopodobniej są to złe indeksy, musimy się ich pozbyć.
Uwaga: Podczas wyszukiwania nieużywanych indeksów w przypadku klastrów replikacji strumieniowej należy sprawdzić wszystkie węzły klastra, ponieważ statystyki nie są globalne, a jeśli indeks nie jest używany na wzorcu, można go użyć na replikach (jeśli jest obciążenie).
Dwa linki:
Są to bardziej zaawansowane przykłady zapytań dotyczące sposobu wyszukiwania nieużywanych indeksów.
Drugi link to dość interesujące zapytanie. Jest w tym bardzo nietrywialna logika. Polecam do recenzji.
Co jeszcze warto podsumować indeksami?
-
Nieużywane indeksy są złe.
-
Zajmują miejsce.
-
Spowolnij operacje aktualizacji.
-
Dodatkowa praca dla próżni.
Jeśli usuniemy nieużywane indeksy, to tylko ulepszymy bazę danych.
Następny widok to pg_stat_activity. Jest to odpowiednik narzędzia ps, tylko w PostgreSQL. Jeśli ps„Och, w takim razie obserwuj procesy w systemie operacyjnym pg_stat_activity pokaże ci aktywność wewnątrz PostgreSQL.
Co możemy stamtąd zabrać?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Widzimy ogólną aktywność, która ma miejsce w bazie danych. Możemy wykonać nowe wdrożenie. Tam wszystko eksplodowało, nowe połączenia nie są akceptowane, w aplikacji leją się błędy.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Możemy uruchomić takie zapytanie i zobaczyć całkowity procent połączeń w stosunku do maksymalnego limitu połączeń i zobaczyć, kto ma najwięcej połączeń. I w tym konkretnym przypadku widzimy tego użytkownika cron_role otworzył 508 połączeń. I coś mu się stało. Musisz się z tym pogodzić i zobaczyć. I całkiem możliwe, że jest to jakaś anomalna liczba połączeń.
Jeśli mamy obciążenie OLTP, zapytania powinny być szybkie, bardzo szybkie i nie powinno być długich zapytań. Jeśli jednak są długie prośby, to w krótkim okresie nie ma się czym martwić, ale na dłuższą metę długie zapytania szkodzą bazie danych, zwiększają efekt rozdęcia tabel, gdy występuje fragmentacja tabeli. Zarówno wzdęcia, jak i długie zapytania muszą zostać usunięte.
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;Uwaga: przy takim żądaniu możemy definiować długie żądania i transakcje. Korzystamy z funkcji clock_timestamp() do ustalenia czasu pracy. Długie prośby, które znaleźliśmy, możemy je zapamiętać, wykonać explain, spojrzeć na plany i jakoś zoptymalizować. Kręcimy obecne długie prośby i żyjemy dalej.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Złe transakcje są bezczynne w transakcjach i bezczynne w transakcjach (przerwanych).
Co to znaczy? Transakcje mają wiele stanów. A jeden z tych stanów może zająć w dowolnym momencie. Istnieje pole do definiowania stanów state W tym widoku. 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 powiedziałem powyżej, te dwa stany bezczynność w transakcji i bezczynność w transakcji (przerwana) są złe. Co to jest? W tym momencie aplikacja otworzyła transakcję, wykonała pewne czynności i zajęła się swoimi sprawami. Transakcja pozostaje otwarta. Zawiesza się, nic się w nim nie dzieje, pobiera połączenie, blokuje się na zmienionych wierszach i potencjalnie wciąż zwiększa rozdęcie innych tabel, ze względu na architekturę silnika transakcyjnego Postrges. I takie transakcje też powinny być rozstrzeliwane, bo generalnie i tak są szkodliwe.
Jeśli widzisz, że masz ich więcej niż 5-10-20 w swojej bazie danych, to musisz się martwić i zacząć coś z nimi robić.
Tutaj również używamy czasu obliczeń clock_timestamp(). Kręcimy transakcje, optymalizujemy aplikację.
Jak powiedziałem powyżej, blokady występują, gdy dwie lub więcej transakcji konkuruje o jeden lub grupę zasobów. Do tego mamy pole waiting z wartością logiczną true lub false.
To prawda - oznacza to, że proces czeka, coś trzeba zrobić. Kiedy proces oczekuje, klient, który zainicjował ten proces, również czeka. Klient w przeglądarce siedzi i też czeka.
Uwaga: _Począwszy od Postgres 9.6, pole waiting usunięte i zastąpione dwoma dodatkowymi polami informacyjnymi wait_event_type и wait_event._
Co robić? Jeśli widzisz prawdę przez długi czas, powinieneś pozbyć się takich próśb. Po prostu fotografujemy takie transakcje. Piszemy do programistów, co trzeba jakoś zoptymalizować, żeby nie było wyścigu o zasoby. A potem programiści optymalizują aplikację, aby tak się nie stało.
A skrajnym, ale jednocześnie potencjalnie nie śmiertelnym przypadkiem jest występowanie zakleszczeń. Dwie transakcje zaktualizowały dwa zasoby, a następnie ponownie uzyskują do nich dostęp, już do przeciwnych zasobów. PostgreSQL w tym przypadku bierze i odpala samą transakcję, aby druga mogła kontynuować pracę. To sytuacja bez wyjścia, a ona sama siebie nie rozumie. Dlatego PostgreSQL jest zmuszony do podjęcia ekstremalnych środków.
A oto dwa zapytania, które pozwalają śledzić blokady. Korzystamy z widoku pg_locks, co pozwala na śledzenie ciężkich zamków.
A pierwszy link to sam tekst żądania. Jest dość długi.
A drugi link to artykuł o zamkach. Warto przeczytać, jest bardzo interesująca.
Więc co widzimy? Widzimy dwie prośby. Transakcja z ALTER TABLE jest transakcją blokującą. Zaczęło się, ale się nie skończyło, a aplikacja, która zaksięgowała tę transakcję, robi gdzieś inne rzeczy. Druga prośba to aktualizacja. Czeka na zakończenie tabeli alter przed kontynuowaniem pracy.
W ten sposób możemy dowiedzieć się, kto kogo zamknął, kto kogo przetrzymuje i możemy zająć się tym dalej.
Kolejnym modułem jest pg_stat_statements. Jak mówiłem, jest to moduł. Aby z niego skorzystać należy załadować jego bibliotekę w konfiguracji, zrestartować PostgreSQL, zainstalować moduł (jednym poleceniem), a wtedy będziemy mieli nowy widok.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Co możemy stamtąd zabrać? Jeśli mówimy o prostych rzeczach, możemy wziąć średni czas wykonania zapytania. Czas rośnie, co oznacza, że PostgreSQL odpowiada wolno i trzeba coś z tym zrobić.
Widzimy najbardziej aktywne transakcje zapisu w bazie danych, które zmieniają dane we współdzielonych buforach. Zobacz, kto tam aktualizuje lub usuwa dane.
Możemy po prostu spojrzeć na różne statystyki dla tych zapytań.
My pg_stat_statements wykorzystywane do tworzenia raportów. Resetujemy statystyki raz dziennie. Gromadźmy to. Zanim następnym razem zresetujesz statystyki, utworzymy raport. Oto link do raportu. Możesz to obejrzeć.
Co my robimy? Obliczamy ogólne statystyki dla wszystkich żądań. Następnie dla każdego zapytania liczymy jego indywidualny wkład w tę ogólną statystykę.
A co możemy zobaczyć? Widzimy całkowity czas wykonania wszystkich żądań określonego typu na tle wszystkich innych żądań. Możemy spojrzeć na użycie procesora i wejścia/wyjścia w odniesieniu do ogólnego obrazu. I już zoptymalizować te żądania. Tworzymy najpopularniejsze zapytania na podstawie tego raportu i już zaczynamy zastanawiać się, co należy zoptymalizować.
Co mamy za kulisami? Jest jeszcze kilka zgłoszeń, których nie uwzględniłem, bo czas jest ograniczony.
Jest pgstattuple jest także dodatkowym modułem ze standardowego pakietu contribs. Pozwala ocenić bloat stoliki tzw. fragmentacja tabeli. A jeśli fragmentacja jest duża, musisz ją usunąć, użyj różnych narzędzi. I funkcja pgstattuple długo działa. A im więcej stołów, tym dłużej będzie działać.
Następnym wkładem jest pg_buffercache. Pozwala sprawdzić wspólne bufory: jak intensywnie i dla których stron buforów tabel są wykorzystywane. I po prostu pozwala zajrzeć do udostępnionych buforów i ocenić, co się tam dzieje.
Kolejnym modułem jest pgfincore. Umożliwia wykonywanie niskopoziomowych operacji na tabelach za pomocą wywołania systemowego mincore(), tj. umożliwia załadowanie tabeli do współdzielonych buforów lub jej rozładowanie. I pozwala między innymi sprawdzić pamięć podręczną stron systemu operacyjnego, czyli ile tabela zajmuje w pamięci podręcznej stron, we współdzielonych buforach i po prostu pozwala ocenić obciążenie tabeli.
Kolejnym modułem jest pg_stat_kcache. Używa również wywołania systemowego getrusage(). I wykonuje go przed i po wykonaniu żądania. A w uzyskanych statystykach pozwala nam oszacować, ile nasze żądanie wydało na dyskowe wejścia/wyjścia, czyli operacje z systemem plików i patrzy na użycie procesora. Moduł jest jednak młody (khe-khe) i do swojego działania wymaga PostgreSQL 9.4 oraz pg_stat_statements, o których wspomniałem wcześniej.
-
Przydatna jest umiejętność korzystania ze statystyk. Nie potrzebujesz oprogramowania innych firm. Możesz popatrzeć, zobaczyć, coś zrobić, wystąpić.
-
Używanie statystyk jest łatwe, to zwykły SQL. Zebrałeś wniosek, skompilowałeś go, wysłałeś, przejrzałeś.
-
Statystyki pomagają odpowiedzieć na pytania. Jeśli masz pytania, sięgasz do statystyk - patrz, wyciągaj wnioski, analizuj wyniki.
-
I eksperymentuj. Wiele zapytań, dużo danych. Zawsze możesz zoptymalizować niektóre istniejące zapytania. Możesz stworzyć własną wersję prośby, która bardziej Ci odpowiada niż oryginał i użyć jej.
referencje
Prawidłowe linki znalezione w artykule, na podstawie których znalazły się w raporcie.
Autorze napisz więcej
(pol)
Kolekcjoner statystyk
Funkcje administracyjne systemu
Moduły Contrib
Narzędzia SQL i przykłady kodu SQL
Dziękuję wszystkim za uwagę!
Źródło: www.habr.com