[MDEV-18856] Benchmark range locking - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Done
Fix Version/s: N/A
Component/s: Storage Engine - RocksDB
Labels:
- benchmarking

Description

Benchmark range locking vs point locking.

Benchmarking script: https://github.com/spetrunia/range-locking-benchmark

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

screenshot-1.png
71 kB
2019-03-12 21:07
screenshot-2.png
57 kB
2019-03-29 08:55
screenshot-3.png
61 kB
2019-03-29 09:04

Issue Links

relates to

MDEV-15603 Gap Lock support in MyRocks

Stalled

MDEV-21186 Benchmark range locking - nov-dec 2019

Closed

MDEV-25161 Benchmark range locking - 2021

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia added a comment - 2019-03-08 08:51 - edited

Results:

SERVER_DIR=mysql-5.6-orig
SYSBENCH_TEST=oltp_read_write.lua
Threads, QPS
1 6104.14
5 19847.76
10 30293.54
20 33025.06
40 34143.55

SERVER_DIR=mysql-5.6-range-locking
SYSBENCH_TEST=oltp_read_write.lua
Threads, QPS
1 6080.82
5 19554.02
10 30053.92
20 32346.45
40 33414.87

Computing the slowdown:

Threads	orig	range-locking	Slowdown, %

1	6104.14	6080.82	0.38

5	19847.76	19554.02	1.48

10	30293.54	30053.92	0.79

20	33025.06	32346.45	2.05

40	34143.55	33414.87	2.13

The machine is AWS c5.2xlarge. 8 user-visible CPUs.

Sergei Petrunia added a comment - 2019-03-08 08:51 - edited Results: SERVER_DIR=mysql-5.6-orig SYSBENCH_TEST=oltp_read_write.lua Threads, QPS 1 6104.14 5 19847.76 10 30293.54 20 33025.06 40 34143.55 SERVER_DIR=mysql-5.6-range-locking SYSBENCH_TEST=oltp_read_write.lua Threads, QPS 1 6080.82 5 19554.02 10 30053.92 20 32346.45 40 33414.87 Computing the slowdown: Threads orig range-locking Slowdown, % 1 6104.14 6080.82 0.38 5 19847.76 19554.02 1.48 10 30293.54 30053.92 0.79 20 33025.06 32346.45 2.05 40 34143.55 33414.87 2.13 The machine is AWS c5.2xlarge. 8 user-visible CPUs.

Sergei Petrunia added a comment - 2019-03-08 16:51

A re-run on t480 laptop (Intel Core i7-8550U, 8 CPUs)

SERVER_DIR=mysql-5.6-orig
SYSBENCH_TEST=oltp_read_write.lua
Threads, QPS
1 1747.17
5 9350.29
10 15083.44
20 16130.49
40 23688.07

SERVER_DIR=mysql-5.6-range-locking
SYSBENCH_TEST=oltp_read_write.lua
Threads, QPS
1 1757.39
5 9038.06
10 15591.72
20 16592.19
40 23555.30

Threads	orig	range-locking	Slowdown, %

1	 1747.17	 1757.39	-0.58

5	 9350.29	 9038.06	3.34

10	15083.44	15591.72	-3.37

20	16130.49	16592.19	-2.86

40	23688.07	 23555.3	0.56

There was even a speedup sometimes.
The question is, is this really a "locking system bound" workload, and if there
is a workload that's more bound by the locking system.

Sergei Petrunia added a comment - 2019-03-08 16:51 A re-run on t480 laptop (Intel Core i7-8550U, 8 CPUs) SERVER_DIR=mysql-5.6-orig SYSBENCH_TEST=oltp_read_write.lua Threads, QPS 1 1747.17 5 9350.29 10 15083.44 20 16130.49 40 23688.07 SERVER_DIR=mysql-5.6-range-locking SYSBENCH_TEST=oltp_read_write.lua Threads, QPS 1 1757.39 5 9038.06 10 15591.72 20 16592.19 40 23555.30 Threads orig range-locking Slowdown, % 1 1747.17 1757.39 -0.58 5 9350.29 9038.06 3.34 10 15083.44 15591.72 -3.37 20 16130.49 16592.19 -2.86 40 23688.07 23555.3 0.56 There was even a speedup sometimes. The question is, is this really a "locking system bound" workload, and if there is a workload that's more bound by the locking system.

Sergei Petrunia added a comment - 2019-03-09 10:08

AWS instance, oltp_write_only test.

SERVER_DIR=mysql-5.6-orig
SYSBENCH_TEST=oltp_write_only.lua
Threads, QPS
1 3443.60
5 9592.55
10 18218.86
20 31140.55
40 47820.60

SERVER_DIR=mysql-5.6-range-locking
SYSBENCH_TEST=oltp_write_only.lua
Threads, QPS
1 3422.26
5 9673.65
10 18238.36
20 31644.35
40 49493.06

Threads	orig	range locking	Slowdown%

