Re: Autovacuum help..

On Tue, 1 Aug 2006 08:02:59 -0700 (PDT)
Sundar Narayanaswamy <sundar007(at)yahoo(dot)com> wrote:

> Hi,
>
> I need your help/suggestions with a problem I am facing related to
> autovacuum.
>
> I am using PostgreSQL 8.1.2 through a JDBC connection. The connection
> is long lived (established when the application starts up and is
> closed only when the application is shutdown).
>
> I have enabled the autovacuum daemon and setup additional parameters
> (for instance, stats_row_level=on) as specified in the PostgreSQL
> documentation.
>
> In the database, I have a table that has a fairly high rate of
> inserts and deletes (~10 rows a second). The table is basically a
> FIFO queue that can have a maximum of 800 entries. As new rows are
> added to the table, oldest rows are deleted such that the table
> always about 800 rows.
>
> The problem I see is that the database size (disk usage) is
> continually increasing even though I have the autovacuum daemon
> enabled and the PostgreSQL log file indicates that the autovacuum
> daemon is processing the databases every minute as expected.
>
> On digging in further, I noticed that the reltuples (in
> pg_catalog.pg_class) for the relevant table keeps increasing
> continually. I also noticed a large number of dead unremovable rows
> when I ran the vacuum analyze command.
>
> After shutting down my application, if I watch the reltuples, it
> continues to stay high until I run the analyze command (analyze
> verbose <table_name>) after which the reltuples drops to about 800
> immediately. The analyze command output also indicates that the dead
> rows have been removed and I notice that the space is reused for
> future inserts when I restart the application.
>
> I am pretty sure that I don't have any transaction that is held open
> forever (the work flow is insert, commit, insert commit etc).
>
> My question is, is autovacuum expected to work in situations like
> this where I have a long lived connection to the database ? After I
> shutdown my application,
>
> why am required to run the "analyze" command before the dead rows are
> removed (autovacuum is not able to remove the dead rows until I run
> the "analyze" command) ?
>
> I'll appreciate your thoughts since I seem to be running out of
> things to try..
>
> Thanks
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: explain analyze is your
> friend

Sundar,

Take a look at the documentation at:
http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM

There are a lot of configuration options that effect the autovacuum
daemon.

John Purser

--
You will pay for your sins. If you have already paid, please disregard
this message.