У нас есть приложение, в котором мы собираем данные на основе временных меток. Поскольку данные за месяц превышают 30 миллионов строк (и растут каждый месяц), мы решили разделить данные по годам и месяцам.
В основном у нас есть одна главная таблица и таблицы разделов, такие как master_y2013m01 и так далее. Из-за нашего стека приложений мы используем триггер перед вставкой для вставки строк в правый раздел и в главную таблицу, а затем триггер пост-вставки, чтобы удалить их из главной таблицы (нам нужна исходная вставка, чтобы вернуть информацию о вставленной строке).
Это означает, что основная таблица быстро заполняется мертвыми кортежами и часто запускает автоочистку (мы используем настройки по умолчанию для автоочистки).
После обновления нашей БД с PostgreSQL 8.4 до 9.1 мы заметили большое увеличение количества операций ввода-вывода и проследили его до автоматического вакуумирования: iotop сообщает о всплесках скорости 4-6 Мбит / с, занимающих 90 секунд, после чего следует 30-секундная пауза. Это большой объем данных, на который стоит обратить внимание, тем более, что основная таблица занимает всего пару мегабайт (включая индексы).
Может ли кто-нибудь объяснить, почему это происходит и как это обойти?
С участием помощь разработчиков Postgres мы нашли ответ, похоже, что, поскольку 9.0 ANALYZE также будет запускаться в дочерних таблицах при запуске в родительских. Поскольку наш вариант использования очень часто запускал автоочистку, он также запускал ANALYZE перед запуском, а также анализировал дочерние таблицы.
Мы решили использовать больший порог для автовакуумирования:
ALTER TABLE ... SET (autovacuum_analyze_threshold = ...);
Это уменьшит частоту ANALYZE и снизит наши значения IO, CPU WAIT и LOAD.