Hi,

I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
postgres 7.4.3. This has been recompiled on the server for 64 stored
procedure parameters, (I assume this makes postgres 64 bit but are not
sure). When the server gets under load from database connections
executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
limit at about 30-35% usage with no iowait reported. If I run a simple
select at this time it takes 5 seconds, the same query runs in 300
millis when the server is not under load so it seems that the database
is not performing well even though there is plenty of spare CPU. There
does not appear to be large amounts of disk IO and my database is about
5.5G so this should fit comfortably in RAM.

changes to postgresql.sql:

max_connections = 500
shared_buffers = 96000
sort_mem = 10240
effective_cache_size = 1000000

Does anyone have any ideas what my bottle neck might be and what I can
do about it?

Thanks for any help.

Matthew.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
> postgres 7.4.3. This has been recompiled on the server for 64 stored
> procedure parameters, (I assume this makes postgres 64 bit but are not
> sure). When the server gets under load from database connections
> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
> limit at about 30-35% usage with no iowait reported. If I run a simple
> select at this time it takes 5 seconds, the same query runs in 300
> millis when the server is not under load so it seems that the database
> is not performing well even though there is plenty of spare CPU. There
> does not appear to be large amounts of disk IO and my database is about
> 5.5G so this should fit comfortably in RAM.
>
> changes to postgresql.sql:
>
> max_connections = 500
> shared_buffers = 96000
> sort_mem = 10240
> effective_cache_size = 1000000
>
> Does anyone have any ideas what my bottle neck might be and what I can
> do about it?

You might want to lower shared_buffers to a lower value. Mine is set
at 32768. Is your db performing complex sort? Remember that this value
is per connection. Maby 1024. effective_cache_size should also be
lowered to something like 32768. As far as I understand shared_buffers
and effective_cache_size have to be altered "in reverse", ie. when
lowering one the other can be raised.

HTH.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

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

http://archives.postgresql.org

> > Does anyone have any ideas what my bottle neck might be and what I can do
> > about it?
>
> Your bottleneck is that you are using a very old version of PostgreSQL. Try
> 8.2 or (if you can) the 8.3 beta series -- it scales a _lot_ better in this
> kind of situation.

You won't know until you've seen what queries are performed. Changing
db-parameters is a short-term solution, upgrading to a newer version
does require some planning.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

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

Hi Matthew,

I know exactly what you experience.
We had a 4-way DC Opteron and Pg 7.4 too.
You should monitor context switches.


First suggest upgrade to 8.2.5 because the scale up is much better with 8.2.

You need to limit the number of concurrent queries to less than 8 (8
cores) if you need to stay with Pg 7.4.

The memory setting is looking good to me. I would increase sort_mem and
effective_cache_size, but this would solve your problem.

Best regards
Sven.



Matthew Lunnon schrieb:
> Hi,
>
> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
> postgres 7.4.3. This has been recompiled on the server for 64 stored
> procedure parameters, (I assume this makes postgres 64 bit but are not
> sure). When the server gets under load from database connections
> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
> limit at about 30-35% usage with no iowait reported. If I run a simple
> select at this time it takes 5 seconds, the same query runs in 300
> millis when the server is not under load so it seems that the database
> is not performing well even though there is plenty of spare CPU. There
> does not appear to be large amounts of disk IO and my database is about
> 5.5G so this should fit comfortably in RAM.
>
> changes to postgresql.sql:
>
> max_connections = 500
> shared_buffers = 96000
> sort_mem = 10240
> effective_cache_size = 1000000
>
> Does anyone have any ideas what my bottle neck might be and what I can
> do about it?
>
> Thanks for any help.
>
> Matthew.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Sven Geisler <sgeisler@aeccom.com> Tel +49.30.921017.81 Fax .50
Senior Developer, AEC/communications GmbH & Co. KG Berlin, Germany

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

Limiting the queries was our initial thought but we then hit a problem
with connection pooling which didn't implement a fifo algorithm. Looks
like I'll have to look deeper into the connection pooling.

So you think the problem might be context switching on the server, I'll
take a closer look at the this

Thanks

Matthew

