X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CF359D1B4B6 for ; Fri, 23 Apr 2004 14:53:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15815-01 for ; Fri, 23 Apr 2004 14:53:20 -0300 (ADT) Received: from mail.astudios.com (unknown [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id C24A2D1C4EC for ; Fri, 23 Apr 2004 14:53:18 -0300 (ADT) thread-index: AcQpXFZpF1kw81j3T6iDrHUVhr260A== Content-Class: urn:content-classes:message Received: from [10.4.2.155] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.0); Fri, 23 Apr 2004 13:56:43 -0400 Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132 From: "Chris Hoover" To: Subject: Re: Help with performance problems Date: Fri, 23 Apr 2004 13:53:17 -0400 User-Agent: KMail/1.6.1 References: <200404230931.17850.revoohc@sermonaudio.com> <200404231316.03447.revoohc@sermonaudio.com> <4089561B.8010001@speedfc.com> In-Reply-To: <4089561B.8010001@speedfc.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-ID: <200404231353.17234.revoohc@sermonaudio.com> X-OriginalArrivalTime: 23 Apr 2004 17:56:43.0515 (UTC) FILETIME=[5651D4B0:01C4295C] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/409 X-Sequence-Number: 6709 Sorry for the confusion here. I can't run any sort of vacuum durin the day= =20 due to performance hits. However, I have run vacuums at night. Several=20 nights a week I run a vacuumdb -f -z on all of the clusters. I can take= =20 serveral hours to complete, but it does complete. During the day, I have tried to run a vacuumdb -v and a vacuumdb -z -v duri= ng=20 the day since I read it is supposed to help performance, but as I said, it= =20 causes to much of a stress on the system. I did change the vacuumdb script to do set the vacuum_mem to 512 when=20 vacuuming to try and help the situation (from the script: ${PATHNAME}psql= =20 $PSQLOPT $ECHOOPT -c "SET vacuum_mem=3D524288;SET autocommit TO 'on';VACUUM= =20 $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= =20 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 busine= ss > > 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 wel= l. > > 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 wa= nt > > 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.