Re: inefficient use of relation extension?

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:

> > I regularly (several times a day) see five or six processes all with
> > pg_locks locktype=extend granted=f on the same table, waiting for a long
> > time.
>
> I'm not sure what's causing that, but I *seriously* doubt that adding
> new pages to FSM right away would make it better. What it sounds like
> is someone is getting hung up while holding the lock. You should try to
> investigate who's got the lock when this happens, and what they're doing
> or waiting for.

After some more research, these facts have arisen:

- the relation extension lock in question is on a toast table
- the entries stored in that table are long enough that they need more
than one page
- the BufMappingLock is seen as severely contended among the various
processes trying to extend the table

So the dozen+ processes fight the BufMapping lwlock while attempting to
extend the toast table, to get a free buffer for the new page; do this
one page at a time, and then go back to do the same thing over and over.

Shared_buffers is large (4.6 GB) and I'm not clear if this is just
inefficiency in the 8.1 bufmgr code, or that bgwriter is not aggresive
enough about clearing pages from the LRU end of the buffer pool.

I'm not really sure what the right way to attack this problem is, but
getting off 8.1 has now gotten a priority.

sinval was tracked too and we found out that it's not an issue at all.

Thanks everyone.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support