PostgreSQL cierpi na problem ten sam, z którym borykają się inne współczesne systemy bazodanowe. Mianowicie każdy restart powoduje wyczyszczenie buforów,
pamięci współdzielonej przez użytkowników Postgresa do przechowywania najczęściej odczytywanych danych. Co za tym idzie,
po każdym restarcie wydajność zapytań drastycznie spada, ponieważ wszystkie bloki danych muszą być przeczytane z dysku przed wróceniem do wyniku.
Aby powrócić do wydajności sprzed restartu, musimy ponownie wypełnić je danymi, co może zająć dłuższy czas, szczególnie przy ogromnych wielkościach buforów współdzielonych (shared_buffers),
które nierzadko potrafią mieć setki gigabajtów.
Społeczność Postgresa wyszła temu naprzeciw i stworzyła rozszerzenie nazwane pg_prewarm. Pozwala ono na załadowanie obiektów do pamięci shared buffers.
Mamy też możliwość wskazać obiekty, które chcemy wczytać, wykonać automatyczny zrzut bloków w shared_buffers do pliku dumpa w momencie zatrzymania klastra lub cyklicznie,
aby nawet w przypadku crasha mieć możliwość załadowania danych do buforów. Wczytanie ze zrzutu pamięci może wykonać się automatycznie podczas startu klastra lub możemy wywołać funkcję uruchamiającą procesy,
które zaczną wypełniać pamięć współdzieloną blokami danych z dumpa. Ładowanie zrzutów pamięci odbywa się zawsze za pomocą dwóch procesów roboczych uruchamianych w tle.

Rozszerzenie pg_prewarm jest dostępne z każdą instalacją Postgresa od wersji 11, wcześniejsze wersje mogą wymagać instalacji dodatkowych paczek.

Konfiguracja pg_prewarm

Pierwszym krokiem konfiguracji będzie weryfikacja, czy rozszerzenie jest dostępne w widoku pg_available_extensions, który zwraca wszystkie dostępne rozszerzenia razem z ich wersją oraz krótkim opisem.

postgres=# select * from pg_available_extensions where name = 'pg_prewarm';


    name    | default_version | installed_version |        comment        

------------+-----------------+-------------------+-----------------------

 pg_prewarm | 1.2             |                   | prewarm relation data

(1 row)

Widzimy dostępny pg_prewarm w wersji 1.2, możemy więc przejść do następnego kroku, czyli załadowania rozszerzenia w aktualnej bazie. Dodając nowe rozszerzenia, należy pamiętać o załadowaniu ich do każdej bazy, w której chcemy z niego korzystać, jednak w przypadku pg_prewarm wystarczy, że dodamy je w bazie Postgresql, ponieważ pamięć współdzielona shared_buffers jest wspólna dla wszystkich baz w klastrze.

postgres=# create extension pg_prewarm;


CREATE EXTENSION

postgres=# select * from pg_available_extensions where name = 'pg_prewarm';

    name    | default_version | installed_version |        comment        

------------+-----------------+-------------------+-----------------------

 pg_prewarm | 1.2             | 1.2               | prewarm relation data

(1 row)

Po załadowaniu rozszerzenia w kolumnie installed_version widzimy aktualnie zainstalowaną wersję danego rozszerzenia. Jeżeli zainstalowaliśmy paczkę z nowszą wersją rozszerzenia, które mamy załadowane w naszej bazie w widoku pg_available_extensions, będziemy widzieli nową, wyższą wersję w kolumnie default_version, a wartość w kolumnie installed version powiększy się po wywołaniu polecenia "ALTER EXTENSION pg_prewarm UPDATE;", które spowoduje aktualizację rozszerzenia.

pg_prewarm w akcji

Na potrzeby demonstracji prześlę do bazy “postgres” dumpa z przykładowymi danymi:

postgres@vagrant:~$ wget https://jsystems.pl/nowy_blog/download/postgresql/tuning.sql 
postgres@vagrant:~$ psql < tuning.sql

W dumpie znajduje się tabela “companies”, która ma ponad 2GB. Postgres, którego użyję, posiada wartość buforów współdzielonych ustawioną na 4GB, więc powinna się w nich zmieścić cała tabela, pozostawiając 2GB pamięci dla systemu.

