I haven't been keeping up on the hardware, so I defer to you on
that. It certainly seems like it would fit with the symptoms. On
the other hand, I haven't seen anything yet to convince me that it
*couldn't* be a client-side or network bottleneck, or the sort of
lock contention bottleneck that showed up in some of Sun's
benchmarks. If it were my problem, I'd be trying to rule out
whichever one of those could be tested most easily, iteratively.
How do I learn more about the actual lock contention in my db? Lock contention makes some sense. Each of the 256 requests are relatively similar. So, I don't doubt that lock contention could be an issue. I just don't know how to observe it or correct it. It seems like if we have processes that are contending for locks, there's not much we can do about it.
Also, as you suggested, identifying what queries are taking most of
the time and trying to optimize them is a route that might help,
regardless.
We often undertake query optimization. And, we often learn things about our app or make small performance gains from it. Sometimes, we are even able to make big changes to the application to make large gains based on how we see queries performing.
So, I agree that it's a good thing. However, query optimizing is tough, since you can't necessarily predict the sizes of your tables in a real-time system that is used differently by different users.
-Kevin