Re: Performance on Bulk Insert to Partitioned Table

Поиск
Список
Период
Сортировка
От Charles Gomes
Тема Re: Performance on Bulk Insert to Partitioned Table
Дата
Msg-id BLU002-W704C797D1B692A1066E743AB370@phx.gbl
обсуждение исходный текст
Ответ на Re: Performance on Bulk Insert to Partitioned Table  (Scott Marlowe <scott.marlowe@gmail.com>)
Ответы Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes <charlesrg@outlook.com>)
Список pgsql-performance
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.marlowe@gmail.com
> To: charlesrg@outlook.com
> CC: pgsql-performance@postgresql.org
>
> On Thu, Dec 20, 2012 at 10: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.
> >
> > 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: Performance on Bulk Insert to Partitioned Table
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: Performance on Bulk Insert to Partitioned Table