On Tue, 1 Aug 2006 08:02:59 -0700 (PDT) Sundar Narayanaswamy <sundar007@xxxxxxxxx> 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.
