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

benchmarSQL has about half reads. So I think it should be effective.

I don't think BufFreelistLock take much time, it just get a buffer from list. It should be very fast.

The test server has 2 CPUs and 12 cores in each CPU. 24 processor totally. CPU Idle time is over 50%. IO only 10%(data is in SSD)

I perf one process of pg. The hot spot is hash search. perf data file is more than 1M, so I do not attach it. I send it separately.

3.63% postgres postgres [.] hash_search_with_hash_value
3.10% postgres postgres [.] AllocSetAlloc
3.04% postgres postgres [.] LWLockAcquire
2.73% postgres postgres [.] _bt_compare
2.66% postgres postgres [.] SearchCatCache
2.18% postgres postgres [.] ExecInitExpr
2.11% postgres postgres [.] GetSnapshotData
1.57% postgres postgres [.] PinBuffer
1.41% postgres postgres [.] XLogInsert
1.36% postgres libc-2.11.3.so [.] _int_malloc
1.31% postgres postgres [.] LWLockRelease
1.09% postgres libc-2.11.3.so [.] __GI_memcpy
0.89% postgres postgres [.] _bt_checkkeys
0.82% postgres libc-2.11.3.so [.] __strncpy_ssse3
0.81% postgres postgres [.] palloc
0.81% postgres postgres [.] fmgr_info_cxt_security
0.76% postgres postgres [.] equal
0.75% postgres postgres [.] s_lock
0.73% postgres postgres [.] heap_hot_search_buffer

>From: Amit Kapila [mailto:amit(dot)kapila16(at)gmail(dot)com]
>Sent: Tuesday, September 02, 2014 10:44 PM
>To: Xiaoyulei
>Cc: pgsql-hackers(at)postgresql(dot)org
>Subject: Re: 答复: [HACKERS] why after increase the hash table
>partitions, TPMC decrease
>
>On Tue, Sep 2, 2014 at 5:20 PM, Xiaoyulei <xiaoyulei(at)huawei(dot)com> wrote:
>>
>> I already modified MAX_SIMUL_LWLOCKS to make sure it is enough.
>
>Okay.
>
>>  
>>
>> Total RAM is 130G, and I set shared_buffers 16G, CPU and IO is not full. 50% CPUs are idle.
>
>As far as I understand, benchmarkSQL measures an OLTP workload
>performance which means it contains mix of reads and writes, now I am
>not sure how you have identified that increasing buffer partitions can
>improve the performance.
>Have you used any profiling?
>
>> So I think maybe pg is blocked by some place in itself.
>
>Yeah, there's another lock BufFreelistLock which is a major cause of
>contention in buffer allocation and for which already work is in
>progress for 9.5.  However as mentioned previously, that will be useful
>mainly for Read only loads.
>
>
>
>
>With Regards,
>Amit Kapila.
>EnterpriseDB: http://www.enterprisedb.com