Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D3F2A1337B3E for ; Mon, 1 Nov 2010 09:56:21 -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 80358-04 for ; Mon, 1 Nov 2010 12:56:14 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm24.bullet.mail.ac4.yahoo.com (nm24.bullet.mail.ac4.yahoo.com [98.139.52.221]) by mail.postgresql.org (Postfix) with SMTP id 5CA3C1337B2F for ; Mon, 1 Nov 2010 09:56:14 -0300 (ADT) Received: from [98.139.52.192] by nm24.bullet.mail.ac4.yahoo.com with NNFMP; 01 Nov 2010 12:56:14 -0000 Received: from [98.139.52.135] by tm5.bullet.mail.ac4.yahoo.com with NNFMP; 01 Nov 2010 12:56:14 -0000 Received: from [127.0.0.1] by omp1018.mail.ac4.yahoo.com with NNFMP; 01 Nov 2010 12:56:14 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 438456.49017.bm@omp1018.mail.ac4.yahoo.com Received: (qmail 98713 invoked by uid 60001); 1 Nov 2010 12:56:14 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1288616174; bh=YvW10TLZj9VsPZVtBQyvnrSE6ayFA81SNE7F7z21QyU=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=D0lyvwMfQ9WgrXRscrC2IHFR7+fuD0p9y1F+RibBd7RzCHkRLPw8GTraXAVg49A5Y19peVUYVMA+cHRSNTqKDzutc/3seR7OVt4cuY7Ytp6Ox3mPQ/GeKbjtaaaHIGmxYFQiaSHTuZvW+Oij+iIp/BN7yrnpNQPuB3fvf97FzJ0= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=WN5Rh1b9hAITCRHct3Xe3EL5KerA02X0QbnW+1Ay8NRQ3zthqvVuk0XREgSsLCptZWqLOK2p8rE+qGNgzLCTmbFPhLbte7K3G+aMMrBhV+s9J/YUcrclLHFjUwjGHS7eRQmHiscO26bNRbxZNbBr9hQrG1XTRMXdgSU4H0o6Jpw=; Message-ID: <235610.98703.qm@web65413.mail.ac4.yahoo.com> X-YMail-OSG: GoQnCVgVM1ng2QXGOR24Su5KK12ByV7rhD68HzL2OMF809Y pvIVdwBtP1iHwGwAGnbCr3p.3PSG7_5aVGR7fMmh7V2lyxaMkPAI.zRNJyAM ftv.qwNx_w22F6pX6HaAVmOS_6T1LkIQM7vqGTOmyi4ibgMz4We.n3J8xILH rd.MP7yWu4IVhU8HCTULoq510r0UXruL9VN6B9NyIh34RNbYcD7MdjgnentC QcWp0RlJRjVSMHSdMZQa3PrytAyreoHx1ShfWulODwmFdvSzPUb_AQ4Jzf69 ub2rrFB7woQ9XwjJC8wzaPeDWZpyYv.nTrcv7ABWdDoyUlzxOwsTksfin626 tpTWzifis5qfH37PnXPxbh4A_x678e8jAYNk- Received: from [194.237.142.17] by web65413.mail.ac4.yahoo.com via HTTP; Mon, 01 Nov 2010 05:56:13 PDT X-Mailer: YahooMailRC/504.5 YahooMailWebService/0.8.107.284920 References: <293254.80548.qm@web65413.mail.ac4.yahoo.com> Date: Mon, 1 Nov 2010 05:56:13 -0700 (PDT) From: Divakar Singh Subject: Re: Insert performance with composite index To: Marti Raudsepp Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-2142692458-1288616173=:98703" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/3 X-Sequence-Number: 41027 --0-2142692458-1288616173=:98703 Content-Type: text/plain; charset=us-ascii Hi Marti, Thanks for your tips. i will try those. I am on Solaris Sparc 5.10 Best Regards, Divakar ________________________________ From: Marti Raudsepp To: Divakar Singh Cc: pgsql-performance@postgresql.org Sent: Mon, November 1, 2010 6:23:17 PM Subject: Re: [PERFORM] Insert performance with composite index On Mon, Nov 1, 2010 at 14:49, Divakar Singh wrote: > I am trying to tune my libpq program for insert performance. > When I tried inserting 1M rows into a table with a Primary Key, it took > almost 62 seconds. > After adding a composite index of 2 columns, the performance degrades to 125 > seconds. This sounds a lot like the bottleneck I was hitting. What Linux kernel version are you running? If it's 2.6.33 or later, see: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php Regards, Marti --0-2142692458-1288616173=:98703 Content-Type: text/html; charset=us-ascii
Hi Marti,
Thanks for your tips. i will try those.
I am on Solaris Sparc 5.10
 
Best Regards,
Divakar


From: Marti Raudsepp <marti@juffo.org>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Mon, November 1, 2010 6:23:17 PM
Subject: Re: [PERFORM] Insert performance with composite index

On Mon, Nov 1, 2010 at 14:49, Divakar Singh <dpsmails@yahoo.com> wrote:
> I am trying to tune my libpq program for insert performance.
> When I tried inserting 1M rows into a table with a Primary Key, it took
> almost 62 seconds.
> After adding a composite index of 2 columns, the performance degrades to 125
> seconds.

This sounds a lot like the bottleneck I was hitting. What Linux kernel
version are you running?

If it's 2.6.33 or later, see:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers
http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

Regards,
Marti

--0-2142692458-1288616173=:98703--