Re: Hot Standby query cancellation and Streaming Replication integration

Replying to my own post - first sign of madness...

Let's see if I've got the concepts clear here, and hopefully my thinking 
it through will help others reading the archives.

There are two queues:
1. Cleanup on the master
2. Replay on the slave

Running write queries on the master adds to both queues.
Running (read-only) queries on the slave prevents you removing from both 
queues.


There are two interesting measurements of "age"/"size":
1. Oldest item in / length of queue (knowable)
2. How long will it take to clear the queue (estimable at best)

You'd like to know #2 to keep up with your workload. Unfortunately, you 
can't for certain unless you have control over new incoming queries (on 
both master and slave).

You might want four separate GUCs for the two measurements on the two 
queues. We currently have two that (sort of) match #1 "Oldest item" 
(vacuum_defer_cleanup_age, max_standby_delay).


Delaying replay on a slave has no effect on the master. If a slave falls 
too far behind it's responsible for catch-up (via normal WAL archives).

There is no point in delaying cleanup on the master unless it's going to 
help one or more slaves. In fact, you don't want to start delaying 
cleanup until you have to, otherwise you're wasting your delay time. 
This seems to be the case with vacuum_defer_cleanup_age. If I have a 
heavily-updated table and I defer vacuuming then when any given query 
starts on the slave it's going to be half used up already.

There's also no point in deferring cleanup on the master if the standby 
is already waiting on a conflict that will cause its queries to be 
cancelled anyway. Not only won't it help, but it might make things worse 
since transactions will be cancelled, the conflict will be replayed and 
(presumably) queries will be re-submitted only to be cancelled again.

This is what Greg Smith's discussion of the keep-alives was about. 
Giving the master enough information to be smarter about cleanup (and 
making the conflicts more fine-grained).

The situation with deferring on one or both ends of process just gets 
more complicated with multiple slaves. There's all sorts of unpleasant 
feedback loops I can envisage there.

For the case of single slave being used to run long reporting queries 
the ideal scenario would be the following. Master starts deferring 
vacuum activity just before the query starts. When that times out, the 
slave will receive the cleanup info, refuse to replay it and start its 
delay. This gives you a total available query time of: natural time between vacuums + vacuum delay + WAL transfer time
+
 
standby delay


I can think of five useful things we should be doing (and might be 
already - don't know).

1. On the master, deduce whether the slave is already waiting on a 
query. If so, don't bother delaying cleanup. Clearly you don't want to 
be signalling hundreds of times a second though. Does the slave pause 
fetching via streaming replication if replay is blocked on a query? 
Could we signal "half-way to max-age" or some such?

2. Perhaps simpler than trying to make the master smarter, just allow 
SET this_transaction_is_probably_a_long_one=true on the slave. That (a) 
clears the queue on the slave and (b) sends the signal to the master 
which then starts deferring vacuum.

3. Do a burst of cleanup activity on the master after blocking. This 
should concentrate conflicts together when they reach the slave. Perhaps 
vacuum_defer_cleanup_age should be vacuum_deferred_queue_size and 
measure the amount of work to do, rather than the max age of the oldest 
cleanup (if I've understood correctly).

4. Do a burst of replay on the slave after blocking. Perhaps every time 
it cancels a transaction it should replay at least half the queued WAL 
before letting new transactions start. Or perhaps it replays any vacuum 
activity it comes across and then stops. That should sync with #2 
assuming the slave doesn't lag the master too much.

5. I've been mixing "defer" and "delay", as do the docs. We should 
probably settle on one or the other. I think defer conveys the meaning 
more precisely, but what about non-native English speakers?

--   Richard Huxton  Archonet Ltd