Skip site navigation (1) Skip section navigation (2)

Re: Hot standby and b-tree killed items

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Subject: Re: Hot standby and b-tree killed items
Date: 2008-12-24 14:59:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

In response to


pgsql-hackers by date

Next:From: Fujii MasaoDate: 2008-12-24 15:10:42
Subject: Re: Sync Rep: First Thoughts on Code
Previous:From: Simon RiggsDate: 2008-12-24 13:48:04
Subject: Re: Hot standby and b-tree killed items

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group