1	  3443.6	   3422.26	0.62

5	 9592.55	 9673.65	-0.01

10	18218.86	18238.36	0.00

20	31140.55	31644.35	-0.02

40	 47820.6	49493.06	-0.03

Sergei Petrunia added a comment - 2019-03-09 10:08 AWS instance, oltp_write_only test. SERVER_DIR=mysql-5.6-orig SYSBENCH_TEST=oltp_write_only.lua Threads, QPS 1 3443.60 5 9592.55 10 18218.86 20 31140.55 40 47820.60 SERVER_DIR=mysql-5.6-range-locking SYSBENCH_TEST=oltp_write_only.lua Threads, QPS 1 3422.26 5 9673.65 10 18238.36 20 31644.35 40 49493.06 Threads orig range locking Slowdown% 1 3443.6 3422.26 0.62 5 9592.55 9673.65 -0.01 10 18218.86 18238.36 0.00 20 31140.55 31644.35 -0.02 40 47820.6 49493.06 -0.03

Sergei Petrunia added a comment - 2019-03-10 11:03

These test runs might be not locking-bound, though.

Sergei Petrunia added a comment - 2019-03-10 11:03 These test runs might be not locking-bound, though.

Sergei Petrunia added a comment - 2019-03-10 14:28

Reduced the table size to 1000 rows (to get more contention), still using the oltp_write_only.lua, I get:

diff --git a/run-test.sh b/run-test.sh

index 6a43097..6a6292d 100755

--- a/run-test.sh

+++ b/run-test.sh

@@ -93,8 +93,8 @@ mkdir -p $RESULT_DIR

 SYSBENCH_ARGS=" --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root \

   --mysql-storage-engine=rocksdb \

   --time=60 \

-  /usr/share/sysbench/oltp_read_write.lua --table-size=1000000"

-SYSBENCH_TEST="oltp_read_write.lua"

+  /usr/share/sysbench/oltp_write_only.lua --table-size=1000"

+SYSBENCH_TEST="oltp_write_only.lua"

ubuntu@ip-172-31-19-114:~/range-locking-benchmark$ ./summarize-result.sh results/06-oltp_write_only-range_locking-smalltable/
SERVER_DIR=mysql-5.6-range-locking
SYSBENCH_TEST=oltp_write_only.lua
Threads, QPS
1 3510.12
5 4714.45
10 4700.50
20 4688.14
40 4889.91

ubuntu@ip-172-31-19-114:~/range-locking-benchmark$ ./summarize-result.sh results/07-oltp_write_only-orig-smalltable/
SERVER_DIR=mysql-5.6-orig
SYSBENCH_TEST=oltp_write_only.lua
Threads, QPS
1 3487.64
5 154.53
10 81.13
20 60.40
40 73.83

Sergei Petrunia added a comment - 2019-03-10 14:28 Reduced the table size to 1000 rows (to get more contention), still using the oltp_write_only.lua , I get: diff --git a/run-test.sh b/run-test.sh index 6a43097..6a6292d 100755 --- a/run-test.sh +++ b/run-test.sh @@ -93,8 +93,8 @@ mkdir -p $RESULT_DIR SYSBENCH_ARGS=" --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root \ --mysql-storage-engine=rocksdb \ --time=60 \ - /usr/share/sysbench/oltp_read_write.lua --table-size=1000000" -SYSBENCH_TEST="oltp_read_write.lua" + /usr/share/sysbench/oltp_write_only.lua --table-size=1000" +SYSBENCH_TEST="oltp_write_only.lua" ubuntu@ip-172-31-19-114:~/range-locking-benchmark$ ./summarize-result.sh results/06-oltp_write_only-range_locking-smalltable/ SERVER_DIR=mysql-5.6-range-locking SYSBENCH_TEST=oltp_write_only.lua Threads, QPS 1 3510.12 5 4714.45 10 4700.50 20 4688.14 40 4889.91 ubuntu@ip-172-31-19-114:~/range-locking-benchmark$ ./summarize-result.sh results/07-oltp_write_only-orig-smalltable/ SERVER_DIR=mysql-5.6-orig SYSBENCH_TEST=oltp_write_only.lua Threads, QPS 1 3487.64 5 154.53 10 81.13 20 60.40 40 73.83

Sergei Petrunia added a comment - 2019-03-12 21:07

A bigger machine - c5.9xlarge, 36 vCPUs:

SERVER_DIR=mysql-5.6-orig
SYSBENCH_TEST=oltp_write_only.lua
Threads, QPS
1 22525.26
5 100552.68
10 172812.39
20 198175.42
40 218914.92
60 222541.10
80 221685.74
100 218005.72

ubuntu@ip-172-31-28-61:~/range-locking-benchmark$ ./summarize-result.sh 02-9xlarge-oltpw-range-locking
SERVER_DIR=mysql-5.6-range-locking
SYSBENCH_TEST=oltp_write_only.lua
Threads, QPS
1 23522.95
5 92144.06
10 140631.97
20 122340.31
40 114454.20
60 110822.75
80 108832.36
100 108108.64

