Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C2EA01337990 for ; Fri, 1 Apr 2011 03:50:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 44428-02 for ; Fri, 1 Apr 2011 06:50:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.8]) by mail.postgresql.org (Postfix) with ESMTP id 2D53013364DE for ; Fri, 1 Apr 2011 03:50:43 -0300 (ADT) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis) id 0MH3DS-1QAS1L1z5G-00DzZI; Fri, 01 Apr 2011 08:50:42 +0200 Message-ID: <4D9575C7.90402@2ndQuadrant.com> Date: Fri, 01 Apr 2011 02:50:47 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: COPY with high # of clients, partitioned table locking issues? References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:JHplmAjNjNTzw0Kh3M/lCRQhnYvkoKeAQadsYrHVWS4 iEd/BRJp0Z3EbdZSx3Uv+ljDv6WfBIx6g9qsEWfAxFheLwM3op HPPHdSAaQ5HceErZAcDh0zLpJlxXH4LwxkgenSN3NABJX73YP1 RtNjYutvSn3SBQgfRTtoKbLCr+rmVP0jc6eYWPk7qn2HqvPMvk 3BGTmb/HJbZSInvHdpuVMOX6l/Cr/xbBNjNoyq5gVI= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201104/2 X-Sequence-Number: 43064 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