Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TlmKF-0002ip-WC for pgsql-performance@arkaria.postgresql.org; Thu, 20 Dec 2012 20:02:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TlmKE-0005cF-PX for pgsql-performance@arkaria.postgresql.org; Thu, 20 Dec 2012 20:02:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TlmKD-0005c9-NM for pgsql-performance@postgresql.org; Thu, 20 Dec 2012 20:02:37 +0000 Received: from cl-262.qas-01.us.sixxs.net ([2001:4830:1600:105::2] helo=tamriel.snowman.net) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TlmKB-0003Fb-0x for pgsql-performance@postgresql.org; Thu, 20 Dec 2012 20:02:37 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 3FB102256F; Thu, 20 Dec 2012 15:02:34 -0500 (EST) Date: Thu, 20 Dec 2012 15:02:34 -0500 From: Stephen Frost To: Charles Gomes Cc: "pgsql-performance@postgresql.org" Subject: Re: Performance on Bulk Insert to Partitioned Table Message-ID: <20121220200234.GJ12354@tamriel.snowman.net> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="aRzsUE0Mp86t8N62" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/3.2.0-4-amd64 (x86_64) X-Uptime: 14:59:18 up 56 days, 18:23, 5 users, load average: 0.57, 0.25, 0.18 User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --aRzsUE0Mp86t8N62 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Charles, * Charles Gomes (charlesrg@outlook.com) wrote: > I=E2=80=99m doing 1.2 Billion inserts into a table partitioned in > 15. Do you end up having multiple threads writing to the same, underlying, tables..? If so, I've seen that problem before. Look at pg_locks while things are running and see if there are 'extend' locks that aren't being immediately granted. Basically, there's a lock that PG has on a per-relation basis to extend the relation (by a mere 8K..) which will block other writers. If there's a lot of contention around that lock, you'll get poor performance and it'll be faster to have independent threads writing directly to the underlying tables. I doubt rewriting the trigger in C will help if the problem is the extent lock. If you do get this working well, I'd love to hear what you did to accomplish that. Note also that you can get bottle-necked on the WAL data, unless you've taken steps to avoid that WAL. Thanks, Stephen --aRzsUE0Mp86t8N62 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlDTbtkACgkQrzgMPqB3kiizSgCeN7Gao7JcfJfVYURxW1yCjOB0 tlQAnRJWuPoSrKP9sPP1GQf5kfWhFYSV =W/+B -----END PGP SIGNATURE----- --aRzsUE0Mp86t8N62--