Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Мы начали с вопросов, связанных с изоляцией, сделали отступление про организацию данных на низком уровне, затем подробно поговорили о версиях строк и о том, как из версий получаются снимки данных.
В прошлый раз мы поговорили о HOT-обновлениях и внутристраничной очистке, а сегодня займемся всем известной обычной очисткой, vacuum vulgaris. Да, про нее написано уже столько всего, что вряд ли я скажу что-то новое, но полнота картины требует жертв. Терпите.
Внутристраничная очистка выполняется быстро, но освобождает только часть места. Она работает в пределах одной табличной страницы и не затрагивает индексы.
Основная, «обычная» очистка выполняется командой VACUUM и ее мы будем называть просто очисткой (а про автоочистку мы будем говорить отдельно).
Итак, очистка обрабатывает таблицу полностью. Она вычищает не только ненужные версии строк, но и ссылки на них из всех индексов.
Обработка происходит параллельно с другой активностью в системе. Таблица и индексы при этом могут использоваться обычным образом и для чтения, и для изменения (однако одновременное выполнение таких команд, как CREATE INDEX, ALTER TABLE и некоторых других будет невозможно).
В таблице просматриваются только те страницы, в которых происходила какая-то активность. Для этого используется карта видимости (напомню, что в ней отмечены страницы, содержащие только достаточно старые версии строк, которые гарантированно видимы во всех снимках данных). Обрабатываются только страницы, не отмеченные в карте, а сама карта при этом обновляется.
В процессе работы обновляется и карта свободного пространства, чтобы отразить появившееся свободное места в страницах.
Как водится, создадим таблицу:
С помощью параметра autovacuum_enabled мы отключаем автоматическую очистку. Про нее мы будем говорить в следующий раз, а пока — для экспериментов — нам важно управлять очисткой вручную.
Сейчас в таблице три версии строки, и на каждую ведет ссылка из индекса:
После очистки «мертвые» версии пропадают и остается только одна, актуальная. И в индексе тоже остается одна ссылка:
Обратите внимание, что два первых указателя получили статус unused, а не dead, как было бы при внутристраничной очистке.
Как PostgreSQL определяет, какие версии строк можно считать «мертвыми»? Мы уже рассматривали понятие горизонта транзакций, когда говорили о снимках данных, но это настолько важная тема, что не грех и повторить.
Снова начнем предыдущий опыт.
Но перед тем, как обновлять строку еще раз, пусть начнется (но не закончится) еще одна транзакция. В нашем примере она будет работать на уровне Read Committed, но должна получить настоящий (не виртуальный) номер транзакции. Например, она может изменить или даже просто заблокировать какие-то строки в любой таблице, не обязательно в vac:
Сейчас в таблице три строки, а в индексе — три ссылки. Что произойдет после очистки?
В таблице осталось две версии строки: очистка решила, что версия (0,2) еще не может быть удалена. Причина, конечно, в горизонте транзакций базы данных, который в нашем примере определяется незавершенной транзакцией:
Можно попросить очистку рассказать о том, что происходит:
Обратите внимание:
Еще раз повторим вывод: наличие в базе данных долгоживущих транзакций (не завершенных или действительно долго выполняющихся) может приводить к разрастанию (bloat) таблиц, независимо от того, как часто выполняется очистка. Поэтому в PostgreSQL плохо сочетаются OLTP- и OLAP-нагрузка в одной базе: отчеты, выполняющиеся часами, не дадут часто обновляемым таблицам вовремя очищаться. Возможным решением может быть создание отдельной «отчетной» реплики.
После завершения открытой транзакции горизонт сдвигается и ситуация исправляется:
Теперь в странице осталась только последняя актуальная версия строки:
В индексе также только одна запись:
Очистка должна обрабатывать и таблицу, и индексы одновременно, и делать это так, чтобы не блокировать работу остальных процессов. Как ей это удается?
Все начинается со сканирования таблицы (с учетом карты видимости, как уже отмечалось). В прочитанных страницах определяются ненужные версии строк и их идентификаторы (tid) записываются в специальный массив. Массив располагается в локальной памяти процесса очистки; для него выделяется фрагмент размером maintenance_work_mem. Значение этого параметра по умолчанию — 64 МБ. Отметим, что это память выделяется сразу в полном объеме, а не по мере необходимости. Правда, если таблица небольшая, то и фрагмент выделяется поменьше.
Дальше одно из двух: либо мы дойдем до конца таблицы, либо выделенная под массив память заканчится. В любом из двух случаев начинается фаза очистки индексов. Для этого каждый из индексов, созданных на таблице, полностью сканируется в поисках записей, которые ссылаются на запомненные версии строк. Найденные записи вычищаются из индексных страниц.
В этом месте мы получаем такую картину: в индексах уже нет ссылок на ненужные версии строк, а в таблице они еще есть. Это ничему не противоречит: выполняя запрос мы либо вообще не попадем на мертвые версии строк (при индексном доступе), либо отметем их при проверке видимости (при сканировании таблицы).
После этого начинается фаза очистки таблицы. Таблица снова сканируется, чтобы прочитать нужные страницы, вычистить из них запомненные версии строк и освободить указатели. Мы можем это сделать, поскольку ссылок из индексов уже нет.
Если на первом проходе таблица не была прочитана полностью, то массив очищается и все повторяется с того места, на котором мы остановились.
Таким образом:
На больших таблицах это может занимать существенное время и создавать значительную нагрузку на систему. Конечно, запросы не будут блокироваться, но «лишний» ввод-вывод тоже неприятен.
Чтобы ускорить процесс, имеет смысл либо вызывать очистку чаще (чтобы за каждый раз очищалось не очень большое количество версий строк), либо выделить больше памяти.
Замечу в скобках, что, начиная с версии 11, PostgreSQL может пропускать сканирование индексов, если в этом нет насущной необходимости. Это должно облегчить жизнь владельцев больших таблиц, в которые строки только добавляются (но не изменяются).
Как понять, что очистка не справляется с работой за один проход?
Первый способ мы уже видели: можно вызывать команду VACUUM с указанием VERBOSE. Тогда на консоль будет выводиться и информация о фазах выполнения работы.
Во-вторых, начиная с версии 9.6 имеется представление pg_stat_progress_vacuum, которое также содержит всю необходимую информацию.
(Есть еще третий путь — выводить информацию в журнал сообщений, но это работает только для автоочистки, о которой пойдет речь в следующий раз.)
Вставим в таблицу побольше строк, чтобы очистка выполнялась ощутимое время, и все их обновим, чтобы очистке было чем заняться.
Уменьшим размер памяти, выделенной под массив идентификаторов:
Запускаем очистку и, пока она работает, обратимся несколько раз к представлению pg_stat_progress_vacuum:
Тут мы в частности видим:
Общий прогресс определяется отношением heap_blks_vacuumed к heap_blks_total, но нужно учитывать, что это значение изменяется не плавно, а “рывками” из-за сканирования индексов. Впрочем, основное внимание надо обратить на количество циклов очистки — значение больше 1 означает, что выделенной памяти не хватило для того, чтобы завершить очистку за один проход.
Вывод завершившейся к этому времени команды VACUUM VERBOSE покажет общую картину:
Здесь видно, что всего было выполнено три прохода по индексам, на каждом из которых очищалось 174480 указателей на мертвые версии строк. Откуда такое число? Одна ссылка (tid) занимает 6 байтов, а 1024*1024/6 = 174762 — это число, которое мы видим в pg_stat_progress_vacuum.max_dead_tuples. Реально может использоваться чуть меньше: так гарантируется, что при чтении очередной страницы все указатели на «мертвые» версии точно поместятся в память.
Анализ, или, иными словами, сбор статистической информации для планировщика запросов, формально никак с очисткой не связан. Тем не менее мы можем выполнять анализ не только командой ANALYZE, но и совмещать очистку с анализом: VACUUM ANALYZE. При этом сначала выполняется очистка, а затем анализ — никакой экономии не происходит.
Но, как мы увидим позже, автоматическая очистка и автоматический анализ выполняются одним процессом и управляются схожим образом.
Как мы видели, обычная очистка освобождает больше места, чем внутристраничная, но и она не всегда решает задачу полностью.
Если таблица или индекс по каким-то причинам сильно выросли в размерах, то обычная очистка освободит место внутри существующих страниц: в них появятся «дыры», которые затем будут использованы для вставки новых версий строк. Но число страниц не изменится, и, следовательно, с точки зрения операционной системы файлы будут занимать ровно столько же места, сколько занимали и до очистки. А это плохо, потому что:
(Единственно исключение составляют полностью очищенные страницы, находящиеся в конце файла — такие страницы «откусываются» от файла и возвращаются операционной системе.)
Если доля полезной информации в файлах опустилась ниже некоторого разумного предела, администратор может выполнить полную очистку таблицы. При этом таблица и все ее индексы перестраиваются полностью с нуля, а данные упаковываются максимально компактно (разумеется, с учетом параметра fillfactor). При перестройке PostgreSQL последовательно перестраивает сначала таблицу, а затем и каждый из ее индексов. Для каждого объекта создаются новые файлы, а в конце перестройки старые файлы удаляются. Следует учитывать, что в процессе работы на диске потребуется дополнительное место.
Для иллюстрации снова вставим в таблицу некоторое количество строк:
Как оценить плотность информации? Для этого удобно воспользоваться специальным расширением:
Функция читает полность всю таблицу и показывает статистику по тому, сколько места какими данными занято в файлах. Основная информация, которая нам сейчас интересна — поле tuple_percent: процент, занятый полезными данными. Он меньше 100 из-за неизбежных накладных расходов на служебную информацию внутри страницы, но тем не менее довольно высок.
Для индекса выводится другая информация, но поле avg_leaf_density имеет тот же смысл: процент полезной информации (в листовых страницах).
А вот какой размер занимают таблица и индекс:
Теперь удалим 90% всех строк. Строки для удаления выбираем случайно, чтобы в каждой странице с большой вероятностью хоть одна строка, да осталась:
Какой размер будут иметь объекты после обычной очистки?
Мы видим, что размер не изменился: обычная очистка никак не может уменьшить размер файлов. Хотя плотность информации, очевидно, уменьшилась примерно в 10 раз:
Теперь проверим, что получится после полной очистки. Вот какие файлы используются сейчас таблицей и индексами:
Теперь файлы заменены на новые. Размер таблицы и индекса существенно уменьшился, а плотность информации, соответственно, увеличилась:
Обратите внимание, что плотность информации в индексе даже увеличилась по сравнению с первоначальной. Заново создать индекс (B-дерево) по имеющимся данным выгоднее, чем вставлять данные в уже имеющийся индекс строка за строкой.
Функции расширения pgstattuple, которые мы использовали, читают полностью всю таблицу. Если таблица большая, то это неудобно, и поэтому там же есть функция pgstattuple_approx, которая пропускает страницы, отмеченные в карте видимости, и показывает примерные цифры.
Еще более быстрый, но и еще менее точный способ — прикинуть отношение объема данных к размеру файла по системному каталогу. Варианты таких запросов можно найти в вики.
Полная очистка не предполагает регулярного использования, так как полностью блокирует всякую работу с таблицей (включая и выполнение запросов к ней) на все время своей работы. Понятно, что на активно используемой системе это может оказаться неприемлемым. Блокировки будут рассмотрены отдельно, а пока ограничимся упоминанием расширения pg_repack, которое блокирует таблицу только на короткое время в конце работы.
Есть несколько команд, которые тоже перестраивают таблицы и индексы полностью, и этим похожи на полную очистку. Все они полностью блокируют работу с таблицей, все они удаляют старые файлы данных и создают новые.
Команда CLUSTER во всем аналогична VACUUM FULL, но дополнительно физически упорядочивает версии строк в соответствии с одним из имеющихся индексов. Это дает планировщику возможность более эффективно использовать индексный доступ в некоторых случаях. Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться.
Команда REINDEX перестраивает отдельный индекс на таблице. Фактически, VACUUM FULL и CLUSTER используют эту команду для того, чтобы перестроить индексы.
Команда TRUNCATE логически работает так же, как и DELETE — удаляет все табличные строки. Но DELETE, как уже было рассмотрено, только помечает версии строк как удаленные, что требует дальнейшей очистки. TRUNCATE же просто создает новый, чистый файл. Как правило, это работает быстрее, но надо учитывать, что TRUNCATE полностью заблокирует работу с таблицей на все время до конца транзакции.
Продолжение следует.
В прошлый раз мы поговорили о HOT-обновлениях и внутристраничной очистке, а сегодня займемся всем известной обычной очисткой, vacuum vulgaris. Да, про нее написано уже столько всего, что вряд ли я скажу что-то новое, но полнота картины требует жертв. Терпите.
Обычная очистка (vacuum)
Что делает очистка
Внутристраничная очистка выполняется быстро, но освобождает только часть места. Она работает в пределах одной табличной страницы и не затрагивает индексы.
Основная, «обычная» очистка выполняется командой VACUUM и ее мы будем называть просто очисткой (а про автоочистку мы будем говорить отдельно).
Итак, очистка обрабатывает таблицу полностью. Она вычищает не только ненужные версии строк, но и ссылки на них из всех индексов.
Обработка происходит параллельно с другой активностью в системе. Таблица и индексы при этом могут использоваться обычным образом и для чтения, и для изменения (однако одновременное выполнение таких команд, как CREATE INDEX, ALTER TABLE и некоторых других будет невозможно).
В таблице просматриваются только те страницы, в которых происходила какая-то активность. Для этого используется карта видимости (напомню, что в ней отмечены страницы, содержащие только достаточно старые версии строк, которые гарантированно видимы во всех снимках данных). Обрабатываются только страницы, не отмеченные в карте, а сама карта при этом обновляется.
В процессе работы обновляется и карта свободного пространства, чтобы отразить появившееся свободное места в страницах.
Как водится, создадим таблицу:
=> CREATE TABLE vac(
id serial,
s char(100)
) WITH (autovacuum_enabled = off);
=> CREATE INDEX vac_s ON vac(s);
=> INSERT INTO vac(s) VALUES ('A');
=> UPDATE vac SET s = 'B';
=> UPDATE vac SET s = 'C';
С помощью параметра autovacuum_enabled мы отключаем автоматическую очистку. Про нее мы будем говорить в следующий раз, а пока — для экспериментов — нам важно управлять очисткой вручную.
Сейчас в таблице три версии строки, и на каждую ведет ссылка из индекса:
=> SELECT * FROM heap_page('vac',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid
-------+--------+----------+----------+-----+-----+--------
(0,1) | normal | 4000 (c) | 4001 (c) | | | (0,2)
(0,2) | normal | 4001 (c) | 4002 | | | (0,3)
(0,3) | normal | 4002 | 0 (a) | | | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
itemoffset | ctid
------------+-------
1 | (0,1)
2 | (0,2)
3 | (0,3)
(3 rows)
После очистки «мертвые» версии пропадают и остается только одна, актуальная. И в индексе тоже остается одна ссылка:
=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid
-------+--------+----------+-------+-----+-----+--------
(0,1) | unused | | | | |
(0,2) | unused | | | | |
(0,3) | normal | 4002 (c) | 0 (a) | | | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
itemoffset | ctid
------------+-------
1 | (0,3)
(1 row)
Обратите внимание, что два первых указателя получили статус unused, а не dead, как было бы при внутристраничной очистке.
И еще раз о горизонте транзакций
Как PostgreSQL определяет, какие версии строк можно считать «мертвыми»? Мы уже рассматривали понятие горизонта транзакций, когда говорили о снимках данных, но это настолько важная тема, что не грех и повторить.
Снова начнем предыдущий опыт.
=> TRUNCATE vac;
=> INSERT INTO vac(s) VALUES ('A');
=> UPDATE vac SET s = 'B';
Но перед тем, как обновлять строку еще раз, пусть начнется (но не закончится) еще одна транзакция. В нашем примере она будет работать на уровне Read Committed, но должна получить настоящий (не виртуальный) номер транзакции. Например, она может изменить или даже просто заблокировать какие-то строки в любой таблице, не обязательно в vac:
| => BEGIN;
| => SELECT s FROM t FOR UPDATE;
| s
| -----
| FOO
| BAR
| (2 rows)
=> UPDATE vac SET s = 'C';
Сейчас в таблице три строки, а в индексе — три ссылки. Что произойдет после очистки?
=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid
-------+--------+----------+----------+-----+-----+--------
(0,1) | unused | | | | |
(0,2) | normal | 4005 (c) | 4007 (c) | | | (0,3)
(0,3) | normal | 4007 (c) | 0 (a) | | | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,3)
(2 rows)
В таблице осталось две версии строки: очистка решила, что версия (0,2) еще не может быть удалена. Причина, конечно, в горизонте транзакций базы данных, который в нашем примере определяется незавершенной транзакцией:
| => SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
| backend_xmin
| --------------
| 4006
| (1 row)
Можно попросить очистку рассказать о том, что происходит:
=> VACUUM VERBOSE vac;
INFO: vacuuming "public.vac"
INFO: index "vac_s" now contains 2 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "vac": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 1 dead row versions cannot be removed yet, oldest xmin: 4006
There were 1 unused item pointers.
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
Обратите внимание:
- 2 nonremovable row versions — в таблице найдено 2 версии, которые нельзя удалить,
- 1 dead row versions cannot be removed yet — из них 1 «мертвая»,
- oldest xmin показывает текущий горизонт.
Еще раз повторим вывод: наличие в базе данных долгоживущих транзакций (не завершенных или действительно долго выполняющихся) может приводить к разрастанию (bloat) таблиц, независимо от того, как часто выполняется очистка. Поэтому в PostgreSQL плохо сочетаются OLTP- и OLAP-нагрузка в одной базе: отчеты, выполняющиеся часами, не дадут часто обновляемым таблицам вовремя очищаться. Возможным решением может быть создание отдельной «отчетной» реплики.
После завершения открытой транзакции горизонт сдвигается и ситуация исправляется:
| => COMMIT;
=> VACUUM VERBOSE vac;
INFO: vacuuming "public.vac"
INFO: scanned index "vac_s" to remove 1 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: "vac": removed 1 row versions in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "vac_s" now contains 1 row versions in 2 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "vac": found 1 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4008
There were 1 unused item pointers.
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
Теперь в странице осталась только последняя актуальная версия строки:
=> SELECT * FROM heap_page('vac',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid
-------+--------+----------+-------+-----+-----+--------
(0,1) | unused | | | | |
(0,2) | unused | | | | |
(0,3) | normal | 4007 (c) | 0 (a) | | | (0,3)
(3 rows)
В индексе также только одна запись:
=> SELECT * FROM index_page('vac_s',1);
itemoffset | ctid
------------+-------
1 | (0,3)
(1 row)
Что происходит внутри
Очистка должна обрабатывать и таблицу, и индексы одновременно, и делать это так, чтобы не блокировать работу остальных процессов. Как ей это удается?
Все начинается со сканирования таблицы (с учетом карты видимости, как уже отмечалось). В прочитанных страницах определяются ненужные версии строк и их идентификаторы (tid) записываются в специальный массив. Массив располагается в локальной памяти процесса очистки; для него выделяется фрагмент размером maintenance_work_mem. Значение этого параметра по умолчанию — 64 МБ. Отметим, что это память выделяется сразу в полном объеме, а не по мере необходимости. Правда, если таблица небольшая, то и фрагмент выделяется поменьше.
Дальше одно из двух: либо мы дойдем до конца таблицы, либо выделенная под массив память заканчится. В любом из двух случаев начинается фаза очистки индексов. Для этого каждый из индексов, созданных на таблице, полностью сканируется в поисках записей, которые ссылаются на запомненные версии строк. Найденные записи вычищаются из индексных страниц.
В этом месте мы получаем такую картину: в индексах уже нет ссылок на ненужные версии строк, а в таблице они еще есть. Это ничему не противоречит: выполняя запрос мы либо вообще не попадем на мертвые версии строк (при индексном доступе), либо отметем их при проверке видимости (при сканировании таблицы).
После этого начинается фаза очистки таблицы. Таблица снова сканируется, чтобы прочитать нужные страницы, вычистить из них запомненные версии строк и освободить указатели. Мы можем это сделать, поскольку ссылок из индексов уже нет.
Если на первом проходе таблица не была прочитана полностью, то массив очищается и все повторяется с того места, на котором мы остановились.
Таким образом:
- таблица всегда сканируется два раза;
- если при очистке удаляется так много версий строк, что все они не помещаются в память размером maintenance_work_mem, то все индексы будут полностью сканироваться столько раз, сколько потребуется.
На больших таблицах это может занимать существенное время и создавать значительную нагрузку на систему. Конечно, запросы не будут блокироваться, но «лишний» ввод-вывод тоже неприятен.
Чтобы ускорить процесс, имеет смысл либо вызывать очистку чаще (чтобы за каждый раз очищалось не очень большое количество версий строк), либо выделить больше памяти.
Замечу в скобках, что, начиная с версии 11, PostgreSQL может пропускать сканирование индексов, если в этом нет насущной необходимости. Это должно облегчить жизнь владельцев больших таблиц, в которые строки только добавляются (но не изменяются).
Мониторинг
Как понять, что очистка не справляется с работой за один проход?
Первый способ мы уже видели: можно вызывать команду VACUUM с указанием VERBOSE. Тогда на консоль будет выводиться и информация о фазах выполнения работы.
Во-вторых, начиная с версии 9.6 имеется представление pg_stat_progress_vacuum, которое также содержит всю необходимую информацию.
(Есть еще третий путь — выводить информацию в журнал сообщений, но это работает только для автоочистки, о которой пойдет речь в следующий раз.)
Вставим в таблицу побольше строк, чтобы очистка выполнялась ощутимое время, и все их обновим, чтобы очистке было чем заняться.
=> TRUNCATE vac;
=> INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000);
=> UPDATE vac SET s = 'B';
Уменьшим размер памяти, выделенной под массив идентификаторов:
=> ALTER SYSTEM SET maintenance_work_mem = '1MB';
=> SELECT pg_reload_conf();
Запускаем очистку и, пока она работает, обратимся несколько раз к представлению pg_stat_progress_vacuum:
=> VACUUM VERBOSE vac;
| => SELECT * FROM pg_stat_progress_vacuum \gx
| -[ RECORD 1 ]------+------------------
| pid | 6715
| datid | 41493
| datname | test
| relid | 57383
| phase | vacuuming indexes
| heap_blks_total | 16667
| heap_blks_scanned | 2908
| heap_blks_vacuumed | 0
| index_vacuum_count | 0
| max_dead_tuples | 174762
| num_dead_tuples | 174480
| => SELECT * FROM pg_stat_progress_vacuum \gx
| -[ RECORD 1 ]------+------------------
| pid | 6715
| datid | 41493
| datname | test
| relid | 57383
| phase | vacuuming indexes
| heap_blks_total | 16667
| heap_blks_scanned | 5816
| heap_blks_vacuumed | 2907
| index_vacuum_count | 1
| max_dead_tuples | 174762
| num_dead_tuples | 174480
Тут мы в частности видим:
- название текущей фазы (phase) — мы говорили о трех основных фазах, но вообще их больше;
- общее число страниц таблицы (heap_blks_total);
- число просканированных страниц (heap_blks_scanned);
- число уже очищенных страниц (heap_blks_vacuumed);
- количество проходов по индексам (index_vacuum_count).
Общий прогресс определяется отношением heap_blks_vacuumed к heap_blks_total, но нужно учитывать, что это значение изменяется не плавно, а “рывками” из-за сканирования индексов. Впрочем, основное внимание надо обратить на количество циклов очистки — значение больше 1 означает, что выделенной памяти не хватило для того, чтобы завершить очистку за один проход.
Вывод завершившейся к этому времени команды VACUUM VERBOSE покажет общую картину:
INFO: vacuuming "public.vac"
INFO: scanned index "vac_s" to remove 174480 row versions
DETAIL: CPU: user: 0.50 s, system: 0.07 s, elapsed: 1.36 s
INFO: "vac": removed 174480 row versions in 2908 pages
DETAIL: CPU: user: 0.02 s, system: 0.02 s, elapsed: 0.13 s
INFO: scanned index "vac_s" to remove 174480 row versions
DETAIL: CPU: user: 0.26 s, system: 0.07 s, elapsed: 0.81 s
INFO: "vac": removed 174480 row versions in 2908 pages
DETAIL: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.10 s
INFO: scanned index "vac_s" to remove 151040 row versions
DETAIL: CPU: user: 0.13 s, system: 0.04 s, elapsed: 0.47 s
INFO: "vac": removed 151040 row versions in 2518 pages
DETAIL: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.08 s
INFO: index "vac_s" now contains 500000 row versions in 17821 pages
DETAIL: 500000 index row versions were removed.
8778 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "vac": found 500000 removable, 500000 nonremovable row versions in 16667 out of 16667 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4011
There were 0 unused item pointers.
0 pages are entirely empty.
CPU: user: 1.10 s, system: 0.37 s, elapsed: 3.71 s.
VACUUM
Здесь видно, что всего было выполнено три прохода по индексам, на каждом из которых очищалось 174480 указателей на мертвые версии строк. Откуда такое число? Одна ссылка (tid) занимает 6 байтов, а 1024*1024/6 = 174762 — это число, которое мы видим в pg_stat_progress_vacuum.max_dead_tuples. Реально может использоваться чуть меньше: так гарантируется, что при чтении очередной страницы все указатели на «мертвые» версии точно поместятся в память.
Анализ
Анализ, или, иными словами, сбор статистической информации для планировщика запросов, формально никак с очисткой не связан. Тем не менее мы можем выполнять анализ не только командой ANALYZE, но и совмещать очистку с анализом: VACUUM ANALYZE. При этом сначала выполняется очистка, а затем анализ — никакой экономии не происходит.
Но, как мы увидим позже, автоматическая очистка и автоматический анализ выполняются одним процессом и управляются схожим образом.
Полная очистка (vacuum full)
Как мы видели, обычная очистка освобождает больше места, чем внутристраничная, но и она не всегда решает задачу полностью.
Если таблица или индекс по каким-то причинам сильно выросли в размерах, то обычная очистка освободит место внутри существующих страниц: в них появятся «дыры», которые затем будут использованы для вставки новых версий строк. Но число страниц не изменится, и, следовательно, с точки зрения операционной системы файлы будут занимать ровно столько же места, сколько занимали и до очистки. А это плохо, потому что:
- замедляется полное сканирование таблицы (или индекса);
- может потребоваться больший буферный кэш (ведь хранятся страницы, а плотность полезной информации падает);
- в дереве индекса может появиться “лишний” уровень, который будет замедлять индексный доступ;
- файлы занимают лишнее место на диске и в резервных копиях.
(Единственно исключение составляют полностью очищенные страницы, находящиеся в конце файла — такие страницы «откусываются» от файла и возвращаются операционной системе.)
Если доля полезной информации в файлах опустилась ниже некоторого разумного предела, администратор может выполнить полную очистку таблицы. При этом таблица и все ее индексы перестраиваются полностью с нуля, а данные упаковываются максимально компактно (разумеется, с учетом параметра fillfactor). При перестройке PostgreSQL последовательно перестраивает сначала таблицу, а затем и каждый из ее индексов. Для каждого объекта создаются новые файлы, а в конце перестройки старые файлы удаляются. Следует учитывать, что в процессе работы на диске потребуется дополнительное место.
Для иллюстрации снова вставим в таблицу некоторое количество строк:
=> TRUNCATE vac;
=> INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000);
Как оценить плотность информации? Для этого удобно воспользоваться специальным расширением:
=> CREATE EXTENSION pgstattuple;
=> SELECT * FROM pgstattuple('vac') \gx
-[ RECORD 1 ]------+---------
table_len | 68272128
tuple_count | 500000
tuple_len | 64500000
tuple_percent | 94.47
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 38776
free_percent | 0.06
Функция читает полность всю таблицу и показывает статистику по тому, сколько места какими данными занято в файлах. Основная информация, которая нам сейчас интересна — поле tuple_percent: процент, занятый полезными данными. Он меньше 100 из-за неизбежных накладных расходов на служебную информацию внутри страницы, но тем не менее довольно высок.
Для индекса выводится другая информация, но поле avg_leaf_density имеет тот же смысл: процент полезной информации (в листовых страницах).
=> SELECT * FROM pgstatindex('vac_s') \gx
-[ RECORD 1 ]------+---------
version | 3
tree_level | 3
index_size | 72802304
root_block_no | 2722
internal_pages | 241
leaf_pages | 8645
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 83.77
leaf_fragmentation | 64.25
А вот какой размер занимают таблица и индекс:
=> SELECT pg_size_pretty(pg_table_size('vac')) table_size,
pg_size_pretty(pg_indexes_size('vac')) index_size;
table_size | index_size
------------+------------
65 MB | 69 MB
(1 row)
Теперь удалим 90% всех строк. Строки для удаления выбираем случайно, чтобы в каждой странице с большой вероятностью хоть одна строка, да осталась:
=> DELETE FROM vac WHERE random() < 0.9;
DELETE 450189
Какой размер будут иметь объекты после обычной очистки?
=> VACUUM vac;
=> SELECT pg_size_pretty(pg_table_size('vac')) table_size,
pg_size_pretty(pg_indexes_size('vac')) index_size;
table_size | index_size
------------+------------
65 MB | 69 MB
(1 row)
Мы видим, что размер не изменился: обычная очистка никак не может уменьшить размер файлов. Хотя плотность информации, очевидно, уменьшилась примерно в 10 раз:
=> SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
tuple_percent | avg_leaf_density
---------------+------------------
9.41 | 9.73
(1 row)
Теперь проверим, что получится после полной очистки. Вот какие файлы используются сейчас таблицей и индексами:
=> SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s');
pg_relation_filepath | pg_relation_filepath
----------------------+----------------------
base/41493/57392 | base/41493/57393
(1 row)
=> VACUUM FULL vac;
=> SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s');
pg_relation_filepath | pg_relation_filepath
----------------------+----------------------
base/41493/57404 | base/41493/57407
(1 row)
Теперь файлы заменены на новые. Размер таблицы и индекса существенно уменьшился, а плотность информации, соответственно, увеличилась:
=> SELECT pg_size_pretty(pg_table_size('vac')) table_size,
pg_size_pretty(pg_indexes_size('vac')) index_size;
table_size | index_size
------------+------------
6648 kB | 6480 kB
(1 row)
=> SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
tuple_percent | avg_leaf_density
---------------+------------------
94.39 | 91.08
(1 row)
Обратите внимание, что плотность информации в индексе даже увеличилась по сравнению с первоначальной. Заново создать индекс (B-дерево) по имеющимся данным выгоднее, чем вставлять данные в уже имеющийся индекс строка за строкой.
Функции расширения pgstattuple, которые мы использовали, читают полностью всю таблицу. Если таблица большая, то это неудобно, и поэтому там же есть функция pgstattuple_approx, которая пропускает страницы, отмеченные в карте видимости, и показывает примерные цифры.
Еще более быстрый, но и еще менее точный способ — прикинуть отношение объема данных к размеру файла по системному каталогу. Варианты таких запросов можно найти в вики.
Полная очистка не предполагает регулярного использования, так как полностью блокирует всякую работу с таблицей (включая и выполнение запросов к ней) на все время своей работы. Понятно, что на активно используемой системе это может оказаться неприемлемым. Блокировки будут рассмотрены отдельно, а пока ограничимся упоминанием расширения pg_repack, которое блокирует таблицу только на короткое время в конце работы.
Похожие команды
Есть несколько команд, которые тоже перестраивают таблицы и индексы полностью, и этим похожи на полную очистку. Все они полностью блокируют работу с таблицей, все они удаляют старые файлы данных и создают новые.
Команда CLUSTER во всем аналогична VACUUM FULL, но дополнительно физически упорядочивает версии строк в соответствии с одним из имеющихся индексов. Это дает планировщику возможность более эффективно использовать индексный доступ в некоторых случаях. Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться.
Команда REINDEX перестраивает отдельный индекс на таблице. Фактически, VACUUM FULL и CLUSTER используют эту команду для того, чтобы перестроить индексы.
Команда TRUNCATE логически работает так же, как и DELETE — удаляет все табличные строки. Но DELETE, как уже было рассмотрено, только помечает версии строк как удаленные, что требует дальнейшей очистки. TRUNCATE же просто создает новый, чистый файл. Как правило, это работает быстрее, но надо учитывать, что TRUNCATE полностью заблокирует работу с таблицей на все время до конца транзакции.
Продолжение следует.
