Sergei Petrunia added a comment - 2020-01-07 10:12 - edited 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.

Sergei Petrunia added a comment - 2020-04-04 20:17 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

Sergei Petrunia added a comment - 2020-04-06 09:34 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' );

Sergei Petrunia added a comment - 2020-04-06 09:38 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).

Sergei Petrunia added a comment - 2020-04-06 12:28 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