Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

Guys,

Sorry to bother you but can anyone help me unsubscribe from this list?
I followed the instructions in the original email and got an error
message...
Thanks,

-- Shaul

On Tue, Nov 21, 2017 at 6:25 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

>
>
> *From:* Henrik Cednert (Filmlance) [mailto:henrik(dot)cednert(at)filmlance(dot)se]
> *Sent:* Tuesday, November 21, 2017 9:29 AM
> *To:* pgsql-performance(at)lists(dot)postgresql(dot)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
>
>
>