RE: 答复: [HACKERS] 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. Attachment is perf data file.


     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