Re: Issues with two-server Synch Rep

On Mon, Oct 11, 2010 at 5:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Obviously.  I presume it'll be something like "update postgresql.conf
>> or recovery.conf and run pg_ctl reload", but I haven't (yet, anyway)
>> verified the actual behavior of the patches, but if the above isn't
>> feasible then we have a problem.
>
> Right.  That's why I asked the question.  Mind you, a superuser function
> on the master would be even better ...

That's probably not going to happen until we have a way to update
postgresql.conf via SQL. Which, I maintain, as I have maintained
before, is not going to happen until we get rid of the comments,
because otherwise absolutely any implementation anyone proposes will
get criticized for failing to handle them sensibly (because it is not
possible to rewrite the file while handling the comments sensibly).

>> What is your source for those numbers?  They could be right, but I
>> simply don't know.
>
> pg_bench tests with asynch rep and standby_delay = 0.  Not rigorous, but
> enough to show that there is a problem there.  Doing pg_bench with a
> small database

Interesting.

>> It would be far better if we could decouple master cleanup from
>> standby cleanup, so that only the machine that actually has the old
>> query gets bloated.  However, no one seems excited about writing that
>> code.
>
> "not excited" == terrified of the amount of troubleshooting involved,
> and likely believing it's impossible.

Quitter! :-)

>> A further grump about our current architecture is that it doesn't seem
>> at all clear how to make it work for partial replication.  I have to
>> wonder whether we are going down the wrong path completely and need to
>> hit the reset button.
>
> The way to do partial replication is Slony, Londiste, Bucardo, etc.

Sure. But we can't forever ignore the fact that trigger-based
replication is not as performant as log-based replication.

>> But neither this nor the pruning problem are
>> things that we can reasonably expect the sync rep patch to solve, if
>> we want it to get committed this release cycle.
>
>>> It is not, given that I've seen several proposals for synch rep which
>>> would make asynch rep even more complicated than it already is.
>>
>> I'm not aware of any proposals on the table which would do that.
>
> Standby registration?

No one that I know of has proposed making that mandatory for async
standbys. In fact, Heikki has said just the opposite, and I agree
with him.

>> Sure, that would be nice to have, and it's a good idea.  But I don't
>> think that's going to be a common failure mode.  What I expect to
>> happen is the standby to hum along with no problem for a long time and
>> then either kick a disk or suffer a power outage.
>
> That might be more common, but it's not an argument against monitoring
> what we *can* monitor for.  More importantly, if monitoring ACK response
> times -- and similar metrics -- is not available via core postgres, it
> is impossible to find them out any other way.  We need to give DBAs the
> tools to do their jobs, even if the tools are at a very low level.

No argument.

>> No, it isn't at all.  What does your application do NOW if the master
>> goes down after you've sent a commit and before you get an
>> acknowledgment back?  Does it assume that the transaction is
>> committed, or does it assume the transaction was aborted by a crash on
>> the master?  Either is possible, right?
>
> This problem certainly exists with async, it's just less likely so
> people are ignoring it.  With a high enough transaction rate, and a
> standby in "apply" mode, it's *certain* to happen on synch rep.  So we
> can't ignore it as a problem anymore.

It exists with no replication at all...

> I don't have any brilliant ideas on a solution for this one.

Right, well, the world is fundamentally asynchronous. In practice,
it's not that hard to write application-dependent logic to handle this
when it matters. Your transaction can, e.g. start by inserting a UUID
in a table somewhere. Then if a crash occurs you probe and see if the
UUID associated with that transaction is there, or not. Or depending
on what your transaction does, there may be some more natural
identifier you can use (e.g. if you are inserting an order into an
orders table, you can look and see if the order number you thought you
created is there).

>>> So, your opinion is "it's out of scope to handle this issue" ?
>>
>> What handling of it would you propose?  Consider the case where you
>
> I was asking a question.  My original question was "do we need to handle
> this?"  I'm taking your viewpoint as "there's no reasonable way to
> handle it, so we shouldn't."  That's a fine answer.  What I want is for
> -hackers to make a *decision* about a very real problem, and not just
> fail to discuss it.

Yes, that's my viewpoint. I think both this an the previous item are
pretty basic database theory - and you can probably read about them in
any good database theory textbook.

>> I agree, but it's not something we can address in the first patch,
>> which is hard enough without adding things that make it even harder.
>> We need to get something simple committed first and then build on it.
>
> The reason I posted the start of this thread is that I know that both
> Fujii and Simon have thought about some of these questions, and even if
> they don't have code for them, they have ideas.  I want to read those
> ideas explained.  Further, the answers to these questions may tell the
> rest of us which parts of each patch are the most valuable.

Fair enough. I was just replying because (1) nobody else had and (2)
I thought it might help to try to separate out which of the issues you
raised are most relevant to the patches at hand.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company