Sorry for the confusion here. I can't run any sort of vacuum durin the day
due to performance hits. However, I have run vacuums at night. Several
nights a week I run a vacuumdb -f -z on all of the clusters. I can take
serveral hours to complete, but it does complete.
During the day, I have tried to run a vacuumdb -v and a vacuumdb -z -v during
the day since I read it is supposed to help performance, but as I said, it
causes to much of a stress on the system.
I did change the vacuumdb script to do set the vacuum_mem to 512 when
vacuuming to try and help the situation (from the script: ${PATHNAME}psql
$PSQLOPT $ECHOOPT -c "SET vacuum_mem=524288;SET autocommit TO 'on';VACUUM
$full $verbose $analyze $table" -d $db ), and I reset it to 8192 at the end.
Anyway, thank you for the ideas so far, and any additional will be greatly
appreciated.
Chris
On Friday 23 April 2004 13:44, Kevin Barnard wrote:
> Chris Hoover wrote:
> >I know the numbers look ok, but we are definetly suffering. Also, if I
> > try to run any sort of vacuum or other db activity during normal business
> > hours, load goes through the roof. I have seen loads of over 10 when
> > trying to vacuum the larger cluster and would have to kill the vacuums
> > due to complaints.
>
> This is your problem then. You have to regularly vacuum the DB. You
> might want to dump and reload or schedule a vacuum full. If you don't
> it doesn't matter what you do you will never get decent performance.
> Make sure you vacuum as a superuser this way you get system tables as well.
>
> Killing a vacuum is bad it tends to make the situation worse. If you
> need to vaccuum one table at a time.
>
> >I think this is probably related to the hardware configuration, but I want
> > to make sure that there are no changes I could make configuration wise to
> > the db that might lighten the problem.
> >
> >I'm especially want to make sure that I have the memory parameters set to
> > good numbers for my db's so that I can minimize thrashing between the
> > postgres memory pools and the hard drive. I am thinking that this may be
> > a big issue here?
>
> Get the vacuum done and don't worry about the hardware or the settings
> until afterwords.