pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.




view thread (14+ messages)  latest in thread

Message-ID: <200404231353.17234.revoohc@sermonaudio.com>
Permalink:  ../200404231353.17234.revoohc@sermonaudio.com/
Also on:    postgresql.org/message-id/200404231353.17234.revoohc@sermonaudio.com

 · 

reply

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