From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Incredibly slow restore times after 9.0>9.2 upgrade
Date: Tue, 28 Oct 2014 22:19:10 +0100
Message-ID: <5450084E.1060808@fuzzy.cz> (raw)
In-Reply-To: <1414529739004-5824701.post@n5.nabble.com>
References: <1414529739004-5824701.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 28.10.2014 21:55, jmcdonagh wrote:
> 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.
So, if I understand it correctly, you've been restoring into 9.0, then
you switched to 9.2 and it's much slower?
Is the 9.2 configured equally to 9.0? If you do something like this
SELECT name, setting
FROM pg_settings
WHERE source = 'configuration file';
on both versions, what do you get?
> 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.
So, where's the bottleneck? Clearly, there's one, so is it a CPU, a disk
or something else? Or maybe network, because you're using EBS?
What do you mean by 'utilization'? How do you measure that?
> The dumps are created with pg_dump -Fc and restored with pg_restore
> -d db -j 2 -O -U postgres PostgreSQL-db.sql.
OK
> 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.
I doubt that. There probably were some changes (after all, we're talking
about 2 major versions), but we generally don't change it in a way
that'd hurt performance.
regards
Tomas
--
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: tv@fuzzy.cz
Subject: Re: Incredibly slow restore times after 9.0>9.2 upgrade
In-Reply-To: <5450084E.1060808@fuzzy.cz>
* 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