2933 Frazer Clement 2009-04-08
Bug#44153 : Add information about Ndb scan partition pruning to MySQL server
added:
mysql-test/include/ndb_init_scan_counts.inc
mysql-test/include/ndb_scan_counts.inc
modified:
mysql-test/suite/ndb/r/ndb_basic.result
mysql-test/suite/ndb/r/ndb_partition_hash.result
mysql-test/suite/ndb/r/ndb_partition_key.result
mysql-test/suite/ndb/r/ndb_partition_list.result
mysql-test/suite/ndb/r/ndb_partition_range.result
mysql-test/suite/ndb/t/ndb_partition_hash.test
mysql-test/suite/ndb/t/ndb_partition_key.test
mysql-test/suite/ndb/t/ndb_partition_list.test
mysql-test/suite/ndb/t/ndb_partition_range.test
sql/ha_ndbcluster.cc
sql/ha_ndbcluster.h
2932 Jonas Oreland 2009-04-08 [merge]
merge 63-main
modified:
mysql-test/suite/ndb/r/ndb_restore_print.result
mysql-test/suite/ndb/t/ndb_restore_print.test
storage/ndb/src/ndbapi/NdbRecAttr.cpp
=== added file 'mysql-test/include/ndb_init_scan_counts.inc'
--- a/mysql-test/include/ndb_init_scan_counts.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/ndb_init_scan_counts.inc 2009-04-08 17:41:18 +0000
@@ -0,0 +1,6 @@
+--disable_query_log
+--disable_result_log
+select @ndb_init_scan_count:=VARIABLE_VALUE from information_schema.session_status where variable_name like 'NDB_SCAN_COUNT';
+select @ndb_init_pruned_scan_count:=VARIABLE_VALUE from information_schema.session_status where variable_name like 'NDB_PRUNED_SCAN_COUNT';
+--enable_query_log
+--enable_result_log
=== added file 'mysql-test/include/ndb_scan_counts.inc'
--- a/mysql-test/include/ndb_scan_counts.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/ndb_scan_counts.inc 2009-04-08 17:41:18 +0000
@@ -0,0 +1,4 @@
+--disable_query_log
+select @ndb_scan_count:=VARIABLE_VALUE-@ndb_init_scan_count AS ndb_scan_count from information_schema.session_status where variable_name like 'NDB_SCAN_COUNT';
+select @ndb_pruned_scan_count:=VARIABLE_VALUE-@ndb_init_pruned_scan_count AS ndb_pruned_scan_count from information_schema.session_status where variable_name like 'NDB_PRUNED_SCAN_COUNT';
+--enable_query_log
=== modified file 'mysql-test/suite/ndb/r/ndb_basic.result'
--- a/mysql-test/suite/ndb/r/ndb_basic.result 2009-01-23 09:55:57 +0000
+++ b/mysql-test/suite/ndb/r/ndb_basic.result 2009-04-08 17:41:18 +0000
@@ -16,6 +16,8 @@ Ndb_number_of_data_nodes #
Ndb_number_of_ready_data_nodes #
Ndb_connect_count #
Ndb_execute_count #
+Ndb_scan_count #
+Ndb_pruned_scan_count #
Ndb_cluster_connection_pool #
Ndb_conflict_fn_max #
Ndb_conflict_fn_old #
=== modified file 'mysql-test/suite/ndb/r/ndb_partition_hash.result'
--- a/mysql-test/suite/ndb/r/ndb_partition_hash.result 2009-03-06 18:06:17 +0000
+++ b/mysql-test/suite/ndb/r/ndb_partition_hash.result 2009-04-08 17:41:18 +0000
@@ -13,11 +13,23 @@ insert into t1 values (2,0,0,0,0),(4,0,0
insert into t1 values (0,1,1,4,0),(0,1,1,5,0);
UPDATE t1 force index (primary) SET ol_tmp = 1
WHERE ol_o_id = 0 AND ol_d_id = 1 AND ol_w_id = 1;
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
SELECT * FROM t1 WHERE ol_w_id = 1 AND ol_d_id = 1 AND ol_o_id = 0;
ol_o_id ol_d_id ol_w_id ol_number ol_tmp
0 1 1 4 1
0 1 1 5 1
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
SELECT SUM(ol_number) FROM t1 WHERE ol_o_id = 0 AND ol_w_id = 1 AND ol_d_id = 1;
SUM(ol_number)
9
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
drop table t1;
=== modified file 'mysql-test/suite/ndb/r/ndb_partition_key.result'
--- a/mysql-test/suite/ndb/r/ndb_partition_key.result 2009-01-19 10:49:56 +0000
+++ b/mysql-test/suite/ndb/r/ndb_partition_key.result 2009-04-08 17:41:18 +0000
@@ -287,3 +287,61 @@ delete from t1 where a = 5;
select * from t1 order by 1;
a b c
drop table t1;
+create table nott1 (a int,
+b int,
+c int,
+primary key(a,b))
+engine=ndb partition by key(a);
+insert into nott1 values (0,0,0);
+insert into nott1 values (0,1,1);
+insert into nott1 values (0,2,2);
+insert into nott1 values (0,3,3);
+insert into nott1 values (1,0,4);
+insert into nott1 values (1,1,5);
+insert into nott1 values (1,2,6);
+insert into nott1 values (2,22,7);
+All partitions scan
+select * from nott1 order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+0 3 3
+1 0 4
+1 1 5
+1 2 6
+2 22 7
+ndb_scan_count
+1
+ndb_pruned_scan_count
+0
+Single partition scan
+select * from nott1 where a=0 order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+0 3 3
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+Single partition scan
+select * from nott1 where a=4 order by c;
+a b c
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+MRR scan on one partition
+select * from nott1 where a=0 and (( b > 0 and b < 3) or
+( b > 5 and b < 10) or
+( b > 22 and b < 50)) order by c;
+a b c
+0 1 1
+0 2 2
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+drop table nott1;
=== modified file 'mysql-test/suite/ndb/r/ndb_partition_list.result'
--- a/mysql-test/suite/ndb/r/ndb_partition_list.result 2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/ndb/r/ndb_partition_list.result 2009-04-08 17:41:18 +0000
@@ -49,3 +49,63 @@ f_int1 f_int2 f_char1 f_char2 f_charbig
1 1 1 1 ===1===
2 1 1 1 ===1===
DROP TABLE t1;
+create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb
+partition by list (a)
+(partition part0 values in (0,1,2),
+partition part1 values in (3,4,5));
+insert into t1 values (0, 0, 0);
+insert into t1 values (0, 1, 1);
+insert into t1 values (0, 2, 2);
+insert into t1 values (1, 0, 3);
+insert into t1 values (1, 1, 4);
+insert into t1 values (1, 2, 5);
+insert into t1 values (4, 0, 6);
+insert into t1 values (4, 1, 7);
+insert into t1 values (4, 2, 8);
+All partitions scan
+select * from t1 order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+1 0 3
+1 1 4
+1 2 5
+4 0 6
+4 1 7
+4 2 8
+ndb_scan_count
+1
+ndb_pruned_scan_count
+0
+Single partition scan
+select * from t1 where a=0 order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+Single partition scan
+select * from t1 where a=4 order by c;
+a b c
+4 0 6
+4 1 7
+4 2 8
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+MRR single partition scan
+select * from t1 where a in (0, 2) order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+drop table t1;
=== modified file 'mysql-test/suite/ndb/r/ndb_partition_range.result'
--- a/mysql-test/suite/ndb/r/ndb_partition_range.result 2009-01-29 13:17:17 +0000
+++ b/mysql-test/suite/ndb/r/ndb_partition_range.result 2009-04-08 17:41:18 +0000
@@ -287,3 +287,76 @@ f1 f2
5 ---1---
6 ---2---
drop table t1;
+create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb
+partition by range (a)
+(partition part0 values less than (3),
+partition part1 values less than (6));
+insert into t1 values (0, 0, 0);
+insert into t1 values (0, 1, 1);
+insert into t1 values (0, 2, 2);
+insert into t1 values (1, 0, 3);
+insert into t1 values (1, 1, 4);
+insert into t1 values (1, 2, 5);
+insert into t1 values (4, 0, 6);
+insert into t1 values (4, 1, 7);
+insert into t1 values (4, 2, 8);
+All partitions scan
+select * from t1 order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+1 0 3
+1 1 4
+1 2 5
+4 0 6
+4 1 7
+4 2 8
+ndb_scan_count
+1
+ndb_pruned_scan_count
+0
+Single partition scan
+select * from t1 where a=0 order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+Single partition scan
+select * from t1 where a=4 order by c;
+a b c
+4 0 6
+4 1 7
+4 2 8
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+Single partition scan
+select * from t1 where a<3 order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+1 0 3
+1 1 4
+1 2 5
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+MRR single partition scan
+select * from t1 where a in (0, 2) order by c;
+a b c
+0 0 0
+0 1 1
+0 2 2
+ndb_scan_count
+1
+ndb_pruned_scan_count
+1
+drop table t1;
=== modified file 'mysql-test/suite/ndb/t/ndb_partition_hash.test'
--- a/mysql-test/suite/ndb/t/ndb_partition_hash.test 2009-03-06 18:06:17 +0000
+++ b/mysql-test/suite/ndb/t/ndb_partition_hash.test 2009-04-08 17:41:18 +0000
@@ -24,6 +24,8 @@ drop table if exists t1;
# Testcase from Mikael Ronstrom.
# Note that the testcase did not fail previously, but accessed
# partitions unnecessarily
+# Testcase now checks that all scan accesses are pruned at
+# NDBAPI level
#
CREATE TABLE t1 (
ol_o_id int NOT NULL,
@@ -39,11 +41,18 @@ insert into t1 values (1,0,0,0,0),(1,0,0
insert into t1 values (2,0,0,0,0),(4,0,0,0,0),(4,0,0,1,0);
insert into t1 values (0,1,1,4,0),(0,1,1,5,0); # Only rows affected.
+--source include/ndb_init_scan_counts.inc
UPDATE t1 force index (primary) SET ol_tmp = 1
WHERE ol_o_id = 0 AND ol_d_id = 1 AND ol_w_id = 1;
+--source include/ndb_scan_counts.inc
+--source include/ndb_init_scan_counts.inc
SELECT * FROM t1 WHERE ol_w_id = 1 AND ol_d_id = 1 AND ol_o_id = 0;
+--source include/ndb_scan_counts.inc
+
+--source include/ndb_init_scan_counts.inc
SELECT SUM(ol_number) FROM t1 WHERE ol_o_id = 0 AND ol_w_id = 1 AND ol_d_id = 1;
+--source include/ndb_scan_counts.inc
drop table t1;
=== modified file 'mysql-test/suite/ndb/t/ndb_partition_key.test'
--- a/mysql-test/suite/ndb/t/ndb_partition_key.test 2008-09-02 16:31:03 +0000
+++ b/mysql-test/suite/ndb/t/ndb_partition_key.test 2009-04-08 17:41:18 +0000
@@ -242,3 +242,45 @@ delete from t1 where a = 5;
select * from t1 order by 1;
drop table t1;
+
+# Create table with distribution key != partition key to
+# test some partition pruning stuff
+#
+create table nott1 (a int,
+ b int,
+ c int,
+ primary key(a,b))
+ engine=ndb partition by key(a);
+
+insert into nott1 values (0,0,0);
+insert into nott1 values (0,1,1);
+insert into nott1 values (0,2,2);
+insert into nott1 values (0,3,3);
+insert into nott1 values (1,0,4);
+insert into nott1 values (1,1,5);
+insert into nott1 values (1,2,6);
+insert into nott1 values (2,22,7);
+
+--echo All partitions scan
+--source include/ndb_init_scan_counts.inc
+select * from nott1 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo Single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from nott1 where a=0 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo Single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from nott1 where a=4 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo MRR scan on one partition
+--source include/ndb_init_scan_counts.inc
+select * from nott1 where a=0 and (( b > 0 and b < 3) or
+ ( b > 5 and b < 10) or
+ ( b > 22 and b < 50)) order by c;
+--source include/ndb_scan_counts.inc
+
+drop table nott1;
=== modified file 'mysql-test/suite/ndb/t/ndb_partition_list.test'
--- a/mysql-test/suite/ndb/t/ndb_partition_list.test 2007-07-04 20:38:53 +0000
+++ b/mysql-test/suite/ndb/t/ndb_partition_list.test 2009-04-08 17:41:18 +0000
@@ -66,3 +66,41 @@ SELECT * FROM t1 ORDER BY f_int1;
DROP TABLE t1;
+# Check partition pruning at NdbApi level for list partitioned table
+#
+create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb
+partition by list (a)
+(partition part0 values in (0,1,2),
+ partition part1 values in (3,4,5));
+
+insert into t1 values (0, 0, 0);
+insert into t1 values (0, 1, 1);
+insert into t1 values (0, 2, 2);
+insert into t1 values (1, 0, 3);
+insert into t1 values (1, 1, 4);
+insert into t1 values (1, 2, 5);
+insert into t1 values (4, 0, 6);
+insert into t1 values (4, 1, 7);
+insert into t1 values (4, 2, 8);
+
+--echo All partitions scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo Single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 where a=0 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo Single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 where a=4 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo MRR single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 where a in (0, 2) order by c;
+--source include/ndb_scan_counts.inc
+
+drop table t1;
=== modified file 'mysql-test/suite/ndb/t/ndb_partition_range.test'
--- a/mysql-test/suite/ndb/t/ndb_partition_range.test 2009-01-29 13:17:17 +0000
+++ b/mysql-test/suite/ndb/t/ndb_partition_range.test 2009-04-08 17:41:18 +0000
@@ -274,3 +274,48 @@ select * from t1 order by f1;
UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 1;
select * from t1 order by f1;
drop table t1;
+
+
+# Check partitioning at NdbApi level for range partitioned table
+#
+create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb
+partition by range (a)
+(partition part0 values less than (3),
+ partition part1 values less than (6));
+
+insert into t1 values (0, 0, 0);
+insert into t1 values (0, 1, 1);
+insert into t1 values (0, 2, 2);
+insert into t1 values (1, 0, 3);
+insert into t1 values (1, 1, 4);
+insert into t1 values (1, 2, 5);
+insert into t1 values (4, 0, 6);
+insert into t1 values (4, 1, 7);
+insert into t1 values (4, 2, 8);
+
+--echo All partitions scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo Single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 where a=0 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo Single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 where a=4 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo Single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 where a<3 order by c;
+--source include/ndb_scan_counts.inc
+
+--echo MRR single partition scan
+--source include/ndb_init_scan_counts.inc
+select * from t1 where a in (0, 2) order by c;
+--source include/ndb_scan_counts.inc
+
+drop table t1;
=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc 2009-03-06 18:06:17 +0000
+++ b/sql/ha_ndbcluster.cc 2009-04-08 17:41:18 +0000
@@ -192,6 +192,8 @@ struct st_ndb_status {
long number_of_ready_data_nodes;
long connect_count;
long execute_count;
+ long scan_count;
+ long pruned_scan_count;
};
static struct st_ndb_status g_ndb_status;
@@ -222,6 +224,8 @@ static int update_status_variables(Thd_n
if (thd_ndb)
{
ns->execute_count= thd_ndb->m_execute_count;
+ ns->scan_count= thd_ndb->m_scan_count;
+ ns->pruned_scan_count= thd_ndb->m_pruned_scan_count;
}
return 0;
}
@@ -236,6 +240,8 @@ SHOW_VAR ndb_status_variables_dynamic[]=
(char*) &g_ndb_status.number_of_ready_data_nodes, SHOW_LONG},
{"connect_count", (char*) &g_ndb_status.connect_count, SHOW_LONG},
{"execute_count", (char*) &g_ndb_status.execute_count, SHOW_LONG},
+ {"scan_count", (char*) &g_ndb_status.scan_count, SHOW_LONG},
+ {"pruned_scan_count", (char*) &g_ndb_status.pruned_scan_count, SHOW_LONG},
{NullS, NullS, SHOW_LONG}
};
@@ -648,6 +654,8 @@ Thd_ndb::Thd_ndb()
(hash_get_key)thd_ndb_share_get_key, 0, 0);
m_unsent_bytes= 0;
m_execute_count= 0;
+ m_scan_count= 0;
+ m_pruned_scan_count= 0;
m_max_violation_count= 0;
m_old_violation_count= 0;
m_conflict_fn_usage_count= 0;
@@ -2923,6 +2931,8 @@ int ha_ndbcluster::ordered_index_scan(co
ERR_RETURN(trans->getNdbError());
DBUG_PRINT("info", ("Is scan pruned to 1 partition? : %u", op->getPruned()));
+ m_thd_ndb->m_scan_count++;
+ m_thd_ndb->m_pruned_scan_count += (op->getPruned()? 1 : 0);
if (uses_blob_value(table->read_set) &&
get_blob_values(op, NULL, table->read_set) != 0)
@@ -3074,6 +3084,9 @@ int ha_ndbcluster::full_table_scan(const
(uchar *)(table->read_set->bitmap),
&options, sizeof(NdbScanOperation::ScanOptions))))
ERR_RETURN(trans->getNdbError());
+
+ m_thd_ndb->m_scan_count++;
+ m_thd_ndb->m_pruned_scan_count += (op->getPruned()? 1 : 0);
}
m_active_cursor= op;
@@ -10386,6 +10399,8 @@ ndb_get_table_statistics(THD *thd, ha_nd
error= pTrans->getNdbError();
goto retry;
}
+ thd_ndb->m_scan_count++;
+ thd_ndb->m_pruned_scan_count += (pOp->getPruned()? 1 : 0);
thd_ndb->m_execute_count++;
DBUG_PRINT("info", ("execute_count: %u", thd_ndb->m_execute_count));
@@ -10891,6 +10906,8 @@ ha_ndbcluster::read_multi_range_first(KE
{
DBUG_PRINT("info", ("Is MRR scan pruned to 1 partition? :%u",
m_multi_cursor->getPruned()));
+ m_thd_ndb->m_scan_count++;
+ m_thd_ndb->m_pruned_scan_count += (m_multi_cursor->getPruned()? 1 : 0);
};
if (any_real_read)
=== modified file 'sql/ha_ndbcluster.h'
--- a/sql/ha_ndbcluster.h 2009-02-16 15:54:40 +0000
+++ b/sql/ha_ndbcluster.h 2009-04-08 17:41:18 +0000
@@ -354,6 +354,9 @@ class Thd_ndb
uint m_old_violation_count;
uint m_conflict_fn_usage_count;
+ uint m_scan_count;
+ uint m_pruned_scan_count;
+
NdbTransaction *global_schema_lock_trans;
uint global_schema_lock_count;
uint global_schema_lock_error;
| Thread |
|---|
| • bzr push into mysql-5.1-telco-6.3 branch (frazer:2932 to 2933)Bug#44153 | Frazer Clement | 8 Apr |
