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 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 answers in
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
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 on standby give
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
* Queries on standby
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 for
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* we need to
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_delay
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 clarification,
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 to be
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
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 pretty
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 tuple
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 passed through to
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
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, Services
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 not just
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 up
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 get
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
xmin
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
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 that are
to
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
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 away tuples
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.
More exotic
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 concerned more
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 purpose of
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 will always
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 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
If we derive
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. Each
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
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
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 than
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, accessed
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 show
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
block
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 thought
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 questions
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 system cache
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 implementation
of
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 inconsistent answers
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 of not
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
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
filesystems
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 solution
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 hung in
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
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, 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 which
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
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 question:
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
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
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 can
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
where the
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
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
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 (or
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
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 at
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...
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
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
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,
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
you
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
[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 availability. A
69 matches
Mail list logo
