Re: Postgre Performance

On Tue, Oct 18, 2011 at 12:43 PM, John R Pierce <pierce(at)hogranch(dot)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.