From: Charles Gomes <charlesrg@outlook.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance on Bulk Insert to Partitioned Table
Date: Thu, 20 Dec 2012 17:56:24 -0500
Message-ID: <BLU002-W16AEB81482AF76F1AC0FCFAB370@phx.gbl> (raw)
In-Reply-To: <CAMkU=1zMNyCUMFGjsJM69FQbY9pYBr9jjW3c5jSe3vwABMudgQ@mail.gmail.com>
References: <BLU002-W2777115565E1CBCF867857AB370@phx.gbl>
<CAMkU=1zMNyCUMFGjsJM69FQbY9pYBr9jjW3c5jSe3vwABMudgQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.janes@gmail.com
> To: charlesrg@outlook.com
> CC: pgsql-performance@postgresql.org
>
> On Thu, Dec 20, 2012 at 9:29 AM, Charles Gomes <charlesrg@outlook.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: charlesrg@outlook.com, jeff.janes@gmail.com
Subject: Re: Performance on Bulk Insert to Partitioned Table
In-Reply-To: <BLU002-W16AEB81482AF76F1AC0FCFAB370@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox