Re: [GENERAL] Vacuum issue
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 signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Vacuum issue
"E.J. Moloney" <[EMAIL PROTECTED]> writes: > I have a database with a table that adds 3 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Vacuum issue
I have a database with a table that adds 3 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Vacuum issue
On Thu, Jul 05, 2007 at 01:17:13PM +0100, E.J. Moloney wrote: > I have a database with a table that adds 3 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Vacuum issue
I have a database with a table that adds 3 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings