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: Performance on Bulk Insert to Partitioned Table > From: scott.marlowe@xxxxxxxxx > To: charlesrg@xxxxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > > On Thu, Dec 20, 2012 at 10: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. > > > > 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@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
