Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tlp2X-0006Ie-Js for pgsql-performance@arkaria.postgresql.org; Thu, 20 Dec 2012 22:56:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Tlp2W-0002Z8-LX for pgsql-performance@arkaria.postgresql.org; Thu, 20 Dec 2012 22:56:32 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tlp2V-0002Y9-Ec for pgsql-performance@postgresql.org; Thu, 20 Dec 2012 22:56:31 +0000 Received: from blu0-omc3-s5.blu0.hotmail.com ([65.55.116.80]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tlp2T-0001x4-DZ for pgsql-performance@postgresql.org; Thu, 20 Dec 2012 22:56:30 +0000 Received: from BLU002-W16 ([65.55.116.73]) by blu0-omc3-s5.blu0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Thu, 20 Dec 2012 14:56:24 -0800 X-EIP: [/mqUbTCMlEAwzeZ6yHjbF1PrEWws2sKq] X-Originating-Email: [charlesrg@outlook.com] Message-ID: From: Charles Gomes To: Jeff Janes CC: "pgsql-performance@postgresql.org" Subject: Re: Performance on Bulk Insert to Partitioned Table Date: Thu, 20 Dec 2012 17:56:24 -0500 Importance: Normal In-Reply-To: References: , Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginalArrivalTime: 20 Dec 2012 22:56:24.0582 (UTC) FILETIME=[3C762A60:01CDDF05] X-Pg-Spam-Score: -0.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 Jeff,=20 The 8288 writes are fine, as the array has a BBU, it's fine. You see about = 4% of the utilization. To target directly instead of doing : INSERT INTO TABLE VALUES () I use: INSERT INTO TABLE_PARTITION_01 VALUES() By targeting it I see a huge performance increase. I haven't tested using 1Billion rows in a single table. The issue is that i= n the future it will grow to more than 1 billion rows, it will get to about= 4Billion rows and that's when I believe partition would be a major improve= ment. ---------------------------------------- > Date: Thu, 20 Dec 2012 14:31:44 -0800 > Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table > From: jeff.janes@gmail.com > To: charlesrg@outlook.com > CC: pgsql-performance@postgresql.org > > On Thu, Dec 20, 2012 at 9:29 AM, Charles Gomes wr= ote: > > Hello guys > > > > > > > > I=92m doing 1.2 Billion inserts into a table partitioned in > > 15. > > > > > > > > When I target the MASTER table on all the inserts and let > > the trigger decide what partition to choose from it takes 4 hours. > > > > If I target the partitioned table directly during the > > insert I can get 4 times better performance. It takes 1 hour. > > How do you target them directly? By implementing the > "trigger-equivalent-code" in the application code tuple by tuple, or > by pre-segregating the tuples and then bulk loading each segment to > its partition? > > What if you get rid of the partitioning and just load data to the > master, is that closer to 4 hours or to 1 hour? > > ... > > > > > > What I noticed that iostat is not showing an I/O bottle > > neck. > > > > iostat =96xN 1 > > > > Device: > > rrqm/s wrqm/s r/s > > w/s rsec/s wsec/s avgrq-sz avgqu-sz > > await svctm %util > > > > Pgresql--data > > 0.00 0.00 0.00 > > 8288.00 0.00 66304.00 > > 8.00 60.92 7.35 > > 0.01 4.30 > > 8288 randomly scattered writes per second sound like enough to > bottleneck a pretty impressive RAID. Or am I misreading that? > > Cheers, > > Jeff > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance=20=09=09=20=09=20=20= =20=09=09=20=20 --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance