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@xxxxxxxxxxx > To: scott.marlowe@xxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: 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: 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
