pgsql: Augment WAL records forbtree delete with GetOldestXmin() to
in [PgSql]
|
Prev: Using the new libpq connection functions in PostgreSQLbinaries
Next: pgsql: Augment WAL records for btree delete with GetOldestXmin() to
From: Simon Riggs on 31 Jan 2010 14:42 On Sun, 2010-01-31 at 14:07 -0500, Tom Lane wrote: > > The commit is a one line change, with parameter to control it, discussed > > by Heikki and myself in December 2008. I stand by the accuracy of the > > change; the parameter is really to ensure we can test during beta. > > Well, I was waiting to see if anyone else had an opinion, but: my > opinion is that a GUC is not appropriate here. Either test it yourself > enough to be sure it's a win, or don't put it in. I will remove the parameter then, keeping the augmentation. That OK? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 31 Jan 2010 15:13 On Sun, 2010-01-31 at 20:48 +0100, Stefan Kaltenbrunner wrote: > Simon Riggs wrote: > > On Sun, 2010-01-31 at 14:07 -0500, Tom Lane wrote: > > > >>> The commit is a one line change, with parameter to control it, discussed > >>> by Heikki and myself in December 2008. I stand by the accuracy of the > >>> change; the parameter is really to ensure we can test during beta. > >> Well, I was waiting to see if anyone else had an opinion, but: my > >> opinion is that a GUC is not appropriate here. Either test it yourself > >> enough to be sure it's a win, or don't put it in. > > > > I will remove the parameter then, keeping the augmentation. That OK? > > Well how much is the actual hit with this on the master for different > workloads do we have realistic numbers on that? Also how much of an > actual win is it in the other direction - as in under what circumstances > and workloads does it help in avoiding superflous cancelations on the > standby? At the moment a btree delete record will cancel every request 1. no matter how long they have been running 2. no matter if they haven't accessed the index being cleaned (they might later, is the thinking...) This change improves case (1) in that it will only remove queries that are older than the oldest snapshot on the primary, should max_standby_delay be exceeded. Case (2) would have been improved by my other proposed patch should max_standby_delay be exceeded. The cost of improving case (1) is that we do the equivalent of taking a snapshot of the procarray while holding locks on the btree block being cleaned. That will increase index contention somewhat in applications that do btree deletes, i.e. non-hot index updates or deletes. Greg Stark's comments have been that he wants to see max_standby_delay = -1 put back, which would effecively prevent both (1) and (2) from occurring. I am working on that now. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 31 Jan 2010 16:04 On Sun, 2010-01-31 at 15:41 -0500, Tom Lane wrote: > Simon Riggs <simon(a)2ndQuadrant.com> writes: > > At the moment a btree delete record will cancel every request > > 1. no matter how long they have been running > > 2. no matter if they haven't accessed the index being cleaned (they > > might later, is the thinking...) > > That seems seriously horrid. What is the rationale for #2 in > particular? I would hope that at worst this would affect sessions > that are actively competing for the index being cleaned. That is exactly the feedback I received from many other people and why I prioritised the relation-specific conflict patch. It's worse that that because point 2 effects WAL cleanup records for the heap also. The rationale is that a session *might* in the future access a table, and if it did so it would receive the wrong answer *potentially*. This is the issue I have been discussing for a long time now, in various forms, starting on-list in Aug 2008. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 31 Jan 2010 16:09 On Sun, 2010-01-31 at 22:05 +0100, Stefan Kaltenbrunner wrote: > hmm makes sense from the HS side - but I think to make a case for a GUC > it would help a lot to see actual numbers(as in benchmarks) showing how > much of a hit it is to the master. Agreed, though my approach to benchmarking was to provide the mechanism by which it was possible to benchmark. I didn't presume to be able to cover wide area with benchmarking tests just for this. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 31 Jan 2010 16:56
On Sun, 2010-01-31 at 23:43 +0200, Heikki Linnakangas wrote: > IIRC it was Greg Stark who suggested last time this was discussed that > we could calculate the exact value for latestRemovedXid in the > standby. When replaying the deletion record, the standby could look at > all the heap tuples whose index pointers are being removed, to see > which one was newest. That can be pretty expensive, involving random > I/O, but it gives an exact value, and doesn't stress the master at > all. And we could skip it if there's no potentially conflicting > read-only queries running. > > That seems like the most user-friendly approach to me. Even though > random I/O is expensive, surely it's better than killing queries. Best solution, no time to do it. Should I revoke this change? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |