#At file:///net/atum17/export/home/tmp/jw159207/mysql/repo/push-scan-scan/ based on revid:ole.john.aske@stripped
3512 Jan Wedvik 2011-06-21
This patch turns of measuring SCAN_ROWS_RETURNED for certain bushy scan
queries. The reason for this is that growth of this counter is platform
dependent for these queries. There are two reasons for this:
1. Distribution hashing (partitioning) of tables is endian dependent. This
may cause data to be more skewed on some platforms. This again requires more
batches to scan the table and thus more repeats of repeatable scans (i.e.
those that will be repeated for each batch of the other branch of a bushy
scan). This increases the overall scan row count.
2. If a timer expires in LQH after receiving SCAN_FRAGREQ, LQH may decide to
send SCAN_FRAGCONF immediately, even if more tuples could fit in the batch.
As above this causes more repeats of repeatable scans.
modified:
mysql-test/suite/ndb/r/ndb_join_pushdown.result
mysql-test/suite/ndb/t/ndb_join_pushdown.test
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-06-16 09:17:41 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-06-21 11:50:25 +0000
@@ -4,6 +4,8 @@ select counter_name, sum(val) as val
from ndbinfo.counters
where block_name='DBSPJ'
group by counter_name;
+create temporary table spj_save_counts like spj_counts_at_startup;
+insert into spj_save_counts values ('SCAN_ROWS_RETURNED', 0);
set @save_ndb_join_pushdown = @@session.ndb_join_pushdown;
set ndb_join_pushdown = true;
create table t1 (
@@ -2057,6 +2059,7 @@ left join tx as x2 on x1.c=x2.a and x1.d
count(*)
304
drop table tx;
+update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
alter table t1 partition by key(a);
explain select count(*) from t1
join t1 as t2 on t2.a = t1.c
@@ -2089,6 +2092,7 @@ join t1 as x2 on x2.a = x1.c and x1.b <
join t1 as x3 on x3.a = x1.c;
count(*)
20000
+update spj_counts_at_startup set val = val + (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') - (select val from spj_save_counts where counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
drop table t1;
drop table tx;
create table t1 (a int, b int, primary key(a) using hash) engine = ndb;
@@ -4333,6 +4337,7 @@ id select_type table type possible_keys
Warnings:
Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
Note 1003 select straight_join `test`.`x1`.`pk` AS `pk`,`test`.`x1`.`u` AS `u`,`test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x2`.`pk` AS `pk`,`test`.`x2`.`u` AS `u`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x3`.`pk` AS `pk`,`test`.`x3`.`u` AS `u`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b` from `test`.`t1` `x1` left join (`test`.`t1` `x2` join `test`.`t1` `x3`) on(((`test`.`x2`.`pk` = `test`.`x1`.`a`) and (`test`.`x3`.`b` = `test`.`x2`.`a`))) where 1
+update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
explain extended select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
@@ -4382,6 +4387,7 @@ join t1 as x7 on x7.b = x1.a
where x3.a < x2.pk and x4.a < x3.pk;
count(*)
632736
+update spj_counts_at_startup set val = val + (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') - (select val from spj_save_counts where counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
explain extended select straight_join count(*) from t1 as x1
left join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
@@ -4426,6 +4432,7 @@ join t1 as x2 on x2.b = x1.a
left join t1 as x3 on x3.b = x1.b;
count(*)
2028
+update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
explain extended
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
@@ -4449,6 +4456,7 @@ join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
count(*)
2028
+update spj_counts_at_startup set val = val + (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') - (select val from spj_save_counts where counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
explain extended select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
join t1 as x2 on x2.pk = x1.a;
@@ -4554,6 +4562,7 @@ id select_type table type possible_keys
1 SIMPLE x2 ref PRIMARY PRIMARY 4 test.x0.c 1 Child of 'x0' in pushed join@1
1 SIMPLE x3 ref PRIMARY PRIMARY 4 test.x2.c 1 Child of 'x2' in pushed join@1
1 SIMPLE x4 eq_ref PRIMARY PRIMARY 8 test.x0.d,test.x3.b 1 Child of 'x3' in pushed join@1
+update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
select straight_join count(*) from t1 as x0
join t3 as x1 on x0.c=x1.a
join t1 as x2 on x0.c=x2.a
@@ -4561,6 +4570,7 @@ join t3 as x3 on x2.c=x3.a
join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
count(*)
4800
+update spj_counts_at_startup set val = val + (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') - (select val from spj_save_counts where counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
drop table t1;
drop table t2;
drop table t3;
@@ -5194,7 +5204,7 @@ PRUNED_RANGE_SCANS_RECEIVED 25
RANGE_SCANS_RECEIVED 720
READS_NOT_FOUND 6616
READS_RECEIVED 52
-SCAN_ROWS_RETURNED 95391
+SCAN_ROWS_RETURNED 76380
TABLE_SCANS_RECEIVED 236
select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT'
from spj_counts_at_end, spj_counts_at_startup
@@ -5203,6 +5213,7 @@ and (spj_counts_at_end.counter_name = 'L
or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT');
LOCAL+REMOTE READS_SENT
35288
+drop table spj_save_counts;
drop table spj_counts_at_startup;
drop table spj_counts_at_end;
scan_count
=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-06-16 09:17:41 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-06-21 11:50:25 +0000
@@ -26,6 +26,30 @@ let $pushed_queries_dropped_at_startup =
let $pushed_queries_executed_at_startup = query_get_value(show status like 'Ndb_pushed_queries_executed', Value, 1);
let $pushed_reads_at_startup = query_get_value(show status like 'Ndb_pushed_reads', Value, 1);
+# Use this table and the two queries below to turn of meassuring
+# SCAN_ROWS_RETURNED for certain bushy scan queries. The reason for this is
+# that growth of this counter is platform dependent for these queries. There
+# are two reasons for this:
+# 1. Distribution hashing (partitioning) of tables is endian dependent. This
+# may cause data to be more skewed on some platforms. This again requires more
+# batches to scan the table and thus more repeats of repeatable scans (i.e.
+# those that will be repeated for each batch of the other branch of a bushy
+# scan). This increases the overall scan row count.
+# 2. If a timer expires in LQH after receiving SCAN_FRAGREQ, LQH may decide to
+# send SCAN_FRAGCONF immediately, even if more tuples could fit in the batch.
+# As above this causes more repeats of repeatable scans.
+
+create temporary table spj_save_counts like spj_counts_at_startup;
+
+insert into spj_save_counts values ('SCAN_ROWS_RETURNED', 0);
+
+# Record current counter value.
+let $save_scan_rows_returned = update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
+
+# Update spj_counts_at_startup to compensate for counter increments since
+# running save_scan_rows_returned.
+let $compensate_scan_rows_returned = update spj_counts_at_startup set val = val + (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') - (select val from spj_save_counts where counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
+
##############
# Test start
@@ -998,6 +1022,7 @@ select count(*) from tx as x1
drop table tx;
# Test bushy join with pruned scan.
+eval $save_scan_rows_returned;
alter table t1 partition by key(a);
explain select count(*) from t1
@@ -1027,6 +1052,7 @@ select count(*) from t1 as x1
join t1 as x2 on x2.a = x1.c and x1.b < 2
join t1 as x3 on x3.a = x1.c;
+eval $compensate_scan_rows_returned;
drop table t1;
drop table tx;
@@ -2771,6 +2797,8 @@ explain extended select straight_join *
# These should be allowed to be executed in 'parallel', depending on
# only the root operation
#
+eval $save_scan_rows_returned;
+
explain extended select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
@@ -2805,6 +2833,7 @@ select straight_join count(*) from t1 as
join t1 as x7 on x7.b = x1.a
where x3.a < x2.pk and x4.a < x3.pk;
+eval $compensate_scan_rows_returned;
#############
# If we have an outer join, we can't create an artificial dep. 'through' the outer join.
@@ -2845,6 +2874,8 @@ select straight_join count(*) from t1 as
#
# Bushy execution is expected for these scans (x2 & x4) wrt. root (x1)
#
+eval $save_scan_rows_returned;
+
explain extended
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
@@ -2859,6 +2890,7 @@ select straight_join count(*) from t1 as
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
+eval $compensate_scan_rows_returned;
#############
# Test bushy lookups + 1scan,
@@ -2965,12 +2997,16 @@ explain select straight_join count(*) fr
join t3 as x3 on x2.c=x3.a
join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
+eval $save_scan_rows_returned;
+
select straight_join count(*) from t1 as x0
join t3 as x1 on x0.c=x1.a
join t1 as x2 on x0.c=x2.a
join t3 as x3 on x2.c=x3.a
join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
+eval $compensate_scan_rows_returned;
+
drop table t1;
drop table t2;
drop table t3;
@@ -3377,12 +3413,7 @@ select sum(spj_counts_at_end.val - spj_c
and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT'
or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT');
-#select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE RANGE_SCANS_SENT'
-# from spj_counts_at_end, spj_counts_at_startup
-# where spj_counts_at_end.counter_name = spj_counts_at_startup.counter_name
-# and (spj_counts_at_end.counter_name = 'LOCAL_RANGE_SCANS_SENT'
-# or spj_counts_at_end.counter_name = 'REMOTE_RANGE_SCANS_SENT');
-
+drop table spj_save_counts;
drop table spj_counts_at_startup;
drop table spj_counts_at_end;
Attachment: [text/bzr-bundle] bzr/jan.wedvik@oracle.com-20110621115025-at5n8tlitwm2d6zd.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(jan.wedvik:3512) | Jan Wedvik | 21 Jun |
