Jeff, 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 in 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 improvement. ---------------------------------------- > Date: Thu, 20 Dec 2012 14:31:44 -0800 > Subject: Re: Performance on Bulk Insert to Partitioned Table > From: jeff.janes@xxxxxxxxx > To: charlesrg@xxxxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > > On Thu, Dec 20, 2012 at 9:29 AM, Charles Gomes <charlesrg@xxxxxxxxxxx> wrote: > > Hello guys > > > > > > > > I’m 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 –xN 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
