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

Мы используем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 минут. Этоsignificant Разница, и я действительно потрясен, почему это может быть. Я & APOS; венастроенный  БД на сервере CI имеет 16 ГБ оперативной памяти, 4 ГБ shared_buffers ... и SSD. Все хорошие вещи. Как это возможно:

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

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

 Craig Ringer11 июл. 2012 г., 01:48
Когда вы имеете в виду «использование транзакций» Вы имеете в виду открытие транзакции, выполнение некоторых тестов, а затем откат? Потому что, на мой взгляд, это только половина теста. Так много может произойти вCOMMIT время, если вы используетеSERIALIZABLE сделки,DEFERRABLE INITIALLY DEFERRED ограничения и т. д., что принятие изменений теста кажется разумным.
 Szymon Lipiński10 июл. 2012 г., 20:32
Кстати, вы делаете это неправильно ... вы не можете очистить базу данных после теста. Вы должны сделать это ДО запуска теста. Вы не можете быть уверены, что база данных очищена после тестов.
 Szymon Lipiński10 июл. 2012 г., 20:31
Вы используете тесты и базу данных на macbook, а тесты и базу данных на CI-сервере? Тесты и база данных на одной машине?
 Craig Ringer11 июл. 2012 г., 01:51
Как долго это "и т. Д." кстати, т.е. сколько таблиц усекается за один раз? Это очень маленькие таблицы или они содержат немного данных? Я не буду слишком шокирован, если крошечные столы быстрееDELETE FROM чемTRUNCATE какTRUNCATE должен выделить новый файл резервной копии, записать заголовки, заменить его на старый,flush the buffer caches for the tableи fsync. Я подозреваю, что документы, вероятно, должны быть обновлены, чтобы отразить этоTRUNCATE намного быстрее с большими таблицами, но не обязательно с крошечными / пустыми.
 Craig Ringer11 июл. 2012 г., 01:47
Какие параметры postgresql.conf используются? Мне интересно, если вы работаете с fsync = off (хорошо, если вы не против потерять все свои данные, как при тестировании), в этом случае баланс междуDELETE а такжеTRUNCATE может быть другим. Я также был бы заинтересован в вашемshared_buffers .

Ответы на вопрос(4)

Решение Вопроса

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

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

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

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

UPDATE: значительное обсуждение производительности pgsql возникла из этого поста. Увидетьэта тема.

UPDATE 2: Улучшения были добавлены в 9.2beta3, которые должны помочь с этим, см.эта почта.

Detailed explanation of TRUNCATE vs DELETE FROM:

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

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

TRUNCATE table; does more than DELETE FROM table;

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

DELETE FROM table; VACCUUM (FULL, ANALYZE) table; (9.0+ only, see footnote)

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

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

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

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

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

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

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

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

DELETE FROM table; is very cheap for small tables with no f/k refs

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

DELETE только становится дороже, если естьlots записей, если есть много ссылок на внешние ключи, которые необходимо проверить, или если вы посчитаете последующие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; does a lot more fixed-cost work and housekeeping than DELETE

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

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

The balance

Во всяком случае, вы можете видеть, что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.

 brad12 июл. 2012 г., 15:54
ах хорошо, спасибо за разъяснение.
 12 июл. 2012 г., 03:46
@brad Для конкретного случаяTRUNCATEда, я говорю, что мое понимание настолько великоshared_buffers может замедлить ход вещей. Я сам не проверял это, но так это звучит при обсуждении ML. И нет, нетVACCUM сделано после усечения - во время усеченияhas the effect of DELETE FROM с последующимVACUUM FULL ANALYZE;фактически он не работает таким образом и не выполняет эти шаги.
 brad12 июл. 2012 г., 15:56
спасибо за ссылку на этот ML, кстати ... здорово видеть разговоры на такие темы, как эта
 brad11 июл. 2012 г., 20:19
Спасибо за исчерпывающий ответ! Согласно документам & gt; & gt; Он [TRUNCATE] имеет тот же эффект, что и неквалифицированный DELETE для каждой таблицы, но поскольку он на самом деле не сканирует таблицы, он работает быстрее. Кроме того, он немедленно восстанавливает дисковое пространство, а не требует последующей операции VACUUM. & GT; & GT; Так что я не думаю, что это на самом деле пылесос после усечения. Вы предлагаете также, что тот факт, что у меня есть 4 ГБ shared_buffers, на самом деле наносит ущерб производительности?
 11 июл. 2012 г., 06:57
И у них также есть различные типы блокировок: блокировка таблицы или блокировка строки.

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

Связанный вопрос: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

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

 brad16 июл. 2012 г., 03:10
эй, спасибо, Станислав. Я действительно видел те сообщения, которые побудили меня обновить db cleaner, чтобы использовать массовое усечение. Это, однако, мало чем помогло мне. По-прежнему на PG кажется, что стратегия удаления значительно быстрее, чем я и воспользовался.

Create a empty database with static "fixture" data in it, and run the tests in that. When you are done, just just drop the database, which should be fast. Create a new table called "test_ids_to_delete" that contains columns for table names and primary key ids. Update your deletion logic to insert the ids/table names in this table instead, which will be much faster than running deletes. Then, write a script to run "offline" to actually delete the data, either after a entire test run has finished, or overnight.

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

В последнее время я сталкивался с подобной проблемой, т.е.

The time to run test suite which used DatabaseCleaner varied widely between different systems with comparable hardware, Changing DatabaseCleaner strategy to :deletion provided ~10x improvement.

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

Ваш ответ на вопрос