Re: Incredibly slow restore times after 9.0>9.2 upgrade

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