From: Henrik Cednert (Filmlance) [mailto:henrik.cedn...@filmlance.se]
Sent: Tuesday, November 21, 2017 9:29 AM
To: pgsql-performance@lists.postgresql.org
Subject: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
Hello
We use a system in filmproduction called DaVinci Resolve. It uses a pgsql
database when you work in a collaborative workflow and multiple people share
projects. Previously it was using pgsql 8.4 but for a new major upgrade they
recommend an upgrade to 9.5. Probably also to some macOS limitation/support and
that 9.x is required for macOS >10.11.
They (BlackMagic Design) provide three tools for the migration.
1. For for dumping everything form the old 8.4 database
2. One for upgrading from 8.4 to 9.5
3. One for restoring the backup in step 1 in 9.5
All that went smoothly and working in the systems also works smoothly and as
good as previously, maybe even a bit better/faster.
What's not working smoothly is my daily pg_dump's though. I don't have a
reference to what's a big and what's a small database since I'm no db-guy and
don't really maintain nor work with it on a daily basis. Pretty much only this
system we use that has a db system like this. Below is a list of what we dump.
930M Nov 18 13:31 filmserver03_2017-11-18_132043_dailies_2017_01.backup
2.2K Nov 18 13:20 filmserver03_2017-11-18_132043_postgres.backup
522K Nov 18 13:20 filmserver03_2017-11-18_132043_resolve.backup
23G Nov 18 19:37 filmserver03_2017-11-18_132043_resolve_2017_01.backup
5.1G Nov 18 20:54 filmserver03_2017-11-18_132043_resolve_2017_02.backup
10G Nov 18 23:34 filmserver03_2017-11-18_132043_resolve_filmserver02.backup
516K Nov 18 23:35 filmserver03_2017-11-18_132043_temp_backup_test.backup
1.9G Nov 19 00:05 filmserver03_2017-11-18_132043_temp_dev_resolve14.backup
The last pg_dump with 8.4 took 212 minutes and 49 seconds.And now with 9.5 the
very same pg_dump takes 644 minutes and 40 seconds. To it takes about three
times as long now and I have no idea to why. Nothing in the system or hardware
other than the pgsql upgrade have change.
I dump the db's with a custom script and this is the line I use to get the DB's:
DATABASES=$(${BINARY_PATH}/psql --user=postgres -w --no-align --tuples-only
--command="SELECT datname from pg_database WHERE NOT datistemplate")
After that I iterate over them with a for loop and dump with:
${BINARY_PATH}/pg_dump --host=localhost --user=postgres --no-password --blobs
--format=custom --verbose --file=${pg_dump_filename}_${database}.backup
${database} | tee -a ${log_pg_dump}_${database}.log
When observing the system during the dump it LOOKS like it did in 8.4. pg_dump
is using 100% of one core and from what I can see it does this through out the
operation. But it's still sooooo much slower. I read about the parallell option
in pg_dump for 9.5 but sadly I cannot dump like that because the application in
question can (probably) not import that format on it's own and I would have to
use pgrestore or something. Which in theory is fine but sometimes one of the
artists have to import the db backup. So need to keep it simple.
The system is:
MacPro 5,1
2x2.66 GHz Quad Core Xeon
64 GB RAM
macOS 10.11.6
PostgreSQL 9.5.4
DB on a 6 disk SSD RAID
I hope I got all the info needed. Really hope someone with more expertise and
skills than me can point me in the right direction.
Cheers and thanks
--
Henrik Cednert
cto | compositor
According to pg_dump command in your script you are dumping your databases in
custom format:
--format=custom
These backups could only be restored using pg_restore (or something that wraps
pg_restore).
So, you can safely add parallel option. It should not affect your restore
procedure.
Regards,
Igor Neyman
