Обсуждение: Limited performance on multi core server
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.
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 */ -- Homepage: http://www.sesse.net/
> 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
> > 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
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
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:
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.ukTel: +44 (0)29 2081 5056www.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:
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.ukTel: +44 (0)29 2081 5056www.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:
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.ukTel: +44 (0)29 2081 5056www.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-performance/2005-06/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/Tidbits/annotated_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
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
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
Hi Matthew, I remember that I also an issue with AMD Opterons before Pg 8.1 There is a specific Opteron behaviour on shared memory locks which adds a extra "penalty" during the execution time for Pg code before 8.1. I can you provide my patch for Pg 8.0 which should be adaptable for Pg 7.4 if you can compile PostgreSQL. But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale up for your concurrent queries will be great. 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
Hi Sven,
yes the patch would be great if you could send it to me, we have already had to compile postgres to up the number of function parameters from 32 to 64.
Meanwhile I will try and persuade my colleagues to consider the upgrade option.
Thanks
Matthew
Sven Geisler wrote:
yes the patch would be great if you could send it to me, we have already had to compile postgres to up the number of function parameters from 32 to 64.
Meanwhile I will try and persuade my colleagues to consider the upgrade option.
Thanks
Matthew
Sven Geisler wrote:
Hi Matthew, I remember that I also an issue with AMD Opterons before Pg 8.1 There is a specific Opteron behaviour on shared memory locks which adds a extra "penalty" during the execution time for Pg code before 8.1. I can you provide my patch for Pg 8.0 which should be adaptable for Pg 7.4 if you can compile PostgreSQL. But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale up for your concurrent queries will be great. 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.ukTel: +44 (0)29 2081 5056www.rwa-net.co.uk --
Hi Sven,
Yes I have done a reasonable amount of query tuning. The application is a web service using an apache/resin combination at the front end, we have thought about using resin threads to limit the number of connections but are worried about backing up connections in apache and getting some overflow here. But some kind of limiting of connections is probably required.
Thanks
Matthew
Sven Geisler wrote:
Yes I have done a reasonable amount of query tuning. The application is a web service using an apache/resin combination at the front end, we have thought about using resin threads to limit the number of connections but are worried about backing up connections in apache and getting some overflow here. But some kind of limiting of connections is probably required.
Thanks
Matthew
Sven Geisler wrote:
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 theperformance 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.ukTel: +44 (0)29 2081 5056www.rwa-net.co.uk --
-- Matthew Lunnon Technical Consultant RWA Ltd. mlunnon@rwa-net.co.ukTel: +44 (0)29 2081 5056www.rwa-net.co.uk --
Hi Matthew,
Please not that is no official patch, but it works with our Opteron
server without any problems. You should run the regression test after
you adapt it for Pg 7.4.
Sven.
Matthew Lunnon schrieb:
> Hi Sven,
>
> yes the patch would be great if you could send it to me, we have already
> had to compile postgres to up the number of function parameters from 32
> to 64.
>
> Meanwhile I will try and persuade my colleagues to consider the upgrade
> option.
>
> Thanks
> Matthew
>
> Sven Geisler wrote:
>> Hi Matthew,
>>
>> I remember that I also an issue with AMD Opterons before Pg 8.1
>> There is a specific Opteron behaviour on shared memory locks which adds
>> a extra "penalty" during the execution time for Pg code before 8.1.
>> I can you provide my patch for Pg 8.0 which should be adaptable for Pg
>> 7.4 if you can compile PostgreSQL.
>>
>> But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale
>> up for your concurrent queries will be great.
>>
>> 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
*** postgresql-8.0.3/src/include/storage/s_lock.h.orig Sun Aug 28 20:41:44 2005
--- postgresql-8.0.3/src/include/storage/s_lock.h Fri Sep 9 14:58:44 2005
***************
*** 120,132 ****
{
register slock_t _res = 1;
! /* Use a non-locking test before asserting the bus lock */
__asm__ __volatile__(
- " cmpb $0,%1 \n"
- " jne 1f \n"
- " lock \n"
" xchgb %0,%1 \n"
- "1: \n"
: "+q"(_res), "+m"(*lock)
:
: "memory", "cc");
--- 120,128 ----
{
register slock_t _res = 1;
! /* xchg implies a LOCK prefix, so no need to say LOCK explicitly */
__asm__ __volatile__(
" xchgb %0,%1 \n"
: "+q"(_res), "+m"(*lock)
:
: "memory", "cc");
*** postgresql-8.0.3/src/backend/storage/lmgr/s_lock.c.orig Fri Aug 26 10:47:35 2005
--- postgresql-8.0.3/src/backend/storage/lmgr/s_lock.c Sat Sep 10 19:19:46 2005
***************
*** 17,22 ****
--- 17,23 ----
#include <time.h>
#include <unistd.h>
+ #include <sched.h>
#include "storage/s_lock.h"
#include "miscadmin.h"
***************
*** 83,89 ****
int spins = 0;
int delays = 0;
! int cur_delay = MIN_DELAY_CSEC;
while (TAS(lock))
{
--- 84,90 ----
int spins = 0;
int delays = 0;
! int cur_delay = 0;
while (TAS(lock))
{
***************
*** 96,102 ****
if (++delays > NUM_DELAYS)
s_lock_stuck(lock, file, line);
! pg_usleep(cur_delay * 10000L);
#if defined(S_LOCK_TEST)
fprintf(stdout, "*");
--- 97,110 ----
if (++delays > NUM_DELAYS)
s_lock_stuck(lock, file, line);
! if (cur_delay == 0)
! {
! /* first time through, try just a sched_yield */
! sched_yield();
! cur_delay = MIN_DELAY_CSEC;
! }
! else
! pg_usleep(cur_delay * 10000L);
#if defined(S_LOCK_TEST)
fprintf(stdout, "*");
Hi Matthew, The apache is able to queue 1024 request. Reducing the number of max_clients was the key to deal with the problem of to much concurrent queries. We have monitored less concurrent http request after we decrease max_clients. We also have introduce a global statement timeout to stop long running queries. Both together protect our database server. The problem we had was only to find the values for our application. Sven. Matthew Lunnon schrieb: > Hi Sven, > > Yes I have done a reasonable amount of query tuning. The application is > a web service using an apache/resin combination at the front end, we > have thought about using resin threads to limit the number of > connections but are worried about backing up connections in apache and > getting some overflow here. But some kind of limiting of connections is > probably required. > > Thanks > Matthew > > Sven Geisler wrote: >> 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 >>> -- >>> >>> >> >> > > -- > 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
Hi Sven,
Does this mean that one option I have is to use a multi core Intel based server instead of an AMD based server?
Matthew
Sven Geisler wrote:
Does this mean that one option I have is to use a multi core Intel based server instead of an AMD based server?
Matthew
Sven Geisler wrote:
Hi Matthew, I remember that I also an issue with AMD Opterons before Pg 8.1 There is a specific Opteron behaviour on shared memory locks which adds a extra "penalty" during the execution time for Pg code before 8.1. I can you provide my patch for Pg 8.0 which should be adaptable for Pg 7.4 if you can compile PostgreSQL. But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale up for your concurrent queries will be great. 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.ukTel: +44 (0)29 2081 5056www.rwa-net.co.uk --
Hi Matthew, You should be upgrade to Pg 8.2.5. We did test on different hardware. The bigest step is to use Pg 8.2.5. The high number of context switches which we saw before simple disappeared. In regards to you question about XEONs: You will have the similar issue with a XEON box. We tried different boxes (4-way DC, 8-way, 8-way DC and 4-way QC). The improvement compared with a 4-way SC wan't really better under Pg 8.0. I'm not saying that a 4-way SC is faster than the 4-way QC in terms of handling more concurrent queries. But there was never a huge step as you would expect from the spend money. Regards Sven. Matthew Lunnon schrieb: > Hi Sven, > > Does this mean that one option I have is to use a multi core Intel based > server instead of an AMD based server? > > Matthew > > Sven Geisler wrote: >> Hi Matthew, >> >> I remember that I also an issue with AMD Opterons before Pg 8.1 >> There is a specific Opteron behaviour on shared memory locks which adds >> a extra "penalty" during the execution time for Pg code before 8.1. >> I can you provide my patch for Pg 8.0 which should be adaptable for Pg >> 7.4 if you can compile PostgreSQL. >> >> But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale >> up for your concurrent queries will be great. >> >> 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
Matthew Lunnon wrote: > Ah I was afraid of that. Maybe I'll have to come out of the dark ages. Yes :) but ignore the comment about the 8.3 Beta series. It is Beta for a reason, that means testing only, no production. Sincerely, Joshua D. Drake >> 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 wrote: > Hi Sven, > > Does this mean that one option I have is to use a multi core Intel based > server instead of an AMD based server? Wow... hold on. >> I remember that I also an issue with AMD Opterons before Pg 8.1 >> There is a specific Opteron behaviour on shared memory locks which adds >> a extra "penalty" during the execution time for Pg code before 8.1. I would like to see some proof of this. >> I can you provide my patch for Pg 8.0 which should be adaptable for Pg >> 7.4 if you can compile PostgreSQL. >> The real answer here is to upgrade to 8.1.10 or 8.2.5. 64bit if you can. >>> 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 This type of machine, assuming you have decent IO available will generally be very fast with anything >= 8.1. >>> changes to postgresql.sql: >>> >>> max_connections = 500 >>> shared_buffers = 96000 Way to high for 7.4. Way to low for 8.1 or above (based on what you mention your work load is) >>> sort_mem = 10240 Based on your specs this actually may be fine but I would suggest reviewing it after you upgrade. >>> effective_cache_size = 1000000 >>> Again, too low for 8.1 or above. Sincerely, Joshua D. Drake -- The PostgreSQL Company: http://www.commandprompt.com/
On Wed, 2007-12-12 at 07:38 -0800, Joshua D. Drake wrote: > Matthew Lunnon wrote: > > Ah I was afraid of that. Maybe I'll have to come out of the dark ages. > > Yes :) but ignore the comment about the 8.3 Beta series. It is Beta for > a reason, that means testing only, no production. Matthew, Some benchmark comparisons would be really useful though, so please don't be dissuaded from looking at 8.3. It's planned to be released after Christmas, so we're probably around 2 business weeks away from release if you ignore the holiday period. Everything you've said suggests you've hit the scalability limit of 7.4, which had a buffer manager that got worse with larger settings, fixed in 8.0. Most of the scalability stuff has been added since then and 8.3 looks to be really fast, but we would still like some more performance numbers. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs wrote: > On Wed, 2007-12-12 at 07:38 -0800, Joshua D. Drake wrote: >> Matthew Lunnon wrote: >>> Ah I was afraid of that. Maybe I'll have to come out of the dark ages. >> Yes :) but ignore the comment about the 8.3 Beta series. It is Beta for >> a reason, that means testing only, no production. > > Matthew, > > Some benchmark comparisons would be really useful though, so please > don't be dissuaded from looking at 8.3. Right, I did say "testing" which is always good. Sincerely, Joshua D. Drake
Matthew Lunnon wrote: > Ah I was afraid of that. Maybe I'll have to come out of the dark ages. At the very least, upgrade to latest 7.4 minor version. It probably won't help with you're performance, but 7.4.3 is very old. There's been a *lot* of bug fixes between 7.4.3 and 7.4.18, including fixes for security vulnerabilities and data corruption bugs. > 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 */ >> > -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com