Vacuum improvement

That a good idea. That way, if your database slows during specific
windows in time, you can vacuum larger sizes, etc. Seemingly would help
you better manage your vacuuming against system loading.

Greg

Gavin Sherry <swm@linuxworld.com.au> writes:

have a parameter which specified how much of the table is vacuumed. That
is, you could specify:
VACUUM FULL test 20 precent;

Erm ... but which 20 percent? In other words, how could you arrange for
repeated applications of such a command to cover the whole table, and
not just retrace an already-cleaned-out portion?

I don't object to the idea in principle, but I am not sure how to
implement it in practice.

regards, tom lane

On Tue, Oct 15, 2002 at 11:52:35PM -0400, Tom Lane wrote:

Gavin Sherry <swm@linuxworld.com.au> writes:

have a parameter which specified how much of the table is vacuumed. That
is, you could specify:
VACUUM FULL test 20 precent;

Erm ... but which 20 percent? In other words, how could you arrange for
repeated applications of such a command to cover the whole table, and
not just retrace an already-cleaned-out portion?

Maybe each relation block can have a last-vacuumed timestamp? Somewhere
in the table there would have to be a linked list of least-recently
vacuumed blocks so the vacuum cleaner does not have to read every
block to know which one to clean.

Or maybe some system table can provide information about activity in
each block since last vacuum. This forces the use of the stat
collector...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sabio habla porque tiene algo que decir;
el tonto, porque tiene que decir algo" (Platon).

On Wed, 2002-10-16 at 05:22, Gavin Sherry wrote:

Hi all,

I'm thinking that there is an improvement to vacuum which could be made
for 7.4. VACUUM FULLing large, heavily updated tables is a pain. There's
very little an application can do to minimise dead-tuples, particularly if
the table is randomly updated. Wouldn't it be beneficial if VACUUM could
have a parameter which specified how much of the table is vacuumed. That
is, you could specify:

VACUUM FULL test 20 precent;

What about

VACUUM FULL test WORK 5 SLEEP 50;

meaning to VACUUM FULL the whole table, but to work in small chunks and
relaese all locks and let others access the tables between these ?

You could even fire up a separate thread to

VACUUM [FULL] test WORK 5 SLEEP 50 CONTINUOUS;

To keep vacuuming a heavily updated table.

------------------
Hannu

On 16 Oct 2002, Hannu Krosing wrote:

On Wed, 2002-10-16 at 05:22, Gavin Sherry wrote:

Hi all,

I'm thinking that there is an improvement to vacuum which could be made
for 7.4. VACUUM FULLing large, heavily updated tables is a pain. There's
very little an application can do to minimise dead-tuples, particularly if
the table is randomly updated. Wouldn't it be beneficial if VACUUM could
have a parameter which specified how much of the table is vacuumed. That
is, you could specify:

VACUUM FULL test 20 precent;

What about

VACUUM FULL test WORK 5 SLEEP 50;

meaning to VACUUM FULL the whole table, but to work in small chunks and
relaese all locks and let others access the tables between these ?

Great idea. I think this could work as a complement to the idea I had. To
answer Tom's question, how would we know what we've vacuumed, we could
store the range of tids we've vacuumed in pg_class. Or, we could store the
block offset of where we left off vacuuming before and using stats, run
for another X% of the heap. Is this possible?

Gavin

Hannu Krosing <hannu@tm.ee> writes:

meaning to VACUUM FULL the whole table, but to work in small chunks and
relaese all locks and let others access the tables between these ?

AFAICS this is impossible for VACUUM FULL. You can't let other accesses
in and then resume processing, because that invalidates all the state
you have about where to put moved tuples.

But the whole point of developing non-FULL vacuuming was to make
something that could be run concurrently with other stuff. I fail
to see the point of insisting that frequent vacuums be FULL.

regards, tom lane

On Wed, 2002-10-16 at 02:29, Gavin Sherry wrote:

On 16 Oct 2002, Hannu Krosing wrote:

On Wed, 2002-10-16 at 05:22, Gavin Sherry wrote:

Hi all,

I'm thinking that there is an improvement to vacuum which could be made
for 7.4. VACUUM FULLing large, heavily updated tables is a pain. There's
very little an application can do to minimise dead-tuples, particularly if
the table is randomly updated. Wouldn't it be beneficial if VACUUM could
have a parameter which specified how much of the table is vacuumed. That
is, you could specify:

VACUUM FULL test 20 precent;

What about

VACUUM FULL test WORK 5 SLEEP 50;

meaning to VACUUM FULL the whole table, but to work in small chunks and
relaese all locks and let others access the tables between these ?

Great idea. I think this could work as a complement to the idea I had. To
answer Tom's question, how would we know what we've vacuumed, we could
store the range of tids we've vacuumed in pg_class. Or, we could store the
block offset of where we left off vacuuming before and using stats, run
for another X% of the heap. Is this possible?

Why couldn't you start your % from the first rotten/dead tuple? Just
reading through trying to find the first tuple to start counting from
wouldn't hold locks would it? That keeps you from having to track stats
and ensures that X% of the tuples will be vacuumed.

Greg

Vacuum full locks the whole table currently. I was thinking if you used a
similar to a hard drive defragment that only 2 rows would need to be locked
at a time. When you're done vacuum/defragmenting you shorten the file to
discard the dead tuples that are located after your useful data. There might
be a need to lock the table for a little while at the end but it seems like
you could reduce that time greatly.

I had one table that is heavily updated and it grew to 760 MB even with
regular vacuuming. A vacuum full reduced it to 1.1 MB. I am running 7.2.0
(all my vacuuming is done by superuser).

But doesn't the solution I offer present a possible work around? The
table wouldn't need to be locked (I think) until the first dead tuple
were located. After that, you would only keep the locks until you've
scanned X% of the table and shrunk as needed. The result, I think,
results in incremental vacuuming with shorter duration locks being
held. It's not ideal (locks) but may shorten the duration behind help
by locks.

I'm trying to figure out if the two approaches can't be combined
somehow. That is, a percent with maybe even a max lock duration?

Greg

On Wed, 2002-10-16 at 12:33, David Walker wrote:

Vacuum full locks the whole table currently. I was thinking if you used a
similar to a hard drive defragment that only 2 rows would need to be locked
at a time. When you're done vacuum/defragmenting you shorten the file to
discard the dead tuples that are located after your useful data. There might
be a need to lock the table for a little while at the end but it seems like
you could reduce that time greatly.

I had one table that is heavily updated and it grew to 760 MB even with
regular vacuuming. A vacuum full reduced it to 1.1 MB. I am running 7.2.0
(all my vacuuming is done by superuser).

Not that I'm against the idea, but isn't this just a sign that your just
not vacuuming frequently enough?

Robert Treat