On Mon, 2010-03-01 at 16:12 -0400, pgsql-hackers-ow...@postgresql.org
wrote:
. . .
However there is a concern with max_standby_age. If you set it to,
say, 300s. Then run a 300s query on the slave which causes the slave
to fall 299s behind. Now you start a new query on the slave -- it gets
a
On 3/2/10 12:47 PM, Marc Munro wrote:
To take it further still, if vacuum on the master could be prevented
from touching records that are less than max_standby_delay seconds old,
it would be safe to apply WAL from the very latest vacuum. I guess HOT
could be handled similarly though that may
On Tue, 2010-03-02 at 12:47 -0800, Marc Munro wrote:
IIUC this is only a problem for WAL from HOT updates and vacuums. If no
vacuums or HOT updates have been performed, there is no risk of
returning bad data. So WAL that does not contain HOT updates or vacuums
could be applied on the
On Fri, 2010-02-26 at 16:44 -0500, Tom Lane wrote:
Greg Stark gsst...@mit.edu writes:
On Fri, Feb 26, 2010 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote:
There's *definitely* not going to be enough information in the WAL
stream coming from a master that doesn't think it has HS slaves.
We
On Fri, 2010-02-26 at 03:33 -0500, Greg Smith wrote:
I really hope this discussion can say focused on if and how it's
possible to improve this area, with the goal being to deliver a product
everyone can be proud of with the full feature set that makes this next
release a killer one. The
Dimitri Fontaine wrote:
Bruce Momjian br...@momjian.us writes:
Doesn't the system already adjust the delay based on the length of slave
transactions, e.g. max_standby_delay. It seems there is no need for a
user switch --- just max_standby_delay really high.
Well that GUC looks like it
Heikki Linnakangas wrote:
Dimitri Fontaine wrote:
Bruce Momjian br...@momjian.us writes:
Doesn't the system already adjust the delay based on the length of slave
transactions, e.g. max_standby_delay. It seems there is no need for a
user switch --- just max_standby_delay really high.
Well
Greg Smith wrote:
Joshua D. Drake wrote:
On Sat, 27 Feb 2010 00:43:48 +, Greg Stark gsst...@mit.edu wrote:
I want my ability to run large batch queries without any performance
or reliability impact on the primary server.
+1
I can use any number of other technologies
Greg,
If you think of it in those terms, the idea that you need to run PITR
backup/archive recovery to not get that behavior isn't an important
distinction anymore. If you run SR with the option enabled you could
get it, any other setup and you won't.
+1.
I always expected that we'd get
Josh Berkus wrote:
Now that I think about it, the xmin thing really doesn't seem
conceptually difficult. If the slave just opens a 2nd, special query
connection back to the master and publishes its oldest xmin there, as
far as the master is concerned, it's just another query backend.
Could it
The part I still don't have good visibility on is how much of the
necessary SR infrastructure needed to support this communications
channel is already available in some form. I had though the walsender
on the master was already receiving messages sometimes from the
walreceiver on the
Thank you for combining a small personal attack with a selfish
commentary about how yours is the only valid viewpoint. Saves me a lot
of trouble replying to your messages, can just ignore them instead if
this is how you're going to act.
Hey, take it easy! I read Stark's post as
Josh Berkus wrote:
Hey, take it easy! I read Stark's post as tongue-in-cheek, which I
think it was.
Yeah, I didn't get that. We've already exchanged mutual off-list
apologies for the misunderstanding in both directions, I stopped just
short of sending flowers.
I did kick off this
Greg Smith wrote:
Josh Berkus wrote:
Now that I think about it, the xmin thing really doesn't seem
conceptually difficult. If the slave just opens a 2nd, special query
connection back to the master and publishes its oldest xmin there, as
far as the master is concerned, it's just
Bruce Momjian wrote:
The first option is to connect to the primary server and keep a query
active for as long as needed to run queries on the standby. This
guarantees that a WAL cleanup record is never generated and query
conflicts do not occur, as described above. This could be done using
Greg Stark wrote:
On Sun, Feb 28, 2010 at 5:28 AM, Greg Smith g...@2ndquadrant.com wrote:
The idea of the workaround is that if you have a single long-running query
to execute, and you want to make sure it doesn't get canceled because of a
vacuum cleanup, you just have it connect back to the
I'm happy to see we've crossed the point where the worst of the Hot
Standby and Streaming Replication issues are sorted out. A look at the
to-do lists: http://wiki.postgresql.org/wiki/Hot_Standby_TODO
http://wiki.postgresql.org/wiki/Streaming_Replication show no Must-fix
items and 5 Serious
Greg Smith wrote:
Attached is a tar file with some test case demo scripts that demonstrate
the worst of the problems here IMHO.
Thanks for that! We've been discussing this for ages, so it's nice to
have a concrete example.
I don't want to belittle that work because it's been important to make
On 26/02/10 08:33, Greg Smith wrote:
There are a number of HS
tunables that interact with one another, and depending your priorities a
few ways you can try to optimize the configuration for what I expect to
be common use cases for this feature.
I've written a blog entry at
On 26/02/10 14:10, Heikki Linnakangas wrote:
Ideally the standby would stash away the old pages or tuples somewhere
so that it can still access them even after replaying the WAL records
that remove them from the main storage. I realize that's not going to
happen any time soon because it's hard
Richard Huxton wrote:
On 26/02/10 08:33, Greg Smith wrote:
I'm not sure what you might be expecting from the above combination, but
what actually happens is that many of the SELECT statements on the table
*that isn't even being updated* are canceled. You see this in the logs:
Hmm - this I'd
On 26/02/10 14:45, Heikki Linnakangas wrote:
Richard Huxton wrote:
On 26/02/10 08:33, Greg Smith wrote:
I'm not sure what you might be expecting from the above combination, but
what actually happens is that many of the SELECT statements on the table
*that isn't even being updated* are
Richard Huxton wrote:
Can we not wait to cancel the transaction until *any* new lock is
attempted though? That should protect all the single-statement
long-running transactions that are already underway. Aggregates etc.
Hmm, that's an interesting thought. You'll still need to somehow tell
the
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
On Fri, Feb 26, 2010 at 10:21 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Richard Huxton wrote:
Can we not wait to cancel the transaction until *any* new lock is
attempted though? That should protect all the single-statement
long-running transactions that are already
Heikki Linnakangas wrote:
How to handle situations where the standby goes away for a while,
such as a network outage, so that it doesn't block the master from ever
cleaning up dead tuples is a concern.
Yeah, that's another issue that needs to be dealt with. You'd probably
need some kind
On 2/26/10 6:57 AM, Richard Huxton wrote:
Can we not wait to cancel the transaction until *any* new lock is
attempted though? That should protect all the single-statement
long-running transactions that are already underway. Aggregates etc.
I like this approach. Is it fragile in some
Greg Stark gsst...@mit.edu writes:
On Fri, Feb 26, 2010 at 7:16 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I don't see a substantial additional burden there. What I would
imagine is needed is that the slave transmits a single number back
--- its current oldest xmin --- and the walsender process
Well, as Heikki said, a stop-and-go WAL management approach could deal
with that use-case. What I'm concerned about here is the complexity,
reliability, maintainability of trying to interlock WAL application with
slave queries in any sort of fine-grained fashion.
This sounds a bit
Greg Stark gsst...@mit.edu writes:
Why shouldn't it have any queries at walreceiver startup? It has any
xlog segments that were copied from the master and any it can find in
the archive, it could easily reach a consistent point long before it
needs to connect to the master. If you really want
bruce wrote:
4 The standby waiting longer than max_standby_delay to acquire a
...
#4 can be controlled by max_standby_delay, where a large value only
delays playback during crash recovery --- again, a rare occurance.
One interesting feature is that max_standby_delay will _only_ delay
Tom Lane t...@sss.pgh.pa.us writes:
Well, as Heikki said, a stop-and-go WAL management approach could deal
with that use-case. What I'm concerned about here is the complexity,
reliability, maintainability of trying to interlock WAL application with
slave queries in any sort of fine-grained
Greg Stark gsst...@mit.edu writes:
On Fri, Feb 26, 2010 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote:
There's *definitely* not going to be enough information in the WAL
stream coming from a master that doesn't think it has HS slaves.
We can't afford to record all that extra stuff in
Dimitri Fontaine wrote:
Tom Lane t...@sss.pgh.pa.us writes:
Well, as Heikki said, a stop-and-go WAL management approach could deal
with that use-case. What I'm concerned about here is the complexity,
reliability, maintainability of trying to interlock WAL application with
slave queries
Bruce Momjian br...@momjian.us writes:
Doesn't the system already adjust the delay based on the length of slave
transactions, e.g. max_standby_delay. It seems there is no need for a
user switch --- just max_standby_delay really high.
Well that GUC looks like it allows to set a compromise
Dimitri Fontaine wrote:
Bruce Momjian br...@momjian.us writes:
Doesn't the system already adjust the delay based on the length of slave
transactions, e.g. max_standby_delay. It seems there is no need for a
user switch --- just max_standby_delay really high.
Well that GUC looks like it
Bruce Momjian wrote:
Doesn't the system already adjust the delay based on the length of slave
transactions, e.g. max_standby_delay. It seems there is no need for a
user switch --- just max_standby_delay really high.
The first issue is that you're basically saying I don't care about high
Bruce Momjian wrote:
5 Early cleanup of data still visible to the current query's
snapshot
#5 could be handled by using vacuum_defer_cleanup_age on the master.
Why is vacuum_defer_cleanup_age not listed in postgresql.conf?
I noticed that myself and fired off a
Greg Smith wrote:
Bruce Momjian wrote:
Doesn't the system already adjust the delay based on the length of slave
transactions, e.g. max_standby_delay. It seems there is no need for a
user switch --- just max_standby_delay really high.
The first issue is that you're basically saying
On Fri, Feb 26, 2010 at 11:56 PM, Greg Smith g...@2ndquadrant.com wrote:
This is also the reason why the whole pause recovery idea is a fruitless
path to wander down. The whole point of this feature is that people have a
secondary server available for high-availability, *first and foremost*,
Greg Smith wrote:
You can think of the idea of passing an xmin back from the standby as
being like an auto-tuning vacuum_defer_cleanup_age. It's 0 when no
standby queries are running, but grows in size to match longer ones. And
you don't have to have to know anything to set it correctly;
Greg Stark wrote:
Well you can go sit in the same corner as Simon with your high
availability servers.
I want my ability to run large batch queries without any performance
or reliability impact on the primary server.
Thank you for combining a small personal attack with a selfish
On Sat, Feb 27, 2010 at 1:53 AM, Greg Smith g...@2ndquadrant.com wrote:
Greg Stark wrote:
Well you can go sit in the same corner as Simon with your high
availability servers.
I want my ability to run large batch queries without any performance
or reliability impact on the primary server.
Bruce Momjian wrote:
Well, I think the choice is either you delay vacuum on the master for 8
hours or pile up 8 hours of WAL files on the slave, and delay
application, and make recovery much slower. It is not clear to me which
option a user would prefer because the bloat on the master might be
Heikki Linnakangas wrote:
One such landmine is that the keepalives need to flow from client to
server while the WAL records are flowing from server to client. We'll
have to crack that problem for synchronous replication too, but I think
that alone is a big enough problem to make this 9.1
Greg Smith wrote:
Heikki Linnakangas wrote:
One such landmine is that the keepalives need to flow from client to
server while the WAL records are flowing from server to client. We'll
have to crack that problem for synchronous replication too, but I think
that alone is a big enough problem
Greg Stark wrote:
Eh? That's not what I meant at all. Actually it's kind of the exact
opposite of what I meant.
Sorry about that--I think we just hit one of those language usage drift
bits of confusion. Sit in the corner has a very negative tone to it
in US English and I interpreted your
On Sat, Feb 27, 2010 at 2:43 AM, Greg Smith g...@2ndquadrant.com wrote:
But if you're running the 8 hour report on the master right now, aren't you
already exposed to a similar pile of bloat issues while it's going? If I
have the choice between sometimes queries will get canceled vs.
On Sat, 27 Feb 2010 00:43:48 +, Greg Stark gsst...@mit.edu wrote:
On Fri, Feb 26, 2010 at 11:56 PM, Greg Smith g...@2ndquadrant.com
wrote:
This is also the reason why the whole pause recovery idea is a
fruitless
path to wander down. The whole point of this feature is that people
have a
Joshua D. Drake wrote:
On Sat, 27 Feb 2010 00:43:48 +, Greg Stark gsst...@mit.edu wrote:
I want my ability to run large batch queries without any performance
or reliability impact on the primary server.
+1
I can use any number of other technologies for high availability.
Greg Stark wrote:
But if they move from having a plain old PITR warm standby to having
one they can run queries on they might well assume that the big
advantage of having the standby to play with is precisely that they
can do things there that they have never been able to do on the master
* Greg Smith g...@2ndquadrant.com [100226 23:39]:
Just not having the actual query running on the master is such a
reduction in damage that I think it's delivering the essence of what
people are looking for regardless. That it might be possible in some
cases to additionally avoid the
Aidan Van Dyk wrote:
Would we (ya, the royal we) be willing to say that if you want the
benifit of removing the MVCC overhead of long-running queries you need
to run PITR backup/archive recovery, and if you want SR, you get a
closed-loop master-follows-save-xmin behaviour?
To turn that
53 matches
Mail list logo
