Vacuum czyli odkurzacz bazy Postgres

Gdy zmieniamy jakiś wiersz, PostgreSQL automatycznie tworzy kopię wiersza na której operujemy. Kopia tego wiersza znajduje się w tabeli w której znajduje się oryginalny wiersz. Dla zapytań odpytujących tę tabelę dostępne są stare wiersze. Po zakończeniu naszej transakcji wszystkie zapytania korzystają już z aktualnego nowego wiersza. Taki sposób funkcjonowania bazy Postgres powoduje, że w plikach tabel powstaje ogromna ilość nieużywanych wierszy do których już nawet nie ma dostępu (nie są one usuwane fizycznie z tabel). To z kolei powoduje niepotrzebny rozrost plików danych.
Miejsce zajmowane przez takie wiersze można odzyskać za pomocą polecenia VACUUM które odzyskuje pustą przestrzeń w tabelach oraz aktualizuje statystyki. Wykonanie polecenia vacuum porządkuje bazę i odzyskuje wolne miejsce na dysku.

1) Z poziomuSQL:
VACUUM [FULL] [FREEZE] [VERBOSE] [table]
VACUUM ANALYZE uaktualnia statystyki optymalizatora:
VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE [table]

2) Z poziomu system operacyjnego:
vacuumdb [connection-option] [--full | -f] [--verbose | -v] [--analyze | -z] [--table | -t table] [dbname]
vacuumdb [connection-option] [--all | -a] [--full | -f] [--verbose | -v] [--analyze | -z]

Wyświetlenie pomocy dla polecenie VACUUMDB

-bash-4.2$ vacuumdb --help

vacuumdb cleans and analyzes a PostgreSQL database.

Usage:
  vacuumdb [OPTION]... [DBNAME]

Options:
  -a, --all                       vacuum all databases
  -d, --dbname=DBNAME             database to vacuum
  -e, --echo                      show the commands being sent to the server
  -f, --full                      do full vacuuming
  -F, --freeze                    freeze row transaction information
  -j, --jobs=NUM                  use this many concurrent connections to vacuum
  -q, --quiet                     don't write any messages
  -t, --table='TABLE[(COLUMNS)]'  vacuum specific table(s) only
  -v, --verbose                   write a lot of output
  -V, --version                   output version information, then exit
  -z, --analyze                   update optimizer statistics
  -Z, --analyze-only              only update optimizer statistics; no vacuum
      --analyze-in-stages         only update optimizer statistics, in multiple
                                  stages for faster results; no vacuum
  -?, --help                      show this help, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
  --maintenance-db=DBNAME   alternate maintenance database

Read the description of the SQL command VACUUM for details.

Report bugs to .

Aby przeprowadzić przykładowo „odkurzanie” bazy DVDRENTAL z poziomu systemu należy uruchomić:

-bash-4.2$ vacuumdb dvdrental

Password:
vacuumdb: vacuuming database "dvdrental"

Wywołanie VACUUM bez parametrów implikuje pełne uruchomienie akcji odzyskiwania dla wszystkich tabel i ich kolumn w aktualnej bazie danych.

Aby „odkurzyć” tylko jedną tabelę wykonujemy

dvdrental=# vacuum rental;

VACUUM

Można też wykonać z opcją VERBOSE co spowoduje wyświetlenie informacji na temat „odkurzania” (np.tabeli PAYMENT).

dvdrental=# vacuum verbose payment;

INFO:  vacuuming "public.payment"
INFO:  index "payment_pkey" now contains 14596 row versions in 42 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_fk_customer_id" now contains 14596 row versions in 42 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_fk_rental_id" now contains 14596 row versions in 42 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_fk_staff_id" now contains 14596 row versions in 42 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "payment": found 0 removable, 42 nonremovable row versions in 1 out of 108 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

3) Przy uruchamianiu / zatrzymywaniu / restarcie serwera:
/etc/init.d/postgresql-x.x [opcja]
gdzie [opcja] może przyjąć następujące wartości:
autovac-start — uruchamianie serwera bazy danych z autovacuum,
autovac-stop — zatrzymywanie serwera bazy danych z autovacuum,
autovac-restart — restartowanie serwera bazy danychz autovacuum.

Aby podejrzeć czy Vacuum aktualnie pracuje najlepiej skorzystać z pg_stat_progress_vacuum

SELECT * FROM pg_stat_progress_vacuum;

 pid | datid | datname | relid | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tu
ples
-----+-------+---------+-------+-------+-----------------+-------------------+--------------------+--------------------+-----------------+------------
-----
(0 rows)

\watch

Spowoduje to cykliczne wyświetlanie powyższego Selecta (w naszym przypadku nic nie pokazuje ale jak kiedyś go złapię to uzupełnię).

Można jeszcze sprawdzić po procesach:

