Re: Limited performance on multi core server - Mailing list pgsql-performance

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