From: Jan Wedvik Date: June 21 2011 11:50am Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (jan.wedvik:3512) List-Archive: http://lists.mysql.com/commits/139557 Message-Id: <20110621115031.F09C0224@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7495250083514170491==" --===============7495250083514170491== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #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; --===============7495250083514170491== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jan.wedvik@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jan.wedvik@stripped # target_branch: file:///net/atum17/export/home/tmp/jw159207/mysql\ # /repo/push-scan-scan/ # testament_sha1: a4788149f0009ec2bd920660c36236c8b7d2d2f8 # timestamp: 2011-06-21 13:50:31 +0200 # source_branch: bzr+ssh://jwedvik@stripped/bzrroot\ # /server/mysql-5.1-telco-7.0/ # base_revision_id: ole.john.aske@stripped\ # b3bhysbmjq3no1m2 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWdhjWkEABH5fgAAwXO///3/1 /oC////wYAp9ntvgux3BQVY6dNaUUEtZGRQAY4yZNNMJkZAwIxNGCMINGmAAQSSAI0CnoKemTQag jQ9JpoANDTQaaDjJk00wmRkDAjE0YIwg0aYABBpoQUJ+qZk0RkaAAAAAAAAEUpoSPCE1NGyY9VP1 JtTRpiAMQAME0AqSIFMJPI0CYmEaaJkmnqNHqNGjCMR6jTxomCrIwEQSSCoIRiKvOwdInDMySFuh xdQ93/aKKUwTc8PJ2RvwSE9kVgUg5MmVMZNz7e7QoOjNoGEx2x1GrrGw/XG/XV+YH59U6+7+Hf37 wZgiFKHgj0I8yVpvpgfczkDEIERxGP9CGOq/ZQzJxr1z20xPKJnERk6TOcQYxYM+i0IGbSVYmB0C LZkRKKh2rN0miPhKvv1R4DIx6w0OofE4J0RDEfcHE9gz3o7plX5qW8W1lgj9X5tbyYsGdn/V7PVv jipVVVVUiqZvLiZs3KsyZvgtZxM2bm1etHojJkfzaaMFNdlpZU38HMum1Z7G9HM2ZRxrmxoa9GUu 4aWLMWoumDKU2dO9vcTDMrVH6xV2ipJi9EWb0Y2cTNsXbDHVdqYsFmOJEiOHMSgbyAxWhvgY6OXo SySV80BqIea8OAkeLvpykVWBKBORXkE+Yk1GlB9pAlKR1o8/GPUjjWR2ONnF34O5ePpzq6TZE5Xu HujZ0Od1r7Op17c8qq2eya3uYGSnY5ePbNEnaj4rS8HKrddtadPwzS9B8aL0bmNpuyw+JALIIOqB CA7Mi5YAsUTQvAqWX6IYogdVw1mViwQTvMVCai3qLCIDCMKSfg6RCRHpL5XuD6nGjK9Cj3kN1kLI DEMKAvxiPJ0uxImoxTZty1owWU1j8T04eqlkLOeThjGAaEvXwl357yg4jibvE5q1kcp5Xw46cgmy FJSkmYcBvez8mYydoV3iNZEeTiz+W4dVgsMirM1OvfcjicO87NTog8GaIUUv5pXFYGmVvClk2itl jgmpXldrnCBkjAUS0kjcfFYyGrfcE7oPToCxHEJHK4nEZiDSmDgaDh4w7eqkRszAlC9jVQr7H47L X0tpstlJ0awQTApZwRxRiPf3Gbp0wcr2kqF7ydbU3iN3bWeI11S1Y3rApIhZxqXJK4rLO4eMMU5b SOrM6nVqFEUOwmYxPjt6DwNZZtppi97rjBiG2mj4xUDbT7+EDlW/hpPBUBPCCLtrHWNQWRgqiuu2 xZZBVR3CxbPaiERlg0/QZP4HnApbe7RzWxYDY0SC2XaQxiOKLIYoMijSEKQQ3rjWmoYTS4wSKgF5 KqTQSma9wptKoKmyyVfzec3xO/tcbnal6qlSD2/c7fNhKh+h/lBr2Y1VUqq+SPw/VB+SyScEZs/9 pJqBT6H+j7TJ+yPhYzST9mSDcuknGi87g/AZS8V7xWBW1PBBmUeMT+itgrgjd9Eks3pJXA9CSUkn gOcPrHqs73wS0diPnCWYphmT7LyNAxkaJMtZZ5vVQPnY/6B8x4lWHQkUQqlx6chJ3sgGcESP0P3F D54LTliGP3fjksOaRUjOaolo12bYaDFtcH3yyfXc/uLewfJidLVRwwkYLRTc6Q2pBlCoUkGc2Rz+ /dLcj8TokWmigSW8nokWnawjc/rMUbImCN8ss91HBqRN/zxzhMjcZe3AgXkDsTMWYiQf+QjqHiXY JWr8EUcgCCO0TW3qxknky8l2BPD1LxeYTsXs5+Lm88e5w4jvdo0LMmFDGON4SNeDzrIZqRmzYPB1 WN37QKF6h1nlFQu4zBbV4bk6YyBbR5XMxODFZdkZFMk9660UxWZPcrxZsaYqd/a0kOrpdi07/f1t 8Q3TZy+MrkDY0jGXiqqdjfvTU9JZSexZwfy1yTtanV+a/nzLDU8Nr5TSPL3+2rVLz0me/zty+Djw SXTYb2OJA4BxGFG8ZlMZzWSXKda7RMO6yJkxkTi9cYIs3tWDy7WxSmRowimzilY5MkXXkdA41o8F mcY+T/ymtSOZ6/ZOZaeOU4fR6G07JFSONT42WknvXwk+ylxrxdSZqc1PFVkwYFkpPtVNVLPZCZLs WBYpSqnCUxpgck4xP0QO3u4uBlp08UYjoInmTW4S5mwWZeTXkGojcDGotXLmjXYIy7TIdZRJm3r8 kMSyGO87yxVhMqKVVNkfg4PsZNHr6B2Ni31XbRPQ/F/CUtms9jgNzqwMlClAkGRKknSamapmISCB N5nF7G81O2a1DiSeqXMpJkxdbrbxszGIXSEUiP/ktNRO0skpCGQswOi9fTpGazyY1IFkIHm4wzOD A/qTichE5J4plx44EqJmKZGTK+CmCyxavGb0tIv9YqYpeRremQaYYSpxLRLR2+pVMmaMkzkZ6aEb OV5YOj989+jBxWak1OexooqSF1pVpLVBRacVflmm6wlAl7caxK9wXjQ0MleAXIQsF5WQnNBIqC1T r/wuNNhZgJWsqWZcb2JQHeu1t/pS0TpjNWz0LM+SlHwWvH31GU9KSk0+DiEzMmLfbU7k6Zm8Wj2U 2MKbIqO6lkrSpaoSpCnLLS1KRKGD3uZoUpXLSsgdIVbamO6pszlVCZS5Y1QDOvSri0HhsJSPa01y Tc2UMrWU3TBsfkuvSlF1lpStqzRTC5hKSlyzkRUe9iwOp4M2SPGRXVmm4Pnuo9ctJ0e3GRVzbuU5 Hf4STHTr2NHOxRbBqrWumKjHfGDCXbjq8XtwYnmsJyNvJjRqG2Niy+aMOGWCs5TN1O9GKKjBtzz1 UZMRo8rYPIsM1NkopNa8Lya1JPFlxLwzaTwVy1IM+NiVkda8T0qJ00wvx3cjobWD4fIZSbVckWlK moqSMqM7DGcJFXFT0vTu74pTUw9DheRqvAcBxchVFmMqJz9DceuTyTo9vKNZHciptaJGDml3b3Oc nQyj5Pq8tJg7pGyE46VwGKVKQs6d1xlzL0u3cah1uGOYw85wXUVtG8DVX4/+LuSKcKEhsMa0gg== --===============7495250083514170491==--