Re: Proposal of tunable fix for scalability of 8.4

At 11:44 AM 3/12/2009, Kevin Grittner wrote:

>I'm probably more inclined to believe that his change may have merit
>than many here, but I can't accept anything based on this test until
>someone answers the question, so far ignored by all responses, of
>where the bottleneck is at the low end which allows linear
>scalability up to 1000 users (which I assume means connections).
>
>I'm particularly inclined to be suspicious of this test since my own
>benchmarks, with real applications replaying real URL requests from
>a production website that gets millions of hits per day, show that
>response time and throughput are improved by using a connection pool
>with queuing to limit the concurrent active queries.
>
>My skepticism is not helped by the fact that in a previous
>discussion with someone about performance as connections are
>increased, this point was covered by introducing a "primitive"
>connection pool -- which used a one second sleep for a thread if the
>maximum number of connections were already in use, rather than
>proper queuing and semaphores.  That really gives no clue how
>performance would be with a real connection pool.
>
>-Kevin

IMHO, Jignesh is looking at performance for a spcialized niche in the
overall space of pg use- that of memory resident DBs.  Here's my
thoughts on the more general problem.  The following seems to explain
all the performance phenomenon discussed so far while suggesting an
improvement in how pg deals with lock scaling and contention.

  Thoughts on lock scaling and contention

logical limits
...for Exclusive locks
a= the number of non overlapping sets of DB entities (tables, rows, etc)
If every exclusive lock wants a different table,
then the limit is the number of tables.
If any exclusive lock wants the whole DB,
then there can only be one lock.
b= possible HW limits
Even if all exclusive locks in question ask for distinct DB entities, it is
possible that the HW servicing those locks could be saturated.
...for Shared locks
a= HW Limits

HW limits
a= network IO
b= HD IO
Note that "a" and "b" may change relative order in some cases.
A possibly unrealistic extreme to demonstrate the point would be a system with
1 HD and 10G networking. It's likely to be HD IO bound before network
IO bound.
c= RAM IO
d= Internal CPU bandwidth

Since a DB must first and foremost protect the integrity of the data being
processed, the above implies that we should process transactions in time order
of resource access (thus transactions that do not share resources can always
run in parallel) while running as many of them in parallel as we can that
a= do not violate the exclusive criteria, and
b= do not over saturate any resource being used for the processing.

This looks exactly like a job scheduling problem from the days of mainframes.
(Or instruction scheduling in a CPU to maximize the IPC of a thread.)

The solution in the mainframe domain was multi-level feedback queues with
priority aging.
Since the concept of a time slice makes no sense in a DB, this becomes a
multi-level resource coloring problem with dynamic feedback based on
exclusivity
and resource contention.

A possible algorithm might be
1= every transaction for a given DB entity has priority over any transaction
submitted at a later time that uses that same DB entity.
2= every transaction that does not conflict with an earlier transaction can
run in parallel with that earlier transaction
3= if any resource becomes saturated, we stop scheduling transactions that use
that resource or that are dependent on that resource until the deadlock is
resolved.

To implement this, we need
a= to be able to count the number of locks for any given DB entity
b= some way of detecting HW saturation

Hope this is useful,
Ron Peacetree