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 <iney...@perceptron.com> wrote: > > > *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 > > >