-bash-4.2$ ps -axww | grep autovacuum

 1054 ?        Ss     0:00 postgres: autovacuum launcher process
 2446 pts/0    R+     0:00 grep --color=auto autovacuum

i w logu Postgresowym

# grep autovacuum /opt/PostgreSQL/9.6/data/pg_log/postgresql-2017-11-08_133026.log

2017-11-08 13:30:27 CET LOG:  autovacuum launcher started

Jeśli chcemy się dowiedzieć kiedy ostatnio (i czy w ogóle) był wykonywany Vacuum na tabelach wykonujemy:

SELECT
schemaname, relname,
last_vacuum, last_autovacuum, vacuum_count, autovacuum_count, -- not available on 9.0 and earlier
last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

 schemaname |    relname    |          last_vacuum          | last_autovacuum | vacuum_count | autovacuum_count | last_analyze | last_autoanalyze
------------+---------------+-------------------------------+-----------------+--------------+------------------+--------------+------------------
 public     | city          | 2017-11-09 13:45:13.904972+01 |                 |            1 |                0 |              |
 public     | inventory     |                               |                 |            0 |                0 |              |
 public     | rental        |                               |                 |            0 |                0 |              |
 public     | payment       |                               |                 |            0 |                0 |              |
 public     | language      |                               |                 |            0 |                0 |              |
 public     | film_category |                               |                 |            0 |                0 |              |
 public     | country       |                               |                 |            0 |                0 |              |
 public     | film_actor    |                               |                 |            0 |                0 |              |
 public     | category      |                               |                 |            0 |                0 |              |
 public     | customer      |                               |                 |            0 |                0 |              |
 public     | film          |                               |                 |            0 |                0 |              |
 public     | staff         |                               |                 |            0 |                0 |              |
 public     | address       |                               |                 |            0 |                0 |              |
 public     | actor         | 2017-11-09 13:47:22.423391+01 |                 |            1 |                0 |              |
 public     | store         |                               |                 |            0 |                0 |              |
(15 rows)

Jak widać był uruchamiany tylko na tabeli CITY.

Po oczyszczeniu wszystkich tabel bazy DVDRENTAL :

dvdrental=# select schemaname,relname,seq_scan,seq_tup_read,n_live_tup,last_vacuum,vacuum_count from pg_stat_user_tables;

 schemaname |    relname    | seq_scan | seq_tup_read | n_live_tup |          last_vacuum          | vacuum_count
------------+---------------+----------+--------------+------------+-------------------------------+--------------
 public     | city          |        3 |         1800 |        600 | 2017-11-09 13:56:46.402689+01 |            2
 public     | inventory     |        3 |        13743 |       4581 | 2017-11-09 13:56:46.400055+01 |            1
 public     | rental        |        4 |        64176 |      16003 | 2017-11-09 14:14:02.427034+01 |            2
 public     | payment       |        5 |        72980 |      14503 | 2017-11-09 14:06:15.720749+01 |            2
 public     | language      |        2 |           12 |          6 | 2017-11-09 13:56:46.404197+01 |            1
 public     | film_category |        2 |         2000 |       1000 | 2017-11-09 13:56:46.398426+01 |            1
 public     | country       |        2 |          218 |        109 | 2017-11-09 13:56:46.398799+01 |            1
 public     | film_actor    |        3 |        16386 |       5462 | 2017-11-09 13:56:46.413784+01 |            1
 public     | category      |        2 |           32 |         16 | 2017-11-09 13:56:46.403815+01 |            1
 public     | customer      |        5 |         2995 |        599 | 2017-11-09 13:56:46.412403+01 |            1
 public     | film          |        5 |         5000 |       1000 | 2017-11-09 13:56:46.414721+01 |            1
 public     | staff         |        2 |            4 |          2 | 2017-11-09 13:56:46.4117+01   |            1
 public     | address       |        3 |         1809 |        603 | 2017-11-09 13:56:46.41285+01  |            1
 public     | actor         |        3 |          600 |        200 | 2017-11-09 13:56:46.399256+01 |            2
 public     | store         |        3 |            6 |          2 | 2017-11-09 13:56:46.403419+01 |            1
(15 rows)

Trochę praktyki

A teraz mały przykład jak to działa w praktyce.
Po podłączeniu się do bazy DVDRENTAL

\c dvdrental

You are now connected to database "dvdrental" as user "postgres".

Tworzymy tabelę TEST_TAB

dvdrental=# CREATE TABLE test_tab (kolumna1 varchar(20));

CREATE TABLE

Robimy dwa małe inserty

dvdrental=# INSERT INTO public.test_tab (kolumna1) VALUES ('Baza Postgres');

INSERT 0 1


dvdrental=# INSERT INTO public.test_tab (kolumna1) VALUES ('Baza X');

INSERT 0 1

Możemy sobie sprawdzić rozmiar początkowy tabeli

