Re: COPY with high # of clients, partitioned table locking issues?

On 03/30/2011 04:56 PM, Strange, John W wrote:
> [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 LOG: process 14405 still waiting for ExclusiveLock on extension of relation 470273 of database 16384 after 5001.894 ms
> [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e LOG: process 7294 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5062.968 ms
> [ 2011-03-30 15:54:56.077 EDT ] 25781 [local] asgprod:4d938556.64b5 LOG: process 25781 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5124.463 ms
>

When you insert something new into the database, sometimes it has to
grow the size of the underlying file on disk to add it. That's called
"relation extension"; basically the table gets some number of 8K blocks
added to the end of it. If your workload tries to push new blocks into
a table with no free space, every operation will become serialized
waiting on individual processes grabbing the lock for relation extension.

The main reasonable way around this from a high level is to write
something that makes the extensions create significantly more data when
they get into this situation than they do right now. Don't just extend
by one block; extend by a large numer instead, if you believe you're in
this sort of situation. That's probably going to take a low-level code
change to actually fix the issue inside PostgreSQL though.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books