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

Hello,

I had this behaviors when the upgraded pg 9.5 was on ssl mode by default.

So i deactivated ssl mode in postgresql.conf. That's all.

Regards,

Patrick

On 11/21/2017 03:28 PM, Henrik Cednert (Filmlance) wrote:
> 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
>
>