Kondycja_systemu_bazodanowego

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.

W tym artykule postaram się przybliżyć to zagadnienie na podstawie bazy danych EuroDB rozbudowującej PostgreSQL, zaczynając od zewnątrz.

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 EuroDB jest EuroLinux i on zostanie użyty jako przykład. 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 najbardziej wydajny sposób w oparciu o moduły EuroDB.

Autorzy

Artykuły na blogu są pisane przez osoby z zespołu EuroLinux. 80% treści zawdzięczamy naszym developerom, pozostałą część przygotowuje dział sprzedaży lub marketingu. Dokładamy starań, żeby treści były jak najlepsze merytorycznie i językowo, ale nie jesteśmy nieomylni. Jeśli zauważysz coś wartego poprawienia lub wyjaśnienia, będziemy wdzięczni za wiadomość.