Re: Performance on Bulk Insert to Partitioned Table

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: [PERFORM] Performance on Bulk Insert to Partitioned Table
> From: jeff(dot)janes(at)gmail(dot)com
> To: charlesrg(at)outlook(dot)com
> CC: pgsql-performance(at)postgresql(dot)org
>
> On Thu, Dec 20, 2012 at 9:29 AM, Charles Gomes <charlesrg(at)outlook(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance