Re: Patch: fix lock contention for HASHHDR.mutex

On Tue, Jan 12, 2016 at 1:50 PM, Aleksander Alekseev <
a(dot)alekseev(at)postgrespro(dot)ru> wrote:

>
> increasing number of lock partitions (see columns "no locks", "lwlock"
> and "spinlock array"). Previously it couldn't help us (see "master"
> column) because of a bottleneck.
>
> If you have any other questions regarding this patch please don't
> hesitate to ask.
>

I have done some performance bench marking for this
patch.(dynahash-optimization-v10-step1.patch)

Machine Detail:
cpu : POWER8
cores: 24 (192 with HT)

Non Default Parameter:
------------------------
Shared Buffer= 30GB
max_wal_size= 10GB
max_connections=500

Test1:
*schema.sql* and *pgbench.sql* are same files which Aleksander has attached
in first mail of the thread.

psql -d postgres -f schema.sql
pgbench -c$ -j$ -f pgbench.sql postgres

client base patch
1 145 145
2 262 258
4 453 472
8 749 732
16 1114 1128
32 1727 1789
64 2729 2793
128 3534 3520

With this test i did not see any improvement, though i think it should
improve the performance, do you have any suggestion to see the results same
as yours ?

I have also dump stacks using some script and I have observed many stacks
dumps as you mentioned in initial thread. And after patch, I found that
number of lock waiting stacks are reduced.

Stack Dump:
-------------------
#0 0x00007f25842899a7 in semop () from /lib64/libc.so.6
#1 0x00000000007116d0 in PGSemaphoreLock (sema=0x7f257cb170d8) at
pg_sema.c:387
#2 0x000000000078955f in LWLockAcquire (lock=0x7f247698a980,
mode=LW_EXCLUSIVE) at lwlock.c:1028
#3 0x00000000007804c4 in LockAcquireExtended
#4 0x000000000077fe91 in LockAcquire
#5 0x000000000077e862 in LockRelationOid
#6 0x000000000053bc7b in find_inheritance_children
#7 0x000000000053bd4f in find_all_inheritors
#8 0x00000000006fc0a2 in expand_inherited_rtentry
#9 0x00000000006fbf91 in expand_inherited_tables

I have tried to analyze using perf also, I can see that amount of time
taken in hash_search_with_hash_value is reduced from 3.86%(master) to
1.72%(patch).

I have plan to do further investigation, in different scenarios of dynahash.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com