Есть вопрос насчёт полезности ежедневной реиндексации БД. Слышал, что банки делают это раз в пару-тройку дней, некоторые делают раз в неделю/месяц. Как понять, как часто надо делать реинднкс? Наблюдать за распуханием и фрагментацией всех индексов?
Когда нужно делать reindex?
Делать когда производительность начинает деградировать настолько что время отклика системы вот вот превзойдет допустимый порог. Преждевременная оптимизация — корень всех зол(с) Д.Кнут
Как понять, как часто надо делать реинднкс? Наблюдать за распуханием и фрагментацией всех индексов?
Первоначально заметить можно через увеличение процента промаха в буфер-кэш по индексу в pg_statio_*_indexes или мониторя отношение pg_relation_size индекса к таблице. Точно проверить можно через pgstatindex вызов - колонки avg_leaf_density, deleted_pages, empty_pages и leaf_fragmentation.
Как часто - решать вам, но лучше этого избегать, корректируя период вакуума, т.к. таблица нв время reindex заблочится на запись.
К сожалению, единственный способ получить информацию о фрагментации b-tree в постгресе - это использовать ф-цию pgstatindex из расширения pgstattuple, что потребует полного перечитывания индекса. На больших системах это может сгенерить много I/O.
Я использую вот такой запрос:
WITH settings AS (
SELECT setting::bigint AS block_size FROM pg_settings WHERE name = 'block_size'
),
btree_indexes AS (
SELECT
class.oid,
nspace.nspname AS schema,
class.relname AS index,
class.reltuples
FROM pg_class class
LEFT JOIN pg_catalog.pg_am am ON class.relam = am.oid
LEFT JOIN pg_catalog.pg_namespace nspace ON class.relnamespace = nspace.oid
WHERE am.amname = 'btree'
AND class.relpersistence = 'p'
AND nspace.nspname <> 'information_schema'
)
SELECT
btree_indexes.schema,
btree_indexes.index,
btree_indexes.reltuples,
frag.leaf_pages,
frag.avg_leaf_density,
frag.index_size AS total_space,
frag.empty_pages * settings.block_size + frag.deleted_pages * settings.block_size AS dead_space,
CASE frag.leaf_pages WHEN 0 THEN 0
ELSE (frag.leaf_pages * settings.block_size * (100-frag.avg_leaf_density)/100)::bigint
END AS wasted_leaf_space
FROM settings, btree_indexes, pgstatindex(btree_indexes.oid) as frag;
Если wasted_leaf_space и dead_space занимают представительные объемы - то это повод задуматься о пересоздании индекса.
+1, так и надо делать по возможности.
Единственное неудобство такого подхода: индекс, являющийся частью constraint или внешнего ключа, нельзя подменить в транзакции, придется в транзакции дропать все полагающиеся на индекс объекты.
Привет,
я сейчас как раз сделал background worker’a, который в фоне чистит отношения и их индексы (только btree) по аналогии с вакуумом, но только точечно. Очищает только те блоки, где имело место обновление/удаление данных.
По идее, он должен предотвращать распухание индекса.
На доступных мне ресурсах и модельных тестах все ок - система не тормозит, отношения очищаются от старых записей. Однако не хватает реальных нагрузок.
Если у тебя есть проблема с распуханием индекса и возможность параллельно запустить тестовую версию на тех же нагрузках и пронаблюдать состояние, то я могу пропатчить для тебя postgresql 10.4 c таким background worker’ом.
Он делает ту же работу, что и autovacuum, но:
- без полного сканирования отношения, сканируются только измененные блоки.
- индекс (b-tree) также не сканируется полностью, выполняется только поиск средствами индекса и удаление туплов, соответствующих удаляемым блокам.
- Нет таймаута перед запуском очистки: она начинается сразу, как только от бэкендов приходит идентификатор измененного блока. Это в том числе должно предохранять и от раздувания индекса.
- Условные блокировки - если worker не может взять блокировку на блок, то откладывает его в сторону до следующей итерации и переходит к другому (возможно, из того же отношения.
- без полного сканирования отношения, сканируются только измененные блоки.
А каким образом собираются эти изменения блоков?
- Нет таймаута перед запуском очистки: она начинается сразу, как только от бэкендов приходит идентификатор измененного блока. Это в том числе должно предохранять и от раздувания индекса.
Ну таймаут, скорее всего, так или иначе нужен. Во-первых, чтобы накопить критическую массу изменений по блоку, чтобы избежать многократного к нему обращения. Во-вторых, необходимо выдержать время, чтобы удалённые туплы стали невидимыми для текущих транзакций.
Хотелось бы посмотреть на исходные коды вашего расширения, если оно, конечно, выложено в открытый доступ.
backend шлёт id блока в момент сразу после heap_page_prune(), т.е. когда в блоке были обнаружены DEAD-кортежи.
- Нет таймаута перед запуском очистки: она начинается сразу, как только от бэкендов приходит идентификатор измененного блока. Это в том числе должно предохранять и от раздувания индекса.
В момент после вызова heap_page_prune() кортежи уже невидимы для транзакций. Однако да, бэкенд накапливает некоторое количество изменений (задано параметром) перед отправкой. В конце транзакции предусмотрена принудительная отправка.
Вообще там несколько параметров, влияющих на эффективность worker’a, с чем и связано моё желание поработать с реальными данными/загрузкой.
https://www.postgresql.org/message-id/f49bb262-d246-829d-f835-3950ddac503c%40postgrespro.ru
Нужно смотреть тред выше по потоку, там есть ещё четыре патча, обеспечивающих быструю очистку индекса.