Postgresql Скорость усечения

Question

Мы используем Postgresql 9.1.4 как наш сервер БД. Я пытался ускорить мой набор тестов, поэтому я немного посмотрел на профилирование базы данных, чтобы точно увидеть, что происходит. Мы используем database_cleaner для усечения таблиц в конце тестов. Да, я знаю, что транзакции выполняются быстрее, я не могу использовать их в определенных обстоятельствах, поэтому меня это не касается.

Что меня беспокоит, так это то, почему TRUNCATION занимает так много времени (дольше, чем использование DELETE) и почему на моем CI-сервере ДАЖЕ Дольше.

Прямо сейчас, локально (на Macbook Air) полный набор тестов занимает 28 минут. Хвостовые логи, каждый раз, когда мы усекаем таблицы... то есть:

TRUNCATE TABLE table1, table2  -- ... etc

для выполнения усечения требуется более 1 секунды. Хвост логов на нашем CI-сервере (Ubuntu 10.04 LTS) занимает урезать целые 8 секунд, а сборка занимает 84 минуты.

Когда я переключился на :deletion Стратегия, моя локальная сборка заняла 20 минут, а CI-сервер отключился до 44 минут. Это существенная разница, и я действительно удивлен, почему это может быть. Я настроил БД на CI-сервере, она имеет 16 ГБ оперативной памяти, 4 ГБ shared_buffers... и SSD. Все хорошие вещи. Как это возможно:

а. что это намного медленнее, чем мой Macbook Air с 2 ГБ оперативной памяти
б. что TRUNCATION намного медленнее, чем DELETE, когда документы postgresql явно указывают, что он должен быть намного быстрее.

Какие-нибудь мысли?

Answer 1

Это происходило несколько раз в последнее время, как в SO, так и в списках рассылки PostgreSQL.

TL;DR для ваших последних двух пунктов:

(a) Больший shared_buffers может быть причиной того, что TRUNCATE медленнее на сервере CI. Другая конфигурация fsync или использование ротационных носителей вместо SSD также может быть причиной ошибки.

(Б) TRUNCATE имеет фиксированную стоимость, но не обязательно медленнее, чем DELETE Плюс это делает больше работы. Смотрите подробное объяснение ниже.

ОБНОВЛЕНИЕ: из этого поста возникла значительная дискуссия о производительности pgsql. Смотрите эту ветку.

ОБНОВЛЕНИЕ 2: Улучшения были добавлены в 9.2beta3, которые должны помочь с этим, см. Этот пост.

Подробное объяснение TRUNCATE против DELETE FROM:

Хотя я не эксперт по этой теме, я понимаю, что TRUNCATE имеет почти фиксированную стоимость за стол, в то время как DELETE не менее O(n) для n строк; хуже, если есть какие-либо внешние ключи, ссылающиеся на удаляемую таблицу.

Я всегда предполагал, что фиксированная стоимость TRUNCATE был ниже, чем стоимость DELETE на почти пустом столе, но это совсем не так.

TRUNCATE table; делает больше, чем DELETE FROM table;

Состояние базы данных после TRUNCATE table почти так же, как если бы вы вместо этого запустили:

• DELETE FROM table;
• VACCUUM (FULL, ANALYZE) table; (Только 9.0+, см. Сноску)

... хотя конечно TRUNCATE на самом деле не достигает своих эффектов с DELETE и VACUUM,

Дело в том, что DELETE а также TRUNCATE делайте разные вещи, чтобы вы не просто сравнивали две команды с одинаковыми результатами.

DELETE FROM table; позволяет сохранять мертвые строки и раздуваться, позволяет индексам переносить мертвые записи, не обновляет статистику таблицы, используемую планировщиком запросов и т. д.

TRUNCATE дает вам совершенно новую таблицу и индексы, как будто они просто CREATE редактор Как будто вы удалили все записи, переиндексировали таблицу и сделали VACUUM FULL,

Если вас не волнует, остался ли на столе негодный материал, потому что вы собираетесь снова заполнить его, лучше воспользоваться DELETE FROM table;,

Потому что ты не бежишь VACUUM вы обнаружите, что мертвые строки и записи индекса накапливаются в виде наворотов, которые нужно сканировать, а затем игнорировать; это замедляет все ваши запросы. Если ваши тесты на самом деле не создают и не удаляют столько данных, которые вы можете не заметить или не заботиться, и вы всегда можете сделать VACUUM или два прохода через тестовый прогон, если вы делаете. Лучше пусть агрессивные настройки автовакуума гарантируют, что автовакуум сделает это за вас в фоновом режиме.

Вы все еще можете TRUNCATE все ваши таблицы после всего набора тестов запускаются, чтобы убедиться, что эффекты не накапливаются во многих прогонах. На 9.0 и новее, VACUUM (FULL, ANALYZE); глобально на столе, по крайней мере, так же хорошо, если не лучше, и это намного проще.

IIRC Pg имеет несколько оптимизаций, которые означают, что он может заметить, когда ваша транзакция - единственная, которая может видеть таблицу и в любом случае немедленно пометить блоки как свободные. В тестировании, когда я хотел создать раздувание, мне нужно было иметь более одного одновременного соединения для этого. Я бы не стал на это полагаться.

DELETE FROM table; очень дешево для небольших столов без ф / к реф

