Мвцк-6. Очистка

Мы начали с вопросов, связанных с изоляция , сделал отступление по поводу низкоуровневая организация данных , тогда мы подробно поговорили о строковых версиях и как получаются версии снимки данных .

Последний раз мы говорили о ГОРЯЧИХ обновлениях и внутристраничной чистке, а сегодня разберемся со всем известной регулярной чисткой, вакуум обыкновенный .

Да, о ней уже столько написано, что вряд ли я скажу что-то новое, но полнота картины требует жертв.

Потерпи.

Обычная чистка (пылесос)

Что дает уборка?

Он работает в пределах одной страницы таблицы и не влияет на индексы.

Основная, «обычная» очистка выполняется командой ВАКУУМ и мы будем называть ее просто очисткой (а об автоочистке поговорим отдельно).

Итак, очистка обрабатывает таблицу полностью.

Он очищает не только ненужные версии строк, но и ссылки на них из всех индексов.

Обработка происходит параллельно с другой деятельностью в системе.

Таблицу и индексы можно использовать обычным способом как для чтения, так и для модификации (однако одновременное выполнение таких команд, как 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';
 

   

Об этом мы поговорим в следующий раз, а пока – для экспериментов – нам важно управлять очисткой вручную.

В настоящее время в таблице есть три версии строки, и каждая связана с индексом:

=> 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)

Обратите внимание, что первые два указателя получили статус неиспользуемых, а не мертвых, как в случае с внутристраничной очисткой.

И еще раз о горизонте транзакций

Давайте снова начнем предыдущий опыт.

=> 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 неудаляемые версии строк - в таблице найдены 2 версии, которые невозможно удалить,
• 1 версию мертвой строки пока удалить невозможно, из них 1 «мертвая»,
• самый старый xmin показывает текущий горизонт.

Поэтому 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 может пропустить сканирование индекса если в этом нет острой необходимости.

Это должно облегчить жизнь владельцам больших таблиц, в которых только добавляются (но не изменяются) строки.

Мониторинг

Во-вторых, начиная с версии 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

Здесь мы видим, в частности:

• название текущей фазы (фазы) – мы говорили о трёх основных фазах, но в целом они более ;
• общее количество страниц таблицы (heap_blks_total);
• количество просканированных страниц (heap_blks_scanned);
• количество уже очищенных страниц (heap_blks_vacuumed);
• количество проходов индекса (index_vacuum_count).

Однако основное внимание следует уделить количеству циклов очистки — значение больше 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

Здесь вы можете видеть, что всего было выполнено три прохода по индексам, каждый из которых очистил 174 480 указателей на неработающие версии строк.

Откуда взялось это число? Одна ссылка (tid) занимает 6 байт, а 1024*1024/6 = 174762 — это число, которое мы видим в pg_stat_progress_vacuum.max_dead_tuples. В действительности можно использовать чуть меньше: это гарантирует, что при чтении следующей страницы все указатели на «мертвые» версии точно поместятся в память.

Анализ

Однако мы можем выполнить анализ не только с помощью команды АНАЛИЗ, но и совместить очистку с анализом: ВАКУУМНЫЙ АНАЛИЗ.

В этом случае сначала производится очистка, а затем анализ – экономии не происходит. Но, как мы увидим позже, автоматическая очистка и автоматический анализ выполняются одним и тем же процессом и управляются аналогичным образом.

Полная очистка (полный пылесос) Как мы видели, обычная очистка освобождает больше места, чем очистка страницы, но не всегда полностью решает проблему.

Если таблица или индекс по каким-то причинам значительно увеличились в размерах, то регулярная очистка освободит место внутри существующих страниц: в них появятся «дыры», которые затем будут использоваться для вставки новых версий строк.

Но количество страниц не изменится, а, следовательно, с точки зрения операционной системы файлы будут занимать ровно столько же места, сколько занимали до очистки.

И это плохо, потому что:

• полное сканирование таблицы (или индекса) замедляется;
• может потребоваться больший буферный кэш (ведь страницы сохраняются, а плотность полезной информации снижается);
• в дереве индексов может появиться «лишний» уровень, что замедлит доступ к индексу;
• файлы занимают дополнительное место на диске и в резервных копиях.

) Если доля полезной информации в файлах падает ниже определенного разумного предела, администратор может провести полную очистку таблицы.

В этом случае таблица и все ее индексы полностью перестраиваются с нуля, а данные упаковываются максимально компактно (разумеется, с учетом параметра 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 полностью заблокирует работу с таблицей на все время до окончания транзакции.

Продолжение .

Теги: #postgresql #sql #tuples #vacuum #vacuum

• Модификации Программного Обеспечения, Вносимые Членами Gomember, Дают Энергию Обществу Инженеров-Нефтяников.

  19 Oct, 24

• Кейс Из России: Успешный И Неудачный Опыт Запуска На Product Hunt

  19 Oct, 24

• Brandyourself — Бесплатный Инструмент Для Управления Вашей Личной Репутацией В Интернете.

  19 Oct, 24

• Будет Четко Указан Уровень Совместимости Игр Со Steam Deck.

  19 Oct, 24

• Быстрое Освоение 3D-Моделирования В Zbrush.

  19 Oct, 24

• Песочница: Один День Из Жизни Нью-Йорка В Миниатюре

  19 Oct, 24

• Файлообменник 2010: Кто В Топ-100?

  19 Oct, 24

• Как Мы Запустили Роутер На Indiegogo Или Какие Возможности Есть На Рынке Связи

  19 Oct, 24

• Восстановление Mbr В Windows 7

  19 Oct, 24

• Небольшое Объявление

  19 Oct, 24