
Pozyskiwanie wiedzy o kondycji PostgreSQL

Systemy bazodanowe, zgodnie z ich podstawowym zastosowaniem, podlegają zmianom w czasie. Zmienia się szereg czynników takich jak ilość danych, sposób ich wykorzystania, wykorzystywane procedury, mechanizmy dostępu, pula klientów. Zmienność zależna jest od wielu czynników i nie da się jej jednoznacznie opisać. Naturalnie, zmienność ta powoduje zmiany w kondycji systemu bazodanowego. Przed użytkownikami odpowiedzialnymi za wydajność i stabilność aplikacji staje zadanie sprawdzenia tej kondycji i podjęcia odpowiednich kroków.
Systemy bazodanowe, zgodnie z ich podstawowym zastosowaniem, podlegają zmianom w czasie. Zmienia się szereg czynników takich jak ilość danych, sposób ich wykorzystania, wykorzystywane procedury, mechanizmy dostępu, pula klientów. Zmienność zależna jest od wielu czynników i nie da się jej jednoznacznie opisać. Naturalnie, zmienność ta powoduje zmiany w kondycji systemu bazodanowego, jakim jest PostgreSQL. Przed użytkownikami odpowiedzialnymi za wydajność i stabilność aplikacji staje zadanie sprawdzenia tej kondycji i podjęcia odpowiednich kroków.
Zasoby systemu operacyjnego wykorzystywane przez PostgreSQL
Podstawowym źródłem informacji o usłudze bez znajomości jej struktury jest zawsze jej środowisko uruchomieniowe. W tym przypadku – system operacyjny. Natywnym środowiskiem dla PostgreSQL (EuroDB) jest Linux, w naszym przypadku rodzina Enterprise Linux. Wybór konkretnych narzędzi to jedynie kwestia przyzwyczajeń i mam pełną świadomość możliwości realizacji poniższych zadań na co najmniej kilka innych sposobów.
Zasoby:
Pamięć:
[root@eurodb-stage ~]# free -m total used free shared buff/cache available Mem: 487 60 185 20 240 361 Swap: 1023 47 976
Powyższa informacja jest tak wysokopoziomowa, że staje się praktycznie bezwartościowa dla administratora. Zatem należałoby wyizolować procesy związane z bazą danych, przy użyciu komendy:
[root@eurodb-stage ~]# ps auxf … postgres 731 0.0 0.7 348596 3500 ? S Jul15 0:21 /usr/bin/postgres -D /dbdata/9.5 postgres 847 0.0 0.1 201372 596 ? Ss Jul15 0:00 \_ postgres: logger process postgres 888 0.0 0.4 348708 2452 ? Ss Jul15 0:02 \_ postgres: checkpointer process postgres 889 0.0 0.4 348596 2096 ? Ss Jul15 0:04 \_ postgres: writer process postgres 890 0.0 0.2 348596 1012 ? Ss Jul15 0:07 \_ postgres: wal writer process postgres 891 0.0 0.2 349036 1420 ? Ss Jul15 0:26 \_ postgres: autovacuum launcher process postgres 892 0.0 0.1 203768 912 ? Ss Jul15 0:37 \_ postgres: stats collector process postgres 893 0.0 0.1 348896 644 ? Ss Jul15 0:00 \_ postgres: bgworker: pglogical supervisor …
Możemy zdeterminować, jaki użytkownik odpowiada za procesy bazodanowe. Proces nadrzędny w swoich parametrach uruchomieniowych zawiera też niezwykle istotną informację:
-D /dbdata/9.5
Mówiącą o miejscu w systemie plików skąd został zainicjalizowany klaster.
Przyjrzyjmy się jeszcze podobnej liście dla bazy, która wykonuje jakieś operacje:
postgres 731 0.0 0.7 348596 3500 ? S Jul15 0:25 /usr/bin/postgres -D /dbdata/9.5 postgres 847 0.0 0.1 201372 600 ? Ss Jul15 0:00 \_ postgres: logger process postgres 888 0.0 0.5 348708 2940 ? Ss Jul15 0:02 \_ postgres: checkpointer process postgres 889 0.0 0.4 348596 2096 ? Ss Jul15 0:05 \_ postgres: writer process postgres 890 0.0 0.9 348596 4708 ? Ds Jul15 0:08 \_ postgres: wal writer process postgres 891 0.0 0.2 349140 1496 ? Ss Jul15 0:31 \_ postgres: autovacuum launcher process postgres 892 0.0 0.1 203768 960 ? Ss Jul15 0:44 \_ postgres: stats collector process postgres 893 0.0 0.1 348896 644 ? Ss Jul15 0:00 \_ postgres: bgworker: pglogical supervisor postgres 25862 0.8 17.0 355396 85096 ? Ss 07:19 0:02 \_ postgres: postgres art [local] INSERT
Interesującą dla administratora informacją jest dodatkowy proces, który pojawił się na liście procesów:
postgres 25862 0.8 17.0 355396 85096 ? Ss 07:19 0:02 \_ postgres: postgres art [local] INSERT
Możemy zauważyć, że proces ma znacznie wyższy PID (zakładając, że baza została uruchomiona przy starcie systemu i nie była restartowana) i zasoby wykorzystane są w większym stopniu niż ma to miejsce w procesach „administracyjnych”. W przypadku silnika PostgreSQL jest to zachowanie dokładnie odzwierciedlające sposób jego działania. Analizując powyższy zapis, możemy stwierdzić, że uruchomiony został kolejny proces.
Analizując ten nowo powstały proces, możemy zweryfikować, jaki program został uruchomiony w ramach ww. procesu:
[root@eurodb-stage ~]# lsof -p 25862 | grep txt postgres 25862 postgres txt REG 253,0 6120872 17677472 /usr/bin/postgres
Jak widać załadowany, został dokładnie ten sam kod co w przypadku głównego nadrzędnego procesu:
[root@eurodb-stage ~]# lsof -p 731 | grep txt postgres 731 postgres txt REG 253,0 6120872 17677472 /usr/bin/postgres
Zatem potwierdzone zostało zachowanie polegające na uruchamianiu całego silnika bazy danych dla każdego procesu bazodanowego.
Jednakże na liście procesów możemy zobaczyć dodatkowe informacje:
postgres art [local] INSERT
Są to kolejno: użytkownik, baza danych, host, działanie.
Parę innych przykładów:
postgres art [local] idle in transaction
Proces oczekuje na działania klienta w obrębie bloku transakcyjnego.
postgres 25862 0.1 16.9 350972 84336 ? Ss 07:19 0:02 \_ postgres: postgres art [local] INSERT waiting postgres 26449 0.0 1.2 349888 6360 ? Ss 07:50 0:00 \_ postgres: postgres art [local] idle in transaction
W tym przypadku mamy dwa procesy bazodanowe, gdzie jeden (26449) oczekuje na działanie użytkownika, a drugi (25862) oczekuje na zwolnienie blokady założonej przez pierwszy (wiemy, że tak jest, bo istnieją tylko dwa procesy bazodanowe).
postgres 26754 0.0 0.7 349532 3780 ? Ss 08:08 0:00 \_ postgres: postgres art [local] idle in transaction postgres 26803 0.0 0.7 349532 3828 ? Ss 08:09 0:00 \_ postgres: postgres art [local] INSERT waiting postgres 26877 0.0 0.6 349408 3004 ? Ss 08:13 0:00 \_ postgres: postgres art [local] idle in transaction
Analogicznie do poprzedniego przypadku widzimy proces, który oczekuje na zwolnienie blokady. Jednakże tym razem istnieją dwa procesy oczekujące w obrębie bloku transakcyjnego i niemożliwe jest zdeterminowanie, który z nich spowodował powstanie blokady.
Korzystając z metody analizy plików wykorzystywanych przez procesy:
[root@eurodb-stage ~]# lsof -p 26877 | grep base postgres 26877 postgres 4u REG 253,0 8192 1050529 /dbdata/9.5/base/18393/2601 [root@eurodb-stage ~]# lsof -p 26803 | grep base postgres 26803 postgres 4u REG 253,0 8192 1050529 /dbdata/9.5/base/18393/2601 [root@eurodb-stage ~]# lsof -p 26754 | grep base postgres 26754 postgres 4u REG 253,0 8192 1050529 /dbdata/9.5/base/18393/2601
Również nie ma możliwości zdeterminowania, który z procesów zablokował proces oczekujący.
Tożsama sytuacja dotyczy zajmowanej przez bazę przestrzeni.
Korzystając z dostępnych narzędzi i obecnie pozyskanej wiedzy wiemy, gdzie został uruchomiony klaster.
[root@eurodb-stage 9.5]# du -mh -d 1 145M ./pg_xlog 512K ./global 180K ./pg_clog 6.6M ./pg_commit_ts 0 ./pg_dynshmem 8.0K ./pg_notify 0 ./pg_serial 0 ./pg_snapshots 136K ./pg_subtrans 0 ./pg_twophase 2.1M ./pg_multixact 3.8G ./base 0 ./pg_replslot 0 ./pg_tblspc 0 ./pg_stat 52K ./pg_stat_tmp 4.0K ./pg_logical 1.8G ./pg_log 5.8G . [root@eurodb-stage 9.5]# pwd /dbdata/9.5
Trzeba od razu zaznaczyć, iż domyślnie program „du” nie podąża za symlinkami i katalog zawierający symlinki do przestrzeni tabel raportowany jest jako pusty:
0 ./pg_tblspc [root@eurodb-stage 9.5]# ll pg_tblspc/ total 0 lrwxrwxrwx. 1 postgres postgres 12 Jul 21 08:29 18407 -> /tmp/art_tbs
Zastosowanie przełącznika -L pozwala rozwiązać ten problem:
[root@eurodb-stage 9.5]# du -mhL -d 1 145M ./pg_xlog 512K ./global 180K ./pg_clog 6.6M ./pg_commit_ts 0 ./pg_dynshmem 8.0K ./pg_notify 0 ./pg_serial 0 ./pg_snapshots 136K ./pg_subtrans 0 ./pg_twophase 2.1M ./pg_multixact 3.8G ./base 0 ./pg_replslot 35M ./pg_tblspc 0 ./pg_stat 52K ./pg_stat_tmp 4.0K ./pg_logical 1.8G ./pg_log 5.8G .
Na podstawie powyższej informacji znamy rozmiar zajmowany przez bazę danych na dysku. Dla osób zaznajomionych z tematem jest to informacja w dużej mierze bezwartościowa.
Możemy tego dowieść, przeprowadzając prosty eksperyment:
Tworzymy nową przestrzeń tabel, tabelę i wypełniamy ją danymi (liczbami naturalnymi począwszy od 1 do 1000000).
Weryfikacja, gdzie znajduje się nasza tabela:
art=# select pg_relation_filepath('size_test'); pg_relation_filepath ---------------------------------------------- pg_tblspc/18415/PG_9.5_201510051/18393/18416 (1 row)
Weryfikacja od strony systemu plików:
[root@eurodb-stage 9.5]# ll -lah pg_tblspc/18415/PG_9.5_201510051/18393/18416 -rw-------. 1 postgres postgres 35M Jul 21 08:59 pg_tblspc/18415/PG_9.5_201510051/18393/18416
Tabela na dysku zajmuje 35 MB a konkretnie 36249600 bajtów przy 1000000 wierszy.
Następnie kasujemy większość danych z tabeli, zostawiając ostatni wiersz:
art=# delete from size_test where num < 1000000; DELETE 999999
Ponownie weryfikujemy rozmiar na dysku:
[root@eurodb-stage 9.5]# ll pg_tblspc/18415/PG_9.5_201510051/18393/18416 -rw-------. 1 postgres postgres 36249600 Jul 21 09:06 pg_tblspc/18415/PG_9.5_201510051/18393/18416
Jak widać, pozostał niezmieniony.
Weryfikujemy ilość wierszy:
art=# select count(1) from size_test; count ------- 1 (1 row)
I w ramach finalnej konkluzji eksperymentu zapiszemy do tabeli 100000 wierszy (10 razy mniej niż za pierwszym razem):
art=# select count(1) from size_test; count -------- 100001 (1 row)
I ponownie sprawdzamy rozmiar na dysku:
[root@eurodb-stage 9.5]# ll pg_tblspc/18415/PG_9.5_201510051/18393/18416 -rw-------. 1 postgres postgres 36249600 Jul 21 09:10 pg_tblspc/18415/PG_9.5_201510051/18393/18416
Wykonamy teraz operację wykraczającą poza tę część artykułu, polegającą na dostosowaniu wykorzystania miejsca na dysku do faktycznej ilości danych.
[root@eurodb-stage 9.5]# ll -lah pg_tblspc/18415/PG_9.5_201510051/18393/18419 -rw-------. 1 postgres postgres 3.5M Jul 21 09:13 pg_tblspc/18415/PG_9.5_201510051/18393/18419
Rozmiar zajmowany na dysku od strony systemu operacyjnego jest już zgodny z założeniami (10 razy mniej rekordów – 10 razy mniejszy rozmiar na dysku).
Zweryfikujmy jeszcze skalę zjawiska, tym razem jest to:
art=# select count(1) from size_test; count ---------- 29100001 (1 row)
rekordów i zajmują na dysku:
-rw-------. 1 postgres postgres 1006M Jul 21 09:26 pg_tblspc/18415/PG_9.5_201510051/18393/18419
Ponownie usuwamy wszystkie z wyjątkiem największego:
art=# delete from size_test where num < 29000000; DELETE 29100000
Weryfikujemy ilość danych:
art=# select count(1) from size_test; count ------- 1 (1 row) Time: 19905.893 ms
Czas dla referencji w późniejszym kroku.
Sprawdzamy zajętość miejsca na dysku:
[root@eurodb-stage 9.5]# ll -lah pg_tblspc/18415/PG_9.5_201510051/18393/18419 -rw-------. 1 postgres postgres 1006M Jul 21 09:44 pg_tblspc/18415/PG_9.5_201510051/18393/18419
Jak widać, tabela zawierająca jeden rekord zajmuje na dysku prawie jeden gigabajt.
Dodatkowo działa bardzo wolno:
art=# select max(num) from size_test; max ---------- 29000000 (1 row) Time: 8875.537 ms
Po oczyszczeniu:
art=# select count(1) from size_test; count ------- 1 (1 row) Time: 4.824 ms art=# select max(num) from size_test; max ---------- 29000000 (1 row) Time: 0.578 ms
I rozmiar na dysku:
-rw-------. 1 postgres postgres 8.0K Jul 21 09:46 pg_tblspc/18415/PG_9.5_201510051/18393/18422
Podsumowanie
Konkludując, korzystając z narzędzi dostępnych w systemie operacyjnym, pomiar rozmiaru tabeli obarczony był błędem na poziomie: 12 876 200% (dwunastu milionów procent) lub jeżeli ktoś woli ułamki, pomiar wykonany był z dokładnością: 0,000007766266445 (gdzie 1 to pomiar wskazujący rozmiar rzeczywisty).
Przykładów niedokładności lub wręcz ułomności w pozyskiwaniu wiedzy o stanie bazy z systemu operacyjnego można przytoczyć o wiele więcej.
W następnym artykule z tego cyklu przybliżymy kolejno pozyskiwanie danych przy pomocy samego silnika PostgreSQL oraz bardziej wydajny sposób w oparciu o moduły EuroDB.