Обсуждение: autovacuum

On Wed, 2006-06-14 at 11:52, Sriram Dandapani wrote:
> Hi
>
>
>
> I have a table into which data flows at a high rate say 2 million rows
> per hour. Every 15 minutes, these rows are processed and deleted.
>
> At any point in time, the row count for the table hovers around 500K
> (due to the deletes).
>
>
>
> I have set max_fsm_pages to about 2000000. I still see the
> pg_total_relation_size constantly reporting an increasing size.
>
>
>
> How do I know if autovacuum is reclaiming space or is my max_fsm
> setting plain wrong. (I understand that autovacuum marks tuples for
> reuse, but shouldn’t pg_total_relation_size take that into account)

I wouldn't really rely on just autovacuum to do this.
But, if you must, adjust the threshholds to have it trigger more easily.

Me personally, if I'm running a script that deletes 500,000 rows, I put
a vacuum at the end of it.  That way you KNOW the vacuum got run.

> How do I know if autovacuum is reclaiming space or is my max_fsm setting
> plain wrong. (I understand that autovacuum marks tuples for reuse, but
> shouldn't pg_total_relation_size take that into account)
>
You can find out if your FSM settings are big enough by running vacuumdb
-av (and yes, it needs to be -a) - obviously you might choose not to run
this when the database is busy doing something else.

Capture the output - at the very end it will tell you whether or not
your FSM settings are adequate to map the reusable space in the database.

You might also want to read the documentation on autovacuum's logging
output options - this may tell you what you want to know - prior to 8.1
autovacuum was documented in (on my system)
/usr/doc/postgres-x.x.x/contrib/README.pg_autovacuum - post 8.1 it's
part of the main distribution and should be documented in the main PG docs.

Hope this helps,
Robin