Re: why after increase the hash table partitions, TPMC decrease

On Tue, Sep 2, 2014 at 1:39 AM, Xiaoyulei <xiaoyulei(at)huawei(dot)com> wrote:

>
>
> We use benchmarksql to start tpcc test in postgresql 9.3.3.
>
> Before test we set benchmarksql client number about 800. And we increase
> the hash partitions from 16 to 1024 , in order to reduce the hash
> partition locks competition.
>

Can you give the complete invocation parameters for benchmarksql?

How many CPUs/cores do you have?

> We expect that after increase the number of partitions, reduces
> lock competition, TPMC should be increased. But the test results on the
> contrary, after modified to 1024, TPMC did not increase, but decrease.
>
> Why such result?
>

Increasing the partition numbers would only help with contention that is
due to hash collision. If all of the contention is on, for example, the
root block of one index, then increasing the number partitions won't change
that, because that block is always going to map to a single partition.

Can you "perf" or some other profiling tool to find where your bottleneck
is?

Cheers,

Jeff