On Dec 7, 2007, at 10:44 AM, Guillaume Cottenceau wrote:
Erik Jones <erik 'at' myemma.com> writes:
vacuum_cost_delay/vacuum_cost_limit (deactivated) 20/200
40/200 100/1000 150/1000 200/1000 300/1000
VACUUM ANALYZE time 54 s 112 s 188
s 109 s 152 s 190 s 274 s
SELECT time 50 s 28 s 26
s 24 s 22 s 20 s 19 s
While you do mention that the table you're running your select on is
too big to fit in the shared_buffers, the drop in time between the
first run and the rest most likely still reflects the fact that when
These figures don't show a difference between first run and
subsequent runs. For each parameter tuning, a couple of runs are
fired after database restart, and once the value is approximately
constant, it's picked and put in this table. The "deactivated"
shows the (stable, from subsequent runs) figure when vacuum delay
is disabled (vacuum_cost_delay parameter quoted), not the first
run, if that's where the confusion came from.
It was.
Is it on pgsql-hackers? I haven't found much stuff in
pgsql-performance while looking for "vacuum_cost_delay tuning".
would be good have multiple autovacuum workers that could be tuned
for different times or workloads. I know Alvarro was going to work
Sounds interesting.
Run the initial archive search against pgsql-general over the last
year for a thread called 'Autovacuum Improvements'
I'm wondering if it would not be possible to dynamically ignore
(or lower, if it makes more sense?) the Cost-Based Vacuum Delay
during vacuum full, if a configurable amount of queries are
waiting for the lock?
(please save yourself from answering "you should never run VACUUM
FULL if you're vacuuming enough" - as long as VACUUM FULL is
available in PostgreSQL, there's no reason to not make it as
practically usable as possible, albeit with low dev priority)
Ok, I won't say what you said not to say. But, I will say that I
don't agree with you're conjecture that VACUUM FULL should be made
more lightweight, it's like using dynamite to knock a whole in a wall
for a window.
Thanks for opening a new kind of trol^Hargument against VACUUM
FULL, that one's more fresh (at least to me, who doesn't follow
the list too close anyway).
Just for the record, I inherited a poorly (actually, "not" would
be more appropriate) tuned database, containing more than 90% of
dead tuples on large tables, and I witnessed quite some
performance improvement while I could fix that.
If you really want the VACUUM FULL effect without having to deal with
vacuum_cost_delay, use CLUSTER. It also re-writes the table and,
AFAIK, is not subject to any of the vacuum related configuration
parameters. I'd argue that if you really need VACUUM FULL, you may
as well use CLUSTER to get a good ordering of the re-written table.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
