On Thu, 2008-09-11 at 17:58 +0300, Heikki Linnakangas wrote:
> BTW, we haven't talked about how to acquire a snapshot in the slave.
> You'll somehow need to know which transactions have not yet committed,
> but will in the future. In the master, we keep track of in-progress
> transaction in the
On Thu, 2008-09-25 at 12:34 +0200, Zeugswetter Andreas OSB sIT wrote:
> > > I wonder whether the cancel can be delayed until a tuple/page is actually
> > > accessed
> > > that shows a too new xid.
> >
> > Yes, its feasible and is now part of the design.
> >
> > This is all about what happens *if*
> > I wonder whether the cancel can be delayed until a tuple/page is actually
> > accessed
> > that shows a too new xid.
>
> Yes, its feasible and is now part of the design.
>
> This is all about what happens *if* we need to remove rows that a query
> can still see.
I was describing a procedure
On Wed, 2008-09-24 at 21:22 -0400, Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Simon Riggs wrote:
> > > 2. Master ignores Standby's OldestXmin
> > > Effects:
> > > * Long running queries on standby...
> > >Have no effect on primary
> > >Can delay apply of WAL records on standby
> > > *
On Thu, 2008-09-25 at 11:14 +0200, Zeugswetter Andreas OSB sIT wrote:
> > Simon Riggs wrote:
> > > 2. Master ignores Standby's OldestXmin
> > > Effects:
> > > * Long running queries on standby...
> > >Have no effect on primary
> > >Can delay apply of WAL records on standby
> > > * Queries
> Simon Riggs wrote:
> > 2. Master ignores Standby's OldestXmin
> > Effects:
> > * Long running queries on standby...
> >Have no effect on primary
> >Can delay apply of WAL records on standby
> > * Queries on standby give consistent answers in all cases.
>
> Just for clarification, if you
On Wed, 2008-09-24 at 21:19 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > 2. Master ignores Standby's OldestXmin
> > Effects:
> > * Long running queries on standby...
> >Have no effect on primary
> >Can delay apply of WAL records on standby
> > * Queries on standby give consistent a
Bruce Momjian wrote:
> Simon Riggs wrote:
> > 2. Master ignores Standby's OldestXmin
> > Effects:
> > * Long running queries on standby...
> >Have no effect on primary
> >Can delay apply of WAL records on standby
> > * Queries on standby give consistent answers in all cases.
>
> Just for c
Simon Riggs wrote:
> 2. Master ignores Standby's OldestXmin
> Effects:
> * Long running queries on standby...
>Have no effect on primary
>Can delay apply of WAL records on standby
> * Queries on standby give consistent answers in all cases.
Just for clarification, if you set a max_slave_de
On Mon, 2008-09-15 at 19:20 +0100, Florian G. Pflug wrote:
> Simon Riggs wrote:
> > On Sat, 2008-09-13 at 10:48 +0100, Florian G. Pflug wrote:
> >
> >> The main idea was to invert the meaning of the xid array in the snapshot
> >> struct - instead of storing all the xid's between xmin and xmax tha
Simon Riggs wrote:
On Sat, 2008-09-13 at 10:48 +0100, Florian G. Pflug wrote:
The current read-only snapshot (which "current" meaning the
corresponding state on the master at the time the last replayed wal
record was generated) was maintained in shared memory. It' xmin field
was continually upd
Simon Riggs wrote:
On Sat, 2008-09-13 at 10:48 +0100, Florian G. Pflug wrote:
The main idea was to invert the meaning of the xid array in the snapshot
struct - instead of storing all the xid's between xmin and xmax that are
to be considering "in-progress", the array contained all the xid's >
xm
On Friday 12 September 2008 07:44:36 Csaba Nagy wrote:
> And then
> the actually interesting question: what will the slave do with views,
> rules, triggers ? I guess triggers are out of the question to be
> executed, what about rules ? Probably must be also ignored... user
> functions will probably
On Mon, 2008-09-15 at 16:26 +0300, Heikki Linnakangas wrote:
> In any case,
> we'll need the capability in the slave to notice when it's about to
> remove a tuple that's still visible to a snapshot in the slave.
Looks like what I'll do is this:
Alter functions in pruneheap.c so that we bubble
On Mon, 2008-09-15 at 09:07 -0400, Merlin Moncure wrote:
> > Well, my impression from all inputs is there is no single preferred
> > route. Any one of the approaches seems to have a possible objection,
> > depending upon the needs of the user. So I'm going to give options.
> >
> > In any case it's
On Mon, 2008-09-15 at 16:26 +0300, Heikki Linnakangas wrote:
> In any case, we'll need the capability in the slave to notice when
> it's about to remove a tuple that's still visible to a snapshot in the
> slave.
Agreed.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Service
Gregory Stark wrote:
Simon Riggs <[EMAIL PROTECTED]> writes:
So passing xl_xmin from master to standby seems not necessary to me. The
standby's OldestXmin needs to be passed through to the master, not the
other way around so that WAL records for tuple removal are not
generated.
I think most p
On Mon, Sep 15, 2008 at 8:40 AM, Simon Riggs <[EMAIL PROTECTED]> wrote:
>
> On Mon, 2008-09-15 at 13:13 +0100, Gregory Stark wrote:
>> Simon Riggs <[EMAIL PROTECTED]> writes:
>>
>> > So passing xl_xmin from master to standby seems not necessary to me. The
>> > standby's OldestXmin needs to be passe
On Mon, 2008-09-15 at 13:13 +0100, Gregory Stark wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
>
> > So passing xl_xmin from master to standby seems not necessary to me. The
> > standby's OldestXmin needs to be passed through to the master, not the
> > other way around so that WAL records for
Simon Riggs <[EMAIL PROTECTED]> writes:
> So passing xl_xmin from master to standby seems not necessary to me. The
> standby's OldestXmin needs to be passed through to the master, not the
> other way around so that WAL records for tuple removal are not
> generated.
I think most people were prett
On Sat, 2008-09-13 at 10:48 +0100, Florian G. Pflug wrote:
> The current read-only snapshot (which "current" meaning the
> corresponding state on the master at the time the last replayed wal
> record was generated) was maintained in shared memory. It' xmin field
> was continually updated with the
On Sat, 2008-09-13 at 10:48 +0100, Florian G. Pflug wrote:
> The main idea was to invert the meaning of the xid array in the snapshot
> struct - instead of storing all the xid's between xmin and xmax that are
> to be considering "in-progress", the array contained all the xid's >
> xmin that are t
Heikki Linnakangas wrote:
BTW, we haven't talked about how to acquire a snapshot in the slave.
You'll somehow need to know which transactions have not yet
committed, but will in the future. In the master, we keep track of
in-progress transaction in the ProcArray, so I suppose we'll need to
do th
On Fri, 2008-09-12 at 15:08 +0300, Hannu Krosing wrote:
> On Fri, 2008-09-12 at 13:54 +0200, Csaba Nagy wrote:
> > > I think that enabling long-running queries this way is both
> > > low-hanging
> > > fruit (or at least medium-height-hanging ;) ) and also consistent to
> > > PostgreSQL philosophy
On Fri, 2008-09-12 at 15:08 +0300, Hannu Krosing wrote:
> > * how will the buffers keep 2 different versions of the same page ?
>
> As the FS snapshot is mounted as a different directory, it will have
> it's own buffer pages.
Lack of knowledge about this shows my ignorance about the implementatio
On Fri, 2008-09-12 at 13:54 +0200, Csaba Nagy wrote:
> > I think that enabling long-running queries this way is both
> > low-hanging
> > fruit (or at least medium-height-hanging ;) ) and also consistent to
> > PostgreSQL philosophy of not replication effort. As an example we trust
> > OS's file sys
On Fri, 2008-09-12 at 12:25 +0100, Gregory Stark wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
>
> > 3. Ignore problem
> > Effects:
> > * Long running queries on standby...
> >Have no effect on primary
> >Do not delay apply of WAL records on standby
> > * Queries on standby give incons
> I think that enabling long-running queries this way is both
> low-hanging
> fruit (or at least medium-height-hanging ;) ) and also consistent to
> PostgreSQL philosophy of not replication effort. As an example we trust
> OS's file system cache and don't try to write our own.
I have again questio
On Fri, 2008-09-12 at 12:31 +0100, Richard Huxton wrote:
> There was a suggestion (Simon - from you?) of a transaction voluntarily
> restricting itself to a set of tables.
While thinking about how easy it would be for the DBA to specify the set
of tables a single query is accessing, first I though
On Fri, 2008-09-12 at 12:31 +0300, Hannu Krosing wrote:
> On Fri, 2008-09-12 at 09:45 +0100, Simon Riggs wrote:
> > On Thu, 2008-09-11 at 15:42 +0300, Heikki Linnakangas wrote:
> > > Gregory Stark wrote:
> > > > b) vacuum on the server which cleans up a tuple the slave has in scope
> > > > has to
Gregory Stark wrote:
> In that case the problem is dealing with different usage patterns on different
> tables. There might be a way to solve just that use case such as deferring WAL
> records for those tables. That doesn't guarantee inter-table data consistency
> if there were other queries which
Simon Riggs <[EMAIL PROTECTED]> writes:
> 3. Ignore problem
> Effects:
> * Long running queries on standby...
>Have no effect on primary
>Do not delay apply of WAL records on standby
> * Queries on standby give inconsistent answers in some cases, though
> doesn't generate any messages to s
On Fri, 2008-09-12 at 13:53 +0300, Hannu Krosing wrote:
> 4. Slave keeps copies of removed pages or rows when WAL apply removes
> old versions .
>
> Possible ways to do this
>
> * inside Slave - have some backup store tied to OldestXmin intervals
>
> * variant 1 - have one global store, acce
On Fri, 2008-09-12 at 11:19 +0100, Simon Riggs wrote:
> On Thu, 2008-09-11 at 01:07 +0100, Simon Riggs wrote:
> > Transaction snapshots is probably the most difficult problem for Hot
> > Standby to resolve.
>
> In summary of thread so far:
>
> When queries on standby run for significantly longer
On Thu, 2008-09-11 at 12:18 +0100, Gregory Stark wrote:
> Is there any middle ground or brilliant ways to get the best of both worlds?
Possibly. Nobody has commented yet on the other ideas on the post
itself.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Supp
On Thu, 2008-09-11 at 01:07 +0100, Simon Riggs wrote:
> Transaction snapshots is probably the most difficult problem for Hot
> Standby to resolve.
In summary of thread so far:
When queries on standby run for significantly longer than longest
queries on primary, some problems can occur. Various
On Thu, 2008-09-11 at 17:04 +0300, Heikki Linnakangas wrote:
> Csaba Nagy wrote:
> > and that means in fact that if you have
> > continuously overlapping small transactions, the "blocking horizon"
> > could be even blocked forever, as there'll always be a query running,
> > and the new queries wil
On Thu, 2008-09-11 at 15:17 +0200, Jochem van Dieten wrote:
> On Thu, Sep 11, 2008 at 2:07 AM, Simon Riggs wrote:
> > Transaction snapshots is probably the most difficult problem for Hot
> > Standby to resolve.
>
> > * remotely from primary node
> > * locally on the standby node
> >
> >
On Fri, 2008-09-12 at 09:45 +0100, Simon Riggs wrote:
> On Thu, 2008-09-11 at 15:42 +0300, Heikki Linnakangas wrote:
> > Gregory Stark wrote:
> > > b) vacuum on the server which cleans up a tuple the slave has in scope
> > > has to
> > >block WAL reply on the slave (which I suppose defeats the
On Fri, 2008-09-12 at 11:21 +0200, Csaba Nagy wrote:
> On Fri, 2008-09-12 at 09:38 +0100, Simon Riggs wrote:
> > If you request a block, we check to see whether there is a lookaside
> > copy of it prior to the tuple removals. We then redirect the block
> > request to a viewpoint relation's block. E
On Fri, 2008-09-12 at 09:38 +0100, Simon Riggs wrote:
> If you request a block, we check to see whether there is a lookaside
> copy of it prior to the tuple removals. We then redirect the block
> request to a viewpoint relation's block. Each viewpoint gets a separate
> relfilenode. We do the switch
On Thu, 2008-09-11 at 15:42 +0300, Heikki Linnakangas wrote:
> Gregory Stark wrote:
> > b) vacuum on the server which cleans up a tuple the slave has in scope has
> > to
> >block WAL reply on the slave (which I suppose defeats the purpose of
> > having
> >a live standby for users concern
On Thu, 2008-09-11 at 11:38 +0300, Hannu Krosing wrote:
> On Thu, 2008-09-11 at 09:24 +0300, Heikki Linnakangas wrote:
>
> > I like the idea of acquiring snapshots locally in the slave much more.
> > As you mentioned, the options there are to defer applying WAL, or cancel
> > queries.
>
> Mor
On Thu, 2008-09-11 at 17:58 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > So part of the handshake between
> > primary and standby must be "what is your recentxmin?". The primary will
> > then use the lower/earliest of the two.
>
> Even then, the master might already have vacuumed awa
[EMAIL PROTECTED] (Heikki Linnakangas) writes:
> Simon Riggs wrote:
>> Taking snapshots from primary has a few disadvantages
>>
>> ...
>> * snapshots on primary prevent row removal (but this was also an
>> advantage of this technique!)
>
> That makes it an awful solution for high ava
Gregory Stark wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
BTW, we haven't talked about how to acquire a snapshot in the slave. You'll
somehow need to know which transactions have not yet committed, but will in the
future.
I'm not sure why you need to know which ones will commit in
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> BTW, we haven't talked about how to acquire a snapshot in the slave. You'll
> somehow need to know which transactions have not yet committed, but will in
> the
> future.
I'm not sure why you need to know which ones will commit in the future. ISTM
Simon Riggs wrote:
So part of the handshake between
primary and standby must be "what is your recentxmin?". The primary will
then use the lower/earliest of the two.
Even then, the master might already have vacuumed away tuples that are
visible to an already running transaction in the slave, be
Thanks for the detailed thinking. At least one very good new idea here,
some debate on other points.
On Thu, 2008-09-11 at 09:24 +0300, Heikki Linnakangas wrote:
> And still we can't escape the scenario that the slave receives a WAL
> record that vacuums away a tuple that's still visible accor
Csaba Nagy wrote:
and that means in fact that if you have
continuously overlapping small transactions, the "blocking horizon"
could be even blocked forever, as there'll always be a query running,
and the new queries will always have the snapshot of the currently
running ones because WAL recovery
Le jeudi 11 septembre 2008, Csaba Nagy a écrit :
> Well now that I think I understand what Heikki meant, I also think the
> problem is that there's no choice at all to advance, because the new
> queries will simply have the same snapshot as currently running ones as
> long as WAL reply is blocked..
On Thu, 2008-09-11 at 15:33 +0200, Dimitri Fontaine wrote:
> What would forbid the slave to choose to replay all currently lagging WALs
> each time it's given the choice to advance a little?
Well now that I think I understand what Heikki meant, I also think the
problem is that there's no choice a
Le jeudi 11 septembre 2008, Heikki Linnakangas a écrit :
> Well, yes, but you can fall behind indefinitely that way. Imagine that
> each transaction on the slave lasts, say 10 minutes, with a new
> transaction starting every 5 minutes. On the master, there's a table
> that's being vacuumed (or HOT-
On Thu, 2008-09-11 at 16:19 +0300, Heikki Linnakangas wrote:
> Well, yes, but you can fall behind indefinitely that way. Imagine that
> each transaction on the slave lasts, say 10 minutes, with a new
> transaction starting every 5 minutes. On the master, there's a table
> that's being vacuumed (
> I'd imagine that even if applying the WAL on the slave is blocked, it's
> still streamed from the master to the slave, and in case of failover the
> slave will fast-forward before starting up as the new master. Of course, if
> it has fallen 3 days behind because of a giant reporting query, it can
Csaba Nagy wrote:
On Thu, 2008-09-11 at 15:42 +0300, Heikki Linnakangas wrote:
One problem with this, BTW, is that if there's a continuous stream of
medium-length transaction in the slave, each new snapshot taken will
prevent progress in the WAL replay, so the WAL replay will advance in
"baby
On Thu, Sep 11, 2008 at 2:07 AM, Simon Riggs wrote:
> Transaction snapshots is probably the most difficult problem for Hot
> Standby to resolve.
> * remotely from primary node
> * locally on the standby node
>
> If we derive a snapshot locally, then we will end up with a situation
> wher
On Thu, 2008-09-11 at 15:42 +0300, Heikki Linnakangas wrote:
> One problem with this, BTW, is that if there's a continuous stream of
> medium-length transaction in the slave, each new snapshot taken will
> prevent progress in the WAL replay, so the WAL replay will advance in
> "baby steps", and
Csaba Nagy wrote:
On Thu, 2008-09-11 at 15:23 +0300, Heikki Linnakangas wrote:
I'd imagine that even if applying the WAL on the slave is blocked, it's
still streamed from the master to the slave, and in case of failover the
slave will fast-forward before starting up as the new master.
Which b
Gregory Stark wrote:
b) vacuum on the server which cleans up a tuple the slave has in scope has to
block WAL reply on the slave (which I suppose defeats the purpose of having
a live standby for users concerned more with fail-over latency).
One problem with this, BTW, is that if there's a
On Thu, 2008-09-11 at 15:23 +0300, Heikki Linnakangas wrote:
> I'd imagine that even if applying the WAL on the slave is blocked, it's
> still streamed from the master to the slave, and in case of failover the
> slave will fast-forward before starting up as the new master.
Which begs the questio
Merlin Moncure wrote:
There is nothing stopping you from setting up two (or more) slave
servers, with one designated as failover that doens't serve queries,
right?
I'd imagine that even if applying the WAL on the slave is blocked, it's
still streamed from the master to the slave, and in case o
On Thu, Sep 11, 2008 at 7:18 AM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> a) transactions with live snapshots on the slave prevent the master from being
> able to vacuum tuples (which defeats the purpose of having a live standby
> server for some users).
>
> or
>
> b) vacuum on the server whi
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Thu, 2008-09-11 at 11:11 +0100, Richard Huxton wrote:
>
>> I have to say I agree with Heikki here. Blocking the master based on
>> what the slave is doing seems to make host standby less useful than warm.
>
> I agree also, that why I flagged it up for
On Thu, 2008-09-11 at 11:11 +0100, Richard Huxton wrote:
> I have to say I agree with Heikki here. Blocking the master based on
> what the slave is doing seems to make host standby less useful than warm.
I agree also, that why I flagged it up for discussion.
--
Simon Riggs www.2ndQu
Heikki Linnakangas wrote:
> Simon Riggs wrote:
>> Taking snapshots from primary has a few disadvantages
>>
>> ...
>> * snapshots on primary prevent row removal (but this was also an
>> advantage of this technique!)
>
> That makes it an awful solution for high availability. A backend
On Thu, 2008-09-11 at 09:24 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Taking snapshots from primary has a few disadvantages
> >
> > ...
> > * snapshots on primary prevent row removal (but this was also an
> > advantage of this technique!)
>
> That makes it an awful
On Thu, 2008-09-11 at 09:24 +0300, Heikki Linnakangas wrote:
> I like the idea of acquiring snapshots locally in the slave much more.
> As you mentioned, the options there are to defer applying WAL, or cancel
> queries.
More exotic ways to defer applying WAL include using some smart
filesystem
Simon Riggs wrote:
Taking snapshots from primary has a few disadvantages
...
* snapshots on primary prevent row removal (but this was also an
advantage of this technique!)
That makes it an awful solution for high availability. A backend hung in
transaction-in-progress state in
Transaction snapshots is probably the most difficult problem for Hot
Standby to resolve. We *must* have a transaction snapshot to access
table data in a consistent way, or we must accept some inconsistency,
though that is not simple either.
We can derive transaction snapshots
* remotely fro
70 matches
Mail list logo
