Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

On Tue, Dec 19, 2017 at 5:52 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Mon, Dec 18, 2017 at 2:04 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> On Sun, Dec 17, 2017 at 12:27 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>
>>> I have to admit that result is surprising to me.
>>
>> I think the environment I used for performance measurement did not
>> have enough resources. I will do the same benchmark on an another
>> environment to see if it was a valid result, and will share it.
>>
> I did performance measurement on an different environment where has 4
> cores and physically separated two disk volumes. Also I've change the
> benchmarking so that COPYs load only 300 integer tuples which are not
> fit within single page, and changed tables to unlogged tables to
> observe the overhead of locking/unlocking relext locks.

I ran same test as asked by Robert it was just an extension of tests
[1] pointed by Amit Kapila,

Machine : cthulhu
------------------------
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 128
On-line CPU(s) list: 0-127
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 8
NUMA node(s): 8
Vendor ID: GenuineIntel
CPU family: 6
Model: 47
Model name: Intel(R) Xeon(R) CPU E7- 8830 @ 2.13GHz
Stepping: 2
CPU MHz: 1064.000
CPU max MHz: 2129.0000
CPU min MHz: 1064.0000
BogoMIPS: 4266.59
Virtualization: VT-x
Hypervisor vendor: vertical
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 24576K
NUMA node0 CPU(s): 0-7,64-71
NUMA node1 CPU(s): 8-15,72-79
NUMA node2 CPU(s): 16-23,80-87
NUMA node3 CPU(s): 24-31,88-95
NUMA node4 CPU(s): 32-39,96-103
NUMA node5 CPU(s): 40-47,104-111
NUMA node6 CPU(s): 48-55,112-119
NUMA node7 CPU(s): 56-63,120-127

It has 2 discs with different filesytem as below
/dev/mapper/vg_mag-data2 ext4 5.1T 3.6T 1.2T 76% /mnt/data-mag2
/dev/mapper/vg_mag-data1 xfs 5.1T 1.6T 3.6T 31% /mnt/data-mag

I have created 2 tables each one on above filesystem.

test_size_copy.sh --> automated script to run copy test.
copy_script1, copy_script2 -> copy pg_bench script's used by
test_size_copy.sh to load to 2 different tables.

To run above copy_scripts in parallel I have run it with equal weights as below.
./pgbench -c $threads -j $threads -f copy_script1(at)1 -f copy_script2(at)1
-T 120 postgres >> test_results.txt

Results :
-----------

Clients HEAD-TPS
--------- ---------------
1 84.460734
2 121.359035
4 175.886335
8 268.764828
16 369.996667
32 439.032756
64 482.185392

Clients N_RELEXTLOCK_ENTS = 1024 %diff with DEAD
----------------------------------------------------------------------------------
1 87.165777 3.20272258112273
2 131.094037 8.02165409439848
4 181.667104 3.2866504381935
8 267.412856 -0.503031594595423
16 376.118671 1.65461058058666
32 460.756357 4.94805927419228
64 492.723975 2.18558736428913

Not much of an improvement from HEAD

Clients N_RELEXTLOCK_ENTS = 1 %diff with HEAD
-----------------------------------------------------------------------------
1 86.288574 2.16412990206786
2 131.398667 8.27266960387414
4 168.681079 -4.09654109854526
8 245.841999 -8.52895416806549
16 321.972147 -12.9797169226933
32 375.783299 -14.4065462395703
64 360.134531 -25.3120196142317

So in case of N_RELEXTLOCK_ENTS = 1 we can see regression as high 25%. ?

[1]https://www.postgresql.org/message-id/CAFiTN-tkX6gs-jL8VrPxg6OG9VUAKnObUq7r7pWQqASzdF5OwA%40mail.gmail.com
--
Thanks and Regards
Mithun C Y
EnterpriseDB: http://www.enterprisedb.com