Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Done
Description
Benchmark range locking vs point locking.
Benchmarking script: https://github.com/spetrunia/range-locking-benchmark
Attachments
Issue Links
- relates to
-
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
Activity
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.
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
|
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
|
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
|
(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.
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.
Results:
SERVER_DIR=mysql-5.6-origSYSBENCH_TEST=oltp_read_write.luaThreads, QPS1 6104.145 19847.7610 30293.5420 33025.0640 34143.55SERVER_DIR=mysql-5.6-range-lockingSYSBENCH_TEST=oltp_read_write.luaThreads, QPS1 6080.825 19554.0210 30053.9220 32346.4540 33414.87Computing the slowdown:
Threads orig range-locking Slowdown, %1 6104.14 6080.82 0.385 19847.76 19554.02 1.4810 30293.54 30053.92 0.7920 33025.06 32346.45 2.0540 34143.55 33414.87 2.13The machine is AWS c5.2xlarge. 8 user-visible CPUs.