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 E7D47D1BA98 for ; Fri, 23 Apr 2004 15:01:34 -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 15115-06 for ; Fri, 23 Apr 2004 15:01:33 -0300 (ADT) Received: from mail.astudios.com (unknown [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 6D806D1BAD5 for ; Fri, 23 Apr 2004 15:01:30 -0300 (ADT) thread-index: AcQpXXtoDiNBWFjzQYyCZJaeKYDeqg== 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 14:04:54 -0400 Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132 From: "Chris Hoover" To: "scott.marlowe" Subject: Re: Help with performance problems Date: Fri, 23 Apr 2004 14:01:29 -0400 User-Agent: KMail/1.6.1 Cc: References: In-Reply-To: MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-ID: <200404231401.29144.revoohc@sermonaudio.com> X-OriginalArrivalTime: 23 Apr 2004 18:04:55.0031 (UTC) FILETIME=[7B493870:01C4295D] 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/410 X-Sequence-Number: 6710 On Friday 23 April 2004 13:21, scott.marlowe wrote: > On Fri, 23 Apr 2004, Chris Hoover wrote: > > DB's on Powervaults 220S using raid 5 (over 6 disks) > > What controller is this, the adaptec? We've found it to be slower than > the LSI megaraid based controller, but YMMV. > We are using the perc3/di controller. Believe it is using the megaraid=20 driver. > > Running RH ES 2.1 > > Are you running the latest kernel for ES 2.1? Early 2.4 kernels are > pretty pokey and have some odd behaviour under load that later 2.4 > kernels seemed to fix. > I'm not sure we are at the latest and greatest for 2.1, but I am trying to = get=20 there. Management won't let me do the upgrade w/o first testing/proving it= =20 will not cause any more issues. Due to all of the current issues, and the= =20 criticality of these systems to our bottom line, they are being very carefu= l=20 with any change that may impact our users further. We are waiting on our datacenter to plug in our test server and powervault = so=20 that we can test the upgrades the the latest RH 2.1 kernel. > > Here is the postgresql.conf from the server with the 11GB db: > > > > max_connections =3D 64 > > shared_buffers =3D 32768 # 256MB=3D32768(buffs)*8192(bytes/buff) > > max_fsm_relations =3D 1000 # min 10, fsm is free space map, ~40 bytes > > max_fsm_pages =3D 10000 # min 1000, fsm is free space map, ~6 bytes > > IF you're doing lots of updates and such, you might want these higher. > Have you vacuumed full the databases since taking over? > > > sort_mem =3D 4096 # 256MB=3D4096(bytes/proc)*64(procs or conns) > > Sorry, that's wrong. sort_mem is measure in kbytes. i.e. 8192 means 8 > megs sort_mem. Try setting it a bit higher (you've got LOTS of ram in > these boxes) to something like 16 or 32 meg. > > > checkpoint_segments =3D 16 # in logfile segments, min 1, 16MB each > > checkpoint_timeout =3D 30 # range 30-3600, in seconds > > effective_cache_size =3D 131072 # typically 8KB each > > This still looks low. On one machine you're showing kernel cache of about > .7 gig, on the other it's 6 gig. 6 gigs of kernel cache would be a > setting of 800000. It's more of a nudge factor than an exact science, so > don't worry too much. I believe changing this requires a restart of the cluster (correct?). If s= o,=20 I'll try bumping up the effective_cache_size over the weekend. Also, will all of the memory available to these machines, should I be runni= ng=20 with larger shared_buffers? It seems like 256M is a bit small. > > If you've got fast I/O look at lowering random page cost to something > between 1 and 2. We use 1.3 to 1.4 on most of our machines with fast > drives under them. > > I'd use vmstat to see if you're I/O bound. > If we end up being I/O bound, should the random page cost be set higher? > also, look for index bloat. Before 7.4 it was a serious problem. With > 7.4 regular vacuuming should reclaim most lost space, but there are corner > cases where you still might need to re-index. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org Thanks for the help, Chris