dvdrental=# \dt+ public.test_tab

                        List of relations
 Schema |   Name   | Type  |  Owner   |    Size    | Description
--------+----------+-------+----------+------------+-------------
 public | test_tab | table | postgres | 8192 bytes |
(1 row)

Teraz zwiększamy ilość danych w tabeli poprzez kolejne inserty (uruchamiamy kilkanaście razy)

dvdrental=# INSERT INTO test_tab SELECT * FROM test_tab;

INSERT 0 4194304

W tabeli mamy ponad 4 miliony rekordów

dvdrental=# SELECT count(*) FROM public.test_tab;

  count
---------
 8388608
(1 row)

Rozmiar tabeli

dvdrental=# \dt+ public.test_tab

                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | test_tab | table | postgres | 321 MB |
(1 row)

A teraz usuwamy połowę rekordów z tabeli

dvdrental=# delete from public.test_tab where kolumna1 = 'Baza X';

DELETE 4194304

Teraz już mamy znacznie mniej wierszy

dvdrental=# SELECT count(*) FROM public.test_tab;

  count
---------
 4194304
(1 row)

Niestety rozmiar tabeli się nie zmienił

dvdrental=# \dt+ public.test_tab

                     List of relations
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | test_tab | table | postgres | 321 MB |
(1 row)

Puszczamy FULL Vacuuma

dvdrental=# vacuum full verbose test_tab;

INFO:  vacuuming "public.test_tab"
INFO:  "test_tab": found 4194304 removable, 4194304 nonremovable row versions in 41121 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.24s/1.57u sec elapsed 2.02 sec.
VACUUM

Vacuum znalazł 4194304 usuwalnych wierszy i 4194304 nieusuwalnych (te które nam zostały po usunięciu połowy danych).

Jakbyśmy puścili normalnego vacuuma to by było trochę inaczej:

dvdrental=# vacuum verbose test_tab;

INFO:  vacuuming "public.test_tab"
INFO:  "test_tab": found 0 removable, 64 nonremovable row versions in 1 out of 41121 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 64 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Jak widać Vacuum zwykły znalazł 0 usuwalnych i 64 nieusuwalnych wierszy na jednej z 41121 stron (pages). Jakbyśmy sprawdzili rozmiar tabeli to by się okazało że rozmiar nie uległ zmianie

Po naszym fullu sprawdzamy wielkość tabeli i okazuje się że rozmiar zmalał o połowę.

dvdrental=# \dt+ public.test_tab

                     List of relations
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | test_tab | table | postgres | 177 MB |
(1 row)

A teraz zmienimy istniejące wiersze na inne (zamienimy słowo POSTRGRES na SQL)

dvdrental=# UPDATE test_tab SET kolumna1='SQL';

UPDATE 4194304

Po sprawdzeniu rozmiary widać, że baza nam się dwukrotnie rozrosła z 177 do 322 MB.

dvdrental=# \dt+ public.test_tab

                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | test_tab | table | postgres | 322 MB |
(1 row)

Puszczamy zwykłego Vacuuma

dvdrental=# vacuum verbose test_tab;

INFO:  vacuuming "public.test_tab"
INFO:  "test_tab": removed 4194304 row versions in 22672 pages
INFO:  "test_tab": found 4194304 removable, 4194304 nonremovable row versions in 41231 out of 41231 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.19s/0.92u sec elapsed 2.02 sec.
VACUUM

Vacuum zwykły w odróżnieniu od poprzednich naszych zabaw z vacuumem (przy DELETE) znalazł 4194304 usuwalnych i 4194304 nieusuwalnych rekordów. Dodatkowo też rozmiar tabeli zmalał.

dvdrental=# \dt+ public.test_tab

                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | test_tab | table | postgres | 145 MB |
(1 row)

Podobnie zachowuje się Vacuum Full który znalazł

dvdrental=# vacuum full verbose test_tab;

INFO:  vacuuming "public.test_tab"
INFO:  "test_tab": found 4194304 removable, 4194304 nonremovable row versions in 41231 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.17s/1.51u sec elapsed 2.05 sec.
VACUUM

Możemy jeszcze sprawdzić status tabeli w widoku pg_stat_user_tables

dvdrental=# select schemaname,relname,seq_scan,seq_tup_read,n_live_tup,last_vacuum,vacuum_count from pg_stat_user_tables where relname = 'test_tab';

 schemaname | relname  | seq_scan | seq_tup_read | n_live_tup |         last_vacuum         | vacuum_count
------------+----------+----------+--------------+------------+-----------------------------+--------------
 public     | test_tab |       27 |     41943038 |    4194304 | 2017-11-09 15:54:05.3957+01 |            1
(1 row)

Możemy spokojnie usunąc tabelę

DROP TABLE IF EXISTS test_tab;

Ten wpis został opublikowany w kategorii Postgres i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz