March 2010 - developers - lists.mariadb.org

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN always show materialization separately CREATION DATE..: Mon, 29 Mar 2010, 06:45 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 110 (http://askmonty.org/worklog/?tid=110) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Low Level Design modified. --- /tmp/wklog.110.old.11745 2010-03-29 06:46:30.000000000 +0000 +++ /tmp/wklog.110.new.11745 2010-03-29 06:46:30.000000000 +0000 @@ -1 +1,8 @@ +For now, all changes will be in select_describe(): +- In the for-each-join-table loop, when we've reached a line where we would + have printed "[Start ]Materialize;" in Extra column, remember the table + number, and emit a materialized table access line instead +- After the loop, do another loop over remembered materialization nests and + print them (a possible difficulty: do we remember what select# they are + from?) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- High-Level Specification modified. --- /tmp/wklog.110.old.11654 2010-03-29 06:46:19.000000000 +0000 +++ /tmp/wklog.110.new.11654 2010-03-29 06:46:19.000000000 +0000 @@ -1 +1,15 @@ +Materialized table access line will look as follows: +Table name +---------- +* Table name will be "SUBQUERY#%d" where %d will refer to the id of first + select in the subquery (when the subquery is a UNION it would be better + to refe to the union-operation line but it has id=NULL so it's not easy + to refer to it) + +Access method +------------- +* SJ-Materialization-lookup will have eq_ref on 'distinct_key' +* SJ-Materialization-scan will have access method ALL, with #rows being + expected number of records in the temp table (i.e. after duplicates are + removed) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Category updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: At the moment, SJM-Materialization is shown in EXPLAIN output in this way: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Materialize | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Start materialize | | 1 | PRIMARY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | End materialize; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ This WL task is to change the output format so it will look as follows: - Tables inside the SJM-nest are displayed as a separate select - within the master select, there is a line that denotes SJM-lookup or SJM-Scan. The above examples will look as follows: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 2 | SUBQUERY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ The rationale behind the change is: - Unification of EXPLAIN output with MWL#90 - The new format is more natural representation of what is going on, conceptually-wise (and may be soon be code-wise) - The new format allows to display E(#records-in-temp-table) for the SJM-Scan case (and for SJM-lookup that number doesn't matter that much) - The new format doesn't put anything into "Extra" column and that's good because that column is already overloaded and horizontal screen space is precious (while vertical is not so much). HIGH-LEVEL SPECIFICATION: Materialized table access line will look as follows: Table name ---------- * Table name will be "SUBQUERY#%d" where %d will refer to the id of first select in the subquery (when the subquery is a UNION it would be better to refe to the union-operation line but it has id=NULL so it's not easy to refer to it) Access method ------------- * SJ-Materialization-lookup will have eq_ref on 'distinct_key' * SJ-Materialization-scan will have access method ALL, with #rows being expected number of records in the temp table (i.e. after duplicates are removed) LOW-LEVEL DESIGN: For now, all changes will be in select_describe(): - In the for-each-join-table loop, when we've reached a line where we would have printed "[Start ]Materialize;" in Extra column, remember the table number, and emit a materialized table access line instead - After the loop, do another loop over remembered materialization nests and print them (a possible difficulty: do we remember what select# they are from?) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN always show materialization separately CREATION DATE..: Mon, 29 Mar 2010, 06:45 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 110 (http://askmonty.org/worklog/?tid=110) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Low Level Design modified. --- /tmp/wklog.110.old.11745 2010-03-29 06:46:30.000000000 +0000 +++ /tmp/wklog.110.new.11745 2010-03-29 06:46:30.000000000 +0000 @@ -1 +1,8 @@ +For now, all changes will be in select_describe(): +- In the for-each-join-table loop, when we've reached a line where we would + have printed "[Start ]Materialize;" in Extra column, remember the table + number, and emit a materialized table access line instead +- After the loop, do another loop over remembered materialization nests and + print them (a possible difficulty: do we remember what select# they are + from?) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- High-Level Specification modified. --- /tmp/wklog.110.old.11654 2010-03-29 06:46:19.000000000 +0000 +++ /tmp/wklog.110.new.11654 2010-03-29 06:46:19.000000000 +0000 @@ -1 +1,15 @@ +Materialized table access line will look as follows: +Table name +---------- +* Table name will be "SUBQUERY#%d" where %d will refer to the id of first + select in the subquery (when the subquery is a UNION it would be better + to refe to the union-operation line but it has id=NULL so it's not easy + to refer to it) + +Access method +------------- +* SJ-Materialization-lookup will have eq_ref on 'distinct_key' +* SJ-Materialization-scan will have access method ALL, with #rows being + expected number of records in the temp table (i.e. after duplicates are + removed) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Category updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: At the moment, SJM-Materialization is shown in EXPLAIN output in this way: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Materialize | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Start materialize | | 1 | PRIMARY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | End materialize; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ This WL task is to change the output format so it will look as follows: - Tables inside the SJM-nest are displayed as a separate select - within the master select, there is a line that denotes SJM-lookup or SJM-Scan. The above examples will look as follows: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 2 | SUBQUERY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ The rationale behind the change is: - Unification of EXPLAIN output with MWL#90 - The new format is more natural representation of what is going on, conceptually-wise (and may be soon be code-wise) - The new format allows to display E(#records-in-temp-table) for the SJM-Scan case (and for SJM-lookup that number doesn't matter that much) - The new format doesn't put anything into "Extra" column and that's good because that column is already overloaded and horizontal screen space is precious (while vertical is not so much). HIGH-LEVEL SPECIFICATION: Materialized table access line will look as follows: Table name ---------- * Table name will be "SUBQUERY#%d" where %d will refer to the id of first select in the subquery (when the subquery is a UNION it would be better to refe to the union-operation line but it has id=NULL so it's not easy to refer to it) Access method ------------- * SJ-Materialization-lookup will have eq_ref on 'distinct_key' * SJ-Materialization-scan will have access method ALL, with #rows being expected number of records in the temp table (i.e. after duplicates are removed) LOW-LEVEL DESIGN: For now, all changes will be in select_describe(): - In the for-each-join-table loop, when we've reached a line where we would have printed "[Start ]Materialize;" in Extra column, remember the table number, and emit a materialized table access line instead - After the loop, do another loop over remembered materialization nests and print them (a possible difficulty: do we remember what select# they are from?) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

1 0

[Maria-developers] New (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

1 0

[Maria-developers] New (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

1 0

Re: [Maria-developers] Summit on the MySQL Ecosystem
by Michael Widenius 28 Mar '10

28 Mar '10

Hi! >>>>> "Brian" == Brian Aker <brian(a)tangent.org> writes: Brian> Hi! Brian> For the last six years I have been running summits at the conference Brian> to discuss timely issues and matters for the mysql ecosystem. Unlike Brian> previous years where we have spent our Friday's at Google, this year Brian> we will be doing the event on Thursday morning from 10:30 am to 3:30 Brian> PM at the conference and Tim O'Reilly has agreed to cohost. Brian> A few topics that a number of you have floated up for this year: Brian> - A better understanding of where the different trees are. Brian> - How does MySQL technology need to evolve in response to the cloud Brian> paradigm? To nosql? Brian> - Are there tools that we need to add for managing DATA, not just Brian> databases. Brian> - What sorts of opportunities exist for co-operation. Brian> - How do we grow the ecosystem? Brian> - Better ways to communicate, and who is leading what fork/distribution. Brian> - How do we keep the ecosystem from splintering? Brian> If you are interested please reply to me by April 7th so that I can be Brian> sure that we have enough space to accommodate everyone. If you have Brian> any additional topics you would like to see covered, please send them Brian> to me. Brian> I am looking forward to seeing each of you at the conference! Brian> Cheers, Brian> -Brian Brian> BTW if you have additional names or people to suggest, please email me Brian> so that I can invite them. You can expect to see a lot of people from the Monty Program AB there. (Count on 6-10 people). Regards, Monty

2 1

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2834: Fixed compiler warnings and sporadic failures in test cases
by noreply＠launchpad.net 28 Mar '10

28 Mar '10

------------------------------------------------------------ revno: 2834 committer: Michael Widenius <monty(a)askmonty.org> branch nick: maria-5.1 timestamp: Sun 2010-03-28 21:10:00 +0300 message: Fixed compiler warnings and sporadic failures in test cases modified: mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test mysql-test/include/default_mysqld.cnf mysql-test/lib/My/SafeProcess/safe_process.cc mysql-test/lib/v1/mysql-test-run.pl mysql-test/suite/rpl/r/rpl_do_grant.result mysql-test/suite/rpl/t/rpl_do_grant.test mysql-test/suite/rpl/t/rpl_name_const.test mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test mysql-test/t/bug47671-master.opt mysql-test/t/ctype_latin1_de-master.opt mysql-test/t/ctype_ucs2_def-master.opt sql-common/client.c sql/item.cc sql/item.h sql/item_cmpfunc.cc sql/item_create.cc sql/item_create.h sql/item_sum.cc sql/item_sum.h sql/set_var.cc sql/sql_yacc.yy storage/example/ha_example.h storage/maria/ma_search.c storage/maria/maria_def.h storage/myisam/ft_stopwords.c storage/xtradb/fil/fil0fil.c storage/xtradb/include/page0page.h storage/xtradb/include/page0page.ic support-files/compiler_warnings.supp -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0