Re: write scalability

Excerpts from Merlin Moncure's message of lun jul 25 17:19:58 -0400 2011:
> On Mon, Jul 25, 2011 at 3:07 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> > Experience
> > with the read scalability stuff has taught me also to look at which
> > LWLocks have the most shared acquisitions, as that can cause spinlock
> > and cache line contention.  The top few culprits are:
> >
> > lwlock 504: shacq 5315030 exacq 0 blk 0
> > lwlock 45: shacq 5967317 exacq 13284 blk 1722
> > lwlock 39: shacq 8219988 exacq 13342 blk 2291
> > lwlock 5: shacq 26306020 exacq 0 blk 0
> > lwlock 4: shacq 28667307 exacq 2628524 blk 3356651
> > lwlock 11: shacq 84913908 exacq 4539551 blk 2119423
> >
> > In all, there were 238777533 shared LWLock acquisitions during this
> > test: 35% CLogControlLock, 12% ProcArrayLock, 11% SInvalReadLock (soon
> > to be dealt with, as discussed elsewhere on-list), and then it gets
> > down into the lock manager locks and a few others.
>
> hm, all the CLogControlLock acquisitions in clog.c appear to be
> exclusive...or did you mean shared in some other sense?

SLRU control locks are also acquired indirectly by slru.c, see
SimpleLruReadPage_ReadOnly.

> TransactionIdGetStatus is taking an exclusive lock which is a red flag
> and a good optimization target, I think.

In fact, if the page that TransactionIdGetStatus is looking for is in
the buffers, it'll only take a shared lock.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support