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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
