Skip site navigation (1) Skip section navigation (2)

Re: Help with performance problems

From: "Chris Hoover" <revoohc(at)sermonaudio(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Help with performance problems
Date: 2004-04-23 17:53:17
Message-ID: 200404231353.17234.revoohc@sermonaudio.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

Responses

pgsql-performance by date

Next:From: Chris HooverDate: 2004-04-23 18:01:29
Subject: Re: Help with performance problems
Previous:From: scott.marloweDate: 2004-04-23 17:21:32
Subject: Re: Help with performance problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group