Sven Geisler wrote:
> Hi Matthew,
>
> I know exactly what you experience.
> We had a 4-way DC Opteron and Pg 7.4 too.
> You should monitor context switches.
>
>
> First suggest upgrade to 8.2.5 because the scale up is much better with 8.2.
>
> You need to limit the number of concurrent queries to less than 8 (8
> cores) if you need to stay with Pg 7.4.
>
> The memory setting is looking good to me. I would increase sort_mem and
> effective_cache_size, but this would solve your problem.
>
> Best regards
> Sven.
>
>
>
> Matthew Lunnon schrieb:
>
>> Hi,
>>
>> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
>> postgres 7.4.3. This has been recompiled on the server for 64 stored
>> procedure parameters, (I assume this makes postgres 64 bit but are not
>> sure). When the server gets under load from database connections
>> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
>> limit at about 30-35% usage with no iowait reported. If I run a simple
>> select at this time it takes 5 seconds, the same query runs in 300
>> millis when the server is not under load so it seems that the database
>> is not performing well even though there is plenty of spare CPU. There
>> does not appear to be large amounts of disk IO and my database is about
>> 5.5G so this should fit comfortably in RAM.
>>
>> changes to postgresql.sql:
>>
>> max_connections = 500
>> shared_buffers = 96000
>> sort_mem = 10240
>> effective_cache_size = 1000000
>>
>> Does anyone have any ideas what my bottle neck might be and what I can
>> do about it?
>>
>> Thanks for any help.
>>
>> Matthew.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
>

--
Matthew Lunnon
Technical Consultant
RWA Ltd.

mlunnon@rwa-net.co.uk
Tel: +44 (0)29 2081 5056
www.rwa-net.co.uk
--

Ah I was afraid of that. Maybe I'll have to come out of the dark ages.

Matthew

Steinar H. Gunderson wrote:
> On Wed, Dec 12, 2007 at 10:16:43AM +0000, Matthew Lunnon wrote:
>
>> Does anyone have any ideas what my bottle neck might be and what I can do
>> about it?
>>
>
> Your bottleneck is that you are using a very old version of PostgreSQL. Try
> 8.2 or (if you can) the 8.3 beta series -- it scales a _lot_ better in this
> kind of situation.
>
> /* Steinar */
>

--
Matthew Lunnon
Technical Consultant
RWA Ltd.

mlunnon@rwa-net.co.uk
Tel: +44 (0)29 2081 5056
www.rwa-net.co.uk
--

Thanks for the information Claus, Why would reducing the effective
cache size help the processor usage? It seems that there is plenty of
resources on the box although I can see that 10MB of sort space could
mount up if we had 500 connections but at the moment we do not have
anything like that number.

Thanks
Matthew.

Claus Guttesen wrote:
>> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
>> postgres 7.4.3. This has been recompiled on the server for 64 stored
>> procedure parameters, (I assume this makes postgres 64 bit but are not
>> sure). When the server gets under load from database connections
>> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
>> limit at about 30-35% usage with no iowait reported. If I run a simple
>> select at this time it takes 5 seconds, the same query runs in 300
>> millis when the server is not under load so it seems that the database
>> is not performing well even though there is plenty of spare CPU. There
>> does not appear to be large amounts of disk IO and my database is about
>> 5.5G so this should fit comfortably in RAM.
>>
>> changes to postgresql.sql:
>>
>> max_connections = 500
>> shared_buffers = 96000
>> sort_mem = 10240
>> effective_cache_size = 1000000
>>
>> Does anyone have any ideas what my bottle neck might be and what I can
>> do about it?
>>
>
> You might want to lower shared_buffers to a lower value. Mine is set
> at 32768. Is your db performing complex sort? Remember that this value
> is per connection. Maby 1024. effective_cache_size should also be
> lowered to something like 32768. As far as I understand shared_buffers
> and effective_cache_size have to be altered "in reverse", ie. when
> lowering one the other can be raised.
>
> HTH.
>
>

--
Matthew Lunnon
Technical Consultant
RWA Ltd.

mlunnon@rwa-net.co.uk
Tel: +44 (0)29 2081 5056
www.rwa-net.co.uk
--

