Вольный перевод статьи «7 советов по массовой загрузке данных PostgreSQL» Иногда возникает необходимость загрузить большой объем данных в базу данных PostgreSQL за несколько простых шагов.
Эту практику обычно называют массовым импортом, когда источником данных служат один или несколько больших файлов.
Иногда этот процесс может быть неприемлемо медленным.
Есть несколько причин такой плохой работы.
Индексы, триггеры, внешние и первичные ключи или даже запись файлов WAL могут вызывать задержки.
В этой статье мы дадим несколько практических советов по массовому импорту данных в базу данных PostgreSQL. Однако могут возникнуть ситуации, когда ни один из них не является эффективным решением проблемы.
Мы рекомендуем читателям рассмотреть преимущества и недостатки любого метода, прежде чем применять его.
Совет 1. Переведите целевую таблицу в нерегистрируемый режим.
В PostgreSQL9.5 и выше целевую таблицу можно переключить в режим без регистрации и после загрузки данных вернуть в режим с записью.
Нерегистрируемый режим гарантирует, что PostgreSQL не отправляет операции загрузки таблицы в журнал упреждающей записи (WAL).ALTER TABLE <target table> SET UNLOGGED; <bulk data insert operations…> ALTER TABLE <target table> LOGGED;
Это может значительно ускорить процесс загрузки.
Однако, поскольку эти операции не протоколируются, данные невозможно восстановить в случае сбоя или незапланированного сбоя службы PostgreSQL во время загрузки.
PostgreSQL автоматически обнаруживает любую нерегистрируемую таблицу после перезапуска.
Кроме того, нерегистрируемые таблицы не реплицируются на подчиненные серверы.
В таких случаях желательно остановить накат изменений в реплике перед загрузкой и восстановить ее после.
В зависимости от объема данных на мастере и количества реплик восстановление репликации может занять довольно длительное время, что может не соответствовать требованиям, предъявляемым к системам высокой доступности.
Мы рекомендуем следующие подходы к заполнению данных в нерегистрируемые таблицы:
- создание резервной копии таблицы перед переходом в нелогируемый режим;
- восстановление переноса изменений на реплики после загрузки данных;
- использование массовых нерегистрируемых вставок для таблиц, которые можно легко повторно загрузить, например больших таблиц поиска или таблиц измерений.
Совет 2. Удаление и повторное создание индексов
Существующие индексы могут вызывать значительные задержки при массовом импорте данных.Это связано с тем, что при добавлении новой строки соответствующая строка индекса также должна быть обновлена.
Мы рекомендуем, если возможно, удалить индексы целевой таблицы перед запуском массового импорта и восстановить их после завершения загрузки.
Хотя создание индексов для больших таблиц может занять много времени, в целом это будет быстрее, чем обновление индексов во время загрузки.
DROP INDEX <index_name1>, <index_name2> … <index_name_n>
<bulk data insert operations…>
CREATE INDEX <index_name> ON <target_table>(column1, …,column n)
Непосредственно перед созданием индексов может быть целесообразно временно увеличить параметр конфигурации.
Maintenance_work_mem .
Выделение дополнительной рабочей памяти может помочь быстрее создавать индексы.
Другой способ защитить себя — сделать копию целевой таблицы в той же базе данных с существующими данными и индексами.
Эту вновь скопированную таблицу затем можно протестировать с помощью массового импорта, используя оба сценария: удаление и перестроение индексов или их динамическое обновление.
Затем метод, показавший наилучшую производительность, можно использовать в основной таблице.
Совет 3. Удаление и повторное создание внешних ключей
Как и индексы, ограничения внешнего ключа могут влиять на производительность массового импорта, поскольку каждый внешний ключ в каждой вставленной строке должен проверяться на соответствие соответствующему первичному ключу.Неявно PostgreSQL использует специальный триггер для выполнения этой проверки.
Когда загружается большое количество строк, этот триггер должен срабатывать для каждой строки, что увеличивает накладные расходы.
Если это не запрещено бизнес-требованиями, мы рекомендуем удалить все внешние ключи в целевой таблице, загрузить все данные в одной транзакции и повторно создать внешние ключи после фиксации транзакции.
ALTER TABLE <target_table>
DROP CONSTRAINT <foreign_key_constraint>;
BEGIN TRANSACTION;
<bulk data insert operations…>
COMMIT;
ALTER TABLE <target_table>
ADD CONSTRAINT <foreign key constraint>
FOREIGN KEY (<foreign_key_field>)
REFERENCES <parent_table>(<primary key field>).
;
Опять увеличивая параметр Maintenance_work_mem поможет повысить производительность при регенерации внешних ключей.
Совет 4. Отключение триггеров
Триггеры INSERT или DELETE (если процесс загрузки также включает в себя удаление записей из целевой таблицы) могут привести к замедлению процесса массового импорта данных.Это связано с тем, что каждый триггер содержит программную логику, которую необходимо протестировать, и операции, которые необходимо выполнять сразу после каждой вставки или удаления строки.
Мы рекомендуем деактивировать все триггеры в целевой таблице перед массовым импортом данных и включить их после завершения загрузки.
Отключение всех триггеров также отключает системные триггеры, которые обеспечивают проверку ограничений внешнего ключа.
ALTER TABLE <target table> DISABLE TRIGGER ALL;
<bulk data insert operations…>
ALTER TABLE <target table> ENABLE TRIGGER ALL;
Совет 5. Используйте команду КОПИРОВАТЬ.
Мы рекомендуем использовать стандартную команду PostgreSQL — COPY для загрузки данных из одного или нескольких файлов.
COPY оптимизирован для массовой загрузки данных.
Это более эффективно, чем выполнение большого количества операторов INSERT или даже одного INSERT с несколькими включениями выражения VALUE. COPY <target table> [( column1>, … , <column_n>)]
FROM '<file_name_and_path>'
WITH (<option1>, <option2>, … , <option_n>)
Другие преимущества использования команды COPY включают в себя:
- поддержка импорта как из текстовых, так и из двоичных файлов;
- работа в транзакционном режиме;
- возможность указать структуру исходных файлов;
- возможность ограничить выбор загружаемых данных с помощью предложения WHERE.
Совет 6. Используйте оператор INSERT с выражением, состоящим из нескольких значений VALUE.
Выполнение нескольких тысяч или даже сотен тысяч операторов INSERT — плохое решение для массового импорта данных.Это связано с тем, что каждая отдельная команда INSERT анализируется и подготавливается оптимизатором запросов, проходит все проверки целостности, упаковывается в отдельную транзакцию и записывается в WAL. Использование инструкции INSERT с несколькими включениями выражения VALUE может помочь избежать этих накладных расходов.
INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>)
VALUES
(<value a>, <value b>, …, <value x>),
(<value 1>, <value 2>, …, <value n>),
(<value A>, <value B>, …, <value Z>),
(<value i>, <value ii>, …, <value L>),
.
;
На производительность INSERT с несколькими VALUES влияют существующие индексы.
Мы рекомендуем удалять индексы перед запуском команды и впоследствии создавать их заново.
Еще один аспект, который следует учитывать, — это общий объем оперативной памяти, доступной PostgreSQL для выполнения INSERT с несколькими значениями.
При запуске такого INSERT в ОЗУ должно поместиться большое количество входных значений, и если доступной памяти недостаточно, процесс может завершиться сбоем.
Рекомендуем установить параметр эффективный_cache_size до значения 50%, а параметр общий_буфер до значения 25% от общего объема оперативной памяти компьютера.
Кроме того, по соображениям безопасности при выполнении серии INSERT с несколькими значениями каждый оператор будет выполняться с ограничением в 1000 строк.
Совет 7: Запускаем АНАЛИЗ
Это не связано с улучшением производительности массового импорта, но мы настоятельно рекомендуем запустить команду АНАЛИЗИРОВАТЬ в целевой таблице сразу после завершения вставки.Большое количество новых строк вызовет значительное смещение данных в столбцах и приведет к устареванию существующей статистики таблицы.
Если оптимизатор запросов использует устаревшую статистику, производительность запросов может быть неприемлемо низкой.
Запуск команды ANALYZE обновит существующую статистику.
Заключение
Массовый импорт данных для приложений баз данных происходит не каждый день, но это влияет на производительность запросов.Именно поэтому необходимо максимально сократить время загрузки.
Одна вещь, которую администраторы баз данных могут сделать, чтобы свести к минимуму вероятность каких-либо неожиданностей, — это выполнить оптимизацию нагрузки в тестовой среде с аналогичным сервером и аналогично настроенным PostgreSQL. Существуют разные сценарии загрузки данных, и лучше попробовать каждый метод и выбрать тот, который хорошо работает. Теги: #postgresql #производительность postgresql
-
«До 10 Тысяч»: 50 Обзоров Аудиотехники
19 Oct, 24 -
Skype Полностью Переходит В Облако
19 Oct, 24 -
Как Собрать Ит-Команду
19 Oct, 24 -
Лекция №02: Msp430. Города И Регистры
19 Oct, 24 -
Тесты, Игры, Браузеры
19 Oct, 24
