Re: Vacuum issue

"E.J. Moloney" <emoloney(at)valetechnology(dot)com> writes:

> I have a database with a table that adds 30000 records a day, I am delete
> records older than 2 days.
> I am vacuuming it once a day , I am having an issue that the disk usage is
> continually rising. i.e. the space been flagged as free by the vacuum process
> isn't being reused.

You delete records older than 2 days in one big batch?

You have two options, either run vacuum more often or increase the fsm
settings. The former is probably a waste of cpu if you're really not deleting
any more records during the day so the latter makes more sense.

The problem is that vacuum is engineered assuming there are a constant stream
of deletes and it will need to be run frequently. So it only needs to find
enough space to cover inserts until the next vacuum and doesn't expect to have
to remember all the space from a large batch delete. The further apart the
vacuums the more space it needs to be able to remember.

If you run "vacuum verbose" it should print out some information that would be
helpful setting the fsm parameters. Actually I think what I would suggest is
that you either CLUSTER or VACUUM FULL the table. Then the next day when you
do the delete and your regular vacuum make it a "VACUUM VERBOSE".

The output of vacuum verbose can be hard to interpret, if you want help
adjusting the fsm settings send it here.

> I am running Postgre 8.4 on a Susse 10.1

No you're not. Postgres 8.3 won't even be released for a few more months!

Perhaps that's 8.2.4?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com