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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books