DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=FkTvHfyXUGR4RO+VneZuP4u8dEr7gBKW3P/T9/bT9f/6i7ensgLht4lIAH2U9EwSth
	R2gaRcvlpCvTU2Sp9YtG+Rxdh2V8oWJzZHtfqtgaF/EZkrLprxlSlbGu4nuNXAEDwLs1
	/o1nlkdd+nHRN/wCNbg4sRTe2Hv69t7DqL4ro=
Message-ID: <4CECD3A8.2050707@gmail.com>
Date: Wed, 24 Nov 2010 10:58:16 +0200
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.15) Gecko/20101027 Thunderbird/3.0.10
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: Ivan Voras <ivoras@freebsd.org>, pgsql-performance@postgresql.org
Subject: Re: Performance under contention
References: <4CE9773D0200002500037CA6@gw.wicourts.gov>
	<iccjub$bn0$1@dough.gmane.org>
	<4CEA373D0200002500037CED@gw.wicourts.gov>
	<ice2pd$ae8$1@dough.gmane.org>
	<4CEC5830.90805@postnewspapers.com.au>
In-Reply-To: <4CEC5830.90805@postnewspapers.com.au>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

24.11.10 02:11, Craig Ringer написав(ла):
> On 11/22/2010 11:38 PM, Ivan Voras wrote:
>> On 11/22/10 16:26, Kevin Grittner wrote:
>>> Ivan Voras<ivoras@freebsd.org> wrote:
>>>> On 11/22/10 02:47, Kevin Grittner wrote:
>>>>> Ivan Voras wrote:
>>>>>
>>>>>> After 16 clients (which is still good since there are only 12
>>>>>> "real" cores in the system), the performance drops sharply
>>>>>
>>>>> Yet another data point to confirm the importance of connection
>>>>> pooling. :-)
>>>>
>>>> I agree, connection pooling will get rid of the symptom. But not
>>>> the underlying problem. I'm not saying that having 1000s of
>>>> connections to the database is a particularly good design, only
>>>> that there shouldn't be a sharp decline in performance when it
>>>> does happen. Ideally, the performance should remain the same as it
>>>> was at its peek.
>>>
>>> Well, I suggested that we add an admission control[1] mechanism,
>>
>> It looks like a hack (and one which is already implemented by connection
>> pool software); the underlying problem should be addressed.
>
> My (poor) understanding is that addressing the underlying problem 
> would require a massive restructure of postgresql to separate 
> "connection and session state" from "executor and backend". Idle 
> connections wouldn't require a backend to sit around unused but 
> participating in all-backends synchronization and signalling. Active 
> connections over a configured maximum concurrency limit would queue 
> for access to a backend rather than fighting it out for resources at 
> the OS level.
>
> The trouble is that this would be an *enormous* rewrite of the 
> codebase, and would still only solve part of the problem. See the 
> prior discussion on in-server connection pooling and admission control.
Hello.

IMHO the main problem is not a backend sitting and doing nothing, but 
multiple backends trying to do their work. So, as for me, the simplest 
option that will make most people happy would be to have a limit 
(waitable semaphore) on backends actively executing the query. Such a 
limit can even be automatically detected based on number of CPUs 
(simple) and spindels (not sure if simple, but some default can be 
used). Idle (or waiting for a lock) backend consumes little resources. 
If one want to reduce resource usage for such a backends, he can 
introduce external pooling, but such a simple limit would make me happy 
(e.g. having max_active_connections=1000, max_active_queries=20).
The main Q here, is how much resources can take a backend that is 
waiting for a lock. Is locking done at the query start? Or it may go 
into wait while consumed much of work_mem. In the second case, the limit 
won't be work_mem limit, but will still prevent much contention.

Best regards, Vitalii Tymchyshyn