Иллюстрация распухания в PostgreSQL. Дополнение

PostgreSQLНедавно я перевёл статью от компании Percona про Иллюстрацию распухания таблиц в PostgreSQL. Но автор той статьи почему-то не решился добавить в неё команду на перезапись таблицы (VACUUM FULL), а рекомендовал мне использовать расширение pg_repack — да, это тоже полезно конечно. Но в ту статью можно было бы добавить и про VACUUM FULL пару строк.

Так что сделаю это за автора. Сделаю небольшое дополнение.

Поэтому — чтобы было понятно о чём речь — прочитайте сначала мой перевод оригинальной статьи — Перевод: Percona: Иллюстрация распухания таблиц в PostgreSQL.

А теперь — приступим:

Создаем простую таблицу:

CREATE TABLE ptolmachev (id integer);
CREATE TABLE

Добавляем пять значений:

INSERT INTO ptolmachev values (1);
INSERT 0 1
INSERT INTO ptolmachev values (2);
INSERT 0 1
INSERT INTO ptolmachev values (3);
INSERT 0 1
INSERT INTO ptolmachev values (4);
INSERT 0 1
INSERT INTO ptolmachev values (5);
INSERT 0 1

Добавляем еще пять значений — но уже в рамках одной транзакции. Теперь (в отличии от оригинальной статьи) другим способом:

INSERT INTO ptolmachev VALUES (6),(7),(8),(9),(10);
INSERT 0 5

Посмотрим внутрь таблицы и выведем невидимые поля (xmin, xmax). Обратите внимание на транзакцию (xmin) 776:

SELECT xmin, xmax, * FROM ptolmachev;
 xmin | xmax | id 
------+------+----
  771 |    0 |  1
  772 |    0 |  2
  773 |    0 |  3
  774 |    0 |  4
  775 |    0 |  5
  776 |    0 |  6
  776 |    0 |  7
  776 |    0 |  8
  776 |    0 |  9
  776 |    0 | 10
(10 rows)

Предположим что pginspect уже установлен. Поэтому заглянем «внутрь» хипа таблицы:

SELECT * FROM heap_page_items(get_raw_page(ptolmachev,0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |    771 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |    772 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 |    773 |      0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 |    774 |      0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 |    775 |      0 |        0 | (0,5)  |           1 |       2304 |     24 |        |       | \x05000000
  6 |   8000 |        1 |     28 |    776 |      0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   7968 |        1 |     28 |    776 |      0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7936 |        1 |     28 |    776 |      0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7904 |        1 |     28 |    776 |      0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7872 |        1 |     28 |    776 |      0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
(10 rows)

Всё как и должно быть — десять строк. Но это не очень интересно. Обновим одну и ту же строку трижды:

UPDATE ptolmachev SET id = 20 WHERE id = 5;
UPDATE 1
UPDATE ptolmachev SET id = 30 WHERE id = 20;
UPDATE 1
UPDATE ptolmachev SET id = 5 WHERE id = 30;
UPDATE 1

Делаю то же самое что в оригинальной статье. Поэтому и результат тот же самый. В таблице ожидаемо 10 строк:

SELECT COUNT(*) FROM ptolmachev;
 count 
-------
    10
(1 row)

Однако в хипе находится больше строк, чем десять. Плюсом появилось три строки:

SELECT * FROM heap_page_items(get_raw_page(ptolmachev,0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |    771 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |    772 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 |    773 |      0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 |    774 |      0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 |    775 |    777 |        0 | (0,11) |       16385 |       1280 |     24 |        |       | \x05000000
  6 |   8000 |        1 |     28 |    776 |      0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   7968 |        1 |     28 |    776 |      0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7936 |        1 |     28 |    776 |      0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7904 |        1 |     28 |    776 |      0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7872 |        1 |     28 |    776 |      0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
 11 |   7840 |        1 |     28 |    777 |    778 |        0 | (0,12) |       49153 |       9472 |     24 |        |       | \x14000000
 12 |   7808 |        1 |     28 |    778 |    779 |        0 | (0,13) |       49153 |       9472 |     24 |        |       | \x1e000000
 13 |   7776 |        1 |     28 |    779 |      0 |        0 | (0,13) |       32769 |      10496 |     24 |        |       | \x05000000
(13 rows)

Почему так? Потому что UPDATE в PostgreSQL работает как DELETE + INSERT. То есть непосредственно UPDATE’a-то и нет. Да и DELETE тоже нет.

А теперь глянем как называется файл на диске, в котором хранится таблица ptolmachev:

SELECT pg_relation_filepath(ptolmachev);
 pg_relation_filepath 
----------------------
 base/13453/24603
(1 row)

Циферки. Но запомним их. Теперь выполним команду VACUUM (еще добавим параметр VERBOSE, для вывода деталей её работы):

VACUUM VERBOSE ptolmachev;
INFO: vacuuming "public.ptolmachev"
INFO: "ptolmachev": found 3 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 780
There were 2 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

Не вдаваясь в детали — три строки можно удалить («3 removable»), а вот десять строк — нельзя («10 nonremovable row versions»).

После работы этой команды опять проверим название файла на диске, которое соответствует таблице ptolmachev:

SELECT pg_relation_filepath(ptolmachev);
 pg_relation_filepath 
----------------------
 base/13453/24603
(1 row)

Название то же самое. Значит — файл тот же самый.

Проверим что у нас в хипе:

SELECT * FROM heap_page_items(get_raw_page(ptolmachev,0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |    771 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |    772 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 |    773 |      0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 |    774 |      0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |     13 |        2 |      0 |        |        |          |        |             |            |        |        |       | 
  6 |   8032 |        1 |     28 |    776 |      0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   8000 |        1 |     28 |    776 |      0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7968 |        1 |     28 |    776 |      0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7936 |        1 |     28 |    776 |      0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7904 |        1 |     28 |    776 |      0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
 11 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       | 
 12 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       | 
 13 |   7872 |        1 |     28 |    779 |      0 |        0 | (0,13) |       32769 |      10496 |     24 |        |       | \x05000000
(13 rows)

По аналогии с оригинальной таблице — три строки были «освобождены» и могут быть переиспользованы. Но место в хипе операционной таблице не отдано.

Идём дальше — добавим в таблицу еще одну строку:

INSERT INTO ptolmachev values (11);
INSERT 0 1

И проверим количество строк в хипе. Напоминаю — раньше было тринадцать штук:

SELECT * FROM heap_page_items(get_raw_page(ptolmachev,0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |    771 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |    772 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 |    773 |      0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 |    774 |      0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |     13 |        2 |      0 |        |        |          |        |             |            |        |        |       | 
  6 |   8032 |        1 |     28 |    776 |      0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   8000 |        1 |     28 |    776 |      0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7968 |        1 |     28 |    776 |      0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7936 |        1 |     28 |    776 |      0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7904 |        1 |     28 |    776 |      0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
 11 |   7840 |        1 |     28 |    780 |      0 |        0 | (0,11) |           1 |       2048 |     24 |        |       | \x0b000000
 12 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       | 
 13 |   7872 |        1 |     28 |    779 |      0 |        0 | (0,13) |       32769 |      10496 |     24 |        |       | \x05000000
(13 rows)

Столько же и осталось. Почему? Потому что одна из строк (найдите какая) была переиспользована.

А вот теперь выполним команду VACUUM FULL:

VACUUM FULL VERBOSE ptolmachev;
INFO:  vacuuming "public.ptolmachev"
INFO:  "ptolmachev": found 0 removable, 11 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

11 строк которые нельзя удалить.

Проверим что в хипе:

SELECT * FROM heap_page_items(get_raw_page(ptolmachev,0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |    771 |      0 |        0 | (0,1)  |           1 |       2816 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |    772 |      0 |        0 | (0,2)  |           1 |       2816 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 |    773 |      0 |        0 | (0,3)  |           1 |       2816 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 |    774 |      0 |        0 | (0,4)  |           1 |       2816 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 |    776 |      0 |        0 | (0,5)  |           1 |       2816 |     24 |        |       | \x06000000
  6 |   8000 |        1 |     28 |    776 |      0 |        0 | (0,6)  |           1 |       2816 |     24 |        |       | \x07000000
  7 |   7968 |        1 |     28 |    776 |      0 |        0 | (0,7)  |           1 |       2816 |     24 |        |       | \x08000000
  8 |   7936 |        1 |     28 |    776 |      0 |        0 | (0,8)  |           1 |       2816 |     24 |        |       | \x09000000
  9 |   7904 |        1 |     28 |    776 |      0 |        0 | (0,9)  |           1 |       2816 |     24 |        |       | \x0a000000
 10 |   7872 |        1 |     28 |    780 |      0 |        0 | (0,10) |           1 |       2816 |     24 |        |       | \x0b000000
 11 |   7840 |        1 |     28 |    779 |      0 |        0 | (0,11) |           1 |      11008 |     24 |        |       | \x05000000
(11 rows)

В нём находится 11 строк — потому что еще две строки (который были как бы «пустыми» — их можно было бы переиспользовать при возможности) были удалены из таблицы.

Проверим файл таблицы на диске:

SELECT pg_relation_filepath(ptolmachev);
 pg_relation_filepath 
----------------------
 base/13453/24606
(1 row)

Файл изменился. Это означает что таблица была (фактически) изменена — пересобрана. Свободное место (две строки, который можно было переиспользовать) были «отданы» операционной системе и она может этим свободным местом пользоваться.

Вот что я имел ввиду когда говорил что можно добавить в статью команду демонстрации VACUUM FULL.


Be the first to comment

Leave a Reply

Ваш Mail не будет опубликован.


*