Re: Performance on Bulk Insert to Partitioned Table

Without hyperthreading CPU still not a bottleneck, while I/O is only 10% utilization.

top - 14:55:01 up 27 min,  2 users,  load average: 0.17, 0.19, 0.14
Tasks: 614 total,  17 running, 597 sleeping,   0 stopped,   0 zombie
Cpu(s): 73.8%us,  4.3%sy,  0.0%ni, 21.6%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  49282716k total,  5855492k used, 43427224k free,    37400k buffers
Swap: 44354416k total,        0k used, 44354416k free,  1124900k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
19903 postgres  20   0 22.7g  34m  32m S  9.6  0.1   0:02.66 postgres: cgomes historical_ticks 10.254.109.10(46103) COPY
19934 postgres  20   0 22.7g  34m  32m S  9.6  0.1   0:02.61 postgres: cgomes historical_ticks 10.254.109.10(46134) COPY
19947 postgres  20   0 22.7g  34m  31m S  9.6  0.1   0:02.64 postgres: cgomes historical_ticks 10.254.109.10(46147) COPY
19910 postgres  20   0 22.7g  34m  32m S  9.2  0.1   0:02.67 postgres: cgomes historical_ticks 10.254.109.10(46110) COPY
19924 postgres  20   0 22.7g  33m  31m S  9.2  0.1   0:02.65 postgres: cgomes historical_ticks 10.254.109.10(46124) COPY
19952 postgres  20   0 22.7g  34m  32m R  9.2  0.1   0:02.71 postgres: cgomes historical_ticks 10.254.109.10(46152) COPY
19964 postgres  20   0 22.7g  34m  32m R  9.2  0.1   0:02.59 postgres: cgomes historical_ticks 10.254.109.10(46164) COPY
19901 postgres  20   0 22.7g  35m  32m S  8.9  0.1   0:02.66 postgres: cgomes historical_ticks 10.254.109.10(46101) COPY
19914 postgres  20   0 22.7g  34m  31m S  8.9  0.1   0:02.62 postgres: cgomes historical_ticks 10.254.109.10(46114) COPY
19923 postgres  20   0 22.7g  34m  31m S  8.9  0.1   0:02.74 postgres: cgomes historical_ticks 10.254.109.10(46123) COPY
19925 postgres  20   0 22.7g  34m  31m R  8.9  0.1   0:02.65 postgres: cgomes historical_ticks 10.254.109.10(46125) COPY
19926 postgres  20   0 22.7g  34m  32m S  8.9  0.1   0:02.79 postgres: cgomes historical_ticks 10.254.109.10(46126) COPY
19929 postgres  20   0 22.7g  34m  31m S  8.9  0.1   0:02.64 postgres: cgomes historical_ticks 10.254.109.10(46129) COPY
19936 postgres  20   0 22.7g  34m  32m S  8.9  0.1   0:02.72 postgres: cgomes historical_ticks 10.254.109.10(46136) COPY

I believe the bottleneck may be that pgsql has fight with it's siblings to update the indexes. Is there a way good way to add probes to check where things are slowing down ?

----------------------------------------
> From: charlesrg(at)outlook(dot)com
> To: scott(dot)marlowe(at)gmail(dot)com
> CC: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
> Date: Thu, 20 Dec 2012 13:55:29 -0500
>
> 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
>
> --
> 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