From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: COPY with high # of clients, partitioned table locking issues?
Date: Fri, 01 Apr 2011 02:50:47 -0400
Message-ID: <4D9575C7.90402@2ndQuadrant.com> (raw)
In-Reply-To: <EF37296944B47C40ADDCCB7BFD6289FE04AEC35D0D@EMASC201VS01.exchad.jpmchase.net>
References: <EF37296944B47C40ADDCCB7BFD6289FE04AEC35D0D@EMASC201VS01.exchad.jpmchase.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: COPY with high # of clients, partitioned table locking issues?
In-Reply-To: <4D9575C7.90402@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox