On Mon, Aug 15, 2011 at 6:22 PM, Greg Stark <stark@mit.edu> wrote:
> On Wed, Aug 3, 2011 at 7:21 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> I'm kind of interested by the
>> result, actually, as I had feared that the spinlock protecting
>> ProcArrayLock was going to be a bigger problem sooner.
>
> I think this depends on how many connections you have. If you try to
> scale up your benchmark by having hundreds of connections then get
> O(n^2) increase in the time spent with the procarray locked. It sounds
> like they pinned the number of connections at the number of cores they
> had. That makes sense if they're intentionally driving a cpu-bound
> benchmark but it means they won't run into this problem.
The experiments that I've done so far seem to indicate that there are
actually a couple of different ways for ProcArrayLock to become a
problem.
First, as you say, if you have a busy read-write workload, you just
get plain old LWLock contention. Everyone who wants a snapshot needs
the lock in shared mode; everyone who wants to commit needs it in
exclusive mode. So, the people who are committing block both
snapshot-taking and other commits; while the snapshot-takers block
commit.
If you switch to a read-only workload, ProcArrayLock becomes
uncontended, since a transaction without an XID does not need to
acquire ProcArrayLock to commit. But if you have enough CPUs (64 will
do it), the spinlock protecting ProcArrayLock becomes a bottleneck.
However, you can only observe this problem if the working set fits in
shared buffers; otherwise, BufFreelistLock contention slows the whole
system to a crawl, and the spinlock contention never materializes.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company