Когда нужно делать reindex?

#1

Есть вопрос насчёт полезности ежедневной реиндексации БД. Слышал, что банки делают это раз в пару-тройку дней, некоторые делают раз в неделю/месяц. Как понять, как часто надо делать реинднкс? Наблюдать за распуханием и фрагментацией всех индексов?

#2

Делать когда производительность начинает деградировать настолько что время отклика системы вот вот превзойдет допустимый порог. Преждевременная оптимизация — корень всех зол(с) Д.Кнут

#3

Как понять, как часто надо делать реинднкс? Наблюдать за распуханием и фрагментацией всех индексов?

Первоначально заметить можно через увеличение процента промаха в буфер-кэш по индексу в pg_statio_*_indexes или мониторя отношение pg_relation_size индекса к таблице. Точно проверить можно через pgstatindex вызов - колонки avg_leaf_density, deleted_pages, empty_pages и leaf_fragmentation.

Как часто - решать вам, но лучше этого избегать, корректируя период вакуума, т.к. таблица нв время reindex заблочится на запись.

#4

почему-то никто не написал про create index concurrently и подмену старого индекса

#5

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

#6

+1, так и надо делать по возможности.
Единственное неудобство такого подхода: индекс, являющийся частью constraint или внешнего ключа, нельзя подменить в транзакции, придется в транзакции дропать все полагающиеся на индекс объекты.

#7

Привет,
я сейчас как раз сделал background worker’a, который в фоне чистит отношения и их индексы (только btree) по аналогии с вакуумом, но только точечно. Очищает только те блоки, где имело место обновление/удаление данных.
По идее, он должен предотвращать распухание индекса.
На доступных мне ресурсах и модельных тестах все ок - система не тормозит, отношения очищаются от старых записей. Однако не хватает реальных нагрузок.
Если у тебя есть проблема с распуханием индекса и возможность параллельно запустить тестовую версию на тех же нагрузках и пронаблюдать состояние, то я могу пропатчить для тебя postgresql 10.4 c таким background worker’ом.

#8

Привет!

А чем ваш background worker будет отличаться от autovaccuum?

#9

Он делает ту же работу, что и autovacuum, но:

  1. без полного сканирования отношения, сканируются только измененные блоки.
  2. индекс (b-tree) также не сканируется полностью, выполняется только поиск средствами индекса и удаление туплов, соответствующих удаляемым блокам.
  3. Нет таймаута перед запуском очистки: она начинается сразу, как только от бэкендов приходит идентификатор измененного блока. Это в том числе должно предохранять и от раздувания индекса.
  4. Условные блокировки - если worker не может взять блокировку на блок, то откладывает его в сторону до следующей итерации и переходит к другому (возможно, из того же отношения.
#10
  1. без полного сканирования отношения, сканируются только измененные блоки.

А каким образом собираются эти изменения блоков?

  1. Нет таймаута перед запуском очистки: она начинается сразу, как только от бэкендов приходит идентификатор измененного блока. Это в том числе должно предохранять и от раздувания индекса.

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

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

#11

backend шлёт id блока в момент сразу после heap_page_prune(), т.е. когда в блоке были обнаружены DEAD-кортежи.

  1. Нет таймаута перед запуском очистки: она начинается сразу, как только от бэкендов приходит идентификатор измененного блока. Это в том числе должно предохранять и от раздувания индекса.

В момент после вызова heap_page_prune() кортежи уже невидимы для транзакций. Однако да, бэкенд накапливает некоторое количество изменений (задано параметром) перед отправкой. В конце транзакции предусмотрена принудительная отправка.
Вообще там несколько параметров, влияющих на эффективность worker’a, с чем и связано моё желание поработать с реальными данными/загрузкой.

https://www.postgresql.org/message-id/f49bb262-d246-829d-f835-3950ddac503c%40postgrespro.ru
Нужно смотреть тред выше по потоку, там есть ещё четыре патча, обеспечивающих быструю очистку индекса.

#12

Спасибо, интересная работа!