Thread: Vacuum issue

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.

Please find below a copy of the cron command being used.
0 0 * * * /data/newsam/scripts/samdatabasemaint.sh >/dev/null 2>&1


Relevent section from script

COMMAND="$prefix/bin/vacuumdb"

# What to use to shut down the postmaster

set -e

# Only start if we can find the postmaster.
test -x $DAEMON || exit 0

# Parse command line parameters.
       su - $PGUSER -c "$COMMAND --analyze -v " >>$PGLOG 2>&1

exit 0


I orgionaly had autovacuum active but that encountered the sam issue

I am running Postgre 8.4 on a Susse 10.1

Thanks in advance for any help

E.J. Moloney

"E.J. Moloney" <emoloney@valetechnology.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

Le lundi 09 juillet 2007, Gregory Stark a écrit :0
> The output of vacuum verbose can be hard to interpret, if you want help
> adjusting the fsm settings send it here.

Using pgfouine, one gets easy to read reports:
  http://pgfouine.projects.postgresql.org/vacuum.html
  http://pgfouine.projects.postgresql.org/reports/sample_vacuum.html

Regards,
--
dim