Re: Incredibly slow restore times after 9.0>9.2 upgrade

Is the instance ebs-optimized? I am wondering if its a configuration on the
instance not postgres or ebs.

On Wed, Oct 29, 2014 at 10:12 AM, jmcdonagh <Joseph(dot)E(dot)McDonagh(at)gmail(dot)com>
wrote:

> Hi Tomas- thank you for your thoughtful response!
>
>
> Tomas Vondra wrote
> > 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?
>
> Yes- but since the move was done utilizing snapshots so the move involves
> new volumes, but I have created new volumes since then to rule out a single
> bad volume.
>
>
> Tomas Vondra wrote
> > 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?
>
> I no longer have the 9.0 box up but we do track configuration via puppet
> and
> git. The only configuration change made for 9.2 is:
>
> -#standard_conforming_strings = off
> +standard_conforming_strings = off
>
> Cause we have an old app that needs this setting on otherwise we'd spend a
> lot of time trying to fix it.
>
>
> Tomas Vondra wrote
> >> 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 bottleneck is I/O somehow. I say somehow, because I see iowait
> averaging
> about 50% between two CPUs, but there is just no writes to the destination
> EBS volume really happening, just reads from the disk where the source dump
> is located, then bursts of writes to the destination volume every so often.
> It's kind of puzzling. This is happening on multiple database servers, in
> multiple availability zones. Driving me bonkers.
>
> What I mean by utilization is util% from iostat -m -x 1.
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Incredibly-slow-restore-times-after-9-0-9-2-upgrade-tp5824701p5824847.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>