1--source include/have_rocksdb.inc 2 3# only SingleDelete increases 4CREATE TABLE t1 (id INT, value int, PRIMARY KEY (id), INDEX (value)) ENGINE=ROCKSDB; 5INSERT INTO t1 VALUES (1,1); 6select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 7select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 8--disable_query_log 9let $i = 1; 10while ($i <= 10000) { 11 let $update = UPDATE t1 SET value=value+1 WHERE value=$i; 12 inc $i; 13 eval $update; 14} 15--enable_query_log 16optimize table t1; 17select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 18select case when variable_value-@d < 10 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 19 20 21# both SingleDelete and Delete increases 22CREATE TABLE t2 (id INT, value int, PRIMARY KEY (id), INDEX (value)) ENGINE=ROCKSDB; 23INSERT INTO t2 VALUES (1,1); 24select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 25select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 26--disable_query_log 27let $i = 1; 28while ($i <= 10000) { 29 let $update = UPDATE t2 SET id=id+1 WHERE id=$i; 30 inc $i; 31 eval $update; 32} 33--enable_query_log 34optimize table t2; 35select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 36select case when variable_value-@d > 9000 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 37 38# only Delete increases 39CREATE TABLE t3 (id INT, value int, PRIMARY KEY (id)) ENGINE=ROCKSDB; 40INSERT INTO t3 VALUES (1,1); 41select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 42select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 43--disable_query_log 44let $i = 1; 45while ($i <= 10000) { 46 let $update = UPDATE t3 SET id=id+1 WHERE id=$i; 47 inc $i; 48 eval $update; 49} 50--enable_query_log 51optimize table t3; 52select case when variable_value-@s = 0 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 53select case when variable_value-@d > 9000 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 54 55# only SingleDelete increases 56CREATE TABLE t4 (id INT, PRIMARY KEY (id)) ENGINE=ROCKSDB; 57INSERT INTO t4 VALUES (1); 58select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 59select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 60--disable_query_log 61let $i = 1; 62while ($i <= 10000) { 63 let $update = UPDATE t4 SET id=id+1 WHERE id=$i; 64 inc $i; 65 eval $update; 66} 67--enable_query_log 68optimize table t4; 69select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 70select case when variable_value-@d < 10 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 71 72# only SingleDelete increases 73CREATE TABLE t5 (id1 INT, id2 INT, PRIMARY KEY (id1, id2), INDEX(id2)) ENGINE=ROCKSDB; 74INSERT INTO t5 VALUES (1, 1); 75select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 76select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 77--disable_query_log 78let $i = 1; 79while ($i <= 10000) { 80 let $update = UPDATE t5 SET id1=id1+1 WHERE id1=$i; 81 inc $i; 82 eval $update; 83} 84--enable_query_log 85optimize table t5; 86select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete'; 87select case when variable_value-@d < 10 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete'; 88 89# SingleDelete used for PK. Verify old PK is always deleted. 90CREATE TABLE t6 ( 91 pk VARCHAR(64) COLLATE latin1_swedish_ci PRIMARY KEY 92) ENGINE=RocksDB; 93INSERT INTO t6 VALUES ('a'); 94SET GLOBAL rocksdb_force_flush_memtable_now=1; 95SELECT * FROM t6; 96UPDATE t6 SET pk='A' WHERE pk='a'; 97SELECT * FROM t6; 98DELETE FROM t6 where pk='A'; 99--echo SELECT should return nothing; 100SELECT * FROM t6; 101SET GLOBAL rocksdb_force_flush_memtable_now=1; 102--echo SELECT should return nothing; 103SELECT * FROM t6; 104 105DROP TABLE t1, t2, t3, t4, t5, t6; 106