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
isin 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.kapila16@gmail.com]
>Sent: Tuesday, September 02, 2014 10:44 PM
>To: Xiaoyulei
>Cc: pgsql-hackers@postgresql.org
>Subject: Re: 答复: [HACKERS] why after increase the hash table 
>partitions, TPMC decrease
>
>On Tue, Sep 2, 2014 at 5:20 PM, Xiaoyulei <xiaoyulei@huawei.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