к DELETE все записи из таблицы без ссылок на внешние ключи, все Pg должны выполнить последовательное сканирование таблицы и установить xmax встреченных кортежей. Это очень дешевая операция - в основном линейное чтение и полулинейная запись. AFAIK это не должно касаться индексов; они продолжают указывать на мертвые кортежи, пока не будут очищены VACUUM это также помечает блоки в таблице, содержащие только мертвые кортежи, как свободные.

DELETE Дороже только, если есть много записей, если есть много ссылок на внешние ключи, которые должны быть проверены, или если вы посчитаете последующие VACUUM (FULL, ANALYZE) table; необходимо соответствовать TRUNCATE эффекты в пределах стоимости вашего DELETE,

В моих тестах здесь DELETE FROM table; как правило, в 4 раза быстрее, чем TRUNCATE на 0,5 мс против 2 мс. Это тестовая БД на SSD, работающая с fsync=off потому что мне все равно, если я потеряю все эти данные. Конечно, DELETE FROM table; не делает все ту же работу, и если я буду следить за VACUUM (FULL, ANALYZE) table; это намного дороже 21 мс, поэтому DELETE только победа, если мне на самом деле не нужен стол безупречный.

TRUNCATE table; делает намного больше фиксированной работы и уборки, чем DELETE

В отличие от TRUNCATE должен сделать много работы. Он должен выделить новые файлы для таблицы, ее таблицы TOAST, если таковые имеются, и каждого индекса в таблице. Заголовки должны быть записаны в эти файлы, и системные каталоги тоже могут нуждаться в обновлении (не уверены в этом, не проверяли). Затем он должен заменить старые файлы новыми или удалить старые, и должен убедиться, что файловая система зафиксировала изменения с помощью операции синхронизации - fsync() или аналогичной, которая обычно сбрасывает все буферы на диск., Я не уверен, что синхронизация пропущена, если вы используете опцию (поедание данных) fsync=off,

Я недавно узнал, что TRUNCATE Также необходимо очистить все буферы PostgreSQL, связанные со старой таблицей. Это может занять нетривиальное количество времени с огромным shared_buffers, Я подозреваю, что именно поэтому он медленнее на вашем CI-сервере.

Баланс

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

Следовательно, вам может быть лучше сделать DELETE FROM table;,

-

Примечание: в БД до 9.0, CLUSTER table_id_seq ON table; ANALYZE table; или же VACUUM FULL ANALYZE table; REINDEX table; будет ближе к TRUNCATE, VACUUM FULL Импл изменился на гораздо лучше в 9.0.

Answer 2

Брэд, просто чтобы ты знал. Я довольно глубоко изучил очень похожий вопрос.

Смежный вопрос: 30 таблиц с несколькими строками - TRUNCATE самый быстрый способ их очистки и сброса прикрепленных последовательностей?

Пожалуйста, также посмотрите на эту проблему и этот запрос:

https://github.com/bmabey/database_cleaner/issues/126

https://github.com/bmabey/database_cleaner/pull/127

Также эта тема: http://archives.postgresql.org/pgsql-performance/2012-07/msg00047.php

Прошу прощения за то, что написал это как ответ, но я не нашел ссылок на комментарии, возможно, потому что там уже слишком много комментариев.

Answer 3

Я столкнулся с подобной проблемой в последнее время, а именно:

1. Время запуска набора тестов, в котором использовался DatabaseCleaner, сильно различалось в разных системах с сопоставимым оборудованием.
2. Изменение стратегии DatabaseCleaner на :deletion обеспечено улучшение в ~10 раз.

Основной причиной замедления была файловая система с журналированием (ext4), используемым для хранения базы данных. Во время операции TRUNCATE демон ведения журнала (jbd2) использовал ~90% емкости дискового ввода-вывода. Я не уверен, является ли это ошибкой, крайним случаем или фактически нормальным поведением в этих обстоятельствах. Это объясняет, однако, почему TRUNCATE был намного медленнее, чем DELETE - он генерировал намного больше записей на диск. Поскольку я не хотел на самом деле использовать DELETE, я прибег к настройке fsync=off и этого было достаточно, чтобы смягчить эту проблему (в данном случае безопасность данных не была важной).

Answer 4

Пара альтернативных подходов для рассмотрения:

• Создайте пустую базу данных со статическими данными "fixture" и запустите тесты в ней. Когда вы закончите, просто удалите базу данных, которая должна быть быстрой.
• Создайте новую таблицу с именем "test_ids_to_delete", которая содержит столбцы для имен таблиц и идентификаторов первичных ключей. Обновите логику удаления, чтобы вместо этого вставлять в эту таблицу имена идентификаторов / таблиц, что будет намного быстрее, чем запуск удалений. Затем напишите сценарий для запуска в автономном режиме, чтобы фактически удалить данные либо после завершения всего теста, либо в течение ночи.

Первый - это подход "чистой комнаты", а второй означает, что некоторые тестовые данные будут храниться в базе данных дольше. "Грязный" подход с удалением в автономном режиме - это то, что я использую для набора тестов с около 20000 тестов. Да, иногда возникают проблемы из-за наличия "дополнительных" тестовых данных в базе данных dev, но иногда. Но иногда эта "грязь" помогала нам находить и исправлять ошибки, потому что "грязь" лучше имитировала реальную ситуацию, так как подход с чистыми комнатами никогда не будет.