revision-id: 441a8905fa0c94b7c8b9a996c799b460553c1d66 (fb-prod201903-174-g441a8905fa0) parent(s): 2527cd79440e2145eca25c7f8735ad1c8b8c4f3b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-10-28 19:56:59 +0300 message: Issue #790, MyRocks/MRR: properly increment per-table read counters - Properly increment I_S.table_statistics.rows_* counters - Do release the PinnableSlice that's holding the retrieved record as soon as we don't need it (before, we released all records in ha_rocksdb::mrr_free_rows(). Now, we only release them there if the mrr scan was not finished). - Clean up in testcases --- mysql-test/suite/rocksdb/r/rocksdb_mrr.result | 148 +++++++++++++++++++++++++- mysql-test/suite/rocksdb/t/rocksdb_mrr.test | 92 ++++++++++++++-- mysql-test/suite/rocksdb/t/rocksdb_mrr2.test | 4 - storage/rocksdb/ha_rocksdb.cc | 23 +++- storage/rocksdb/ha_rocksdb.h | 1 + 5 files changed, 254 insertions(+), 14 deletions(-) diff --git a/mysql-test/suite/rocksdb/r/rocksdb_mrr.result b/mysql-test/suite/rocksdb/r/rocksdb_mrr.result index 6aa2ff4dc23..465966c5b6e 100644 --- a/mysql-test/suite/rocksdb/r/rocksdb_mrr.result +++ b/mysql-test/suite/rocksdb/r/rocksdb_mrr.result @@ -1,4 +1,3 @@ -drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int); @@ -241,8 +240,151 @@ ROCKSDB_NUMBER_MULTIGET_BYTES_READ 370 ROCKSDB_NUMBER_MULTIGET_GET 1 ROCKSDB_NUMBER_MULTIGET_KEYS_READ 10 drop table t11,t12; -# Same as above but for MRR (not BKA): -# This will not use MRR: +# +# Check the userstat counters +# +show variables like '%user_table_stat%'; +Variable_name Value +user_table_stats_control OFF +set optimizer_switch='mrr=off'; +flush statistics; +select * from t3,t0 where t3.col1=t0.a; +pk1 pk2 col1 filler a +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 5 5 5 5 +6 6 6 6 6 +7 7 7 7 7 +8 8 8 8 8 +9 9 9 9 9 +# COUNTERS FOR: mrr=OFF, secondary index lookups +select +rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from +information_schema.table_statistics +where table_name = 't3'; +rows_inserted 0 +rows_updated 0 +rows_deleted 0 +rows_read 10 +rows_requested 20 +set optimizer_switch='mrr=on'; +flush statistics; +select * from t3,t0 where t3.col1=t0.a; +pk1 pk2 col1 filler a +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 5 5 5 5 +6 6 6 6 6 +7 7 7 7 7 +8 8 8 8 8 +9 9 9 9 9 +# COUNTERS FOR: mrr=ON, secondary index lookups +select +rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from +information_schema.table_statistics +where table_name = 't3'; +rows_inserted 0 +rows_updated 0 +rows_deleted 0 +rows_read 10 +rows_requested 20 +set optimizer_switch='mrr=off'; +flush statistics; +explain +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t0.a,test.t0.a 1 NULL +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; +pk1 pk2 col1 filler a +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 5 5 5 5 +6 6 6 6 6 +7 7 7 7 7 +8 8 8 8 8 +9 9 9 9 9 +# COUNTERS FOR: mrr=OFF, primary index lookups +select +rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from +information_schema.table_statistics +where table_name = 't3'; +rows_inserted 0 +rows_updated 0 +rows_deleted 0 +rows_read 10 +rows_requested 10 +set optimizer_switch='mrr=on'; +flush statistics; +explain +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t0.a,test.t0.a 1 Using join buffer (Batched Key Access) +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; +pk1 pk2 col1 filler a +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 5 5 5 5 +6 6 6 6 6 +7 7 7 7 7 +8 8 8 8 8 +9 9 9 9 9 +# COUNTERS FOR: mrr=ON, primary index lookups +select +rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from +information_schema.table_statistics +where table_name = 't3'; +rows_inserted 0 +rows_updated 0 +rows_deleted 0 +rows_read 10 +rows_requested 10 +# +# Check how the counters are incremented when SQL +# layer doesn't read all of the MultiGet results +# +create table t20 (a int); +insert into t20 values (1); +set global rocksdb_force_flush_memtable_now=1; +explain +select a, a+20 in (select t2.filler from t2,t0 where t2.pk=t0.a+20) from t20; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t20 ALL NULL NULL NULL NULL 1 NULL +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 NULL +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using join buffer (Batched Key Access) +create temporary table t11 as +select * from information_schema.global_status where variable_name like 'rocksdb_num%'; +flush statistics; +select a, a+20 in (select t2.filler from t2,t0 where t2.pk=t0.a+20) from t20; +a a+20 in (select t2.filler from t2,t0 where t2.pk=t0.a+20) +1 1 +# COUNTERS FOR: mrr=ON, primary index lookups +select rows_read, rows_requested +from information_schema.table_statistics +where table_name = 't2'; +rows_read 10 +rows_requested 10 +drop table t20; +# +# Check how MRR works without BKA +# explain select t3.col1 from t3 where t3.col1=20 or t3.col1 between 25 and 28; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range col1 col1 5 NULL 2 Using where; Using index diff --git a/mysql-test/suite/rocksdb/t/rocksdb_mrr.test b/mysql-test/suite/rocksdb/t/rocksdb_mrr.test index 74675b1cb5b..9b94cb3cbe9 100644 --- a/mysql-test/suite/rocksdb/t/rocksdb_mrr.test +++ b/mysql-test/suite/rocksdb/t/rocksdb_mrr.test @@ -1,9 +1,8 @@ +# +# Test for MyRocks' MRR implementation based on MultiGet. +# --source include/have_rocksdb.inc ---disable_warnings -drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; ---enable_warnings - create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -172,8 +171,89 @@ having drop table t11,t12; ---echo # Same as above but for MRR (not BKA): ---echo # This will not use MRR: +--echo # +--echo # Check the userstat counters +--echo # +show variables like '%user_table_stat%'; +set optimizer_switch='mrr=off'; +flush statistics; + +select * from t3,t0 where t3.col1=t0.a; + +--echo # COUNTERS FOR: mrr=OFF, secondary index lookups +query_vertical +select + rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from + information_schema.table_statistics +where table_name = 't3'; + +set optimizer_switch='mrr=on'; +flush statistics; + +select * from t3,t0 where t3.col1=t0.a; + +--echo # COUNTERS FOR: mrr=ON, secondary index lookups +query_vertical +select + rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from + information_schema.table_statistics +where table_name = 't3'; + +set optimizer_switch='mrr=off'; +flush statistics; +explain +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; + +--echo # COUNTERS FOR: mrr=OFF, primary index lookups +query_vertical +select + rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from + information_schema.table_statistics +where table_name = 't3'; + +set optimizer_switch='mrr=on'; +flush statistics; +explain +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; +select * from t3,t0 where t3.pk1=t0.a and t3.pk2=t0.a; + +--echo # COUNTERS FOR: mrr=ON, primary index lookups +query_vertical +select + rows_inserted, rows_updated, rows_deleted, rows_read, rows_requested +from + information_schema.table_statistics +where table_name = 't3'; + +--echo # +--echo # Check how the counters are incremented when SQL +--echo # layer doesn't read all of the MultiGet results +--echo # +create table t20 (a int); +insert into t20 values (1); +set global rocksdb_force_flush_memtable_now=1; + +explain +select a, a+20 in (select t2.filler from t2,t0 where t2.pk=t0.a+20) from t20; + +create temporary table t11 as +select * from information_schema.global_status where variable_name like 'rocksdb_num%'; +flush statistics; +select a, a+20 in (select t2.filler from t2,t0 where t2.pk=t0.a+20) from t20; +--echo # COUNTERS FOR: mrr=ON, primary index lookups +query_vertical +select rows_read, rows_requested +from information_schema.table_statistics +where table_name = 't2'; + +drop table t20; +--echo # +--echo # Check how MRR works without BKA +--echo # explain select t3.col1 from t3 where t3.col1=20 or t3.col1 between 25 and 28; --echo # This will use MRR: diff --git a/mysql-test/suite/rocksdb/t/rocksdb_mrr2.test b/mysql-test/suite/rocksdb/t/rocksdb_mrr2.test index f9ec2fe518e..8e747b3cfd4 100644 --- a/mysql-test/suite/rocksdb/t/rocksdb_mrr2.test +++ b/mysql-test/suite/rocksdb/t/rocksdb_mrr2.test @@ -5,10 +5,6 @@ # --source include/have_rocksdb.inc ---disable_warnings -drop table if exists t0,t1,t2; ---enable_warnings - show variables like 'rocksdb_block_cache_size%'; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index e32d1bfa93f..bbdaf9c9976 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -6218,6 +6218,7 @@ ha_rocksdb::ha_rocksdb(my_core::handlerton *const hton, mrr_rowid_reader(nullptr), mrr_n_elements(0), mrr_enabled_keyread(false), + mrr_used_cpk(false), m_in_rpl_delete_rows(false), m_in_rpl_update_rows(false), m_force_skip_unique_check(false) {} @@ -15340,9 +15341,11 @@ int ha_rocksdb::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, // ICP is not supported for PK, so we don't expect that BKA's variant // of ICP would be used: DBUG_ASSERT(!mrr_funcs.skip_index_tuple); + mrr_used_cpk = true; mrr_rowid_reader = new Mrr_pk_scan_rowid_source(this, seq_init_param, n_ranges, mode); } else { + mrr_used_cpk = false; auto reader = new Mrr_sec_key_rowid_source(this); reader->init(seq, seq_init_param, n_ranges, mode); mrr_rowid_reader = reader; @@ -15485,6 +15488,9 @@ int ha_rocksdb::mrr_fill_buffer() { Rdb_transaction *const tx = get_or_create_tx(table->in_use); + if (active_index == table->s->primary_key) + stats.rows_requested += mrr_n_elements; + tx->multi_get(m_pk_descr->get_cf(), mrr_n_elements, mrr_keys, mrr_values, mrr_statuses, mrr_sorted_mode); @@ -15503,12 +15509,24 @@ void ha_rocksdb::mrr_free() { } void ha_rocksdb::mrr_free_rows() { + ssize_t n_pinned = 0; for (ssize_t i = 0; i < mrr_n_elements; i++) { - mrr_values[i].Reset(); + if (mrr_values[i].IsPinned()) { + n_pinned++; + mrr_values[i].Reset(); + } mrr_values[i].~PinnableSlice(); mrr_statuses[i].~Status(); // no need to free mrr_keys } + + // There could be rows that MultiGet has returned but MyRocks hasn't + // returned to the SQL layer (typically due to LIMIT clause) + // Count them in in "rows_read" anyway. (This is only necessary when using + // clustered PK. When using a secondary key, the index-only part of the scan + // that collects the rowids has caused all counters to be incremented) + if (mrr_used_cpk) stats.rows_read += n_pinned; + mrr_n_elements = 0; // We can't rely on the data from HANDLER_BUFFER once the scan is over, so: mrr_values = nullptr; @@ -15565,6 +15583,7 @@ int ha_rocksdb::multi_range_read_next(char **range_info) { m_retrieved_record.Reset(); m_retrieved_record.PinSlice(mrr_values[cur_key], &mrr_values[cur_key]); + mrr_values[cur_key].Reset(); /* If we found the record, but it's expired, pretend we didn't find it. */ if (m_pk_descr->has_ttl() && @@ -15574,6 +15593,8 @@ int ha_rocksdb::multi_range_read_next(char **range_info) { } rc = convert_record_from_storage_format(&rowkey, table->record[0]); + if (active_index == table->s->primary_key) + stats.rows_read++; break; } table->status = rc ? STATUS_NOT_FOUND : 0; diff --git a/storage/rocksdb/ha_rocksdb.h b/storage/rocksdb/ha_rocksdb.h index 6d3fa75e3fa..0290a80637b 100644 --- a/storage/rocksdb/ha_rocksdb.h +++ b/storage/rocksdb/ha_rocksdb.h @@ -692,6 +692,7 @@ class ha_rocksdb : public my_core::handler { // if true, MRR code has enabled keyread (and should disable it back) bool mrr_enabled_keyread; + bool mrr_used_cpk; int mrr_fill_buffer(); void mrr_free_rows();