Re: Performance on Bulk Insert to Partitioned Table

None of the cores went to 100%. Looking at top during the inserts I can see several cores working, but never more than 60% busy. The machine has 8 cores (16 in HT).
The load is spread through the cores, didn't have a single maxed out. However with HT on, technically it is overloaded.

top - 13:14:07 up 7 days, 3:10, 3 users, load average: 0.25, 0.12, 0.10
Tasks: 871 total, 13 running, 858 sleeping, 0 stopped, 0 zombie
Cpu(s): 60.6%us, 5.0%sy, 0.0%ni, 34.1%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 49282716k total, 9311612k used, 39971104k free, 231116k buffers
Swap: 44354416k total, 171308k used, 44183108k free, 2439608k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21832 postgres 20 0 22.7g 93m 90m S 15 0.2 0:19.91 postgres: cgomes historical_ticks 10.254.109.10(44093) COPY
21817 postgres 20 0 22.7g 92m 89m S 15 0.2 0:20.24 postgres: cgomes historical_ticks 10.254.109.10(44077) idle
21842 postgres 20 0 22.7g 96m 93m S 15 0.2 0:20.39 postgres: cgomes historical_ticks 10.254.109.10(44103) COPY
21792 postgres 20 0 22.7g 93m 90m R 15 0.2 0:20.34 postgres: cgomes historical_ticks 10.254.109.10(44045) COPY
21793 postgres 20 0 22.7g 90m 88m S 15 0.2 0:20.13 postgres: cgomes historical_ticks 10.254.109.10(44048) COPY
21806 postgres 20 0 22.7g 94m 91m S 15 0.2 0:20.14 postgres: cgomes historical_ticks 10.254.109.10(44066) COPY
21809 postgres 20 0 22.7g 92m 89m S 15 0.2 0:19.82 postgres: cgomes historical_ticks 10.254.109.10(44069) COPY
21813 postgres 20 0 22.7g 92m 89m S 15 0.2 0:19.98 postgres: cgomes historical_ticks 10.254.109.10(44073) COPY
21843 postgres 20 0 22.7g 95m 92m S 15 0.2 0:20.56 postgres: cgomes historical_ticks 10.254.109.10(44104) COPY
21854 postgres 20 0 22.7g 91m 88m S 15 0.2 0:20.08 postgres: cgomes historical_ticks 10.254.109.10(44114) COPY
21796 postgres 20 0 22.7g 89m 86m S 14 0.2 0:20.03 postgres: cgomes historical_ticks 10.254.109.10(44056) COPY
21797 postgres 20 0 22.7g 92m 90m R 14 0.2 0:20.18 postgres: cgomes historical_ticks 10.254.109.10(44057) COPY
21804 postgres 20 0 22.7g 95m 92m S 14 0.2 0:20.28 postgres: cgomes historical_ticks 10.254.109.10(44064) COPY
21807 postgres 20 0 22.7g 94m 91m S 14 0.2 0:20.15 postgres: cgomes historical_ticks 10.254.109.10(44067) COPY
21808 postgres 20 0 22.7g 92m 89m S 14 0.2 0:20.05 postgres: cgomes historical_ticks 10.254.109.10(44068) COPY
21815 postgres 20 0 22.7g 90m 88m S 14 0.2 0:20.13 postgres: cgomes historical_ticks 10.254.109.10(44075) COPY
21818 postgres 20 0 22.7g 91m 88m S 14 0.2 0:20.01 postgres: cgomes historical_ticks 10.254.109.10(44078) COPY
21825 postgres 20 0 22.7g 92m 89m S 14 0.2 0:20.00 postgres: cgomes historical_ticks 10.254.109.10(44085) COPY
21836 postgres 20 0 22.7g 91m 88m R 14 0.2 0:20.22 postgres: cgomes historical_ticks 10.254.109.10(44097) COPY
21857 postgres 20 0 22.7g 89m 86m R 14 0.2 0:19.92 postgres: cgomes historical_ticks 10.254.109.10(44118) COPY
21858 postgres 20 0 22.7g 95m 93m S 14 0.2 0:20.36 postgres: cgomes historical_ticks 10.254.109.10(44119) COPY
21789 postgres 20 0 22.7g 92m 89m S 14 0.2 0:20.05 postgres: cgomes historical_ticks 10.254.109.10(44044) COPY
21795 postgres 20 0 22.7g 93m 90m S 14 0.2 0:20.27 postgres: cgomes historical_ticks 10.254.109.10(44055) COPY
21798 postgres 20 0 22.7g 89m 86m S 14 0.2 0:20.06 postgres: cgomes historical_ticks 10.254.109.10(44058) COPY
21800 postgres 20 0 22.7g 93m 90m S 14 0.2 0:20.04 postgres: cgomes historical_ticks 10.254.109.10(44060) COPY
21802 postgres 20 0 22.7g 89m 87m S 14 0.2 0:20.10 postgres: cgomes historical_ticks 10.254.109.10(44062) COPY

Looks like I will have to disable HT.

I've been looking at converting the trigger to C, but could not find
a good example trigger for partitions written in C to start from. Have
you heard of anyone implementing the partitioning trigger in C ?

----------------------------------------
> Date: Thu, 20 Dec 2012 10:39:25 -0700
> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
> From: scott(dot)marlowe(at)gmail(dot)com
> To: charlesrg(at)outlook(dot)com
> CC: pgsql-performance(at)postgresql(dot)org
>
> On Thu, Dec 20, 2012 at 10: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.
> >
> > I’m trying to get more performance while still using the
> > trigger to choose the table, so partitions can be changed without changing the
> > application that inserts the data.
> >
> > What I noticed that iostat is not showing an I/O bottle
> > neck.
>
> SNIP
>
> > I also don’t see a CPU bottleneck or context switching
> > bottle neck.
>
> Are you sure? How are you measuring CPU usage? If you've got > 1
> core, you might need to look at individual cores in which case you
> should see a single core maxed out.
>
> Without writing your trigger in C you're not likely to do much better
> than you're doing now.
>
>
> --
> 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