postgres=# show shared_buffers;


 shared_buffers 
----------------
 4GB

(1 row)
postgres=# \dt+ companies
                                         List of relations
 Schema |       Name       | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------------------+-------+----------+-------------+---------------+---------+-------------
 public | companies        | table | postgres | permanent   | heap          | 2177 MB |
 
(1 row)

Wykonam teraz kilkakrotnie ten sam select, aby załadować jak najwięcej bloków tabeli companies do pamięci.

postgres=# explain (analyze,buffers)select * from companies;
                        QUERY PLAN                                                        
------------------------------------------------------------------------------
 Seq Scan on companies  (cost=0.00..298560.52 rows=1999852 width=1060) (actual
           time=0.727..3457.154 rows=2000000 loops=1)
   Buffers: shared read=278562
 Planning:
   Buffers: shared hit=100 read=23
 Planning Time: 6.552 ms
 Execution Time: 3518.307 ms
(6 rows)


postgres=# explain (analyze,buffers)select * from companies;
                        QUERY PLAN                                                        
------------------------------------------------------------------------------
 Seq Scan on companies  (cost=0.00..298560.52 rows=1999852 width=1060) (actual 
           time=0.032..593.641 rows=2000000 loops=1)
   Buffers: shared hit=32 read=278530
 Planning Time: 0.062 ms
 Execution Time: 646.925 ms
(4 rows)

Pierwsze wykonanie potrzebowało ok 3,5 sekundy do zwrócenia wyniku, po kilku kolejnych wykonaniach część bloków danych została wrzucona do pamięci, a czas wykonania zapytania spadł do około 650 milisekund. W wierszach “Buffers: …” widzimy informację o ilości bloków przeczytanych z pamięci oraz z dysku lub z pamięci podręcznej dysku, “hit” - wartości znalezione w pamięci RAM, “read” - wartości odczytane z dysku. Warto pamiętać, że Postgres korzysta również z cache, więc często nawet jeżeli czegoś nie mamy w buforach współdzielonych, sam fakt, że linux przechowuje je w pamięci podręcznej, pozwala nam znacznie przyspieszyć wykonanie takiego zapytania. Na powyższym przykładzie widzieliśmy, że pierwsze wykonanie czytało wszystko z dysku, przy następnych kolejne bloki lądowały w shared buforach, ale nadal większość czytana była z “dysku” przy jednoczesnym znacznym przyspieszeniu czasu wykonania zapytania. Jest to spowodowane właśnie korzystaniem z pamięci podręcznej, do której linux wrzuca często używane bloki danych.

Sprawdzę teraz, jak będzie wyglądał plan wykonania zapytania po ręcznym załadowaniu całej tabeli companies do pamięci współdzielonej. Zacznę od restartu serwera, aby upewnić się, że pamięć jest pusta , następnie wywołuję funkcję wczytującą tabele to pamięci i ponownie wykonam select z explain.

postgres=# select pg_prewarm('companies');
 pg_prewarm 
------------
     278562
(1 row)
 
postgres=# explain (analyze,buffers)select * from companies;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on companies  (cost=0.00..298567.12 rows=2000512 width=1062) (actual time=0.047..177.284 rows=2000000 loops=1)
   Buffers: shared hit=278562
 Planning Time: 0.033 ms
 Execution Time: 227.135 ms
(4 rows)

Po wczytaniu całej tabeli czas potrzebny na wykonanie zapytania spadł ponownie o ponad połowę w porównaniu do wcześniejszego wyniku!

Powyższy test wykonany był w izolowanych warunkach, na bezczynnej bazie z wykorzystaniem tylko jednego zapytania i tabeli. Sprawdźmy jeszcze, jaką różnicę będziemy w stanie osiągnąć dla bardziej różnorodnego obciążenia wygenerowanego za pomocą pgbench.

Przeprowadziliśmy testy ręczne dla kilku wywołań, teraz sprawdzimy, jaki wzrost wydajności możemy osiągnąć przy użyciu masowych testów benchmarkiem PG_BENCH. Najpierw wygeneruję testowe dane, a następnie uruchomię test trwający minutę. Następnie zrestartuję serwer, aby upewnić się, że pamięci współdzielona i podręczna są wyczyszczone, załaduję tabele za pomocą pg_prewarm i wykonam test ponownie.

postgres@vagrant:~$ pgbench -i -s 150
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
15000000 of 15000000 tuples (100%) done (elapsed 10.28 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 17.21 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 10.38 s, vacuum 0.52 s, primary keys 6.21 s).


postgres=# \dt+
                                         List of relations
 Schema |       Name       | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------------------+-------+----------+-------------+---------------+---------+-------------
 public | pgbench_accounts | table | postgres | permanent   | heap          | 1922 MB | 
 public | pgbench_branches | table | postgres | permanent   | heap          | 40 kB   | 
 public | pgbench_history  | table | postgres | permanent   | heap          | 0 bytes | 
 public | pgbench_tellers  | table | postgres | permanent   | heap          | 104 kB  | 
(4 rows)

postgres@vagrant:~$ pgbench -T 60 -S
pgbench (15.2 (Ubuntu 15.2-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 150
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 373662
number of failed transactions: 0 (0.000%)
latency average = 0.161 ms
initial connection time = 1.953 ms
tps = 6227.712352 (without initial connection time)

Pgbench świeżo po restarcie systemu, z pustymi buforami, w ciągu minuty osiągnął w sumie 373662 transakcji, 6227 transakcji na sekundę. Wykonam teraz restart serwera i uruchomię test ponownie, ale po wcześniejszym załadowaniu największej tabeli do shared buforów.

postgres=# select pg_prewarm('pgbench_accounts');
 pg_prewarm 
------------
     248895
(1 row)


postgres@vagrant:~$ pgbench -T 60 -S
pgbench (15.2 (Ubuntu 15.2-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 150
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 1173817
number of failed transactions: 0 (0.000%)
latency average = 0.051 ms
initial connection time = 1.689 ms
tps = 19559.914627 (without initial connection time)

Po wstępnym “rozgrzaniu” buforów pgbench osiągnął 1173817 transakcji w sumie, a 19559 transakcji na sekundę, czyli około trzykrotnie lepszy rezultat. Test był przeprowadzony na małej wielkości shared_buffers, tylko kilku tabelach i mało zróżnicowanych zapytaniach. Im więcej obiektów odpytujemy i im większa jest pamięć współdzielona, tym dłużej może trwać rozgrzewanie buforów, a tym samym niższa wydajność bazy może utrzymywać się przez dłuższy czas po restarcie, żeby finalnie zapytania powróciły do wydajności przed restartem.

W powyższych przykładach załadowaliśmy dane z tabeli ręcznie, ale pg_prewarm ma możliwość robienia tego za nas automatycznie, bez jakiejkolwiek ingerencji z naszej strony.

Działa to w ten sposób, że pg_prewarm zrzuca co jakiś czas stan bufora shared_buffers na dysk. W przypadku restartu, po uruchomieniu serwera wczytywany jest stan bufora z dysku z chwili niewiele przed restartem.

Aby z tej funkcjonalności skorzystać, musimy dodać pg_prewarm to do parametru shared_preload_libraries oraz dodać dwa kolejne parametry dla pg_prewarma w pliku postgresql.conf, a także zrestartować klaster. Parametry te, oprócz autoprewarm_interval, są wczytywane tylko podczas startu Postgresa.

shared_preload_libraries = 'pg_prewarm'
pg_prewarm.autoprewarm = true
pg_prewarm.autoprewarm_interval = 300s

Od tej chwili pg_prewarm będzie wykonywał zrzut pamięci co autoprewarm_interval sekund oraz w momencie zatrzymania instancji. Podczas startu uruchomi proces roboczy, który załaduje bloki ze zrzutu do pamięci. W ten sposób odtworzymy stan buforów z chwili max “pg_prewarm.autoprewarm_interval” sekund przed restartem.

Jak widać na powyższym przykładzie restart i spowodowane tym wyczyszczenie pamięci współdzielonej Postgresa nie musi być skomplikowane. W łatwy sposób możemy przeprowadzić te czynności.