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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
