Обсуждение: Postgre Performance

In response to "Deshpande, Yogesh Sadashiv (STSD-Openview)" <yogesh-sadashiv.deshpande@hp.com>:

> Hello ,
>
> We have a setup where in there are around 100 process running in parallel every 5 minutes and each one of them opens
aconnection to database. We are observing that for each connection , postgre also created on sub processes. We have set
max_connectionto 100. So the number of sub process in the system is close to 200 every 5 minutes. And because of this
weare seeing very high CPU usage. 

This does not follow logically, in my experience.  We have many servers that
have over 300 simultaneous connections, and the connections themselves do
not automatically create high CPU usage.

Unless of course, there is an issue with the particular OS you're using,
which you didn't mention.

> We need following information
>
> 1.       Is there any configuration we do that would pool the connection request rather than coming out with
connectionlimit exceed. 

Use pgpool or pgbouncer.

> 2.       Is there any configuration we do that would limit the sub process to some value say 50 and any request for
connectionwould get queued. 

Set the max connection and handle the connection retry in your application.

> Basically we wanted to limit the number of processes so that client code doesn't have to retry for unavailability for
connectionor sub processes , but postgre takes care of queuing? 

pgpool and pgbouncer handle some of that, but I don't know if they do
exactly everything that you want.  Probably a good place to start, though.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On 10/18/2011 06:57 AM, Deshpande, Yogesh Sadashiv (STSD-Openview) wrote:
> Hello ,
>
> We have a setup where in there are around 100 process running in
> parallel every 5 minutes and each one of them opens a connection to
> database. We are observing that for each connection , postgre also
> created on sub processes. We have set max_connection to 100. So the
> number of sub process in the system is close to 200 every 5 minutes. And
> because of this we are seeing very high CPU usage. We need following
> information
>
> 1.Is there any configuration we do that would pool the connection
> request rather than coming out with connection limit exceed.

Yes you need a pooler. Initiating connections like that is expensive.
The use of PgBouncer is your friend here.

http://wiki.postgresql.org/wiki/PgBouncer

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

On 10/18/11 9:51 AM, Bill Moran wrote:
>> Basically we wanted to limit the number of processes so that client code doesn't have to retry for unavailability
forconnection or sub processes , but postgre takes care of queuing? 
> pgpool and pgbouncer handle some of that, but I don't know if they do
> exactly everything that you want.  Probably a good place to start, though.

pools work great when you have a lot of clients that only sporadically
make queries, like web users. each client (like the webserver) grabs a
connection from the pool, runs its transactions, then releases the
connection back to the pool.    a pool won't help much if all 100 of
your clients want to make a query at the same time.

your 4 CPU 8GB machine will likely be optimal doing no more than about 8
queries at once. (give or take a few, depending on how many disk drives
in your raids and how much IO concurrency the server can support).
oh, you mentioned MS Windows in there, ok, 8 is optimistic, the optimal
value may be more like 4.

if you have 100 clients that simultaneously want to make queries each 5
minutes, you should consider using some sort of message queueing system,
where your clients send a message to an application service, and the app
server runs as many queue workers as you find are optimal, each of which
reads a message from the queue, processes database requests to satisfy
the message request, and returns the results to the client, then grabs
the next queue entry and repeat....


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On Tue, Oct 18, 2011 at 12:43 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 10/18/11 9:51 AM, Bill Moran wrote:
>>>
>>> Basically we wanted to limit the number of processes so that client code
>>> doesn't have to retry for unavailability for connection or sub processes ,
>>> but postgre takes care of queuing?
>>
>> pgpool and pgbouncer handle some of that, but I don't know if they do
>> exactly everything that you want.  Probably a good place to start, though.
>
> pools work great when you have a lot of clients that only sporadically make
> queries, like web users. each client (like the webserver) grabs a connection
> from the pool, runs its transactions, then releases the connection back to
> the pool.    a pool won't help much if all 100 of your clients want to make
> a query at the same time.
>
> your 4 CPU 8GB machine will likely be optimal doing no more than about 8
> queries at once. (give or take a few, depending on how many disk drives in
> your raids and how much IO concurrency the server can support).    oh, you
> mentioned MS Windows in there, ok, 8 is optimistic, the optimal value may be
> more like 4.
>
> if you have 100 clients that simultaneously want to make queries each 5
> minutes, you should consider using some sort of message queueing system,
> where your clients send a message to an application service, and the app
> server runs as many queue workers as you find are optimal, each of which
> reads a message from the queue, processes database requests to satisfy the
> message request, and returns the results to the client, then grabs the next
> queue entry and repeat....

Or he could spend $35k or so on an HP DL580 with 4x8 core Xeons in it.