Sergei Petrunia added a comment - 2019-03-12 21:07 A bigger machine - c5.9xlarge, 36 vCPUs: SERVER_DIR=mysql-5.6-orig SYSBENCH_TEST=oltp_write_only.lua Threads, QPS 1 22525.26 5 100552.68 10 172812.39 20 198175.42 40 218914.92 60 222541.10 80 221685.74 100 218005.72 ubuntu@ip-172-31-28-61:~/range-locking-benchmark$ ./summarize-result.sh 02-9xlarge-oltpw-range-locking SERVER_DIR=mysql-5.6-range-locking SYSBENCH_TEST=oltp_write_only.lua Threads, QPS 1 23522.95 5 92144.06 10 140631.97 20 122340.31 40 114454.20 60 110822.75 80 108832.36 100 108108.64

Sergei Petrunia added a comment - 2019-03-29 08:56 - edited

(Posting results from March, 13th) The tests were run on the c5.9xlarge instance.
A possible reason why range locking performance is worse could be that it is simply doing more locking. For example, it acquires locks on secondary indexes. Re-running the sysbench test but without secondary indexes:

orig range-locking no-secondary, orig no-secondary, range-locking
1 22525.26 23522.95 23449.64 24499.37
5 100552.68 92144.06 110104.93 105632.95
10 172812.39 140631.97 199158.13 172619.56
20 198175.42 122340.31 264702.31 171823.65
40 218914.92 114454.2 325606.21 154786.56
60 222541.1 110822.75 345715.22 150504.49
80 221685.74 108832.36 346243.2 150345.16
100 218005.72 108108.64 355985.66 152040.97

QPS is higher without the secondary indexes in both cases. However the difference between range locking and point locking only grows larger.

Sergei Petrunia added a comment - 2019-03-29 08:56 - edited (Posting results from March, 13th) The tests were run on the c5.9xlarge instance. A possible reason why range locking performance is worse could be that it is simply doing more locking. For example, it acquires locks on secondary indexes. Re-running the sysbench test but without secondary indexes: orig range-locking no-secondary, orig no-secondary, range-locking 1 22525.26 23522.95 23449.64 24499.37 5 100552.68 92144.06 110104.93 105632.95 10 172812.39 140631.97 199158.13 172619.56 20 198175.42 122340.31 264702.31 171823.65 40 218914.92 114454.2 325606.21 154786.56 60 222541.1 110822.75 345715.22 150504.49 80 221685.74 108832.36 346243.2 150345.16 100 218005.72 108108.64 355985.66 152040.97 QPS is higher without the secondary indexes in both cases. However the difference between range locking and point locking only grows larger.

Sergei Petrunia added a comment - 2019-03-29 09:08

Modified the code to add a new mode: range locking subsystem is used, but we only take point locks.

orig range-locking no-secondary, orig no-secondary, range-locking range-locking-force-point
1 22525.26 23522.95 23449.64 24499.37 24397.8
5 100552.68 92144.06 110104.93 105632.95 105774.04
10 172812.39 140631.97 199158.13 172619.56 172205.06
20 198175.42 122340.31 264702.31 171823.65 171822.15
40 218914.92 114454.2 325606.21 154786.56 154483.92
60 222541.1 110822.75 345715.22 150504.49 150290.71
80 221685.74 108832.36 346243.2 150345.16 150074.48
100 218005.72 108108.64 355985.66 152040.97 152179.49

the performance in this mode was the same as with range locking. The conclusion: the cause of worse performance is that the range locking system by itself has a higher overhead (or, a lower concurrency). The fact that range locking mode acquires more locks does not seem to play a big role.

Sergei Petrunia added a comment - 2019-03-29 09:08 Modified the code to add a new mode: range locking subsystem is used, but we only take point locks. orig range-locking no-secondary, orig no-secondary, range-locking range-locking-force-point 1 22525.26 23522.95 23449.64 24499.37 24397.8 5 100552.68 92144.06 110104.93 105632.95 105774.04 10 172812.39 140631.97 199158.13 172619.56 172205.06 20 198175.42 122340.31 264702.31 171823.65 171822.15 40 218914.92 114454.2 325606.21 154786.56 154483.92 60 222541.1 110822.75 345715.22 150504.49 150290.71 80 221685.74 108832.36 346243.2 150345.16 150074.48 100 218005.72 108108.64 355985.66 152040.97 152179.49 the performance in this mode was the same as with range locking. The conclusion: the cause of worse performance is that the range locking system by itself has a higher overhead (or, a lower concurrency). The fact that range locking mode acquires more locks does not seem to play a big role.

People

Assignee:: Sergei Petrunia

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2019-03-08 08:48

Updated:: 2023-10-19 18:44

Resolved:: 2023-10-19 18:44

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration