Re: High-Concurrency GiST in postgreSQL

On 12/05/11 1:34 PM, C. Mundi wrote:
> Thanks, Andy. You're quite right of course. I'm thinking of
> concurrent clients. Lots of them. I envision thousands of actors
> (which could be threads within a process or separate processes)
> behaving as clients, each with its own connection to a single-threaded
> database server. So concurrency here simply means that the database
> has to be able to handle a lot of "simultaneous" connections coming at
> it fast and asynchronously. (I'm prepared to deploy a thin queuing
> layer if it turns out that I saturate the server.) The compute nodes
> are doing heavy physics which depends on spatially organized data, and
> it is very difficult to predict which rows an actor will need next.
> (In fact, knowing that would presuppose that the problem to be solved
> could be factored at great savings in computation.)

for optimal performance, you typically don't want more active queries
than the number of CPU hardware threads if you're running purely from
cache, and maybe that number + the number of physical disk drives, if
you're doing lots of disk IO. I'm running OLTP style operations on a
dual 6-core 'sandy bridge' xeon, this has 12 cores of 2 threads each, or
24 total hardware threads, and the server has 25 2.5" SAS drives, so we
find staying around 50 active queries is the sweet spot. we manage
this by using a message queueing system, where we can dynamically tune
the worker thread count, where these worker threads do the actual
database connections... that way 1000s of actual clients can lob
requests into the messaging system (AMQP, JMS, etc would be suitable
candidates, but we have our own inhouse system for legacy reasons), and
50 worker processes take these requests, handle them, and reply.

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