Postgres 8.4.4 (x32 на Win7 x64) очень медленное ОБНОВЛЕНИЕ на маленьком столе
У меня есть очень простое заявление об обновлении:
UPDATE W SET state='thing'
WHERE state NOT IN ('this','that') AND losttime < CURRENT_TIMESTAMP;
Таблица W имеет только 90 строк, хотя столбцы потерянного времени и состояния для каждой строки обновляются каждые ~10 секунд. Существуют индексы состояния и потерянного времени (а также первичный индекс).
Я заметил, что с большими базами данных (т.е. в других таблицах есть много записей, а не в таблице W) в течение определенного периода времени, запрос становится все медленнее и медленнее. После запуска в течение 48 часов я синхронизирую его, запустив в окне запросов PqAdminIII, и его выполнение заняло 17 минут!
У меня есть аналогичный запрос в другой таблице, которая показывает ту же проблему:
UPDATE H SET release='1'
WHERE a NOT IN (SELECT id from A WHERE state!='done') AND release!='1';
У H нет никаких индексов, но я попытался поместить и удалить индекс для H(релиз) без изменений в поведении. Этот запрос, после того как база данных проработала 48 часов и таблица H содержит ~100 тыс. Строк, занимает 27 минут. На сервере Postgres поток будет полностью привязан (100% загрузка ЦП) на время запроса, поэтому не похоже, что существует конфликт между сетью, диском и т. Д.
Таким образом, в общих чертах я вижу, что моя база данных работает, как и ожидалось, в течение примерно 5 минут, а затем постепенно все останавливается, поскольку выполнение базовых команд UPDATE, связанных с обслуживанием, начинает выполняться все дольше и дольше. Ко второму дню требуется час, чтобы выполнить простой цикл обслуживания (горстка ОБНОВЛЕНИЙ), который с самого начала работал ~100 мс. Мне кажется очевидным, что снижение производительности является суперлинейным с количеством информации в базе данных - возможно, N^2 или что-то подобное.
Автовакуум включен по умолчанию. Я прочитал руководство (снова) и не увидел ничего, что выскочило на меня.
Я чешу голову здесь. Я не вижу каких-либо исправлений ошибок, которые кажутся актуальными в примечаниях к выпуску 9.0.1 и 9.0.2. Может ли кто-нибудь помочь мне понять, что происходит? Спасибо м
-xxxx-
Итак, у меня могут быть две проблемы здесь.
Первое обновление теперь работает быстро. Не уверен, что случилось, поэтому я буду исходить из того, что мне нужно чаще запускать VACUUM / ANALYZE или какую-то комбинацию - скажем, каждую минуту или около того. Я действительно хотел бы знать, почему автовакуум не делает это для меня.
Второе обновление продолжает работать медленно. План запроса предполагает, что индексы не используются эффективно и что происходит перекрестное пересечение 80k*30k, что может объяснить суперлинейное время выполнения, которое, как мне кажется, наблюдается. (Все ли согласны с такой интерпретацией плана?)
Я могу преобразовать ОБНОВЛЕНИЕ в ВЫБОР:
SELECT * from H
where a not in (SELECT id from A where state='done') AND release!='1';
с аналогичным временем выполнения (27 минут).
Если я не доверяю оптимизатору postgres и делаю это:
WITH r as (select id from A where state='done')
SELECT a from H
JOIN on H.a=r.id
WHERE H.released='0';
тогда запрос выполняется в ~500 мс.
Как мне перевести эти знания обратно в ОБНОВЛЕНИЕ, которое работает с приемлемой скоростью? Моя попытка:
UPDATE H SET release='1'
FROM A
where A.state!='done' AND release!='1' AND A.id=H.a;
работает около 140 секунд, что быстрее, но все еще очень и очень медленно.
Куда я могу пойти отсюда?
-xxxx-
VACUUM ANALYZE был добавлен как часть "обычного обслуживания", когда приложение будет запускать его примерно раз в минуту или около того, независимо от того, какой автозапуск работает.
Кроме того, переписал второй запрос, чтобы исключить известное для медленного предложения NOT IN, заменив его на "Left Anti-Semi Join" (да?)
UPDATE H SET release='1'
WHERE release='0' AND NOT EXISTS (SELECT * FROM A WHERE id=H.a AND state!='done');
3 ответа
PostgreSQL инвентарь MVCC,
Это означает, что каждый раз, когда вы делаете обновление, создается новая копия строки, а старая помечается как удаленная (но не удаляется физически).
Это замедляет запросы.
Ты должен бежать VACUUM своевременно.
PostgreSQL 8.4.4 работает autovacuum Демон, чтобы сделать это, но это может иметь некоторые проблемы при вашей установке.
Улучшается ли ситуация, когда вы бежите VACUUM вручную?
Проверить с pg_total_relation_size('tablename') не раздуты ли ваши таблицы. В этом случае вам может понадобиться настроить конфигурацию автоочистки.
Другой альтернативой является то, что таблицы заблокированы. Заглянуть в pg_stat_activity или же pg_locks выяснить.