MVCC-6. Очистка

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

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

    Продолжение.
    Postgres Professional
    136.85
    Разработчик СУБД Postgres Pro
    Share post

    Similar posts

    Comments 16

      +2
      Возможно, имеет смысл реализовать VACUUM DEFRAGMENT, который итеративно (по несколько страниц) перенесет записи из конца файла в начало (например, реализовав UPDATE в результате которого сами данные не меняются, а только их физическое расположение), а затем выполнит тоже, что и VACUUM для этой таблицы. Далее прервется для выполнения возможных запросов типа CREATE INDEX, ALTER TABLE и перейдет на следующую итерацию. Таким образом, реализуется перенос данных из конца файла в начало, аналог VACUUM FULL, но без длительной блокировки таблицы. Нагружаться дисковая система будет чуть больше, но такую дефрагментацию можно будет в любой момент прервать и проделанная часть работы сохранится.
        +1

        Дык это уже давно реализовано (например, Максимом Богуком). Но вот именно в ядре такого нет.

          +1
          Спасибо за ответ. Попробовал я проверить, как работает этот инструмент, но так и не понял. Там механизм работы описан таким образом:
          Если кратко то если в таблице есть свободное место то при update записи новая версия пойдет с это свободное место. При этом безусловное предпочтение отдается свободному месту в начале таблицы при его наличии. В итоге если обновлять таблицу (fake updates вида поле=поле) начиная с последней страницы в какой то момент все записи с последней страницы перейдут в свободное место в начале таблицы.


          Я проделал следующее:
          DROP TABLE IF EXISTS __test;
          CREATE TABLE __test as select id,random() as f1,random() as
              f2,random()::text as f3,now() as mtime,(random()>1)::boolean as flag
              FROM generate_series(1,10000000) as t(id);
          select ctid,* from __test order by ctid desc limit 10 ;
          -- тут, насколько я понимаю, в ctid первая цифра - номер страницы, вторая - номер строки
          delete from __test where id < 10000000;
          vacuum __test;
          select ctid,* from __test order by ctid desc limit 10 ;
          -- осталась одна строка в самом конце, как и ожидалось
          update __test set mtime=clock_timestamp() where id=10000000;
          select ctid,* from __test order by ctid desc limit 10 ;
          -- строка переместилась в начало страницы, но не в первую страницу таблицы.
          

          Возможно, я что-то понял не так, но у меня этот механизм не работает. Впрочем, логика понятна. Если перемещать запись в начало таблицы, то придется обновлять две страницы — откуда строку удалили и куда записали. При записи в ту же страницу, обновляется единственная страница.

          select version();
                                                          version                                                 
          --------------------------------------------------------------------------------------------------------
           PostgreSQL 11.3 (Debian 11.3-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-7) 8.3.0, 64-bit
          
            0

            Это работает HOT, пытаясь удержать обновления в пределах той же страницы.
            Думаю, что инструмент использует не SQL UPDATE, а что-то более низкоуровневое. Я не смотрел, но пожалуй надо будет разобраться.

              0
              Я понял, как эта штука работает. При UPDATE, если есть свободное место в той же странице, новая версия строки записывается в нее, но когда место заканчивается, вот тогда запись происходит в начало таблицы. Т.е. нужно делать UPDATE записей в последней странице, пока там место не закончится.
              update foo set a = a where ctid >= '(3,0)';
              UPDATE 117
              update foo set a = a where ctid >= '(3,0)';
              UPDATE 117
              update foo set a = a where ctid >= '(3,0)';
              UPDATE 21
              update foo set a = a where ctid >= '(3,0)';
              UPDATE 0
              

              После чего можно сделать VACUUM. Этот инструмент вполне решает нужную мне задачу. Было бы отлично, если бы эта функция входила в штатную поставку Postgres в какой-нибудь Additional Supplied Modules, например, (если внутри SQL функции или процедуры разрешена команда VACUUM).
                0
                Кстати, что скажешь насчет такого механизма ускорения копирования хвоста таблицы в начало. Задается threshold, насколько заполненной должна быть таблица (70%, например, или используются параметры для запуска автовакуума), VACUUM пробегает по таблице и помечает все страницы хвоста с данными, превышающие этот порог (место, необходимое для данных + 30%), как подлежащие постепенному освобождению (отключает запись в эти страницы при обновлении данных) и при каждом UPDATE строки этих страниц перемещаются в начало, независимо от наличия места в текущей странице. Таким образом, все строки страниц в хвосте таблицы после однократного обновления переместятся в начало независимо от наличия места в текущей странице.
                  0

                  Ну, придумать-то много чего можно. Самая проблема — убедить сообщество в том, что это действительно так необходимо и что у решения нет неприятных побочных эффектов.
                  Сейчас все очарованы магией подключаемых движков хранения. Вот у zheap нет проблем с разрастанием (правда, наверняка есть много других, но это другое дело).

          +1
          Егор, большое спасибо за очередную отличную статью! Вопросы :)

          Поэтому в PostgreSQL плохо сочетаются OLTP- и OLAP-нагрузка в одной базе: отчеты, выполняющиеся часами, не дадут часто обновляемым таблицам вовремя очищаться. Возможным решением может быть создание отдельной «отчетной» реплики.


          Но при этом физически они выглядят одинаково. Пусть на мастере совершилась очистка а на реплике идет отчетная транзакция, которая завершится через пару часов условно.

          WAL sender отправляет сегмент, где страницы уже очищены. Реплика не будет его накатывать, пока транзакция не завершится? И все остальные сегменты тоже выстроятся в очередь.

          Если так то отчетная реплика может существенно отставать от мастера
            +1

            Владимир, спасибо.
            Да, идея именно такая. Реплика будет отставать. Но для отчетов, которые выполняются часами, пара часов отставания обычно не страшна.
            Ведь даже если запустить такой отчет на мастере, за пару часов его работы многое может измениться, но эти изменения в отчет не попадут, потому что — согласованность.

              0
              Будет ли мастер хранить сегмент WAL в этом случае до тех пор, пока реплика его себе не накатит?

              Получается затятная ситуация:
              * Очень длинные отчеты нагружают мастер. Только ли тем, что нужно хранить сегменты WAL? Не страдает ли от этого VACUUM, table bloat? Так ли критично то, что сегменты хранятся полдня? Но задержки такие идут скажем на постоянной основе.
              * Очень длинные отчеты конкурируют между собой. Если один аналитик запустил отчет на полдня, то другие должны учитывать, что не могут получить актуальные данные в этот период.

              Тогда получается, что нужно делать даже 2 реплики — одну для очень долгих отчетов, другую — для отчетов но покороче.
                0
                Будет ли мастер хранить сегмент WAL в этом случае до тех пор, пока реплика его себе не накатит?

                Нет, хранить будет реплика.


                Очень длинные отчеты нагружают мастер. Только ли тем, что нужно хранить сегменты WAL? Не страдает ли от этого VACUUM, table bloat? Так ли критично то, что сегменты хранятся полдня? Но задержки такие идут скажем на постоянной основе.

                Если отчёты работают на мастере, то WAL избыточно хранить не надо. Проблема будет только в откладывании очистки и распухании таблиц.


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

                Это мы уже про реплику говорим, правильно?
                Сами по себе отчёты (=запросы) конкурировать не должны, они выполняются одновременно. При этом реплика может откладывать применение журнальных записей (обычно связанных с очисткой), которые несовместимы с запросами. Ну да, будет отставание. Его можно мониторить.


                Тогда получается, что нужно делать даже 2 реплики — одну для очень долгих отчетов, другую — для отчетов но покороче.

                Это, мне кажется, перебор, слишком сложно. Короткие отчёты и на мастере никому сильно не помешают.


                Я вообще про репликацию думаю отдельную серию написать. Там очень много разных вариантов, можно по-разному настраивать в зависимости от задачи.

                  0
                  Я немного расплывчато сформулировал кейс. Он такой:
                  * Пусть решили сделать «отчетную» реплику по просьбе аналитиков (и чтобы они нам не грузили мастер).
                  * Создали отдельного читающего юзера для всех аналитиков, поставили уровень изоляции Repeatable Read для согласованности, раздали логин-пароль (один на всех)
                  * Аналитики начали экспериментировать, строить свои отчеты.
                  * И вот аналитик Вася запускает отчет на таблице orders, длительность которого 3 часа.
                  * Реплика начала отставать все больше и больше с каждым часом, потому что запрос Васи затронул очень много данных в таблице orders.
                  * Вася не подозревал что получится такой долгий запрос и терпеливо решил подождать.
                  * Аналитик Петя решил посмотреть данные за последний час, он предполагает что данные актуальны и не знает о запросе Васи.
                  * Аналитик Петя строит аггрегаты, получает результат, не подозревая, что он пользуется устаревшими данными.
                  * И не дай бог это некий отчет, который будет использоваться при финансовых расчетах.

                  Если я все правильно понял, такая ситуация вполне имеет место быть.

                  Получается, что придется делать вот такое:
                  * Мониторим отставание (само собой)
                  * Учим аналитиков как смотреть свежесть данных, выводим им где-то например текущее оставание реплики в админке
                  * Учим аналитиков смотреть текущие транзакции, помогаем отследить «ждунов» — запускающих слишком длинные транзакции без согласования с остальными.
                  * Бедным аналитикам приходится кооперироваться между собой в чатиках, составлять расписания «длинных выгрузок» и т.п.

                  Очень неудобно. Как вариант можно сделать «отчетную реплику для коротких запросов» и «отчетную тормозящую реплику» для длинных. И даже может «финансовую реплику» куда ходить будут строго по расписанию.

                  Как Вам такая идея? Может есть идея получше? Кейс очень актуальный.

                  Сорри, что немного не в тему статьи
                    0
                    Реплика начала отставать все больше и больше с каждым часом, потому что запрос Васи затронул очень много данных в таблице orders.

                    Тут логическая ошибка. Реплика будет отставать не из-за того, что кто-то затронул какие-то данные. У запросов на реплике есть обычные снимки, вот они и используются, чтобы понять, конфликтует журнальная запись или нет. Так же, как на мастере.


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

                    Ну, отчёт покажет абсолютно корректные, согласованные данные. Просто несколько устаревшие. Для какого-нибудь ежемесячного закрытия отчётности, например, это вообще не страшно (потому что отчётный период уже закрыт).


                    Ну а так в целом да, всё так. Но ещё раз скажу, что схема с двумя репликами для разных отчетов кажется мне слишком сложной. Не очень длинное и требующее актуальных данных — на мастер, остальное — на реплику.


                    Да, ещё можно настроить max_standby_streaming_delay так, чтобы конфликтующие записи применялись, но с задержкой. Тогда реплика будет отставать не больше, чем на это значение, а у более долгих запросов будет шанс доработать, если они не «наступят» на отсутствующие данные.

                    0
                    Еще вариант: длинные транзакции заранее планируются и запускаются по расписанию. Но тут нужно участие DBA — хотелось бы этого избежать тоже
              +1
              Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться.


              О команде CLUSTER очень часто упоминают и постоянно критикуют ее за то, что ее эффект «выдыхается» (в силу MVCC) и за то, что она блокирует все.

              Когда все-таки ее целесообразно использовать?
                +1

                Скажу честно — не знаю, есть ли реальные примеры успешного применения. Мне не попадались.

              Only users with full accounts can post comments. Log in, please.