Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eHd8X-0002PL-Vk for pgsql-performance@arkaria.postgresql.org; Wed, 22 Nov 2017 22:04:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eHd8W-0000eu-4p for pgsql-performance@arkaria.postgresql.org; Wed, 22 Nov 2017 22:04:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eHcFD-0004LE-M5 for pgsql-performance@lists.postgresql.org; Wed, 22 Nov 2017 21:07:43 +0000 Received: from mail-wr0-x233.google.com ([2a00:1450:400c:c0c::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eHcF5-00005X-FN for pgsql-performance@lists.postgresql.org; Wed, 22 Nov 2017 21:07:42 +0000 Received: by mail-wr0-x233.google.com with SMTP id a63so15766987wrc.12 for ; Wed, 22 Nov 2017 13:07:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hipay-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=ECCbNTCAqNH938lJMiC5q+2cDUvhaLPsaoRtlbsjhA0=; b=BNd1BiqM++53Vh5cbl8KZWbaGkP12tCgcozvn5svlWAPQQKykxhnNowbBAa50JyLzf 9GhEkACJe2qPt8wChI3MpehKw7leoeHaST2u5doljR13dc48wTv9Q3roOQm3TcJWeMwj N4Y74eGvUmoNxo4XPxUh6FAJeuO549GZ3WuczyJlWGRGIUdk8biGrH4eQyusGJ4eA+Gg 98F7pZztke3AZYCvM7bKpGOuUd03XXGZn5hnK+rWL6/rXN3ZxHxaZztuxvWR3eW4G8Wl H3Kv6drT9ghpbWpUg67RHQYTLOqoFfovIRjRCLxCC+zztM4ha1bpeh0uScxzUj2qCgzV BJ/Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=ECCbNTCAqNH938lJMiC5q+2cDUvhaLPsaoRtlbsjhA0=; b=PR5rqJ0jizkDzbwsWz9w8H8wBS5ZyBSeasc6NE+cExX8pvXbJ0AtnzsdNqHLaDwwPn bGQwsK1YB74l7pn0NkhY3FO6T3yU663H8H2Z/tt0/XVjL3509ZyE0nQqQBCUntA6UDG0 BJ7/8fx0pPJMC7g12JPYSVgOZBcuShnY2PEvsKKdbNz00AxZmI7ogFoCGF8TIxZ1S9ti 1oCge8dywUfehVHTcBG9rQml3wytemVJW9B0L/gz0q3jSlkb+b76cFvn/BWBQkAXDtKh SyutbL1R7F8IYSokENWA7ZhX/HwUBwb9UVjhTJzqUrF7FY1zvrTCDo5Ia4O3Velu2UuG wvsg== X-Gm-Message-State: AJaThX4K329WDwRFL1RdhmVDhswxbLCcPP/XrvQR0nCCNsj03z/P/zHZ xW+E1unAQf3UNNQ3EtBXdatIQNn6ugY= X-Google-Smtp-Source: AGs4zMbJXxcPajqdg0Hz87tdgPjy1Hu5o2DlRcVzj1L2IbM5bt54cxgnB82+Rs/w44QmO0CdO5Qgrg== X-Received: by 10.223.132.101 with SMTP id 92mr18476718wrf.85.1511384851953; Wed, 22 Nov 2017 13:07:31 -0800 (PST) Received: from [192.168.2.254] (28.243.6.93.rev.sfr.net. [93.6.243.28]) by smtp.gmail.com with ESMTPSA id m133sm6833088wmd.40.2017.11.22.13.07.31 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 22 Nov 2017 13:07:31 -0800 (PST) Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade To: pgsql-performance@lists.postgresql.org References: <8F33D0C7-E12E-4996-990C-3CF0C5ED0437@filmlance.se> From: Patrick KUI-LI Message-ID: <0bc6cc72-7d7d-b157-4824-7a6ea2985a60@hipay.com> Date: Wed, 22 Nov 2017 22:07:30 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.4.0 MIME-Version: 1.0 In-Reply-To: <8F33D0C7-E12E-4996-990C-3CF0C5ED0437@filmlance.se> Content-Type: multipart/alternative; boundary="------------5AEC53AAF781E8FAAC064DD1" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------5AEC53AAF781E8FAAC064DD1 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit 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 > > --------------5AEC53AAF781E8FAAC064DD1 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

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



--------------5AEC53AAF781E8FAAC064DD1--