Postgresql: база данных не принимает команды, чтобы избежать потери данных

Question

Получил ошибку при создании / удалении / обновлении запросов:

ОШИБКА: база данных не принимает команды, чтобы избежать потери данных в базе данных "mydb". УКАЗАНИЕ: остановите администратора почты и используйте автономный серверный модуль для очистки этой базы данных. Вам также может потребоваться зафиксировать или откатить старые подготовленные транзакции.

Таким образом, база данных заблокирована, и можно выполнять только запросы SELECT.

Размер базы данных 350 ГБ. 1 таблица (my_table) имеет ~1 миллиард строк.

система: "PostgreSQL 9.3.4 на x86_64-unknown-linux-gnu, скомпилированный gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-битный"

postgresq.conf некоторые настройки:

effective_io_concurrency = 15           # 1-1000; 0 disables prefetching
autovacuum_vacuum_cost_delay = -1

#vacuum_cost_delay = 0                  # 0-100 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200 

Я не использую подготовленные транзакции. Но используются базовые хранимые процедуры (что означает автоматические транзакции, верно?) 50 миллионов раз в день.

В настоящее время выполняется "autovacuum: VACUUM ANALYZE public.My_table (для предотвращения циклического изменения)", это почти 12 часов этого запроса.

Насколько я понимаю, проблема с невакуумированными мертвыми трупами, верно?

Как решить эту проблему и предотвратить это в будущем? Пожалуйста помоги:)

Конец истории ( ~ месяц спустя) Теперь моя большая таблица разбита на тысячи таблиц. Каждый маленький стол пылесосится гораздо быстрее. Конфигурация автовакуума была установлена ​​ближе к настройке по умолчанию. При необходимости я мог бы снова стать более агрессивным, но пока база данных с миллиардами строк работает довольно хорошо.

Таким образом, проблема темы не должна появляться снова.

PS Теперь я смотрю на Postgres-XL как на следующий шаг масштабируемости данных.

Answer 1

Проблема не в мертвых кортежах, а в идентификаторах транзакций, которые контролируют видимость строк. Каждая транзакция получает последовательный XID, так как они 32-битные целые, они в конечном итоге будут обернуты.

Смотрите здесь для более подробной информации: http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html, но короткая версия заключается в том, что все таблицы должны быть VACUUMed (либо вручную, либо с помощью autovacuum), по крайней мере, каждые 2 миллиарда транзакций. Чем дольше вы идете без пылесоса, тем дольше это занимает.

Чтобы исправить вашу текущую проблему, вам не нужно делать VACUUM ANALYZEпросто VACUUM - Я не уверен, какая разница в скорости, но она должна быть быстрее.

На каком оборудовании это работает, и какое у вас maintenance_work_mem установлен в? Вы можете поднять его (возможно, временно), чтобы завершить ВАКУУМ быстрее.

В будущем вам просто нужно больше VACUUM: либо увеличить частоту автовакуума (см. Здесь: https://dba.stackexchange.com/questions/21068/aggressive-autovacuum-on-postgresql, например), либо даже запланировать ручные VACUUM. с хрон. Также посмотрите на vacuum_freeze_min_age и связанные настройки.

Что это за данные и какие транзакции вы выполняете? Это довольно большая таблица, можно ли ее разбить (например, по дате)?

редактировать

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

Отвечая на комментарии

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

Выполнение дампа / восстановления кажется радикальным, и я не могу себе представить, что это будет быстрее, чем заполнение VACUUM.

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

Вы на правильном пути - получение автовакуума, чтобы не отставать от ваших вставок / обновлений, является лучшим решением (регистрация его активности должна помочь понять, что происходит сейчас).

Судя по структуре вашей таблицы, это может быть классический случай разделения таблиц ( http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html) - я прав, думая, что это все вставки, скорее чем обновляет / удаляет? Если вы всегда пишете в один небольшой раздел, вы можете пылесосить его более агрессивно (для каждой таблицы можно настроить автовакуум), а VACUUM заморозить другие.

Answer 2

Я думаю, что у вас нет выбора, кроме как остановить базу данных, перезапустить в автономном режиме и сделать вакуум. Разрешение autovac complete не поможет, потому что после его завершения он перейдет к обновлению системного каталога, чтобы отразить это завершение, и это обновление будет отклонено, поскольку он не может получить необходимый идентификатор транзакции. По крайней мере, это был мой опыт.

Что касается предотвращения этого в будущем, вы регулярно перезапускаете свою базу данных? Если вы перезапускаете свою базу данных каждые 24 часа, но у вас есть таблица, для очистки которой требуется 30 часов, то эта таблица никогда не будет успешно очищена, и в конечном итоге у вас возникнут проблемы.