Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Done
-
None
Description
Start with re-running the benchmark from MDEV-18856
Attachments
Issue Links
- relates to
-
-
- Stalled
-
-
-
- Closed
-
-
MDEV-21314 Range Locking: individual rows are locked when scanning PK
-
- Open
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Start with re-running the benchmark from |
| Attachment | results.2.tgz [ 49561 ] |
| Attachment | screenshot-1.png [ 49562 ] |
$ ./summarize-result.sh 01-orig
|
SERVER_DIR=mysql-5.6-orig
|
SYSBENCH_TEST=oltp_write_only.lua
|
Threads, QPS
|
1 2906.45
|
5 8066.79
|
10 15603.26
|
20 29148.83
|
40 48499.77
|
60 64196.04
|
80 77988.82
|
100 86106.47
|
$ ./summarize-result.sh 02-range-locking
|
SERVER_DIR=mysql-5.6-range-locking
|
SYSBENCH_TEST=oltp_write_only.lua
|
Threads, QPS
|
1 2763.86
|
5 8075.20
|
10 15812.50
|
20 28855.33
|
40 47700.14
|
60 62971.66
|
80 73209.33
|
100 77859.95
|
| Attachment | screenshot-2.png [ 49563 ] |
Re-running the same test, with datadirs on /dev/shm:
SERVER_DIR=mysql-5.6-orig
|
SYSBENCH_TEST=oltp_write_only.lua
|
Threads, QPS
|
1 21619.67
|
5 99452.12
|
10 163245.53
|
20 186656.49
|
40 210683.79
|
60 212242.46
|
80 211478.62
|
100 212492.93
|
SERVER_DIR=mysql-5.6-range-locking
|
SYSBENCH_TEST=oltp_write_only.lua
|
Threads, QPS
|
1 21759.10
|
5 83961.74
|
10 126674.09
|
20 113256.19
|
40 105837.74
|
60 102231.74
|
80 99290.24
|
100 96958.58
|
Now, the results are similar to ones I got in MDEV-18856
| Summary | Benchmark range locking - 2019 | Benchmark range locking - nov 2019 |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-21314 [ MDEV-21314 ] |
| Summary | Benchmark range locking - nov 2019 | Benchmark range locking - nov-dec 2019 |
| Attachment | screenshot-mdev21314.png [ 49688 ] |
See also MDEV-21314. It improves range locking benchmark results by 30%:
| Attachment | screenshot-3.png [ 49689 ] |
Another benchmark run: use the code with MDEV-21314 patch, also use 4 tables, and put each table_name.index_name into a separate column family (Sysbench tables have one PK and one SK):
https://github.com/spetrunia/range-locking-benchmark/blob/master/make-4-cfs.sql
The idea is that in the current code, each column family has its own locktree. Will this "sharding" help range locking?
SERVER_DIR=mysql-5.6-orig
|
SYSBENCH_TEST=oltp_write_only.lua
|
Threads, QPS
|
1 20619.83
|
5 90130.52
|
10 141258.78
|
20 148354.29
|
40 157550.24
|
60 154532.01
|
80 154797.41
|
100 154561.62
|
SERVER_DIR=mysql-5.6-range-locking
|
SYSBENCH_TEST=oltp_write_only.lua
|
Threads, QPS
|
1 21107.55
|
5 88956.27
|
10 136591.85
|
20 143490.58
|
40 152266.30
|
60 146737.87
|
80 149700.89
|
100 152439.42
|
| Attachment | results-dec14.zip [ 49690 ] |
| Attachment | screenshot-4.png [ 50216 ] |
Re-ran the test with the current tree (fix for MDEV-21314 is now pushed)
Threads orig range-locking X/Y*100
|
1 21773.96 22157.8 101.76
|
5 96915.43 91853.83 94.78
|
10 164660.49 141687.23 86.05
|
20 189349.62 153491.88 81.06
|
40 212023.8 144322.75 68.07
|
60 208919.2 140069.32 67.04
|
80 213477.57 137555.59 64.44
|
100 212903.14 136357.16 64.05
|
Using range locking gives a 36% slowdown.
^ 
Results for two tables, each index in its own Column Family:
Threads orig-2t2cf rl-2t2cf X/Y*100
|
1 21064.12 21310.59 101.17
|
5 94306.66 89324.75 94.72
|
10 146055.7 141178.98 96.66
|
20 158258.87 157948.36 99.80
|
40 176671 173845.97 98.40
|
60 176492.18 168174.68 95.29
|
80 175106.98 172390.58 98.45
|
100 174936.57 171553.95 98.07
|
That is, using range locking gives a 2% slowdown.
| Attachment | image-2020-01-07-13-12-36-597.png [ 50217 ] |
| Fixing Priority | 250 |
| Attachment | no-secondary-key.png [ 51069 ] |
Added to benchmark script an option to drop the secondary index before doing the benchmark run.
Results from AWS c5.9xlarge:
Threads 5.6-orig 5.6-range-locking
|
1 23365.4 23867.27
|
5 107743.89 103913.59
|
10 192588.82 179823.72
|
20 242046.75 218521.9
|
40 311548.42 221593.97
|
60 338349.25 219302.88
|
80 333746.94 218607.08
|
100 349949.47 218468.91
|
Threads Perf%
|
1 102.15
|
5 96.44
|
10 93.37
|
20 90.28
|
40 71.13
|
60 64.82
|
80 65.50
|
100 62.43
|
| Link | This issue relates to MDEV-15603 [ MDEV-15603 ] |
A transaction from sysbench oltp_write_only looks like this:
BEGIN; |
UPDATE sbtest1 SET k=k+1 WHERE id=559022; |
UPDATE sbtest1 SET c='string_const5' WHERE id=503909; |
DELETE FROM sbtest1 WHERE id=498658; |
INSERT INTO sbtest1 (id, k, c, pad) |
VALUES (498658, 500692, 'string-const', 'string-const'); |
COMMIT; |
(note that the INSERT inserts the record with the same pk that DELETE has removed).
Number of locks taken:
With point locking, it acquires 3 locks (the INSERT doesn't acquire a lock because the DELETE has already taken it)
With range locking, it acquires 8 locks:
-- 3 locks (pk lock, old sk lock. new sk lock)
|
UPDATE sbtest1 SET k=k+1 WHERE id=559022; |
-- 1 lock on pk (no locks on because it is not changed)
|
UPDATE sbtest1 SET c='string_const7' WHERE id=503909; |
-- 2 locks: pk lock + sk lock
|
DELETE FROM sbtest1 WHERE id=498658; |
-- 2 locks: pk lock + sk lock
|
INSERT INTO sbtest1 (id, k, c, pad) |
VALUES (498658, 500692, 'string-const', 'string-const'); |
If the table doesn't have a secondary key on k:
Point locking will still acquire 3 locks.
Range locking will acquire 4 locks (each statement will take a lock on PK, INSERT will also acquire it, this is a difference from point locking).
| Attachment | oltp-update-non-index.png [ 51077 ] |
Sysbench's oltp_update_non_index.lua test does just one UPDATE... WHERE id=... per transaction. This means both point and range locking will only take one lock per transaction.
Threads point-locking range-locking %age
|
1 15,143.92 15,499.32 102.35
|
5 62,079.59 62,454.53 100.60
|
10 92,648.59 91,622.05 98.89
|
20 118,375.89 104,903.19 88.62
|
40 141,924.48 120,886.89 85.18
|
60 157,484.78 123,580.78 78.47
|
80 164,006.80 123,861.08 75.52
|
100 167,474.16 122,780.59 73.31
|
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 101508 ] | MariaDB v4 [ 131200 ] |
| Resolution | Done [ 10200 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
Start with re-running the benchmark from
MDEV-18856.