Thread: High Load on Postgres 7.4.16 Server

High Load on Postgres 7.4.16 Server

From
"John Allgood"
Date:
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.

Thanks




Re: High Load on Postgres 7.4.16 Server

From
Jeff Frost
Date:
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

Re: High Load on Postgres 7.4.16 Server

From
"C. Bergström"
Date:
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.
>
Update to 8.x.x at least

C.

Re: High Load on Postgres 7.4.16 Server

From
"John Allgood"
Date:
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.

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



Re: High Load on Postgres 7.4.16 Server

From
Jeff Frost
Date:
On Thu, 5 Apr 2007, 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.

Normally, you would look at the output of 'free' and set it to the amount of
cache/8.  For example:

              total       used       free     shared    buffers     cached
Mem:       2055120    2025632      29488          0     505168     368132
-/+ buffers/cache:    1152332     902788
Swap:      2048184       2380    2045804

So, you could take 902788/8 = 112848.  This machine is a bad example as it's
just a workstation, but you get the idea.

That tells the planner it can expect the OS cache to have that much of the DB
cached.  It's kind of an order of magnitude knob, so it doesn't have to be
that precise.

Since you're running multiple postmasters on the same machine (5 per machine
right?), then setting the shared_buffers up to 50000 (400MB) on each
postmaster is probably desirable, though if you have smaller DBs on some of
them, it might only be worth it for the largest one.  I suspect that having
the effective_cache_size set to the output of free on each postmaster is
desirable, but your case likely requires some benchmarking to find the optimal
config.

If you look through the archives, there is a formula for calculating what you
need to set the kernel shared memory parameters.  Otherwise, you can just
start postgres and look at the log as it'll tell you what it tried to
allocate.

Hopefully there's someone with experience running multiple postmasters on the
same machine that can speak to the postgresql.conf knobs more specifically.

I'd still suggest you upgrade to at least 8.1.8.




>
> 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

Re: High Load on Postgres 7.4.16 Server

From
Dave Cramer
Date:
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


Re: High Load on Postgres 7.4.16 Server

From
"John Allgood"
Date:
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



Re: High Load on Postgres 7.4.16 Server

From
Dave Cramer
Date:
The problem with this is that it doesn't leverage shared buffers and
kernel buffers well.

Anyways, my bet is that your SAN isn't performing as you expect on
the new hardware.

Dave
On 5-Apr-07, at 4:13 PM, John Allgood wrote:

> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match


Re: High Load on Postgres 7.4.16 Server

From
Tom Lane
Date:
"John Allgood" <jallgood@the-allgoods.net> writes:
> ... 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.

While I agree with the other comments that you should think about moving
to something newer than 7.4.x, there really shouldn't be any meaningful
performance difference between 7.4.13 and 7.4.16.  I'm guessing some
sort of pedestrian pilot error, like not using similar postgresql.conf
settings or forgetting to ANALYZE the database after reloading it into
the new installation.

            regards, tom lane

Re: High Load on Postgres 7.4.16 Server

From
"John Allgood"
Date:
Hey Guys thanks for the input. I do have some more questions. I am looking a
doing some additional tuning on the system. The first think I am looking at
is the OS tuning. What kernel parameters would I look at setting on a RHEL 4
box? I have set the SHMMAX and SHMALL to 1GB. What other tuning options
should I look into setting? Am I correct to assume to whatever I set the
shared memory too that I can't set the total of all postgres buffers to
larger than the shared memory. I am still trying to learn about properly
tuning an OS and PostgreSQL system correctly. I would be interested in
hearing about what other people on the list have there kernel tuned too.

Best Regards
John Allgood

-----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:27 PM
To: John Allgood
Cc: 'Jeff Frost'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] High Load on Postgres 7.4.16 Server

The problem with this is that it doesn't leverage shared buffers and
kernel buffers well.

Anyways, my bet is that your SAN isn't performing as you expect on
the new hardware.

Dave
On 5-Apr-07, at 4:13 PM, John Allgood wrote:

> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org


--
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