Агрессивный Autovacuum на PostgreSQL

Я пытаюсь заставить PostgreSQL агрессивно автоматизировать вакуумную базу данных. В настоящее время я настроил автоматический вакуум следующим образом:

  • autovacuum_vacuum_cost_delay = 0 # Отклонить вакуум на основе стоимости
  • autovacuum_vacuum_cost_limit = 10000 # Максимальное значение
  • autovacuum_vacuum_threshold = 50 # Значение по умолчанию
  • autovacuum_vacuum_scale_factor = 0.2 # Значение по умолчанию

Я заметил, что автоматический вакуум срабатывает только тогда, когда база данных не находится под нагрузкой, поэтому я попадаю в ситуации, когда есть более мертвые кортежи, чем живые кортежи. См. Прилагаемый скриншот для примера. В одной из таблиц есть 23 живых кортежа, но 16845 мертвых кортежей, ожидающих вакуума. Это безумие!

Множество мертвых кортежей

Автоматический вакуум срабатывает, когда тестовый запуск завершается, а сервер базы данных простаивает, чего я не хочу, поскольку я бы хотел, чтобы автоматический вакуум ударялся, когда количество мертвых кортежей превышает 20% живых кортежей + 50, поскольку была настроена база данных. Автоматический вакуум, когда сервер простаивает, бесполезен для меня, поскольку ожидается, что производственный сервер достигнет 1000 секунд обновлений /сек для устойчивого периода, поэтому мне нужен автоматический вакуум для запуска, даже когда сервер находится под нагрузкой.

Есть ли что-то, что мне не хватает? Как заставить автоматический вакуум работать, пока сервер находится под большой нагрузкой?

Обновление

Может ли это быть проблемой блокировки? Указанные таблицы представляют собой сводные таблицы, которые заполняются через триггер после вставки. Эти таблицы заблокированы в режиме SHARE ROW EXCLUSIVE, чтобы предотвратить одновременную запись в ту же строку.

32 голоса | спросил CadentOrange 18 J000000Wednesday12 2012, 17:35:45

5 ответов


34

Eelke почти наверняка прав, что ваша блокировка блокирует autovacuum. Autovacuum предназначен для уклонения от пользовательской активности. Если эти таблицы заблокированы, autovacuum не может их вакуумировать.

Тем не менее, для потомков я хотел привести пример набора настроек для гиперагрессивного autovacuum, так как настройки, которые вы дали, не совсем это делают. Обратите внимание, что сделать autovacuum более агрессивным вряд ли решит вашу проблему. Также обратите внимание, что настройки autovacuum по умолчанию основаны на запуске более 200 тестовых прогонов с использованием DBT2, которые ищут оптимальную комбинацию настроек, поэтому значения по умолчанию следует считать хорошими, если у вас нет веских оснований думать иначе или если ваша база данных не находится вне пределов основной поток для баз данных OLTP (например, крошечная база данных, которая получает 10 тыс. обновлений в секунду или хранилище данных 3 ТБ).

Сначала включите ведение журнала, чтобы вы могли проверить, делает ли autovacuum то, что вы думаете:

log_autovacuum_min_duration = 0

Затем давайте сделаем больше работников autovac и проверим их чаще:

autovacuum_max_workers = 6
autovacuum_naptime = 15s

Понизьте пороги для автоматического вакуума и автоанализа, чтобы запускаться раньше:

autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1

autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05 

Затем давайте сделаем autovacuum менее прерываемым, поэтому он выполняется быстрее, но ценой более сильного воздействия на одновременную активность пользователя:

autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

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

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

Опять же, это вряд ли поможет решить вашу реальную проблему.

ответил Josh Berkus 29 J000000Sunday12 2012, 00:50:06
30

