From: jmcdonagh <Joseph.E.McDonagh@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Incredibly slow restore times after 9.0>9.2 upgrade
Date: Tue, 28 Oct 2014 13:55:39 -0700 (PDT)
Message-ID: <1414529739004-5824701.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi, we have a nightly job that restores current production data to the
development databases in a 'warm spare' database so that if the developers
need fresh data, it's ready during the day. When we moved from 9.0 to 9.2
suddenly the restores began to take from a few hours to more like 15 hours
or so. We're in Amazon EC2, I've tried new EBS volumes, warmed them up,
threw IOPS at them, pretty much all the standard stuff to get more disk
performance.
Here's the thing, the disk isn't saturated. The behavior I'm seeing seems
very odd to me; I'm seeing the source disk which holds the dump saturated by
reads, which is great, but then I just see nothing being written to the
postgres volume. Just nothing happening, then a small burst. There is no
write queue backup on the destination disk either. if I look at
pg_stat_activity I'll see something like:
COPY salesforce_reconciliation (salesforce_id, email, advisor_salesforce_id,
processed) FROM stdin
and even for small tables, that seems to take a very long time even though
the destination disk is almost at 0 utilization.
The dumps are created with pg_dump -Fc and restored with pg_restore -d db -j
2 -O -U postgres PostgreSQL-db.sql.
Is it possible that some default settings were changed from 9.0 to 9.2 that
would cause this kind of behavior? I'm stumped here. Thanks in advance for
any consideration here.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Incredibly-slow-restore-times-after-9-0-9-2-upgrade-tp582470...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Joseph.E.McDonagh@gmail.com
Subject: Re: Incredibly slow restore times after 9.0>9.2 upgrade
In-Reply-To: <1414529739004-5824701.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox