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 sta
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 m
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
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
On Fri, 2010-02-26 at 16:44 -0500, Tom Lane wrote:
> Greg Stark writes:
> > On Fri, Feb 26, 2010 at 9:19 PM, Tom Lane 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
Greg Stark wrote:
On Sun, Feb 28, 2010 at 5:28 AM, Greg Smith 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 master to keep an op
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
co
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 ju
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 discu
> 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 tongue-i
> 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 st
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 b
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 g
Greg Smith wrote:
> Joshua D. Drake wrote:
> > On Sat, 27 Feb 2010 00:43:48 +, Greg Stark 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 technol
Heikki Linnakangas wrote:
> Dimitri Fontaine wrote:
>> Bruce Momjian 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
Dimitri Fontaine wrote:
> Bruce Momjian 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 se
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 quest
* Greg Smith [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 overhead that come
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
previou
Joshua D. Drake wrote:
On Sat, 27 Feb 2010 00:43:48 +, Greg Stark 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.
Remove "must be an
On Sat, 27 Feb 2010 00:43:48 +, Greg Stark wrote:
> On Fri, Feb 26, 2010 at 11:56 PM, Greg Smith
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 availa
On Sat, Feb 27, 2010 at 2:43 AM, Greg Smith 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. "sometimes
> the master
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 you
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
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 material
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
p
On Sat, Feb 27, 2010 at 1:53 AM, Greg Smith 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.
>>
>
> Tha
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
commentary
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;
On Fri, Feb 26, 2010 at 11:56 PM, Greg Smith 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*, but
> they'd like
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 basical
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 corr
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
a
Dimitri Fontaine wrote:
> Bruce Momjian 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
Bruce Momjian 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 between HA and
rep
Dimitri Fontaine wrote:
> Tom Lane 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
Greg Stark writes:
> On Fri, Feb 26, 2010 at 9:19 PM, Tom Lane 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 installations for
>> which it's just
Tom Lane 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 fashion.
Some ad
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_ del
Greg Stark 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 to protect y
> 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 brute-
Greg Stark writes:
> On Fri, Feb 26, 2010 at 7:16 PM, Tom Lane 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 publishes
>> that number a
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 non-
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 so
On Fri, Feb 26, 2010 at 10:21 AM, Heikki Linnakangas
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 underway. Aggregates etc.
>
> Hmm
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
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
th
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 canceled.
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 -
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 t
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
http://blog.2ndqu
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 ma
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 I
53 matches
Mail list logo
