From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance under contention
Date: Wed, 24 Nov 2010 10:58:16 +0200
Message-ID: <4CECD3A8.2050707@gmail.com> (raw)
In-Reply-To: <4CEC5830.90805@postnewspapers.com.au>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, craig@postnewspapers.com.au, ivoras@freebsd.org
Subject: Re: Performance under contention
In-Reply-To: <4CECD3A8.2050707@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox