PostgreSQL: улучшение производительности pg_dump, pg_restore

Когда я начал, я использовал pg_dump в стандартном формате. Я был непросвещен.

Исследование показало мне улучшения размера времени и размера с помощью pg_dump -Fc | gzip -9 -c > dumpfile.gz. Я был просвещен.

Когда пришло время создать базу данных заново,

# create tablespace dbname location '/SAN/dbname';
# create database dbname tablespace dbname;
# alter database dbname set temp_tablespaces = dbname;

% gunzip dumpfile.gz              # to evaluate restore time without a piped uncompression
% pg_restore -d dbname dumpfile   # into a new, empty database defined above

Я чувствовал себя неосведомленным: для восстановления базы данных потребовалось 12 часов, и только часть того, чем она станет:

# select pg_size_pretty(pg_database_size('dbname'));
47 GB

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

Пожалуйста, просветите меня.

Ответ 1

Сначала проверьте, что вы получаете разумную производительность ввода-вывода от установки вашего диска. Затем проверьте правильность настройки PostgreSQL. В частности, shared_buffers должен быть установлен правильно, maintenance_work_mem должен быть увеличен во время восстановления, full_page_writes должен быть отключен во время восстановления, wal_buffers должен быть увеличен до 16 МБ во время восстановления, checkpoint_segments должен быть увеличен до чего-то например, 16 во время восстановления, у вас не должно быть необоснованного входа в систему (например, при ведении журнала каждого оператора), auto_vacuum должен быть отключен во время восстановления.

Если вы на 8.4 также экспериментируете с параллельным восстановлением, параметр --jobs для pg_restore.

Ответ 2

Две проблемы/идеи:

Задав -Fc, вывод pg_dump уже сжат. Сжатие не является максимальным, поэтому вы можете сэкономить экономию пространства, используя "gzip -9", но я бы поставил ему недостаточно, чтобы гарантировать дополнительное время (и ввода-вывода), используемое для сжатия и разжатия версии резервной копии -Fc.
Если вы используете PostgreSQL 8.4.x, вы можете ускорить восстановление из резервной копии -Fc с помощью новой опции командной строки pg_restore "-jn", где n = количество параллельных подключений для использования для восстановления, Это позволит pg_restore загружать более одной таблицы данных или генерировать более одного индекса одновременно.

Ответ 3

Я предполагаю, что вам нужна резервная копия, а не крупное обновление базы данных.

Для резервного копирования больших баз данных вы должны настроить непрерывное архивирование вместо pg_dump.

Настройте архивирование WAL.
Сделайте свои базовые резервные копии, например, каждый день, используя psql template1 -c "select pg_start_backup(' `date +% F-% T`` ') " rsync -a --delete/var/lib/pgsql/data//var/backups/pgsql/base/ psql template1 -c" выберите pg_stop_backup()"

Восстановление будет таким же простым, как восстановление базы данных и журналов WAL не старше pg_start_backup времени из места резервного копирования и запуска Postgres. И это будет намного быстрее.

Ответ 4

zcat dumpfile.gz | pg_restore -d db_name

Удаляет полную запись несжатых данных на диск, что в настоящее время является вашим узким местом.

Ответ 5

Улучшить pg dump & restore

PG_DUMP | всегда используйте каталог формата с опцией -j

time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

PG_RESTORE | всегда используйте настройку для postgres.conf с помощью каталога формата. -j option

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1

time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/`

Для получения дополнительной информации

https://github.com/YanarAssaf/PostgreSQL/wiki/Improve-pg-dump%7Crestore

Ответ 6

Как вы, возможно, догадались, что сжатие резервной копии приводит к более высокой производительности, ваша резервная копия связана с I/O. Это не должно удивлять, поскольку резервное копирование в значительной степени всегда будет связано с I/O. Сжатие данных обрабатывает нагрузку ввода-вывода для загрузки ЦП, и поскольку большинство ЦП не работают во время передачи данных монстров, сжатие выводится как чистая победа.

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

Ответ 7

В дополнение к другим предложениям не забудьте настроить вашу конфигурацию, включая изменения в тегах maintenance_work_mem и checkpoint_segments.

Смотрите эту страницу для подсказок производительности для массовой вставки данных в PostgreSQL.