Прошу совета гуру по postgre - непонятная медленная работа pg_dump/pg_restore
Есть большая база на много гиг. Создание полного бэкапа занимает несколько суток. При расчете потраченного на бакап кол-ва времени на полученный объем бэкап-файла, получается, что скорость всего около 2Мб/с :(
Перерыл кучу док, мануалов, советов и т.д. - ничего не помогает, сейчас после небольшого шаманства с настройками bgwriter максимум что достиг - это около 4 Метров в сек создается бэкап (14 гиг бакап за 55 минут, но это не полный бакап базы - только малая часть).
Аппаратная начинка - рейд-1, скорость записи по тестам 120-130МБайт/с, памяти 16Гб, особо без нагрузки, винт не занят, io простаивает - т.е. упирания в производительность винтов нет. Т.е. во время бакапа можно легко что-то по сети (через ftp) на этот же рейд что-то залить со скоростью 80-90МБ/с или скачать с такой же скоростью. Соответственно, при поднятии из бакапа - тоже скорость создания новых файлов как-то не ахти совсем - т.е. вполне может очередной 1G-файл большой таблицы писаться минуты 3, иногд и 5 минут..
Все остальное просто летает - индексы на ssd-рейд-1, все просто моментально работает. Но вот что делать с бэкапом - не понимаю. Прошу совета, помощи.Настройки postgresql (версия 8.4, один из последних билдов):
max_connections = 1000
shared_buffers = 1600MB
temp_buffers = 4MB
work_mem = 2MB
maintenance_work_mem = 1400MB
effective_cache_size = 8000MB
bgwriter_delay = 140
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 4.0
fsync = off
synchronous_commit = off
full_page_writes = off
wal_buffers = 16MB
wal_writer_delay = 50ms
commit_delay = 4000
commit_siblings = 3
checkpoint_segments = 100
checkpoint_completion_target = 0.9
---
остальные настройки вроде вообще никак не влияют на скорость создания бэкапов.
Вот пример как делается бэкап:
pg_dump --ignore-version --file=fulldump.pgdump --blobs --no-owner --superuser=postgres --format=c --schema=\"$dbschema\" $dbname
а вот так рестор:
pg_restore --no-data-for-failed-tables --clean --jobs=7 --dbname=$dbname fulldump.pgdumpЧто не так? :(
>[оверквотинг удален]
> checkpoint_segments = 100
> checkpoint_completion_target = 0.9
> ---
> остальные настройки вроде вообще никак не влияют на скорость создания бэкапов.
> Вот пример как делается бэкап:
> pg_dump --ignore-version --file=fulldump.pgdump --blobs --no-owner --superuser=postgres
> --format=c --schema=\"$dbschema\" $dbname
> а вот так рестор:
> pg_restore --no-data-for-failed-tables --clean --jobs=7 --dbname=$dbname fulldump.pgdump
> Что не так? :(Хочешь быстро - делай http://www.postgresql.org/docs/8.4/static/continuous-archivi... , будет тебе "со скоростью диска". Только оно в 2-5-10 раз больше по объёму, чем pg_dump-ом.
Если нет, ну дефрагментацию базы какую ни то сделай. Индексы, может быть...
pg_dump умеет выносить только таблицу целиком. Отсюда: дапмпить не все таблицы (только маленькие, если это допустимо) существенно быстрее (схему лучще сохранять целиком - взаимозависимости о частям могут не срастись по-лёгкому). Больую(-ие) тадлицы либо дампить _реже_, либо покрутить свои скрипты-костыли с _инкрементальным_ до-бэкапом (если,например, записи только добавляются и есть растуший индекс или метка времени).
TFM = http://www.postgresql.org/docs/8.4/static/backup-dump.html#B...
Да, кста, pg_restore умеет --jobs=7 *только* для _формата_бэкапа_ -Fc. Это в его man-е написано.
Особо профессиональные восстанавливальщики (искать в интернетах) крутят для restore-а конфигурацию сервера: чего-то там с буферами, чекпоинтами и ещё чем-то. Я так далеко :)) в бэкапах никогда не заходил.
>[оверквотинг удален]
>> остальные настройки вроде вообще никак не влияют на скорость создания бэкапов.
>> Вот пример как делается бэкап:
>> pg_dump --ignore-version --file=fulldump.pgdump --blobs --no-owner --superuser=postgres
>> --format=c --schema=\"$dbschema\" $dbname
>> а вот так рестор:
>> pg_restore --no-data-for-failed-tables --clean --jobs=7 --dbname=$dbname fulldump.pgdump
>> Что не так? :(
> Хочешь быстро - делай http://www.postgresql.org/docs/8.4/static/continuous-archivi...
> , будет тебе "со скоростью диска". Только оно в 2-5-10 раз
> больше по объёму, чем pg_dump-ом.wal-логирование - интересный метод, но требуется просто немеряного места особенно в данном случае - база более 400гиг весит. Не подходит :( Я уже смотрел в его сторону, но есть минусы большие:
- требуется много места дополнительно
- забакапить только одну схему или одну таблицу нельзя
- много телодвижений при процессе восстановления и много что надо учесть, перенести> Если нет, ну дефрагментацию базы какую ни то сделай. Индексы, может быть...
База только после рестора, свеженарезанный рейд-1 - т.е. вообще смысла нету - да и другие операции все делаются без проблем ведь.
> pg_dump умеет выносить только таблицу целиком. Отсюда: дапмпить не все таблицы (только
> маленькие, если это допустимо) существенно быстрее (схему лучще сохранять целиком -
> взаимозависимости о частям могут не срастись по-лёгкому). Больую(-ие) тадлицы либо дампить _реже_, либо покрутить свои скрипты-костыли с _инкрементальным_ до-бэкапом (если,например,
> записи только добавляются и есть растуший индекс или метка времени).Да, сейчас так именно и делаю - т.е. делаю бакап по частям - самые тяжелые таблицы бакапятся не часто, но просто процесс из бакапа и рестора очень длительный :( ну никак у меня не вяжется, что бакап 300гиг занимает трое суток, а рестор этой таблицы занимает еще полтора суток. База не нагружена вообще в моменты бакапа.. т.е. почему так медленно - не понятно.
> TFM = http://www.postgresql.org/docs/8.4/static/backup-dump.html#B...
Да, эта ссылка зачитана до дыр. Бакап через обычный формат, с заворачиванием в тар или gzip - очень нравится, но опять же - место тратится и утрачивается возможность заресторить отдельную таблицу или схему.
> Да, кста, pg_restore умеет --jobs=7 *только* для _формата_бэкапа_ -Fc. Это в его
> man-е написано.ну, собственно, именно этот формат и юзается :-) В строчке бакапа именно этот формат и указан. По сути этот параметр влияет только на то, что на стадии создания индексов пг начинает распараллеливаться. А вот на стадии заливки данных никакой параллелизации не происходит вообще.
> Особо профессиональные восстанавливальщики (искать в интернетах) крутят для restore-а
> конфигурацию сервера: чего-то там с буферами, чекпоинтами и ещё чем-то. Я
> так далеко :)) в бэкапах никогда не заходил.Вот - вроде крутил-вертел, но как-то без особого эффекта :(
Из Вашего конфига не видно параметров autovacuum.
Еще не понятно, какая ФС под БД и с какими опциями смонтирована (noatime)?В целом в Вашем конфиге есть практически все, что рекомендуют для быстрого создания резервных копий:
autovacuum = off
fsync = off
full_page_writes = offЕсли с ФС все в порядке, то я бы попробовал вернуть параметры PostgreSQL к значениям по умолчанию (или рекомендациям, например, pgtune - http://pgfoundry.org/projects/pgtune/) - не получится ли выявить проблемное значение параметра последовательно (если проблема в этом).
> Из Вашего конфига не видно параметров autovacuum.
> Еще не понятно, какая ФС под БД и с какими опциями смонтирована
> (noatime)?ext3, монтируется с noatime
> В целом в Вашем конфиге есть практически все, что рекомендуют для быстрого
> создания резервных копий:
> autovacuum = off
> fsync = off
> full_page_writes = offautovacuum у меня on, но я пробовал его отключать - ноль эмоций. Мало того, когда он включен, то просто он не запускается, если база сейчас занята - в логе появляется варнинг об этом, что мол автовакуум запустился, но ждет, так как сейчас база занята. По сути он и не мешается. Так как бакап я запускаю не во время активной работы, а когда полный штиль - никаких инсертов-удалений.
> Если с ФС все в порядке, то я бы попробовал вернуть параметры
> PostgreSQL к значениям по умолчанию (или рекомендациям, например, pgtune - http://pgfoundry.org/projects/pgtune/)
> - не получится ли выявить проблемное значение параметра последовательно (если проблема
> в этом).Уже делал - pgtune вообще ничего не поправил - только небольшую оптимизацию по стоимости запросов сделал и все. Чистый конфиг еще больше тормозит и тупит - слишком большой объем у некоторых таблиц для дефаултной конфигурации.
Если в Вашей базе блобы, то похоже, что не Вы один страдаете - поробуйте погуглить
postgresql blob pg_dump slow
есть соответствующие письма в списках рассылки PosqgreSQL.
Например
http://www.postgresql.org/message-id/4B9C97E1.40509@dav...
пишут, что использование COPY BINARY помогает
> Если в Вашей базе блобы, то похоже, что не Вы один страдаете
> - поробуйте погуглить
> postgresql blob pg_dump slow
> есть соответствующие письма в списках рассылки PosqgreSQL.
> Например
> http://www.postgresql.org/message-id/4B9C97E1.40509@dav...
> пишут, что использование COPY BINARY помогаетОО. Спасибо! Очень интересные выводы в топике том. Да - в одной тяжелой таблице есть блобы. В целом там интересная выкладка по выбору степени компрессии - на размере итоговом не так сильно сказывается, а вот время выполнения - чуть ли не в два-три раза уменьшается.
Спасибо большое.
> Если в Вашей базе блобы, то похоже, что не Вы один страдаете
> - поробуйте погуглить
> http://www.postgresql.org/message-id/4B9C97E1.40509@dav...
> пишут, что использование COPY BINARY помогаетBINARY, к сожалению, тут не поможет мне, но играние с уровнем компрессии - то, что нужно, оказалось.
Теперь тяжелые таблицы с кучей блобов бэкаплю с -Z1, а все остальное с -Z4. Результат налицо - судя по тому, что за час сделалось уже 65гиг бэкапа тяжелой таблицы - где-то за 5 часов все сделается, а не как раньше за 3 суток. А все остальное теперь бакапится тоже - не час, а всего 30 минут. Размер бакапов увеличился на 5-10%, что не так критично в данном случае - главное, что времени съэкономлено море.Спасибо большое еще раз :-)
>[оверквотинг удален]
> BINARY, к сожалению, тут не поможет мне, но играние с уровнем компрессии
> - то, что нужно, оказалось.
> Теперь тяжелые таблицы с кучей блобов бэкаплю с -Z1, а все остальное
> с -Z4. Результат налицо - судя по тому, что за час
> сделалось уже 65гиг бэкапа тяжелой таблицы - где-то за 5 часов
> все сделается, а не как раньше за 3 суток. А все
> остальное теперь бакапится тоже - не час, а всего 30 минут.
> Размер бакапов увеличился на 5-10%, что не так критично в данном
> случае - главное, что времени съэкономлено море.
> Спасибо большое еще раз :-)Ok. Спасибо за информацию.