Очистка базы данных
Общие сведения
Исторические данные образуются из за многоверсионности.
В табличных страницах копятся старые версии строк.
В страницах индексов накапливаются ссылки на старые версии строк.
Какое то время исторические данные нужны, чтобы транзакции могли работать со своими снимками данных. Однако строки становятся не актуальными для снимков даных, становятся "мертвыми", накапливаются и поиск запросов среди этих строк замедляется.
Очистка подразумевает под собой удаление старых версий строк и индексов.
Команда VACUUM
VACUUM FULL высвобождает больше дискового пространства и работает медленнее и требует блокировки обрабатываемых таблиц
VACUUM в отличии от команды выше удаляет неиспользуемые версии строк в таблицах и индексах и помечает пространство свободным, но не возвращает свободное место системе
Карта видимости
Очистка обновляет карту видимости и карту свободного пространства
В карте видимости отмечены страницы, которые содержат только актуальные версии строк, видимые во всех снимках данных
Это страницы, которые давно не изменялись и успели полностью отчиститься от неактуальных версий
Карта видимости применяется для оптимизации отчистки, для ускорения доступа только по индексу, в отмеченных страницах не может быть мертвых версий строк
Карта видимости применяется Для ускорения доступа только по индексу. Получив из индекса ссылку на версию строки, нужно прочитать табличную страницу, чтобы проверить ее видимость. Но если в самом индексе уже есть все нужные столбцы, и при этом страница отмечена в карте видимости, то обращения к таблице можно избежать
Если не обновлять карту видимости, индексный доступ будет работать менее эффективно
Карта свободного пространства
В карте свободного пространства отмечено наличие пустого места внутри страниц
Это место постоянно меняется: при добавлении новых версий строк оно уменьшается, при очистке — увеличивается
Карта используется при вставке новых версий строк, чтобы быстро найти подходящую страницу, на которую поместятся добавляемые данные. Для ускорения поиска карта свободного пространства имеет сложную древовидную структуру
Карта свободного пространства может существовать и для индексов. Но, поскольку индексная запись вставляется в строго определенное место индекса, в карте отмечаются только пустые страницы, образовавшиеся при удалении из них всех записей. Такие страницы исключаются из индексной структуры и при необходимости подключаются затем в подходящее место индекса
Обновление статистики
Обновление статистики ( анализ ), используется для работы оптимизатора, например количество строкв таблицах и распределение данных в столбцах
Для анализа из таблицы читается случайная выборка данных определенного размера. Это позволяет быстро собрать информацию даже по очень большим таблицам. Результат получается не точный,но этого и не требуется. В любом случае данные все время изменяются и постоянно поддерживать абсолютно точную статистику невозможно. Достаточно, чтобы она периодически обновлялась и не слишком сильно отличалась от действительности.Если не обновлять статистику, она перестанет соответствовать реальным данным и оптимизатор станет строить плохие планы выполнения. Из-за этого запросы могут начать выполняться на порядки медленнее, чем могли бы
PostgreSQL упорядочивает события с помощью номеров транзакций
Достаточно старые версии строк должны помечаться как «замороженные»
Для того чтобы не просматривать лишние страницы, в карту видимости добавлен бит, отмечающий те страницы, на которых все версии строк уже заморожены
Если не выполнять заморозку своевременно, сервер не сможет выделить очередной номер транзакции. Это аварийная ситуация: сервер остановится, все незавершенные транзакции оборвутся. После этого администратор должен будет вручную стартовать сервер и выполнить заморозку
Планировщик запросов в PostgreSQL выбирая наиболее удачный запрос, полагается на статистку. Статистика собирается с помощью команды ANALYZE и может вызываться в одиночку и дополнительно с командой VACUUM
Очистка обновляет карту видимости и карту свободного пространства
В карте видимости отмечены страницы, которые содержат только актуальные версии строк, видимые во всех снимках данных. Иными словами, это страницы, которые давно не изменялись и успели полностью очиститься от неактуальных версий
Карта видимости применяется: Для оптимизации очистки и Для ускорения доступа только по индексу
Информация о версионности хранится только для таблиц, но не для индексов, поэтому у индексов не бывает карты видимости
Получив из индекса ссылку на версию строки, нужно прочитать табличную страницу, чтобы проверить ее видимость. Но если в самом индексе уже есть все нужные столбцы, и при этом страница отмечена в карте видимости, то обращения к таблице можно избежать.
Если не обновлять карту видимости, индексный доступ будет работать менее эффективно.
Источник: https://edu.postgrespro.ru/dba1-13/dba1_06_arch_vacuum_overview.html
Результат обычного VACUUM может быть неудовлетворительным, когда вследствие массового изменения или удаления строк в таблице оказывается много мёртвых версий строк. Если у вас есть такая таблица и вам нужно освободить лишнее пространство, которое она занимает, используйте команду VACUUM FULL или, в качестве альтернативы, CLUSTER или один из вариантов ALTER TABLE, выполняющий перезапись таблицы. Эти команды записывают абсолютно новую копию таблицы и строят для неё индексы. Все эти варианты требуют блокировки в режиме ACCESS EXCLUSIVE. Заметьте, что они также на время требуют дополнительного пространства на диске в объёме, приблизительно равном размеру таблицы, поскольку старые копии таблицы и индексов нельзя удалить до завершения создания новых копий.
Если у вас есть таблица, всё содержимое которой периодически нужно удалять, имеет смысл делать это, выполняя только TRUNCATE, а не DELETE и затем VACUUM. TRUNCATE немедленно удаляет всё содержимое таблицы, не требуя последующей очистки (VACUUM или VACUUM FULL) для высвобождения неиспользуемого дискового пространства. Недостатком такого подхода является нарушение строгой семантики MVCC.
Планировщик запросов в PostgreSQL, выбирая эффективные планы запросов, полагается на статистическую информацию о содержимом таблиц. Эта статистика собирается командой ANALYZE, которая может вызываться сама по себе или как дополнительное действие команды VACUUM. Статистика должна быть достаточно точной, так как в противном случае неудачно выбранные планы запросов могут снизить производительность базы данных.
Команду ANALYZE можно выполнять для отдельных таблиц и даже просто для отдельных столбцов таблицы
Для столбцов, которые часто используются в предложениях WHERE, и имеют очень неравномерное распределение данных, может потребоваться более детальная, по сравнению с другими столбцами, гистограмма данных. В таких случаях можно воспользоваться командой ALTER TABLE SET STATISTICS или изменить значение по умолчанию параметра уровня БД default_statistics_target
Для отслеживания возраста самых старых значений XID в базе данных команда VACUUM сохраняет статистику по XID в системных таблицах pg_class и pg_database
SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');
SELECT datname, age(datfrozenxid) FROM pg_database;
Столбец age показывает количество транзакций от граничного значения XID до XID текущей транзакции
Источник: https://postgrespro.ru/docs/postgresql/13/routine-vacuuming
VACUUM ANALYZE
Не точная статистика может снизить производительность БД.
#!/bin/bash
echo "Информация по базе данных"
echo "Версия postgresql"
psql -V
echo "Список баз данных"
sudo -u postgres psql -l
echo "Работающий кластер"
pg_lsclusters
#sudo -u postgres psql -c "SHOW SERVER_VERSION"
echo "*****РАЗМЕР БАЗЫ******"
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('db_name'))"
#Полная очистка
echo "Подождите VACUUM FULL ANALYZE....."
sudo -u postgres psql -c "VACUUM FULL ANALYZE"
#реиндексация
echo "Подождите делается REINDEX...."
sudo -u postgres psql -c "REINDEX DATABASE db_name" -x db_name
echo "Текущий размер базы megaplan..."
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('db_name'))"
#команда оптимизирует индексы — это должно дополнительно ускорять
sudo -u postgres vacuumdb -Z --analyze-in-stages db_name
Автоматическая отчистка
Autovacuum launcher - фоновый процесс периодически запускает рабочие процессы
Autovacuum worker - очищает таблицыотдельной базы данных,требующие обработки
Autovacum динамически реагирует на частоту обновления таблиц: чем активней изменения, тем чаще таблица будет обрабатываться
В системе постоянно присутствует процесс autovacuum launcher, который планирует работу очистки и запускает необходимое число рабочих процессов autovacuum worker, работающих параллельно
Очистка работает постранично, не приводя к блокировкам других транзакций, хотя и создает, конечно, нагрузку на подсистему ввода-вывода
Автоматическая очистка перестанет работать при отключении любого из двух параметров autovacuum или track_counts
Автоочистка должна работать
Отказ от очистки влечет за собой последствия, описанные выше: неконтролируемое разрастание файлов, замедление запросов и риск аварийной остановки сервера. В конечном итоге это приведет к полному параличу системы
VACUUM (без FULL) только высвобождает пространство и делает его доступным для повторного использования. Эта форма команды может работать параллельно с обычными операциями чтения и записи таблицы, так она не требует исключительной блокировки
VACUUM FULL переписывает всё содержимое таблицы в новый файл на диске, не содержащий ничего лишнего, что позволяет возвратить неиспользованное пространство операционной системе. Эта форма работает намного медленнее и запрашивает блокировку в режиме ACCESS EXCLUSIVE для каждой обрабатываемой таблицы
VERBOSE - Выводит подробный отчёт об очистке для каждой таблицы
ANALYZE - Обновляет статистику, которую использует планировщик для выбора наиболее эффективного способа выполнения запроса
DISABLE_PAGE_SKIPPING - Обычно VACUUM пропускает страницы, учитывая карту видимости. Этот параметр отключает пропуск страниц и предназначен для использования только когда целостность карты видимости вызывает подозрения, что возможно при аппаратных или программных сбоях, приводящих к разрушению БД
INDEX_CLEANUP - Указывает, что команда VACUUM должна попытаться удалить элементы индекса, указывающие на «мёртвые» кортежи. Обычно это желательная операция и она выполняется по умолчанию, если только для таблицы, подлежащей очистке, не задан параметр vacuum_index_cleanup
TRUNCATE - Указывает, что команда VACUUM должна попытаться обрезать пустые страницы в конце таблицы, чтобы освободившееся место было возвращено операционной системе. Обычно это желательная операция и она выполняется по умолчанию, если только для таблицы, подлежащей очистке, не сброшен параметр vacuum_truncate
#Для нескольких параметров используются скобки
VACUUM ( VERBOSE, ANALYZE, TRUNCATE, INDEX_CLEANUP, PARALLEL 3 );
ANALYZE
ANALYZE - собирает статистику базы данных
ANALYZE собирает статистическую информацию о содержимом таблиц в базе данных и сохраняет результаты в системном каталоге pg_statistic. Впоследствии планировщик запросов будет использовать эту статистику для выбора наиболее эффективных планов выполнения запросов
Дополнительно можно задать для таблицы список имён столбцов, в этом случае статистика будет собираться только по этим столбцам
К дополнительным параметрам относится verbose для более подробного вывода сообщений
Если автоочистка отключена, рекомендуется запускать ANALYZE время от времени, либо после кардинальных изменений в таблице. Точная статистика помогает планировщику выбрать наиболее эффективный план запроса и тем самым увеличивает скорость выполнения запроса. Обычно для баз, где данные в основном читаются, выполняют VACUUM и ANALYZE раз в день, во время наименьшей активности. (Этого будет недостаточно, если данные меняются очень активно.)
ANALYZE запрашивает для целевой таблицы блокировку только на чтение, так что эта команда может выполняться параллельно с другими операциями с таблицей
В больших таблицах ANALYZE не просматривает все строки, а обрабатывает только небольшую случайную выборку
testdb=# create table bloat(
testdb(# id integer GENERATED ALWAYS AS IDENTITY,
testdb(# d time
testdb(# d timestamptz
testdb(# ) WITH ( autovacuum_enabled = off );
CREATE TABLE
testdb=# --В данном примере была создана таблица с отключенной автоочисткой
testdb=# --Заполнить таблицу данными
testdb=# --Создадим индекс
testdb=#
testdb=# --В данном примере была создана таблица с отключенной автоочисткой
--Заполнить таблицу данными
--Создадим индекс
INSERT INTO bloat (d)
SELECT current_timestamp FROM generate_series(1,100000);
INSERT 0 100000
testdb=#
testdb=# CREATE INDEX ON bloat(d);
CREATE INDEX
testdb=#
testdb=# \d bloat
Table "public.bloat"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+------------------------------
id | integer | | not null | generated always as identity
d | timestamp with time zone | | |
Indexes:
"bloat_d_idx" btree (d)
testdb=#
testdb=# --На данный момент все строки имеют только одну актуальную версию
testdb=#
testdb=# --Теперь обновим часть строк
testdb=# UPDATE bloat SET d = d + interval '1 day' WHERE id <= 10000;
UPDATE 10000
testdb=#
testdb=# --Запустим очистку вручную в режиме verbose
testdb=#
testdb=# VACUUM ( verbose ) bloat;
testdb=#
testdb=# --Из вывода можно увидеть, что из таблицы были вычищены мертвые версии строк
testdb=# --INFO: scanned index "bloat_d_idx" to remove 10000 row versions
testdb=# --INFO: table "bloat": removed 10000 dead item identifiers in 55 pages
testdb=# --8 index pages are currently deleted, of which 0 are currently reusable.
В данном случае очистка вычищает неактуальные версии строк из страниц. В страницах образуется свободное пространство, которое затем используется для размещения новых данных. Но освободившееся место не возвращается операционной системе, то есть с точки зрения ОС размер файлов данных не уменьшается
В случае индексов (B-деревьев) дело осложняется тем, что если на странице не хватает места для размещения индексной записи, страница расщепляется на две. Получившиеся страницы уже никогда не объединяются, даже если из них будут удалены все индексные записи
Если выполняется одномоментное изменение большого объема данных или в системе присутствуют долгие транзакции (удерживающие снимки данных и не позволяющие вычищать неактуальные версии строк), очистка не сможет своевременно освобождать место. В результате размер таблиц и индексов может продолжать увеличиваться.Разрастание файлов данных ведет не только к перерасходу места на диске (в том числе и для резервных копий), но и к ухудшению производительности.
Просмотр раздувания базы данных
Источник: https://wiki.postgresql.org/wiki/Show_database_bloat
Эти запросы дают лишь приблизительную оценку активности по увеличению таблицы и не должны рассматриваться как 100% точное представление о пространстве, занимаемом объектами базы данных. Чтобы получить более точную информацию о раздувании базы данных, следует обратиться к модулям pgstattuple или pg_freespacemap contrib.
pgstattuple
pgstattuple - предоставляет различные функции для получения статистики на уровней кортежей
Право EXECUTE для них имеет только роль pg_stat_scan_tables
Функция pgstattuple возвращает физическую длину отношения, процент «мёртвых» кортежей и другую информацию. Она может быть полезна для принятия решения о необходимости очистки. В аргументе передаётся имя (возможно, дополненное схемой) или OID целевого отношения
CREATE EXTENSION pgstattuple;
CREATE EXTENSION
megatest=# SELECT * FROM pgstattuple('pg_catalog.pg_proc');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
1064960 | 3785 | 960365 | 90.18 | 4 | 2079 | 0.2 | 69460 | 6.52
(1 row)
table_len bigint Физическая длина отношения в байтах
tuple_count bigint Количество «живых» кортежей
tuple_len bigint Общая длина «живых» кортежей в байтах
tuple_percent float8 Процент «живых» кортежей или доля полезной информации (не 100% из-за накладных расходов).
dead_tuple_count bigint Количество «мёртвых» кортежей
dead_tuple_len bigint Общая длина «мёртвых» кортежей в байтах
dead_tuple_percent float8 Процент «мёртвых» кортежей
free_space bigint Общий объём свободного пространства в байтах
free_percent float8 Процент свободного пространства
Функция pgstattuple получает блокировку отношения только для чтения. Таким образом, её результаты отражают не мгновенный снимок; на них будут влиять параллельные изменения
Чтобы не читать всю таблицу целиком, можно попросить pgstattuple показать приблизительную информацию:
SELECT * FROM pgstattuple_approx('bloat') \gx
Функция pgstatindex возвращает запись с информацией об индексе типа B-дерево
SELECT * FROM pgstatindex('pg_cast_oid_index');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 0 | 16384 | 1 | 0 | 1 | 0 | 0 | 57.21 | 0
(1 row)
version integer Номер версии B-дерева
tree_level integer Уровень корневой страницы в дереве
index_size bigint Общий объём индекса в байтах
root_block_no bigint Расположение страницы корня (0, если её нет)
internal_pages bigint Количество «внутренних» страниц (верхнего уровня)
leaf_pages bigint Количество страниц на уровне листьев
empty_pages bigint Количество пустых страниц
deleted_pages bigint Количество удалённых страниц
avg_leaf_density float8 Средняя плотность(заполненность) страниц на уровне листьев
leaf_fragmentation float8 Фрагментация на уровне листьев(характеристика физической упорядоченности страниц)
Функция pgstatginindex возвращает запись с информацией об индексе типа GIN
SELECT * FROM pgstatginindex('test_gin_index');
Функция pgstathashindex возвращает запись с информацией о хеш-индексе
select * from pgstathashindex('con_hash_index');
Функция pg_relpages возвращает число страниц в отношении.
Полная очистка
Команда VACUUM FULL полностью перезаписывает содержимое таблицы и ее индексов, минимизируя занимаемое место. Однако этот процесс требует исключительной блокировки таблицы и поэтому не может выполняться параллельно с другими транзакциями
REINDEX устанавливает блокировки на запись в таблицу и на использование перестраиваемого индекса, поэтому транзакции, пытающиеся изменить таблицу или планировать запрос к ней, будут приостановлены
Карта видимости
Карта видимости - специальная информация, которая хранится рядом с каждой табличкой. В этой карте отмечены те страницы, на которых все версии строк видны на всех снимках
Карта видимости помогает отчистке пропускать таблицу, если в ней нет актуальных версий строк, в противном случае придется перессматривать всю таблицу на наличие актуальных строк, что занимает лишнее время.
В индексе нет информации о версионности. Перед тем как обращаться к индексу, необходимо проверить информацию о наличии актуальных строк. Это убивает смысл индексного чтения, так как все равно придется перечитывать саму таблицу, однако если страница отмечена в карте индекса, тогда видимость можно не проверять, напрямую обращаясь к данным через индекс
REINDEX
Если используется команда VACUUM FULL, индексы перестраиваются заново
REINDEX сразу после a VACUUM FULL бесполезен
REINDEX - перестраивает индекс, обрабатывая данные таблицы, к которой относится индекс, заменяя старую копию индекса
По другому REINDEX удаляет ссылки на мертвые версии строк
Переиндексация применяется если индекс был поврежден, если индекс стал раздутым ( в нем много пустых строк или почти пустых страниц )
REINDEX перестраивает индексы в таблице, схеме, а также в соответствующей таблице TOAST, если такая имеется
SYSTEM перестраивает все индексы в системных каталогах текущей базы данных
Можно указать имя определенного индекса
CONCURRENTLY - перестроит индекс, не устанавливая никаких блокировок, которые бы предотвращали добавление, изменение или удаление записей в таблице, тогда как по умолчанию операция перестроения индекса блокирует запись (но не чтение) в таблице до своего завершения. При переиндексации в неблокирующем режиме есть ряд особенностей
TABLESPACE - Задаёт новое табличное пространство, в котором будут перестроены индексы. Однако неблокирующее перестроение выполняется дольше и может завершиться неудачно (из-за взаимоблокировок) — в таком случае индекс потребуется еще раз перестроить.
В случае подозрений в повреждении индекса таблицы пользователя, этот индекс или все индексы таблицы можно перестроить, используя команду REINDEX INDEX или REINDEX TABLE
Всё усложняется, если возникает необходимость восстановить повреждённый индекс системной таблицы. В этом случае важно, чтобы система сама не использовала этот индекс
Чтобы надёжно восстановить рабочее состояние, сервер следует запускать с параметром -P, который отключает использование индексов при поиске в системных каталогах
Один из вариантов сделать это — выключить сервер PostgreSQL и запустить его снова в однопользовательском режиме, с параметром -P в командной строке. Затем можно выполнить REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE или REINDEX INDEX, в зависимости от того, что вы хотите восстановить
Каждый процесс, выполняющий операцию REINDEX, будет выдавать информацию о ходе её выполнения, отображаемую в представлении pg_stat_progress_create_index
#Перестроение одного индекса
REINDEX INDEX my_index;
#Перестроение всех индексов в определённой базе данных, в предположении, что целостность системных индексов под сомнением:
export PGOPTIONS="-P"
psql broken_db
broken_db=> REINDEX DATABASE broken_db;
#Перестроение всех индексов таблицы, допускающее одновременные операции чтения и записи
REINDEX TABLE CONCURRENTLY my_broken_table;
Представление pg_stat_activity в PostgreSQL предоставляет информацию о текущей активности серверного процесса, включая его состояние и текущий запрос
#Проверяем что автоотчистка запущена
SELECT pid, backend_start, backend_type
FROM pg_stat_activity
WHERE backend_type = 'autovacuum launcher';
#Выключаем автоочистку
ALTER SYSTEM SET autovacuum = off;
#Перечитываем конфигурацию
SELECT pg_reload_conf();
#Проверяем, что процесс автоотчистки отключен
SELECT pid, backend_start, backend_type
FROM pg_stat_activity
WHERE backend_type = 'autovacuum launcher';
#Восстанавливаем автоотчистку
ALTER SYSTEM RESET autovacuum;
#Перечитываем конфигурацию
SELECT pg_reload_conf();
Источники
Связанные темы
Оптимизация запросов в Postgresql
Использование модуля pg_stat_statements в postgresql