Re: Scalability in postgres - Mailing list pgsql-performance

James Mansion <james@mansionfamily.plus.com> wrote:
> Kevin Grittner wrote:
>> Sure, but the architecture of those products is based around all
>> the work being done by "engines" which try to establish affinity to
>> different CPUs, and loop through the various tasks to be done.  You
>> don't get a context switch storm because you normally have the
>> number of engines set at or below the number of CPUs.  The down
>> side is that they spend a lot of time spinning around queue access
>> to see if anything has become available to do -- which causes them
>> not to play nice with other processes on the same box.
>>
> This is just misleading at best.

What part?  Last I checked, Sybase ASE and SQL Server worked as I
described.  Those are the products I was describing.  Or is it
misleading to say that you aren't likely to get a context switch storm
if you keep your active thread count at or below the number of CPUs?

> I'm sorry, but (in particular) UNIX systems have routinely
> managed large numbers of runnable processes where the run queue
> lengths are long without such an issue.

Well, the OP is looking at tens of thousands of connections.  If we
have a process per connection, how many tens of thousands can we
handle before we get into problems with exhausting possible pid
numbers (if nothing else)?

> This is not an issue with the number of runnable threads,
> but with the way that they wait and what they do.

Well, I rather think it's about both.  From a description earlier in
this thread, it sounds like Oracle effective builds a connection pool
into their product, which gets used by default.  The above-referenced
products use a more extreme method of limiting active threads.
Perhaps they're silly to do so; perhaps not.

I know that if you do use a large number of threads, you have to be
pretty adaptive.  In our Java app that pulls data from 72 sources and
replicates it to eight, plus feeding it to filters which determine
what publishers for interfaces might be interested, the Sun JVM does
very poorly, but the IBM JVM handles it nicely.  It seems they use
very different techniques for the monitors on objects which
synchronize the activity of the threads, and the IBM technique does
well when no one monitor is dealing with a very large number of
blocking threads.  They got complaints from people who had thousands
of threads blocking on one monitor, so they now keep a count and
switch techniques for an individual monitor if the count gets too
high.

Perhaps something like that (or some other new approach) might
mitigate the effects of tens of thousands of processes competing for
for a few resources, but it fundamentally seems unwise to turn those
loose to compete if requests can be queued in some way.

-Kevin