> Thanks for the information Claus, Why would reducing the effective cache
> size help the processor usage? It seems that there is plenty of resources
> on the box although I can see that 10MB of sort space could mount up if we
> had 500 connections but at the moment we do not have anything like that
> number.

There is a discussion at
http://archives.postgresql.org/pgsql...6/msg00477.php
which can give a clearer picture. But in general rasing values can be
counterproductive.

If you know that you won't need more than 250 connections that would
be a reasonable value. You may wan't to read
http://www.varlena.com/GeneralBits/T...ed_conf_e.html as
well. This has some rules of thumb on the settings for 7.4.x.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wed, 12 Dec 2007, Matthew Lunnon wrote:

> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
> postgres 7.4.3.
> shared_buffers = 96000

As you've already been told repeatedly 7.4 is a release from long before
optimizations to work well on a multi-core server like this. I'll only
add that because of those problems, larger values of shared_buffers were
sometimes counter-productive with these old versions. You should try
reducing that to the 10,000-50000 range and see if things improve; that's
the general range that was effective with 7.4. Continue to set
effective_cache_size to a large value so that the optimizer knows how much
RAM is really available.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hi Matthew,

The context switching isn't the issue. This is an indicator which is
useful to identify your problem.

What kind of application do you running? Can you limit the database clients?

We have a web application based on apache running. We have a limit
number of apache processes which are able to connect the database.
We use that to reduce the number of concurrent queries.
The apache does the rest for us - the apache does queue incoming http
request if all workers are busy. The configuration helps us to solve the
performance issue with to much concurrent queries.

I assume that you already checked you application and each sql query is
necessary and tuned as best as you can.

Regards
Sven.

Matthew Lunnon schrieb:
> Limiting the queries was our initial thought but we then hit a problem
> with connection pooling which didn't implement a fifo algorithm. Looks
> like I'll have to look deeper into the connection pooling.
>
> So you think the problem might be context switching on the server, I'll
> take a closer look at the this
>
> Thanks
>
> Matthew
>
> Sven Geisler wrote:
>> Hi Matthew,
>>
>> I know exactly what you experience.
>> We had a 4-way DC Opteron and Pg 7.4 too.
>> You should monitor context switches.
>>
>>
>> First suggest upgrade to 8.2.5 because the scale up is much better with 8.2.
>>
>> You need to limit the number of concurrent queries to less than 8 (8
>> cores) if you need to stay with Pg 7.4.
>>
>> The memory setting is looking good to me. I would increase sort_mem and
>> effective_cache_size, but this would solve your problem.
>>
>> Best regards
>> Sven.
>>
>>
>>
>> Matthew Lunnon schrieb:
>>
>>> Hi,
>>>
>>> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
>>> postgres 7.4.3. This has been recompiled on the server for 64 stored
>>> procedure parameters, (I assume this makes postgres 64 bit but are not
>>> sure). When the server gets under load from database connections
>>> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
>>> limit at about 30-35% usage with no iowait reported. If I run a simple
>>> select at this time it takes 5 seconds, the same query runs in 300
>>> millis when the server is not under load so it seems that the database
>>> is not performing well even though there is plenty of spare CPU. There
>>> does not appear to be large amounts of disk IO and my database is about
>>> 5.5G so this should fit comfortably in RAM.
>>>
>>> changes to postgresql.sql:
>>>
>>> max_connections = 500
>>> shared_buffers = 96000
>>> sort_mem = 10240
>>> effective_cache_size = 1000000
>>>
>>> Does anyone have any ideas what my bottle neck might be and what I can
>>> do about it?
>>>
>>> Thanks for any help.
>>>
>>> Matthew.
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>>
>>
>
> --
> Matthew Lunnon
> Technical Consultant
> RWA Ltd.
>
> mlunnon@rwa-net.co.uk
> Tel: +44 (0)29 2081 5056
> www.rwa-net.co.uk
> --
>

--
Sven Geisler <sgeisler@aeccom.com> Tel +49.30.921017.81 Fax .50
Senior Developer, AEC/communications GmbH & Co. KG Berlin, Germany

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq