Недавно я перевёл статью от компании 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.
Leave a Reply