From: Chris Hoover <revoohc@sermonaudio.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with performance problems
Date: Fri, 23 Apr 2004 13:53:17 -0400
Message-ID: <200404231353.17234.revoohc@sermonaudio.com> (raw)
In-Reply-To: <4089561B.8010001@speedfc.com>
References: <200404230931.17850.revoohc@sermonaudio.com>
<200404231316.03447.revoohc@sermonaudio.com>
<4089561B.8010001@speedfc.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: revoohc@sermonaudio.com
Subject: Re: Help with performance problems
In-Reply-To: <200404231353.17234.revoohc@sermonaudio.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox