I've been spending some time debugging a customer's performance problem,
and what I see is that there are a bunch of processes all waiting for
the relation extension lock for a particular relation.
While looking at this code I notice something that troubles me. Just
after extending the relation, we don't insert the new page into the FSM.
So if the extending backend does not do any other insertion on the page,
it is forgotten as possible insert target until the next vacuum.
Moreover, after we acquire the extension lock, we don't recheck the
table to see if the lock was just released by someone who had just
extended the table. So we extend the table again, even though there is
an almost-empty page at the end.
Both these things seem to be compounding in our customer's machine.
First it was both Slony's sl_log table and its TOAST table. Then we
disabled Slony because it was too far behind. Then the behavior
appeared again in a regular user table.
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 am actually checking this in 8.1, but the code is almost identical in
HEAD.
So I am wishing for two things:
1. that the new page is registered on the FSM so that other backends can
use it before the next VACUUM.
2. that if we need to wait for the lock at all, we release it and
restart at the top of RelationGetBufferForTuple, so that if someone else
already extended we can reuse the same page. (This would be done only
once so that we don't loop forever or an insane number of times.)
Thoughts?
PS: now that we have the new FSM stuff, perhaps the first part is bogus;
but in that case, the comment at the bottom of RelationGetBufferForTuple
needs to be updated.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support