Re: Hot standby and b-tree killed items

On Wednesday 24 December 2008 08:48:04 Simon Riggs wrote:
> On Wed, 2008-12-24 at 17:56 +0530, Pavan Deolasee wrote:
> > Again, I haven't seen how frequently queries may get canceled. Or if
> > the delay is set to a large value, how far behind standby may get
> > during replication, so I can't really comment. Have you done any tests
> > on a reasonable hardware and checked if moderately long read queries
> > can be run on standby without standby lagging behind a lot.
>
> Queries get cancelled if data they need to see if removed and the
> max_standby_delay expires. So lag will be max_standby_delay, by
> definition.
>
> Not sure what further tests would show. Queries that run for longer than
> max_standby delay plus mean time between cleanup records will currently
> end up being cancelled.
>

I think the uncertainty comes from peoples experience with typical replication
use cases vs a lack of experience with this current implementation.

One such example is that it is pretty common to use read-only slaves to do
horizontal scaling of read queries across a bunch of slaves. This is not the
scenario of running reporting queries on a second machine to lower load; you
would be running a large number of read-only, relativly short, oltp-ish
queries (think pg_benchs select only test i suppose), but you also have a
fairly regular stream of inserts/updates going on with these same tables, its
just you have 95/5 split of read/write (or similar).

This is standard practice in things like mysql or using slony or what have
you. I suspect it's one of the first things people are going to want to do
with hot standby. But it's unclear how well this will work because we don't
have any experience with it yet, coupled with the two downsides being
mentioned as canceled queries and replay lag, which happen to be probably the
two worst downsides you would have in the above scenario. :-)

Hmm.... I'm not sure why I didn't think of running this test before, but
read/write pg_bench on a master with pg_bench select test on slave isn't that
bad of a scenario to match the above; it might be a little too much activity
on the master, but has anyone else run such a test?

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com