Re: High Load on Postgres 7.4.16 Server

We run multiple postmasters because we can shutdown one postmaster/database
without affecting the other postmasters/databases. Each database is a
division in our company. If we had everything under one postmaster if
something happened to the one the whole company would be down.

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Cramer
Sent: Thursday, April 05, 2007 4:01 PM
To: John Allgood
Cc: 'Jeff Frost'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] High Load on Postgres 7.4.16 Server


On 5-Apr-07, at 3:33 PM, John Allgood wrote:

> The hard thing about running multiple postmasters is that you have
> to tune
> each one separate. Most of the databases I have limited the max-
> connections
> to 30-50 depending on the database. What would reasonable values for
> effective_cache_size and random_page_cost. I think I have these
> default.
> Also what about kernel buffers on RHEL4.
>
random_page_cost should be left alone

Why do you run multiple postmasters ? I don't think this is not the
most efficient way to utilize your hardware.

Dave

> Thanks
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jeff
> Frost
> Sent: Thursday, April 05, 2007 3:24 PM
> To: John Allgood
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] High Load on Postgres 7.4.16 Server
>
> On Thu, 5 Apr 2007, John Allgood wrote:
>
>> Hello All
>>
>>     I sent this message to the admin list and it never got through so I
>> am trying the performance list.
>>     We moved our application to a new machine last night. It is a Dell
>> PowerEdge 6950 2X Dual Core. AMD Opteron 8214 2.2Ghz. 8GB Memory. The
>> machine is running Redhat AS 4 Upd 4 and Redhat Cluster Suite. The
>> SAN is
> an
>> EMC SAS connected via fibre. We are using Postgres 7.4.16. We have
> recently
>> had some major hardware issues and replaced the hardware with
>> brand new
> Dell
>> equipment. We expected a major performance increase over the previous
> being
>> the old equipment was nearly three years old
>>     I will try and explain how things are configured. We have 10
>> separate postmasters running 5 on each node. Each of the
>> postmasters is a
>> single instance of each database. Each database is separated by
>> division
> and
>> also we have them separate so we can restart an postmaster with
>> needing to
>> restart all databases My largest database is about 7 GB. And the
>> others
> run
>> anywhere from 100MB - 1.8GB.
>>     The other configuration was RHEL3 and Postgres 7.4.13 and Redhat
>> Cluster Suite. The application seemed to run much faster on the older
>> equipment.
>>     My thoughts on the issues are that I could be something with the OS
>> tuning. Here is what my kernel.shmmax, kernel.shmall = 1073741824. Is
> there
>> something else that I could tune in the OS. My max_connections=35 and
> shared
>> buffers=8192 for my largest database.
>
> John,
>
> Was the SAN connected to the previous machine or is it also a new
> addition
> with the Dell hardware?  We had a fairly recent post regarding a
> similar
> upgrade in which the SAN ended up being the problem, so the first
> thing I
> would do is test the SAN with bonnie-++ and/or move your
> application to use
> a
> local disk and test again.  With 8GB of RAM, I'd probably set the
> shared_buffers to at least 50000...If I remember correctly, this
> was the
> most
> you could set it to on 7.4.x and continue benefitting from it.  I'd
> strongly
>
> encourage you to upgrade to at least 8.1.8 (and possibly 8.2.3) if
> you can,
> as
> it has much better shared memory management.  You might also want
> to double
> check your effective_cache_size and random_page_cost to see if they
> are set
> to
> reasonable values.  Did you just copy the old postgresql.conf over?
>
> This is the beginning of the thread I mentioned above:
>
> http://archives.postgresql.org/pgsql-performance/2007-03/msg00104.php
>
> --
> Jeff Frost, Owner     <jeff@frostconsultingllc.com>
> Frost Consulting, LLC     http://www.frostconsultingllc.com/
> Phone: 650-780-7908    FAX: 650-649-1954
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 268.18.26/746 - Release Date:
> 4/4/2007
> 1:09 PM
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.26/746 - Release Date: 4/4/2007
1:09 PM