Ответ 1
См. заполнить базу данных в руководстве PostgreSQL, depesz excellent-as -общая статья по этой теме и этот вопрос SO.
(Обратите внимание, что этот ответ относится к загрузке данных в существующую БД или к созданию нового. Если вам интересна производительность восстановления БД с помощью pg_restore или psql выполнения вывода pg_dump, большая часть это не действует, поскольку pg_dump и pg_restore уже выполняют такие действия, как создание триггеров и индексов после завершения восстановления схемы + данных).
Там многое предстоит сделать. Идеальным решением было бы импортировать в таблицу UNLOGGED без индексов, затем изменить ее на регистрацию и добавить индексы. К сожалению, в PostgreSQL 9.4 нет поддержки для изменения таблиц из UNLOGGED для входа в систему. 9.5 добавляет ALTER TABLE ... SET LOGGED, чтобы вы могли это сделать.
Если вы можете отключить свою базу данных в автономном режиме для массового импорта, используйте pg_bulkload.
В противном случае:
-
Отключить любые триггеры в таблице
-
Отбросьте индексы перед запуском импорта, заново создайте их. (Для создания индекса за один проход требуется гораздо меньше времени, чем для добавления к нему одинаковых данных, и полученный индекс намного более компактен).
-
При выполнении импорта в рамках одной транзакции безопасно удалять ограничения внешнего ключа, выполнять импорт и воссоздавать ограничения перед фиксацией. Не делайте этого, если импорт разделен на несколько транзакций, поскольку вы можете ввести неверные данные.
-
Если возможно, используйте
COPYвместоINSERTs -
Если вы не можете использовать
COPY, рассмотрите возможность использования многозначногоINSERT, если это возможно. Вы, кажется, делаете это уже. Не пытайтесь перечислить слишком много значений в одномVALUES; эти значения должны располагаться в памяти несколько раз, поэтому держите их в несколько сотен за утверждение. -
Вставляйте свои вставки в явные транзакции, делая сотни тысяч или миллионы вставок для каждой транзакции. Там нет практического предела AFAIK, но пакетная обработка позволит вам восстановить ошибку, отметив начало каждой партии в ваших входных данных. Опять же, вы, похоже, уже это делаете.
-
Используйте
synchronous_commit=offи огромныйcommit_delayдля уменьшения затрат fsync(). Это не поможет, если вы потратили свою работу на крупные транзакции. -
INSERTилиCOPYпараллельно с несколькими соединениями. Сколько зависит от вашей дисковой подсистемы; как правило, вы хотите, чтобы одно подключение на физический жесткий диск использовалось при использовании прямого прикрепленного хранилища. -
Задайте высокое значение
checkpoint_segmentsи включитеlog_checkpoints. Посмотрите журналы журналов PostgreSQL и убедитесь, что они не жалуются на часто встречающиеся контрольные точки. -
Если вам не нравится потерять весь ваш кластер PostgreSQL (ваша база данных и все остальные в одном кластере) до катастрофического повреждения, если система во время импорта выйдет из строя, вы можете остановить Pg, установить
fsync=off, запустите Pg, сделайте свой импорт, затем (жизненно) остановите Pg и снова установитеfsync=on. См. Конфигурация WAL. Не делайте этого, если в любой базе данных вашей установки PostgreSQL уже есть какие-либо данные. Если вы установитеfsync=off, вы также можете установитьfull_page_writes=off; снова, просто не забудьте включить его после импорта, чтобы предотвратить повреждение базы данных и потерю данных. См. несрочные настройки в руководстве Pg.
Вы также должны посмотреть на настройку вашей системы:
-
Используйте SSD хорошего качества для хранения как можно больше. Хорошие SSD-накопители с надежными защищенными от электропитания кэшами с обратной записью делают транзакции невероятно быстрыми. Они менее полезны, когда вы следуете приведенному выше совету - что уменьшает количество дисков/количество
fsync(), но все равно может быть большой помощью. Не используйте дешевые SSD без надлежащей защиты от сбоя питания, если вы не заботитесь о сохранении ваших данных. -
Если вы используете RAID 5 или RAID 6 для прямого прикрепленного хранилища, остановитесь сейчас. Верните данные, перестройте свой RAID-массив на RAID 10 и повторите попытку. RAID 5/6 безнадежно для производительности навальной записи - хотя хороший RAID-контроллер с большим кешем может помочь.
-
Если у вас есть возможность использовать аппаратный RAID-контроллер с большим кэшем обратной записи с батареей, это может действительно улучшить производительность записи для рабочих нагрузок с большим количеством коммитов. Это не помогает, если вы используете асинхронную фиксацию с commit_delay или если вы делаете меньше транзакций во время массовой загрузки.
-
Если возможно, храните WAL (
pg_xlog) на отдельном диске/диске. Там мало смысла использовать отдельную файловую систему на том же диске. Люди часто предпочитают использовать пару RAID1 для WAL. Опять же, это оказывает большее влияние на системы с высокими коэффициентами фиксации, и это малоэффективно, если вы используете таблицу с разметкой в качестве цели загрузки данных.
Вы также можете быть заинтересованы в Оптимизировать PostgreSQL для быстрого тестирования.