Просто чтобы узнать, какие таблицы пригодны для autovacuum вообще, может использоваться следующий запрос (на основе http://www.postgresql.org/docs/current/static/routine-vacuuming.html ). Обратите внимание, однако, что запрос не ищет настройки для таблицы:

 SELECT psut.relname,
     to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
     to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
     to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
     to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
     to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
         + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
            * pg_class.reltuples), '9G999G999G999') AS av_threshold,
     CASE
         WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
             + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
                * pg_class.reltuples) < psut.n_dead_tup
         THEN '*'
         ELSE ''
     END AS expect_av
 FROM pg_stat_user_tables psut
     JOIN pg_class on psut.relid = pg_class.oid
 ORDER BY 1;
ответил pygrac 22 FebruaryEurope/MoscowbFri, 22 Feb 2013 13:45:25 +0400000000pmFri, 22 Feb 2013 13:45:25 +040013 2013, 13:45:25
9

Да, это проблема блокировки. В соответствии с этой страница (не полная) ВАКУУМ нуждается в расширении SHARE UPDATE EXCLUSIVE, который блокируется уровень блокировки, который вы используете.

Вы уверены, что вам нужен этот замок? PostgreSQL совместим с ACID, поэтому одновременная запись в большинстве случаев не является проблемой, поскольку PostgreSQL прервет одну из транзакций, если произойдет нарушение сериализации.

Также вы можете блокировать строки, используя ВЫБЕРИТЕ ДЛЯ ОБНОВЛЕНИЯ для блокировки строк вместо всей таблицы.

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

ответил Eelke 24 J000000Tuesday12 2012, 09:01:59
5

Увеличение числа автовакуумных процессов и сокращение времени наработки, вероятно, поможет. Вот конфигурация PostgreSQL 9.1, которую я использую на сервере, хранящем информацию о резервном копировании, и в результате получает много активности вставки.

http://www.postgresql.org/docs/тока /статическая /среда-конфигурация-autovacuum.html

autovacuum_max_workers = 6              # max number of autovacuum subprocesses
autovacuum_naptime = 10         # time between autovacuum runs
autovacuum_vacuum_cost_delay = 20ms     # default vacuum cost delay for

Я также попытаюсь снизить cost_delay, чтобы сделать более пылесосом более агрессивным.

Я также могу проверить autovacuuming с помощью pgbench.

http://wiki.postgresql.org/wiki/Pgbenchtesting

Высокий конфликтный пример:

Создать базу данных bench_replication

pgbench -i -p 5433 bench_replication

Запустить pgbench

pgbench -U postgres -p 5432 -c 64 -j 4 -T 600 bench_replication

Проверить состояние автовыпускания

psql
>\connect bench_replicaiton
bench_replication=# select schemaname, relname, last_autovacuum from pg_stat_user_tables;
 schemaname |     relname      |        last_autovacuum        
------------+------------------+-------------------------------
 public     | pgbench_branches | 2012-07-18 18:15:34.494932+02
 public     | pgbench_history  | 
 public     | pgbench_tellers  | 2012-07-18 18:14:06.526437+02
 public     | pgbench_accounts | 
ответил Craig Efrein 18 J000000Wednesday12 2012, 20:30:37
4

Существующий скрипт «qualify for autovacuum» очень полезен, но (как правильно указано) отсутствовали специальные параметры таблицы. Вот модифицированная версия, которая учитывает эти параметры:

WITH rel_set AS
(
    SELECT
        oid,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_vac_threshold,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)::NUMERIC
        END AS rel_av_vac_scale_factor
    FROM pg_class
) 
SELECT
    PSUT.relname,
    to_char(PSUT.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum,
    to_char(PSUT.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
    to_char(C.reltuples, '9G999G999G999')               AS n_tup,
    to_char(PSUT.n_dead_tup, '9G999G999G999')           AS dead_tup,
    to_char(coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_threshold,
    CASE
        WHEN (coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
        THEN '*'
    ELSE ''
    END AS expect_av
FROM
    pg_stat_user_tables PSUT
    JOIN pg_class C
        ON PSUT.relid = C.oid
    JOIN rel_set RS
        ON PSUT.relid = RS.oid
ORDER BY C.reltuples DESC;
ответил Vadim Zingertal 18 AM00000010000002031 2016, 01:06:20

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132