Some thoughts about i/o priorities and throttling vacuum
So when I suggested on linux-kernel that vacuumcould benefit from some way to
prioritize i/o resourcse, someone suggested vacuum could just throttle its own
disk accesses.
While I think they their conception of vacuum is still broken and the
throttling methods they described are the wrong direction, on further thought
I think they actually have the right idea.
pg_autovacuum knows at what rate free space has been accumulating. It knows
how large the fsm available is. It can therefore calculate exactly how much
time it has available to complete the next vacuum run before the fsm runs out
(assuming the free space continues accumulating at a constant rate).
If it passed that information on to vacuum then vacuum could throttle its own
disk accesses by, say, reading 64k at a time then sleeping for a fraction of a
second. The time spent sleeping would be calculated to have the vacuum take
the required total time.
This would produce a more even and less resource hogging duty cycle where
vacuum would be continuously running at low levels, rather than a duty cycle
where it doesn't run at all until it's needed, but then floods the disk
controllers with continuous sequential reads.
(There are a few details of course. You would need to leave a safety margin in
case free space accumulation speeds up. And accounting for the actual time
spent doing the vacuum would make calculating the sleep time tricky. But they
seem fairly tractable problems.)
Personally I think i/o priorities give much better leverage. It would let
vacuum run as fast as the disk subsystems can handle during idle times, and
then fade away as soon as any heavy transaction load appears. But the flip
side is that with i/o prioritization vacuum might not actually finish in time.
--
greg
Greg Stark <gsstark@mit.edu> writes:
... vacuum could throttle
its own disk accesses by, say, reading 64k at a time then sleeping for
a fraction of a second.
...
Personally I think i/o priorities give much better leverage.
Pie in the sky is great too ;-). But there is no such thing as i/o
priorities, at least not in any portable sense.
OTOH I was just musing to myself earlier today that putting a tunable
delay into VACUUM's per-page loop might make it more friendly to
competing processes. I dunno if it'd work or just be a waste of time,
but it does seem worth experimenting with.
Want to try it out and report back?
regards, tom lane
I think adding tunable delay per-page loop into VACUUM will help keep system
responsive at all times. In many cases, especially for mostly read-only
tables, plain VACUUM does not need to complete immediately (VACUUM FULL
should complete immediately). I prefer that VACUUM takes its sweet time to
run as long as it doesn't disrupt other queries. See my other post on
"VACUUM degrades performance significantly. Database becomes unusable!" on
pgsql-general mailing list.
Regards,
Stephen
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:16818.1066282922@sss.pgh.pa.us...
Show quoted text
Greg Stark <gsstark@mit.edu> writes:
... vacuum could throttle
its own disk accesses by, say, reading 64k at a time then sleeping for
a fraction of a second.
...
Personally I think i/o priorities give much better leverage.Pie in the sky is great too ;-). But there is no such thing as i/o
priorities, at least not in any portable sense.OTOH I was just musing to myself earlier today that putting a tunable
delay into VACUUM's per-page loop might make it more friendly to
competing processes. I dunno if it'd work or just be a waste of time,
but it does seem worth experimenting with.Want to try it out and report back?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Stephen wrote:
I think adding tunable delay per-page loop into VACUUM will help keep system
responsive at all times. In many cases, especially for mostly read-only
tables, plain VACUUM does not need to complete immediately (VACUUM FULL
should complete immediately). I prefer that VACUUM takes its sweet time to
run as long as it doesn't disrupt other queries. See my other post on
"VACUUM degrades performance significantly. Database becomes unusable!" on
pgsql-general mailing list.
Of course, this makes VACUUM run longer, and if you are waiting for it
to finish, it would be worse, like if you are running it at night or
something.
I think the delay has to take into account the number of active
transactions or something.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Of course, this makes VACUUM run longer, and if you are waiting for it
to finish, it would be worse, like if you are running it at night or
something.
My plan was that the time delay would be a parameter and pg_autovacuum would
set it based on the observed rate at which free space is accumulating.
Someone could manually specify a delay, but by default it would run with no
delay when run on the command line.
I think the delay has to take into account the number of active
transactions or something.
That's a possibility. That's actually what the linux-kernel folk suggested.
Someone there suggested using aio to do carefully schedule i/o only when no
i/o was pending from transactions.
But vacuum has no way to judge whether those transactions are really doing
much disk i/o or only reading cached blocks, or even whether the disk i/o
they're doing is on the same disk. They could also be waiting on the client or
on locks from other transactions.
--
greg
On Thu, 2003-10-16 at 16:16, Greg Stark wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Of course, this makes VACUUM run longer, and if you are waiting for it
to finish, it would be worse, like if you are running it at night or
something.My plan was that the time delay would be a parameter and pg_autovacuum would
set it based on the observed rate at which free space is accumulating.
I don't know that pg_autovacuum is smart enough to make a good guess as
to an appropriate parameter.
I think the delay has to take into account the number of active
transactions or something.
I think this is a better plan than pg_autovacuum, this would also allow
vacuum to have a different delay for each loop depending on the current
number of transactions.
But vacuum has no way to judge whether those transactions are really doing
much disk i/o or only reading cached blocks, or even whether the disk i/o
they're doing is on the same disk. They could also be waiting on the client or
on locks from other transactions.
True, it would be a rough estimate, but at least one based on something
representative of system I/O load at that moment.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Of course, this makes VACUUM run longer, and if you are waiting for it
to finish, it would be worse, like if you are running it at night or
something.
I think the delay has to take into account the number of active
transactions or something.
I was just thinking of a GUC parameter: wait N milliseconds between
pages, where N defaults to zero probably. A user who wants to run his
vacuum as a background process could set N larger than zero. I don't
believe we are anywhere near being able to automatically adjust the
delay based on load, and even if we could, this would ignore the point
you make above --- the user's intent has to matter as much as anything
else.
regards, tom lane
Is it possible to have an optional delay in plain VACUUM for each invocation
rather than database wide? Something along the line of an optional THROTTLE
or DELAY parameter for the VACUUM command. The THROTTLE is ignored when FULL
or FREEZE is selected.
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [THROTTLE] ANALYZE [ table [ (column
[, ...] ) ] ]
This way autovacuum can still throttle VACUUM as needed in future (either in
contrib or backend) and administrators can decide to apply different delays
for different tables depending on the usage.
Regards, Stephen
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:16916.1066349859@sss.pgh.pa.us...
Show quoted text
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Of course, this makes VACUUM run longer, and if you are waiting for it
to finish, it would be worse, like if you are running it at night or
something.
I think the delay has to take into account the number of active
transactions or something.I was just thinking of a GUC parameter: wait N milliseconds between
pages, where N defaults to zero probably. A user who wants to run his
vacuum as a background process could set N larger than zero. I don't
believe we are anywhere near being able to automatically adjust the
delay based on load, and even if we could, this would ignore the point
you make above --- the user's intent has to matter as much as anything
else.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Tom Lane wrote:
I was just thinking of a GUC parameter: wait N milliseconds between
pages, where N defaults to zero probably. A user who wants to run his
vacuum as a background process could set N larger than zero. I don't
believe we are anywhere near being able to automatically adjust the
delay based on load, and even if we could, this would ignore the point
you make above --- the user's intent has to matter as much as anything
else.
I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At the
most it does vacuum /vacuum analyse, none of which chew disk bandwidth. And if
pg_autovacuum is running along with postmaster all the time, with aggressive
polling like 5 sec, the database should not accumulate any dead tuples nor it
would suffer xid wraparound as there are vacuum happening constantly.
What's left in above scenario? As long as all the requirements for pg_autovacuum
are met, namely setting it up, setting it up aggressively and tuning
postgresql.conf correctly, vacuum and related problems should be a thing in
past, at least as far as 7.4 and onwards is considered.
Of course RSM implementation for vacuum would still be much needed but right
now, it does not affect disk IO directly(except for tossing buffer cache out of
track that is).
What am I missing?
Shridhar
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote:
I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At
the most it does vacuum /vacuum analyse, none of which chew disk bandwidth.
The latter is false. VACUUM FULL certainly uses _more_ disk
bandwidth than VACUUM, but it's just false that plain VACUUM doesn't
contend for disk. And if you're already maxed, then that extra
bandwidth you cannot afford.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110Andrew Sullivan wrote:
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote:
I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At
the most it does vacuum /vacuum analyse, none of which chew disk bandwidth.The latter is false. VACUUM FULL certainly uses _more_ disk
bandwidth than VACUUM, but it's just false that plain VACUUM doesn't
contend for disk. And if you're already maxed, then that extra
bandwidth you cannot afford.
What part of plain vacuum takes disk bandwidth? WAL? Clog? Certainly not data
files themselves, right?
OK, I understand some system can be saturated enough to have additional WAL/Clog
burdon, but genuinely curious, how much disk bandwidth is required for plain
vacuum and what are the factors it depends upon?
Shridhar
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote:
And if pg_autovacuum is running along with postmaster all the time, with
aggressive polling like 5 sec, the database should not accumulate any dead
tuples nor it would suffer xid wraparound as there are vacuum happening
constantly.
The database can suffer XID wraparound anyway if there's at least one
table without updates, because the autovacuum daemon will never vacuum
it (correct me if I'm wrong).
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Tiene valor aquel que admite que es un cobarde" (Fernandel)
Alvaro Herrera wrote:
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote:
And if pg_autovacuum is running along with postmaster all the time, with
aggressive polling like 5 sec, the database should not accumulate any dead
tuples nor it would suffer xid wraparound as there are vacuum happening
constantly.The database can suffer XID wraparound anyway if there's at least one
table without updates, because the autovacuum daemon will never vacuum
it (correct me if I'm wrong).
If a table is never updated and hence not vacuumed at all, why would it be
involved in a transaction that would have xid wrap around?
pg_autovacuum takes care of insert/updates/deletes. If a table never
participates in above three and hence escape from pg_autovauum, it also escapes
from xid wraparound, isn't it?
Shridhar
On Fri, Oct 17, 2003 at 07:41:38PM +0530, Shridhar Daithankar wrote:
Alvaro Herrera wrote:
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote:
The database can suffer XID wraparound anyway if there's at least one
table without updates, because the autovacuum daemon will never vacuum
it (correct me if I'm wrong).If a table is never updated and hence not vacuumed at all, why would it be
involved in a transaction that would have xid wrap around?
Because the tuples on it were involved in some insert operation at some
time (else the table would not have any tuples). So it _has_ to be
vacuumed, else you run the risk of losing the tuples when the wraparound
happens. (Sorry, I don't know how to explain this better.)
Maybe in this case it's best to do a VACUUM FREEZE; that'd ensure that
the table would never ever need a vacuum again until it suffers
an insert, delete or update. Perhaps the autovacuum daemon could detect
the case where a table has only very old tuples and freeze it.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El n�mero de instalaciones de UNIX se ha elevado a 10,
y se espera que este n�mero aumente" (UPM, 1972)
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
What part of plain vacuum takes disk bandwidth?
Reading (and possibly rewriting) all the pages.
regards, tom lane
Alvaro Herrera wrote:
On Fri, Oct 17, 2003 at 07:41:38PM +0530, Shridhar Daithankar wrote:
Alvaro Herrera wrote:
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote:
The database can suffer XID wraparound anyway if there's at least one
table without updates, because the autovacuum daemon will never vacuum
it (correct me if I'm wrong).If a table is never updated and hence not vacuumed at all, why would it be
involved in a transaction that would have xid wrap around?Because the tuples on it were involved in some insert operation at some
time (else the table would not have any tuples). So it _has_ to be
vacuumed, else you run the risk of losing the tuples when the wraparound
happens. (Sorry, I don't know how to explain this better.)
OK. So here is what I understand. I have a table which contains 100 rows which
appeated there due to some insert operation. Then I vacuum it. And sit there for
internity for rest of the database to approach the singularity(the xid
wraparound..:-) Nice term, isn't it?).
So this static table is vulnerable to xid wraparound? I doubt.
Did I miss something?
Shridhar
Tom Lane wrote:
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
What part of plain vacuum takes disk bandwidth?
Reading (and possibly rewriting) all the pages.
I was under impression that was for shared memory pages only and not for disk pages.
OK. I can see difference of understanding here.
Plain Vacuum goes around the table/database and makes space, shared buffers and
disks, reusable whenever possible but *does not* free any space.
Would it be possible to have a vacuum variant that would just shuffle thr.
shared buffers and not touch disk at all? pg_autovacuum could probably be ulra
agressive with such a shared-buffers only scan? Is it possible or feasible?
IMO that could be a clever solution rather than throttling IO for vacuum. For
one thing, getting that throttiling right, would be extremely difficult and
varying from site to site. If it is going to be tough to tune, then it will be
underutilised and will lose it's value rather rapidly.
Just a thought..
Shridhar
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
Would it be possible to have a vacuum variant that would just shuffle thr.
shared buffers and not touch disk at all?
What would be the use of that? You couldn't predict *anything* about
the coverage. Maybe you find all the free space in a particular table,
but most likely you don't.
In any case an I/O-free vacuum is impossible since once you have decided
to recycle a particular tuple, you don't have any option about removing
the corresponding index entries first. So unless both the table and all
its indexes are in RAM, you will be incurring I/O.
regards, tom lane
On Fri, 2003-10-17 at 09:34, Shridhar Daithankar wrote:
I am slightly confused here. IIRC pg_autovacuum never did a vacuum full.
Correct.
At the
most it does vacuum /vacuum analyse,
Incorrect, it either does vacuum analyse, or just analyse
none of which chew disk bandwidth.
Incorrect, vacuum can have lots of disk I/O, analyze has considerably
less, but still some.
And if
pg_autovacuum is running along with postmaster all the time, with aggressive
polling like 5 sec, the database should not accumulate any dead tuples
True, however, I think such aggressive polling will be a net loss in
efficiency.
nor it
would suffer xid wraparound as there are vacuum happening constantly.
Wrong, pg_autovacuum typically just does vacuum [table name], which does
not effect the xid wraparound issue, one has to issue a vacuum against
an entire database to effect that.
What's left in above scenario? As long as all the requirements for pg_autovacuum
are met, namely setting it up, setting it up aggressively and tuning
postgresql.conf correctly, vacuum and related problems should be a thing in
past, at least as far as 7.4 and onwards is considered.
Well it still remains to be seen if the client side implementation of
pg_autovacuum is sufficient. Also, we will see if index bloat is
handled (less an autovac issue, but semi-related). Ideally, autovac
should make better decisions based on FSM and perhaps even the RSM (is
that what it was called?) that people have talked about setting up.
With all that said, hopefully pg_autovacuum proves to be a successful
experiment, and if so, then it needs to be integrated into core somehow.
Matthew
On Fri, Oct 17, 2003 at 07:55:44PM +0530, Shridhar Daithankar wrote:
OK. So here is what I understand. I have a table which contains 100 rows
which appeated there due to some insert operation. Then I vacuum it. And
sit there for internity for rest of the database to approach the
singularity(the xid wraparound..:-) Nice term, isn't it?).So this static table is vulnerable to xid wraparound? I doubt.
Did I miss something?
You are missing the part when the XID that was formerly a "committed
transaction" becomes an uncommitted transaction when the wraparound
occurs... so the tuples will have creation XID by an uncommitted
transaction, and current transactions will not see them. Voila, your
table is empty.
The trick to keep in mind is that the XID comparison functions use
"modulo" operations, _but_ there are special "frozen" XIDs that are
always "committed" -- that's why a VACUUM FREEZE would relieve the table
forever from this problem.
(At least this is how I understand it -- I could be totally wrong here)
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Los dioses no protegen a los insensatos. �stos reciben protecci�n de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)
