Turning off HOT/Cleanup sometimes
VACUUM cleans up blocks, which is nice because it happens offline in a
lazy manner.
We also make SELECT clean up blocks as it goes. That is useful in OLTP
workloads, but it means that large SQL queries and pg_dump effectively
do much the same work as VACUUM, generating huge amounts of I/O and
WAL on the master, the cost and annoyance of which is experienced
directly by the user. That is avoided on standbys.
Effects of that are that long running statements often run much longer
than we want, increasing bloat as a result. It also produces wildly
varying response times, depending upon extent of cleanup required.
It is a simple task to make that behaviour optional on the master.
I propose a USERSET parameter, prune_cost_limit (<---insert better name here)
which will make the behaviour optional, default -1, in normal user
processes. VACUUM will ignore this parameter and so its actions will
never be deferred.
In detail, this parameter would disable pruning for any scan larger
than the cost limit. So large scans will disable the behaviour. The
default, -1, means never disable pruning, which is the current
behavour.
We track the number of pages dirtied by the current statement. When
this reaches prune_cost_limit, we will apply these behaviours to all
shared_buffer block accesses...
(1) avoid running heap_page_prune_opt()
(2) avoid dirtying the buffer for hints. (This is safe because the
hinted changes will either be lost or will be part of the full page
image when we make a logged-change).
(i.e. doesn't apply to temp tables)
For example, if we set prune_cost_limit = 4 this behaviour allows
small index lookups via bitmapheapscan to continue to cleanup, while
larger index and seq scans will avoid cleanup.
There would be a postgresql.conf parameter prune_cost_limit, as well
as a table level parameter that would prevent pruning except via
VACUUM.
This will help in these ways
* Reduce write I/O from SELECTs and pg_dump - improving backups and BI queries
* Allow finer grained control over Hot Standby conflicts
* Potentially allow diagnostic inspection of older data via SeqScan
Prototype patch shows this is possible and simple enough for 9.4.
Major objections? Or should I polish up and submit?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jan 8, 2014 at 3:33 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
VACUUM cleans up blocks, which is nice because it happens offline in a
lazy manner.We also make SELECT clean up blocks as it goes. That is useful in OLTP
workloads, but it means that large SQL queries and pg_dump effectively
do much the same work as VACUUM, generating huge amounts of I/O and
WAL on the master, the cost and annoyance of which is experienced
directly by the user. That is avoided on standbys.
On a pgbench workload, though, essentially all page cleanup happens as
a result of HOT cleanups, like >99.9%. It might be OK to have that
happen for write operations, but it would be a performance disaster if
updates didn't try to HOT-prune. Our usual argument for doing HOT
pruning even on SELECT cleanups is that not doing so pessimizes
repeated scans, but there are clearly cases that end up worse off as a
result of that decision.
I'm not entirely wild about adding a parameter in this area because it
seems that we're increasingly choosing to further expose what arguably
ought to be internal implementation details. The recent wal_log_hints
parameter is another recent example of this that I'm not thrilled
with, but in that case, as in this one, I can see the value of it.
Still, I think it'd be loads better to restrict what you're talking
about here to the SELECT-only case; I have a strong feeling that this
will be a disaster on write workloads.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Jan 8, 2014 at 3:33 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
We also make SELECT clean up blocks as it goes. That is useful in OLTP
workloads, but it means that large SQL queries and pg_dump effectively
do much the same work as VACUUM, generating huge amounts of I/O and
WAL on the master, the cost and annoyance of which is experienced
directly by the user. That is avoided on standbys.
On a pgbench workload, though, essentially all page cleanup happens as
a result of HOT cleanups, like >99.9%. It might be OK to have that
happen for write operations, but it would be a performance disaster if
updates didn't try to HOT-prune. Our usual argument for doing HOT
pruning even on SELECT cleanups is that not doing so pessimizes
repeated scans, but there are clearly cases that end up worse off as a
result of that decision.
My recollection of the discussion when HOT was developed is that it works
that way not because anyone thought it was beneficial, but simply because
we didn't see an easy way to know when first fetching a page whether we're
going to try to UPDATE some tuple on the page. (And we can't postpone the
pruning, because the query will have tuple pointers into the page later.)
Maybe we should work a little harder on passing that information down.
It seems reasonable to me that SELECTs shouldn't be tasked with doing
HOT pruning.
I'm not entirely wild about adding a parameter in this area because it
seems that we're increasingly choosing to further expose what arguably
ought to be internal implementation details.
I'm -1 for a parameter as well, but I think that just stopping SELECTs
from doing pruning at all might well be a win. It's at least worthy
of some investigation.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 9, 2014 at 12:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Jan 8, 2014 at 3:33 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
We also make SELECT clean up blocks as it goes. That is useful in OLTP
workloads, but it means that large SQL queries and pg_dump effectively
do much the same work as VACUUM, generating huge amounts of I/O and
WAL on the master, the cost and annoyance of which is experienced
directly by the user. That is avoided on standbys.On a pgbench workload, though, essentially all page cleanup happens as
a result of HOT cleanups, like >99.9%. It might be OK to have that
happen for write operations, but it would be a performance disaster if
updates didn't try to HOT-prune. Our usual argument for doing HOT
pruning even on SELECT cleanups is that not doing so pessimizes
repeated scans, but there are clearly cases that end up worse off as a
result of that decision.My recollection of the discussion when HOT was developed is that it works
that way not because anyone thought it was beneficial, but simply because
we didn't see an easy way to know when first fetching a page whether we're
going to try to UPDATE some tuple on the page. (And we can't postpone the
pruning, because the query will have tuple pointers into the page later.)
Maybe we should work a little harder on passing that information down.
It seems reasonable to me that SELECTs shouldn't be tasked with doing
HOT pruning.I'm not entirely wild about adding a parameter in this area because it
seems that we're increasingly choosing to further expose what arguably
ought to be internal implementation details.I'm -1 for a parameter as well, but I think that just stopping SELECTs
from doing pruning at all might well be a win. It's at least worthy
of some investigation.
Unfortunately, there's no categorical answer. You can come up with
workloads where HOT pruning on selects is a win; just create a bunch
of junk and then read the same pages lots of times in a row. And you
can also come up with workloads where it's a loss; create a bunch of
junk and then read them just once. I don't know how easy it's going
to be to set that parameter in a useful way for some particular
environment, and I think that's possibly an argument against having
it. But the argument that we don't need a parameter because one
behavior is best for everyone is not going to fly.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas escribi�:
Unfortunately, there's no categorical answer. You can come up with
workloads where HOT pruning on selects is a win; just create a bunch
of junk and then read the same pages lots of times in a row. And you
can also come up with workloads where it's a loss; create a bunch of
junk and then read them just once. I don't know how easy it's going
to be to set that parameter in a useful way for some particular
environment, and I think that's possibly an argument against having
it. But the argument that we don't need a parameter because one
behavior is best for everyone is not going to fly.
In the above, there's the underlying assumption that it doesn't matter
*what* we do with the page after doing or not doing pruning. But this
is not necessarily the case: in the case of an UPDATE, having the space
be freed beforehand is beneficial because there's the option of putting
the new version of the tuple in the same page, potentially saving lots
of I/O (bring up another destination page for the new tuple, write the
new tuple there, end up dirtying two pages instead of one). But in a
SELECT, the effect is only that you will have to skip less dead tuples,
which is not as exciting.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 9 January 2014 17:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Jan 8, 2014 at 3:33 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
We also make SELECT clean up blocks as it goes. That is useful in OLTP
workloads, but it means that large SQL queries and pg_dump effectively
do much the same work as VACUUM, generating huge amounts of I/O and
WAL on the master, the cost and annoyance of which is experienced
directly by the user. That is avoided on standbys.On a pgbench workload, though, essentially all page cleanup happens as
a result of HOT cleanups, like >99.9%. It might be OK to have that
happen for write operations, but it would be a performance disaster if
updates didn't try to HOT-prune. Our usual argument for doing HOT
pruning even on SELECT cleanups is that not doing so pessimizes
repeated scans, but there are clearly cases that end up worse off as a
result of that decision.My recollection of the discussion when HOT was developed is that it works
that way not because anyone thought it was beneficial, but simply because
we didn't see an easy way to know when first fetching a page whether we're
going to try to UPDATE some tuple on the page. (And we can't postpone the
pruning, because the query will have tuple pointers into the page later.)
Maybe we should work a little harder on passing that information down.
It seems reasonable to me that SELECTs shouldn't be tasked with doing
HOT pruning.I'm not entirely wild about adding a parameter in this area because it
seems that we're increasingly choosing to further expose what arguably
ought to be internal implementation details.I'm -1 for a parameter as well, but I think that just stopping SELECTs
from doing pruning at all might well be a win. It's at least worthy
of some investigation.
Turning HOT off completely would be an absolute disaster for OLTP on
high update use cases against medium-large tables. That scenario is
well represented by pgbench and TPC-C. I am *not* suggesting we
recommend that and would look for very large caveats in the docs.
(That may not have been clear, I guess I just assumed people would
know I was heavily involved in the HOT project and understood its
benefits).
As stated, I am interested in turning off HOT in isolated, user
specified situations, perhaps just for isolated tables.
I'm not crazy about exposing magic parameters either but then I'm not
crazy about either automatic settings or deferring things because we
don't know how to set it. In general, I prefer the idea of having a
user settable parameter in one release then automating it in a later
release if clear settings emerge from usage. I'll submit a patch with
parameter, to allow experimentation, for possible removal at commit or
beta.
If I had to suggest a value for an internal parameter, I would say
that each SELECT statement should clean no more than 4 blocks. That
way current OLTP behaviour is mostly preserved while the big queries
and pg_dump don't suck in unpredictable ways.
I'll submit the patch and we can talk some more.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Robert Haas escribi�:
But the argument that we don't need a parameter because one
behavior is best for everyone is not going to fly.
In the above, there's the underlying assumption that it doesn't matter
*what* we do with the page after doing or not doing pruning. But this
is not necessarily the case: in the case of an UPDATE, having the space
be freed beforehand is beneficial because there's the option of putting
the new version of the tuple in the same page, potentially saving lots
of I/O (bring up another destination page for the new tuple, write the
new tuple there, end up dirtying two pages instead of one). But in a
SELECT, the effect is only that you will have to skip less dead tuples,
which is not as exciting.
Yeah. Once they're hinted dead, it doesn't cost that much to skip over
them. Not to mention that you might well never visit them at all, if
this is an indexscan that knows which TIDs it needs to look at.
It's possible that it can be shown that different use-cases have
sufficiently different behaviors that we really do need a user-visible
parameter. I don't want to start from that position though. If we
did have a simple GUC parameter, it'd likely end up in the same boat
as, say, enable_seqscan, which is way too blunt an instrument for real
world use --- so I'm afraid this would soon bloat into a request for
per-table settings, planner hints, or god knows what to try to confine
the effects to the queries where it's appropriate. Let's not go there
without proof that we have to. It's a much better thing if we can get
the system's native behavior to be tuned well enough by depending on
things it already knows.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:
But in a
SELECT, the effect is only that you will have to skip less dead tuples,
which is not as exciting.
Agreed. There's also the option to have it be done based on some
expectation of future work- that is, if we have to traverse X number of
dead tuples during a select, then don't bother with HOT pruning, but if
we get up to X+Y dead tuples, then do HOT pruning.
That said, I'm not entirely convinced that traversing these dead tuples
is all *that* painful during SELECT. If there's that many levels then
hopefully it's not long til an UPDATE comes along and cleans them up.
Thanks,
Stephen
* Simon Riggs (simon@2ndQuadrant.com) wrote:
I'm -1 for a parameter as well, but I think that just stopping SELECTs
from doing pruning at all might well be a win. It's at least worthy
of some investigation.Turning HOT off completely would be an absolute disaster for OLTP on
high update use cases against medium-large tables. That scenario is
well represented by pgbench and TPC-C. I am *not* suggesting we
recommend that and would look for very large caveats in the docs.
This is true even if we're only talking about turning it off for the
SELECT case...? That's what's under discussion here, after all.
Certainly, we wouldn't turn it off completely...
(That may not have been clear, I guess I just assumed people would
know I was heavily involved in the HOT project and understood its
benefits).
I'm certainly aware that you were heavily involved in HOT but I don't
think anyone is argueing to turn it off for everything.
As stated, I am interested in turning off HOT in isolated, user
specified situations, perhaps just for isolated tables.
I tend to agree w/ Tom on this point- having this be a per-table
configurable doesn't sound very appealing to me and it wouldn't address
the case you mentioned around pg_dump, but I'm sure that'd be the next
step for this and a per-session GUC wouldn't be sufficient.
I'm not crazy about exposing magic parameters either but then I'm not
crazy about either automatic settings or deferring things because we
don't know how to set it. In general, I prefer the idea of having a
user settable parameter in one release then automating it in a later
release if clear settings emerge from usage. I'll submit a patch with
parameter, to allow experimentation, for possible removal at commit or
beta.
Ugh, adding GUCs is bad *because* we end up never being able to remove
them.
If I had to suggest a value for an internal parameter, I would say
that each SELECT statement should clean no more than 4 blocks. That
way current OLTP behaviour is mostly preserved while the big queries
and pg_dump don't suck in unpredictable ways.
Right, this was one idea that I had also, as noted in the other
subthread. I'm not convinced that it's a great idea and it'd probably
be good to do a bit of testing to see just what the cost is; perhaps
even just come up with a "worst-case" example to see the difference
between a "clean" table and one with HOT chains as deep as they can go..
I'll submit the patch and we can talk some more.
Neat.
Thanks!
Stephen
Stephen Frost <sfrost@snowman.net> writes:
That said, I'm not entirely convinced that traversing these dead tuples
is all *that* painful during SELECT. If there's that many levels then
hopefully it's not long til an UPDATE comes along and cleans them up.
There's always VACUUM ;-)
If you take about ten steps back, what's happening here is that
maintenance work that we'd originally delegated to VACUUM, precisely so
that it wouldn't have to be done by foreground queries, is now being done
by foreground queries. And oddly enough, people don't like that.
There is a reasonable argument for forcing UPDATE queries to do it anyway,
to improve the odds they can do same-page updates (whether HOT or
otherwise). And probably an INSERT should do it on a page that it's
selected as an insertion target. But I think the argument that the
original do-maintenance-in-background-whenever-possible design was wrong
is a lot harder to sustain for SELECT or even DELETE queries. As I said
upthread, I think the current behavior was *not* chosen for performance
reasons but just to limit the scope of what we had to change for HOT.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/9/14, 12:54 PM, Tom Lane wrote:
Stephen Frost <sfrost@snowman.net> writes:
That said, I'm not entirely convinced that traversing these dead tuples
is all *that* painful during SELECT. If there's that many levels then
hopefully it's not long til an UPDATE comes along and cleans them up.There's always VACUUM ;-)
If you take about ten steps back, what's happening here is that
maintenance work that we'd originally delegated to VACUUM, precisely so
that it wouldn't have to be done by foreground queries, is now being done
by foreground queries. And oddly enough, people don't like that.There is a reasonable argument for forcing UPDATE queries to do it anyway,
to improve the odds they can do same-page updates (whether HOT or
otherwise). And probably an INSERT should do it on a page that it's
selected as an insertion target. But I think the argument that the
original do-maintenance-in-background-whenever-possible design was wrong
is a lot harder to sustain for SELECT or even DELETE queries. As I said
upthread, I think the current behavior was *not* chosen for performance
reasons but just to limit the scope of what we had to change for HOT.
Instead of looking at how to avoid this work in SELECTs maybe it'd be more useful to look at how we can get it done more quickly in the background. The VSM is already a step in the right direction, but it seems the big use case here is when some bulk operation comes through and touches a sizeable number of blocks (but perhaps not enough to hit autovac thresholds).
ISTM it wouldn't be too difficult for a backend to track how many blocks in a relation it's dirtied (keep in mind that count doesn't have to be perfect). If we tracked that info, it could be put into a maintenance queue (LISTEN/NOTIFY?) along with our XID. That gives us a list of relations to vacuum and exactly when to vacuum them. Thanks to the VSM we wouldn't need to track individual pages (though it might be useful to track the minimum and maximum block IDs we hit, per relation).
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 9, 2014 at 1:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Stephen Frost <sfrost@snowman.net> writes:
That said, I'm not entirely convinced that traversing these dead tuples
is all *that* painful during SELECT. If there's that many levels then
hopefully it's not long til an UPDATE comes along and cleans them up.There's always VACUUM ;-)
If you take about ten steps back, what's happening here is that
maintenance work that we'd originally delegated to VACUUM, precisely so
that it wouldn't have to be done by foreground queries, is now being done
by foreground queries. And oddly enough, people don't like that.
People *think* they don't like that, because that's the way it works
right now. If it worked some other way, there's a good chance people
would be complaining about that behavior, too. I submitted a patch a
few years back to limit the setting of hint bits by foreground
processes to approximately 5% of the buffers they touched in a large
scan, so that no single scan would incur all the cost of setting the
hint bits; instead, the cost would be amortized over the first 20 or
so scans. However, nobody was very enthusiastic about that patch,
because while it greatly softened the blow for the first scan,
subsequent scans were slower, because now they had to carry part of
the burden, too. And you know what? People didn't like *that*
either.
The problem with saying that we should let VACUUM do this work is the
same as the problem with saying that if you're late for your Concorde
flight, you should go running across the tarmac and try to catch it.
The cost of dead tuples is related in a linear fashion to the rate at
which pages are accessed. Not coincidentally, the number of
opportunities for HOT pruning is *also* related in a linear fashion to
the rate at which pages are accessed. This is why it works so well.
The rate at which vacuuming happens does not ramp up in the same way;
it's limited by autovacuum cost settings (which people tend not have
set correctly, and don't adjust themselves on the fly) or by their
hardware capabilities. If autovacuum can't keep up, foreground
activity doesn't slow down to compensate; instead, the system just
bloats out of control. While people may not like having this
maintenance activity in the foreground, they like not having it at all
even less.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-09 16:27:23 -0500, Robert Haas wrote:
People *think* they don't like that, because that's the way it works
right now. If it worked some other way, there's a good chance people
would be complaining about that behavior, too.
I think on of the primary reason why it's causing huge slowdowns is that
the ring buffer of scan strategies causes dirty buffer writes pretty
much immediately, when a buffer is reused.
Not that delaying the writeout would work all that effectively right
now, with the current bgwriter...
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
The problem with saying that we should let VACUUM do this work is the
same as the problem with saying that if you're late for your Concorde
flight, you should go running across the tarmac and try to catch it.
The cost of dead tuples is related in a linear fashion to the rate at
which pages are accessed. Not coincidentally, the number of
opportunities for HOT pruning is *also* related in a linear fashion to
the rate at which pages are accessed. This is why it works so well.
That seems like a large oversimplification. Some (most?) of the costs of
dead tuples are proportional to the rate of dead tuple creation. I grant
that there are also some costs proportional to the rate at which scans
visit dead tuples, but I really don't believe that the latter are
dominant. So I think it's bogus to claim that the current behavior is
somehow optimal.
One more time: the sole reason it works the way it does now is that that
was the path of least resistance back in 2007, and we never yet got around
to trying to optimize that. I'm glad to see someone wanting to revisit
the issue, but I don't think that we necessarily have to go as far as
creating user-visible knobs in order to make it better.
The rate at which vacuuming happens does not ramp up in the same way;
it's limited by autovacuum cost settings (which people tend not have
set correctly, and don't adjust themselves on the fly)
True, but that seems like a pretty well-defined improvement project right
there (as well as an argument against user-visible knobs in general ;-)).
Nasby's speculations just upthread could be useful here, too.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 9, 2014 at 4:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
The problem with saying that we should let VACUUM do this work is the
same as the problem with saying that if you're late for your Concorde
flight, you should go running across the tarmac and try to catch it.
The cost of dead tuples is related in a linear fashion to the rate at
which pages are accessed. Not coincidentally, the number of
opportunities for HOT pruning is *also* related in a linear fashion to
the rate at which pages are accessed. This is why it works so well.That seems like a large oversimplification. Some (most?) of the costs of
dead tuples are proportional to the rate of dead tuple creation. I grant
that there are also some costs proportional to the rate at which scans
visit dead tuples, but I really don't believe that the latter are
dominant. So I think it's bogus to claim that the current behavior is
somehow optimal.
This doesn't make any sense to me at all. What costs are proportional
to the rate of dead tuple creation? I'm referring specifically to the
performance penalty that scans incur for having to skip over dead
tuples, and those costs aren't incurred when the tuples are created,
but rather when you try to access the still-live data afterwards. If
anything, our system is a big WIN at the time tuples are created,
precisely because we leave the old tuples around to be cleaned up
later rather than getting rid of them at once. That's why, for
example, we tend to win delete-heavy benchmarks vs. other database
systems.
One more time: the sole reason it works the way it does now is that that
was the path of least resistance back in 2007, and we never yet got around
to trying to optimize that. I'm glad to see someone wanting to revisit
the issue, but I don't think that we necessarily have to go as far as
creating user-visible knobs in order to make it better.
Sure, I'm not denying that. The fact that it was the path of least
resistance doesn't mean it was a bad idea. I'm happy to see it
improved, too, but I think it's important to understand what happens
now. And at least on the pgbench tests I've done, what happens is
that VACUUM makes no significant contribution to pruning; IIRC, it
would have to visit pages at least 1000 times more often to be
relevant. So when somebody says "relying on vacuum instead of doing
HOT pruning" what I hear is "flush performance down the toilet"... but
of course the real way to resolve this is to test whatever patch Simon
or someone else eventually posts, not to speculate without data.
The rate at which vacuuming happens does not ramp up in the same way;
it's limited by autovacuum cost settings (which people tend not have
set correctly, and don't adjust themselves on the fly)True, but that seems like a pretty well-defined improvement project right
there (as well as an argument against user-visible knobs in general ;-)).
Nasby's speculations just upthread could be useful here, too.
Fair point.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Robert Haas (robertmhaas@gmail.com) wrote:
So when somebody says "relying on vacuum instead of doing
HOT pruning" what I hear is "flush performance down the toilet"... but
of course the real way to resolve this is to test whatever patch Simon
or someone else eventually posts, not to speculate without data.
I don't think anyone was seriously proposing that (certainly not with
today's VACUUM). What I've heard speculated about is doing HOT pruning
during UPDATE and/or INSERT but specifically not during SELECT. I
concur that we need data to really understand the difference, hopefully
there'll be a patch posted which we can play with.
Thanks,
Stephen
On 8 January 2014 08:33, Simon Riggs <simon@2ndquadrant.com> wrote:
VACUUM cleans up blocks, which is nice because it happens offline in a
lazy manner.We also make SELECT clean up blocks as it goes. That is useful in OLTP
workloads, but it means that large SQL queries and pg_dump effectively
do much the same work as VACUUM, generating huge amounts of I/O and
WAL on the master, the cost and annoyance of which is experienced
directly by the user. That is avoided on standbys.Effects of that are that long running statements often run much longer
than we want, increasing bloat as a result. It also produces wildly
varying response times, depending upon extent of cleanup required.It is a simple task to make that behaviour optional on the master.
I propose a USERSET parameter, prune_cost_limit (<---insert better name here)
which will make the behaviour optional, default -1, in normal user
processes. VACUUM will ignore this parameter and so its actions will
never be deferred.In detail, this parameter would disable pruning for any scan larger
than the cost limit. So large scans will disable the behaviour. The
default, -1, means never disable pruning, which is the current
behavour.We track the number of pages dirtied by the current statement. When
this reaches prune_cost_limit, we will apply these behaviours to all
shared_buffer block accesses...(1) avoid running heap_page_prune_opt()
(2) avoid dirtying the buffer for hints. (This is safe because the
hinted changes will either be lost or will be part of the full page
image when we make a logged-change).(i.e. doesn't apply to temp tables)
For example, if we set prune_cost_limit = 4 this behaviour allows
small index lookups via bitmapheapscan to continue to cleanup, while
larger index and seq scans will avoid cleanup.There would be a postgresql.conf parameter prune_cost_limit, as well
as a table level parameter that would prevent pruning except via
VACUUM.This will help in these ways
* Reduce write I/O from SELECTs and pg_dump - improving backups and BI queries
* Allow finer grained control over Hot Standby conflicts
* Potentially allow diagnostic inspection of older data via SeqScanPrototype patch shows this is possible and simple enough for 9.4.
Major objections? Or should I polish up and submit?
Patch attached, implemented to reduce writes by SELECTs only.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
prune_page_limit.v3.patchapplication/octet-stream; name=prune_page_limit.v3.patchDownload+156-8
On Tue, Jan 14, 2014 at 4:13 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 8 January 2014 08:33, Simon Riggs <simon@2ndquadrant.com> wrote:
VACUUM cleans up blocks, which is nice because it happens offline in a
lazy manner.We also make SELECT clean up blocks as it goes. That is useful in OLTP
workloads, but it means that large SQL queries and pg_dump effectively
do much the same work as VACUUM, generating huge amounts of I/O and
WAL on the master, the cost and annoyance of which is experienced
directly by the user. That is avoided on standbys.Effects of that are that long running statements often run much longer
than we want, increasing bloat as a result. It also produces wildly
varying response times, depending upon extent of cleanup required.It is a simple task to make that behaviour optional on the master.
I propose a USERSET parameter, prune_cost_limit (<---insert better name here)
which will make the behaviour optional, default -1, in normal user
processes. VACUUM will ignore this parameter and so its actions will
never be deferred.In detail, this parameter would disable pruning for any scan larger
than the cost limit. So large scans will disable the behaviour. The
default, -1, means never disable pruning, which is the current
behavour.We track the number of pages dirtied by the current statement. When
this reaches prune_cost_limit, we will apply these behaviours to all
shared_buffer block accesses...(1) avoid running heap_page_prune_opt()
(2) avoid dirtying the buffer for hints. (This is safe because the
hinted changes will either be lost or will be part of the full page
image when we make a logged-change).(i.e. doesn't apply to temp tables)
For example, if we set prune_cost_limit = 4 this behaviour allows
small index lookups via bitmapheapscan to continue to cleanup, while
larger index and seq scans will avoid cleanup.There would be a postgresql.conf parameter prune_cost_limit, as well
as a table level parameter that would prevent pruning except via
VACUUM.This will help in these ways
* Reduce write I/O from SELECTs and pg_dump - improving backups and BI queries
* Allow finer grained control over Hot Standby conflicts
* Potentially allow diagnostic inspection of older data via SeqScanPrototype patch shows this is possible and simple enough for 9.4.
Major objections? Or should I polish up and submit?Patch attached, implemented to reduce writes by SELECTs only.
I am still not sure whether we want this, but I think it's definitely
an improvement over the previous version. Assorted comments:
- Naming consistency seems to me to dictate that there should be more
similarity between the reloption name (allow_buffer_cleanup) and the
GUC (prune_page_dirty_limit).
- The documentation doesn't describe the use case where suppressing
cleanup on a per-table basis would be desirable, and I can't think of
one, either.
- There are a variety of ways to limit pruning; here, you've chosen to
limit it to a particular number of pruning operations per executor
invocation. But the flag is global, not part of the executor state,
so a query that calls a PL/pgsql function during execution will reset
the counter for the parent query also, which doesn't seem very
principled.
In a patch I posted a few years ago to set hint bits only sometimes, I
settled on an algorithm where I dirtied the first 50 pages per scan
and then skipped the next 950, or something like that. The idea was
that you wanted the pages that did get dirtied to be clustered
together to avoid random I/O; and also that you wanted table of
arbitrary size to get hinted within a certain number of scans (e.g.
20). The limiting here is much more aggressive, so on large tables it
will amount to basically no pruning at all. I dunno whether that's a
good idea or not. But if the idea of making this an integer rather
than a boolean is to allow some pruning to still happen while keeping
it checked within reasonable bounds, I'm not sure it will succeed.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 January 2014 16:47, Robert Haas <robertmhaas@gmail.com> wrote:
There would be a postgresql.conf parameter prune_cost_limit, as well
as a table level parameter that would prevent pruning except via
VACUUM.This will help in these ways
* Reduce write I/O from SELECTs and pg_dump - improving backups and BI queries
* Allow finer grained control over Hot Standby conflicts
* Potentially allow diagnostic inspection of older data via SeqScan
Patch attached, implemented to reduce writes by SELECTs only.
I am still not sure whether we want this, but I think it's definitely
an improvement over the previous version. Assorted comments:- Naming consistency seems to me to dictate that there should be more
similarity between the reloption name (allow_buffer_cleanup) and the
GUC (prune_page_dirty_limit).
Now that I've written the patch, I'm seeing those as two different
things, but YMMV and I am very open to naming suggestions.
- The documentation doesn't describe the use case where suppressing
cleanup on a per-table basis would be desirable, and I can't think of
one, either.
We already know that HOT is ineffective in areas of high contention
(previous thread by me). Prior experience was that smaller tables
didn't show much apparent benefit from using HOT either; its
effectiveness was limited to medium and large tables being updated.
The two already stated use cases that would apply are these ones
* Allow finer grained control over Hot Standby conflicts
* Potentially allow diagnostic inspection of older data via SeqScan
So the use cases for the two parameters seem quite different and we
may decide we want one but not the other.
- There are a variety of ways to limit pruning; here, you've chosen to
limit it to a particular number of pruning operations per executor
invocation. But the flag is global, not part of the executor state,
so a query that calls a PL/pgsql function during execution will reset
the counter for the parent query also, which doesn't seem very
principled.
That is subtle thing in this patch and I agree that potential problem
exists. The current limit is set according to the current executing
statement, but the current total is not reset until start of the top
level statement. So the behaviour is not reset during statements
executed within PL/pgSQL function.
In a patch I posted a few years ago to set hint bits only sometimes, I
settled on an algorithm where I dirtied the first 50 pages per scan
and then skipped the next 950, or something like that. The idea was
that you wanted the pages that did get dirtied to be clustered
together to avoid random I/O; and also that you wanted table of
arbitrary size to get hinted within a certain number of scans (e.g.
20). The limiting here is much more aggressive, so on large tables it
will amount to basically no pruning at all. I dunno whether that's a
good idea or not. But if the idea of making this an integer rather
than a boolean is to allow some pruning to still happen while keeping
it checked within reasonable bounds, I'm not sure it will succeed.
It sounds like you're in favour of the overall concept of limiting
writes, which is good.
The behaviour I think we need, based on listening to everybody so far is
* OLTP is unaffected
* Large SELECTs and pg_dump don't cause lots of write I/O.
and hence why "prune_page_dirty_limit" offers a change in behaviour at
a certain point.
Reducing cleanup to "only 5%" just reduces but doesn't remove the
problem. If the data is stored on very poor I/O infrastructure, any
significant volume of writes can adversely affect performance. As we
reduce the percentage, we also reduce the benefit from inducing writes
in the first place and so I would question why bother at all using a
percentage. For me, a parameter that gives you absolute rather than
relative control is more desirable.
The current behaviour assumes it is OK for the first/next user to
touch the data to be the one that won't mind re-writing everything. In
time critical applications, the first/next user could well have a very
urgent need to access the data quickly and doesn't want to have to pay
this price. In seldom-accessed data applications, VACUUM has lots of
time to run out of hours, so users are OK to defer this work. Some
applications exist where we literally want zero I/O.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jan 15, 2014 at 5:14 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
We already know that HOT is ineffective in areas of high contention
(previous thread by me). Prior experience was that smaller tables
didn't show much apparent benefit from using HOT either; its
effectiveness was limited to medium and large tables being updated.
The two already stated use cases that would apply are these ones
Do you have a link to that previous thread? I don't happen to recall
that conversation.
I've found that HOT can be very important on smaller tables, so I'm
skeptical of that as a general conclusion. What I think might be true
is that if VACUUM is going to hit the table often enough to make you
happy, then you don't really need HOT. In other words, if the update
rate is non-zero but low, not too much cruft will accumulate before
the table gets vacuumed, and you may be OK. If the update rate is
high, though, I think disabling HOT will be painful on a table of any
size. There might be exceptions, but I can't think of what the are
off-hand.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
