Re: Minimizing Recovery Time (wal replication)

On Thu, Apr 9, 2009 at 6:38 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> What does vmstat say about the bi/bo during this time period?  It sounds
> like the volume of random I/O produced by recovery is just backing up as
> expected.  Some quick math:

I'll have to capture this, unfortunately I won't be able to do that
until tomorrow. This machine I was looking at is already failed over
and I'm currently creating a new snapshot. I won't have a new hot
spare to replace it until the morning.

> 15GB RAM * 5% dirty_ratio = 750MB ; there's where your measured 760MB
> bottleneck is coming from.

That was what I thought, good to have it confirmed by somebody else.

> 750MB / 10 minutes = 1.25MB/s ; that's in the normal range for random writes
> with a single disk

Yes, this is an interesting problem I'm having, more on it below...

> Therefore my bet is that "vmstat 1" will show bo~=1250 the whole time you're
> waiting there, with matching figures from the iostat to the database disk
> during that period.
>
> Basically your options here are:
>
> 1) Decrease the maximum possible segment backlog so you can never get this
>   far behind

I understand conceptually what you are saying, but I don't know how to
practically realize this. :) Do you mean lower checkpoint_segments?

> 2) Increase the rate at which random I/O can be flushed to disk by either
>   a) Improving things with a [better] battery-backed controller disk cache
>   b) Stripe across more disks

This is the problem that has been my nightmare for the past few
months. It actually is an 8 drive raid 10, BUT, it's on virtualized
infrastructure up in Amazon's cloud running on 8 EBS volumes. I've
found performance to be... inconsistent at best. Sometimes it's
great, sometimes it's not so great.

We have a legacy database (~120gb) which grew in our old data center
on very powerful hardware. We moved it up to Amazon's cloud a few
months ago, and have been scrambling ever since.

I wouldn't change what we're doing, the benefits so far have
outweighed the pain, and we're actively working on the software to
make better use of the cloud infrastructure (i.e. many smaller
databases instead of one big database, lots of caching, the usual
stuff). Unfortunately, that takes time and I'm trying to limp along
as best I can with the legacy database until we can get everything
migrated.

So, to recap, I've raided up the volumes, thrown as much RAM and CPU
at the process as is available and just can't seem to tease any more
performance out.

Thanks,
Bryan