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

On Thu, Jul 05, 2007 at 01:17:13PM +0100, E.J. Moloney wrote:
> 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.

Are you sure this table is responsible for the disk space increase?
Might the growth be elsewhere?  Does this table receive a lot of
updates?  Have you done a database-wide VACUUM VERBOSE and examined
the free space map info at the end to see if you need to adjust
your FSM settings?

Have you checked whether the table's indexes are what's growing?
Certain usage patterns can cause indexes to grow even if they're
being vacuumed regularly; you might need to reindex periodically.

> Please find below a copy of the cron command being used.

Have you checked the vacuumdb output to confirm that it's running
successfully?

>      su - $PGUSER -c "$COMMAND --analyze -v " >>$PGLOG 2>&1

This command is vacuuming only one database, probably "postgres"
(but check the output to be sure).  Is that where your tables are?

> I am running Postgre 8.4 on a Susse 10.1

PostgreSQL (not "Postgre") 8.4 doesn't exist; do you mean 8.2.4?

--
Michael Fuhr