Re: Performance on Bulk Insert to Partitioned Table

On Thursday, December 20, 2012, Charles Gomes wrote:

> 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
>

...

> I believe the bottleneck may be that pgsql has fight with it's siblings to
> update the indexes.

I think that should mostly show up as idle or wait, not as user time.
Since your at 75% user already, you couldn't expect more than a 33%
improvement by fixing that, assuming that that were the problem.

> Is there a way good way to add probes to check where things are slowing
down ?

What happens if you just drop the indexes as an experiment? That should
put a hard limit on the amount the indexes can be slowing you down.

I like oprofile to give you good bottom level profile of where CPU time is
going. Unfortunately, it will probably just show you "AllocSetAlloc".
Also, I don't trust it on virtualized systems, if you are on one of those.

There are many other ways of approaching it, but mostly you have to already
have a good theory about what is going on in order know which one to use or
to interpret the results, and many of them require you to make custom
compiles of the postgres server code.

Cheers,

Jeff