Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager - Mailing list pgsql-hackers

On Tue, Dec 19, 2017 at 5:52 AM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> On Mon, Dec 18, 2017 at 2:04 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>> On Sun, Dec 17, 2017 at 12:27 PM, Robert Haas <robertmhaas@gmail.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@1 -f copy_script2@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