[Maria-developers] Updated (by Guest): Table elimination (17)
--- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...: Table elimination CREATION DATE..: Sun, 10 May 2009, 19:57 SUPERVISOR.: Monty IMPLEMENTOR: Psergey COPIES TO..: CATEGORY...: Server-Sprint TASK ID: 17 (http://askmonty.org/worklog/?tid=17) VERSION: Server-5.1 STATUS.: Assigned PRIORITY...: 60 WORKED HOURS...: 0 ESTIMATE...: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Wed, 10 Jun 2009, 01:23)=-=- Low Level Design modified. --- /tmp/wklog.17.old.1842 2009-06-10 01:23:42.0 +0300 +++ /tmp/wklog.17.new.1842 2009-06-10 01:23:42.0 +0300 @@ -131,6 +131,11 @@ - this is what happens for constant tables, too. - I don't see how showing them could be of any use. They only make it harder to read the rewritten query. + It turns out that + - it is easy to have EXPLAIN EXTENDED show permanent (once-per-statement +lifetime) changes. + - it is hard to have it show per-execution data. This is because the warning +text is generated after the execution structures have been destroyed. * Table elimination is performed after constant table detection (but before the range analysis). Constant tables are technically different from -=-=(Guest - Wed, 03 Jun 2009, 22:01)=-=- Low Level Design modified. --- /tmp/wklog.17.old.21801 2009-06-03 22:01:34.0 +0300 +++ /tmp/wklog.17.new.21801 2009-06-03 22:01:34.0 +0300 @@ -1,3 +1,6 @@ +The code (currently in development) is at lp: +~maria-captains/maria/maria-5.1-table-elimination tree. + 1. Conditions for removal 1.1 Quick check if there are candidates -=-=(Guest - Wed, 03 Jun 2009, 15:04)=-=- Low Level Design modified. --- /tmp/wklog.17.old.20378 2009-06-03 15:04:54.0 +0300 +++ /tmp/wklog.17.new.20378 2009-06-03 15:04:54.0 +0300 @@ -135,3 +135,8 @@ Considering we've already done the join_read_const_table() call, is there any real difference between constant table and eliminated one? If there is, should we mark const tables also as eliminated? + +* For Multi-table UPDATEs/DELETEs, need to also analyze the SET clause: + - affected tables must not be eliminated + - tables that are used on the right side of the SET x=y assignments must +not be eliminated either. -=-=(Psergey - Wed, 03 Jun 2009, 12:07)=-=- Dependency created: 29 now depends on 17 -=-=(Guest - Tue, 02 Jun 2009, 00:54)=-=- Low Level Design modified. --- /tmp/wklog.17.old.23548 2009-06-02 00:54:13.0 +0300 +++ /tmp/wklog.17.new.23548 2009-06-02 00:54:13.0 +0300 @@ -128,3 +128,10 @@ - this is what happens for constant tables, too. - I don't see how showing them could be of any use. They only make it harder to read the rewritten query. + +* Table elimination is performed after constant table detection (but before + the range analysis). Constant tables are technically different from + eliminated ones (e.g. the former are shown in EXPLAIN and the latter aren't). + Considering we've already done the join_read_const_table() call, is there any + real difference between constant table and eliminated one? If there is, should + we mark const tables also as eliminated? -=-=(Psergey - Mon, 01 Jun 2009, 20:46)=-=- Low Level Design modified. --- /tmp/wklog.17.old.17448 2009-06-01 20:46:40.0 +0300 +++ /tmp/wklog.17.new.17448 2009-06-01 20:46:40.0 +0300 @@ -122,3 +122,9 @@ always. If we want table elimination to work in presence of grouping, need to devise some other way of analyzing aggregate functions. + +* Should eliminated tables be shown in EXPLAIN EXTENDED? + - If we just ignore the question, they will be shown +- this is what happens for constant tables, too. + - I don't see how showing them could be of any use. They only make it +harder to read the rewritten query. -=-=(Guest - Mon, 01 Jun 2009, 12:49)=-=- Low Level Design modified. --- /tmp/wklog.17.old.32202 2009-06-01 12:49:15.0 +0300 +++ /tmp/wklog.17.new.32202 2009-06-01 12:49:15.0 +0300 @@ -8,7 +8,7 @@ 6. Todo, issues to resolve 6.1 To resolve 6.2 Resolved - +7. Additional issues It's not really about elimination of tables, it's about elimination of inner @@ -116,3 +116,9 @@ * We remove ON clauses within semi-join nests. If these clauses contain subqueries, they probably should be gone from EXPLAIN output also? +* Aggregate functions report they depend on all tables, that is, + + item_agg_func->used_tables() == (1ULL << join->tables) - 1 + + always. If we want table elimination to work in presence of grouping, need + to devise some other way of analyzing aggregate functions. -=-=(Guest - Fri, 29 May 2009, 00:45)=-=- Low Level Design modified. --- /tmp/wklog.17.old.1348 2009-05-29 00:45:21.
[Maria-developers] Updated (by Guest): Table elimination (17)
--- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...: Table elimination CREATION DATE..: Sun, 10 May 2009, 19:57 SUPERVISOR.: Monty IMPLEMENTOR: Psergey COPIES TO..: CATEGORY...: Server-Sprint TASK ID: 17 (http://askmonty.org/worklog/?tid=17) VERSION: Server-5.1 STATUS.: Assigned PRIORITY...: 60 WORKED HOURS...: 0 ESTIMATE...: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Wed, 10 Jun 2009, 01:23)=-=- Low Level Design modified. --- /tmp/wklog.17.old.1842 2009-06-10 01:23:42.0 +0300 +++ /tmp/wklog.17.new.1842 2009-06-10 01:23:42.0 +0300 @@ -131,6 +131,11 @@ - this is what happens for constant tables, too. - I don't see how showing them could be of any use. They only make it harder to read the rewritten query. + It turns out that + - it is easy to have EXPLAIN EXTENDED show permanent (once-per-statement +lifetime) changes. + - it is hard to have it show per-execution data. This is because the warning +text is generated after the execution structures have been destroyed. * Table elimination is performed after constant table detection (but before the range analysis). Constant tables are technically different from -=-=(Guest - Wed, 03 Jun 2009, 22:01)=-=- Low Level Design modified. --- /tmp/wklog.17.old.21801 2009-06-03 22:01:34.0 +0300 +++ /tmp/wklog.17.new.21801 2009-06-03 22:01:34.0 +0300 @@ -1,3 +1,6 @@ +The code (currently in development) is at lp: +~maria-captains/maria/maria-5.1-table-elimination tree. + 1. Conditions for removal 1.1 Quick check if there are candidates -=-=(Guest - Wed, 03 Jun 2009, 15:04)=-=- Low Level Design modified. --- /tmp/wklog.17.old.20378 2009-06-03 15:04:54.0 +0300 +++ /tmp/wklog.17.new.20378 2009-06-03 15:04:54.0 +0300 @@ -135,3 +135,8 @@ Considering we've already done the join_read_const_table() call, is there any real difference between constant table and eliminated one? If there is, should we mark const tables also as eliminated? + +* For Multi-table UPDATEs/DELETEs, need to also analyze the SET clause: + - affected tables must not be eliminated + - tables that are used on the right side of the SET x=y assignments must +not be eliminated either. -=-=(Psergey - Wed, 03 Jun 2009, 12:07)=-=- Dependency created: 29 now depends on 17 -=-=(Guest - Tue, 02 Jun 2009, 00:54)=-=- Low Level Design modified. --- /tmp/wklog.17.old.23548 2009-06-02 00:54:13.0 +0300 +++ /tmp/wklog.17.new.23548 2009-06-02 00:54:13.0 +0300 @@ -128,3 +128,10 @@ - this is what happens for constant tables, too. - I don't see how showing them could be of any use. They only make it harder to read the rewritten query. + +* Table elimination is performed after constant table detection (but before + the range analysis). Constant tables are technically different from + eliminated ones (e.g. the former are shown in EXPLAIN and the latter aren't). + Considering we've already done the join_read_const_table() call, is there any + real difference between constant table and eliminated one? If there is, should + we mark const tables also as eliminated? -=-=(Psergey - Mon, 01 Jun 2009, 20:46)=-=- Low Level Design modified. --- /tmp/wklog.17.old.17448 2009-06-01 20:46:40.0 +0300 +++ /tmp/wklog.17.new.17448 2009-06-01 20:46:40.0 +0300 @@ -122,3 +122,9 @@ always. If we want table elimination to work in presence of grouping, need to devise some other way of analyzing aggregate functions. + +* Should eliminated tables be shown in EXPLAIN EXTENDED? + - If we just ignore the question, they will be shown +- this is what happens for constant tables, too. + - I don't see how showing them could be of any use. They only make it +harder to read the rewritten query. -=-=(Guest - Mon, 01 Jun 2009, 12:49)=-=- Low Level Design modified. --- /tmp/wklog.17.old.32202 2009-06-01 12:49:15.0 +0300 +++ /tmp/wklog.17.new.32202 2009-06-01 12:49:15.0 +0300 @@ -8,7 +8,7 @@ 6. Todo, issues to resolve 6.1 To resolve 6.2 Resolved - +7. Additional issues It's not really about elimination of tables, it's about elimination of inner @@ -116,3 +116,9 @@ * We remove ON clauses within semi-join nests. If these clauses contain subqueries, they probably should be gone from EXPLAIN output also? +* Aggregate functions report they depend on all tables, that is, + + item_agg_func->used_tables() == (1ULL << join->tables) - 1 + + always. If we want table elimination to work in presence of grouping, need + to devise some other way of analyzing aggregate functions. -=-=(Guest - Fri, 29 May 2009, 00:45)=-=- Low Level Design modified. --- /tmp/wklog.17.old.1348 2009-05-29 00:45:21.
Re: [Maria-developers] [Merge] lp:~maria-captains/maria/maria-xtradb into lp:maria
Kristian, Thank you for looking into this! I will look how we can backport your changes. We need to have xtradb compiled against standard 5.1, if it is not broken - it should be not problem to include your fixes. Thanks! Vadim Kristian Nielsen wrote: > Kristian Nielsen writes: > >> Just to avoid duplicate work, let me just mention that I am now starting to >> look into this issue. I will try to find a good way to make XtraDB >> compile/work on all the platforms/compilers we want to support, and report >> back what I discover. > > I got some good progress on this. I re-did the merge using the merge-into bzr > plugin (advise from bzr developers). This should allow us to merge directly > from the lp:percona-xtradb tree into lp:maria in the future. > > I placed the files in storage/xtradb/ and left storage/innodb/ in place. This > should hopefully make merging from MySQL slightly easier, and seems to work > well (and it is kind of nice also). > > I integrated the XtraDB testsuite into the tree so there is no need to run > setup.sh or anything. I also fixed a number of test suite failures, and even a > small bug or two. > > I fixed the code to compile without GCC atomic operation > intrinsics. Performance will be worse of course, but it is good to have it at > least working. I still need to fix Maria configure.in to not disable GCC > atomics for xtradb by default. > > I pushed the result so far to Launchpad: > > lp:~maria-captains/maria/mariadb-xtradb-merge2 > > In particular you might be interested in the changeset 2702: > > > http://bazaar.launchpad.net/~maria-captains/maria/mariadb-xtradb-merge2/revision/2702 > > Maybe you will want to merge some of that back into XtraDB? > > Comments are welcome, of course. I think that after fixing GCC atomics in > configure this should be ready to push into MariaDB 5.1. Would be good to get > a review of especially the 2702 commit. > > - Kristian. > -- Vadim Tkachenko, CTO Percona Inc. ICQ: 369-510-335, Skype: vadimtk153, Phone +1-888-401-3403 MySQL Performance Blog - http://www.mysqlperformanceblog.com MySQL Consulting http://www.percona.com/ ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Merge] lp:~maria-captains/maria/maria-xtradb into lp:maria
Kristian Nielsen writes: > Just to avoid duplicate work, let me just mention that I am now starting to > look into this issue. I will try to find a good way to make XtraDB > compile/work on all the platforms/compilers we want to support, and report > back what I discover. I got some good progress on this. I re-did the merge using the merge-into bzr plugin (advise from bzr developers). This should allow us to merge directly from the lp:percona-xtradb tree into lp:maria in the future. I placed the files in storage/xtradb/ and left storage/innodb/ in place. This should hopefully make merging from MySQL slightly easier, and seems to work well (and it is kind of nice also). I integrated the XtraDB testsuite into the tree so there is no need to run setup.sh or anything. I also fixed a number of test suite failures, and even a small bug or two. I fixed the code to compile without GCC atomic operation intrinsics. Performance will be worse of course, but it is good to have it at least working. I still need to fix Maria configure.in to not disable GCC atomics for xtradb by default. I pushed the result so far to Launchpad: lp:~maria-captains/maria/mariadb-xtradb-merge2 In particular you might be interested in the changeset 2702: http://bazaar.launchpad.net/~maria-captains/maria/mariadb-xtradb-merge2/revision/2702 Maybe you will want to merge some of that back into XtraDB? Comments are welcome, of course. I think that after fixing GCC atomics in configure this should be ready to push into MariaDB 5.1. Would be good to get a review of especially the 2702 commit. - Kristian. ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2702)
#At lp:maria
2702 kniel...@knielsen-hq.org 2009-06-09
XtraDB after-merge fixes.
Fixes to get the test suite to run without failures.
removed:
storage/xtradb/setup.sh
modified:
mysql-test/r/information_schema.result
mysql-test/r/information_schema_all_engines.result
mysql-test/r/innodb-autoinc.result
mysql-test/r/innodb-index.result
mysql-test/r/innodb-zip.result
mysql-test/r/innodb.result
mysql-test/r/innodb_bug36169.result
mysql-test/r/innodb_xtradb_bug317074.result
mysql-test/r/row-checksum-old.result
mysql-test/r/row-checksum.result
mysql-test/t/information_schema.test
mysql-test/t/innodb-analyze.test
mysql-test/t/innodb-autoinc.test
mysql-test/t/innodb-index.test
mysql-test/t/innodb-zip.test
mysql-test/t/innodb.test
mysql-test/t/innodb_bug34300.test
mysql-test/t/innodb_bug36169.test
mysql-test/t/innodb_bug36172.test
mysql-test/t/innodb_xtradb_bug317074.test
mysql-test/t/partition_innodb.test
mysys/thr_mutex.c
storage/xtradb/ibuf/ibuf0ibuf.c
storage/xtradb/include/sync0rw.h
storage/xtradb/include/sync0rw.ic
storage/xtradb/include/univ.i
storage/xtradb/srv/srv0start.c
per-file messages:
mysql-test/r/information_schema.result
Additional variables available now.
Sort output to avoid depending on engine order.
mysql-test/r/information_schema_all_engines.result
More variables now.
mysql-test/r/innodb-autoinc.result
Avoid picking up pbxt variables in result
mysql-test/r/innodb-index.result
Save state to not corrupt following testcases.
Suppress an expected warning.
mysql-test/r/innodb-zip.result
Work around a problem with dependency on zlib version
mysql-test/r/innodb.result
Checksums have changed in Maria.
Save and restore server state to not corrupt following testcases.
mysql-test/r/innodb_bug36169.result
Save and restore server state to not corrupt following testcases.
mysql-test/r/innodb_xtradb_bug317074.result
Save and restore server state to not corrupt following testcases.
mysql-test/r/row-checksum-old.result
Update result file
mysql-test/r/row-checksum.result
Update result file
mysql-test/t/information_schema.test
Sort output to avoid depending on engine order.
mysql-test/t/innodb-analyze.test
Save and restore server state to not corrupt following testcases.
mysql-test/t/innodb-autoinc.test
Save and restore server state to not corrupt following testcases.
mysql-test/t/innodb-index.test
Save state to not corrupt following testcases.
Suppress an expected warning.
mysql-test/t/innodb-zip.test
Work around a problem with dependency on zlib version
mysql-test/t/innodb.test
Save and restore server state to not corrupt following testcases.
Update --replace statements for new mysql-test-run
mysql-test/t/innodb_bug34300.test
Save and restore server state to not corrupt following testcases.
mysql-test/t/innodb_bug36169.test
Save and restore server state to not corrupt following testcases.
mysql-test/t/innodb_bug36172.test
Save and restore server state to not corrupt following testcases.
mysql-test/t/innodb_xtradb_bug317074.test
Save and restore server state to not corrupt following testcases.
mysql-test/t/partition_innodb.test
Fix regexps to work with new SHOW INNODB STATUS output.
mysys/thr_mutex.c
Initialize mutex deadlock detection lazily.
This allows to test XtraDB, which initializes huge amounts of mutexes
without using any but a few of them.
storage/xtradb/ibuf/ibuf0ibuf.c
Fix problem where value of INNODB_IBUF_MAX_SIZE would depend on the
alignment of memory
allocated by the buffer pool.
storage/xtradb/include/sync0rw.h
Fix XtraDB to compile without GCC atomic operation intrinsics (performance
may suffer
when they are not available though).
storage/xtradb/include/sync0rw.ic
Fix XtraDB to compile without GCC atomic operation intrinsics (performance
may suffer
when they are not available though).
storage/xtradb/include/univ.i
Fix for MariaDB
storage/xtradb/setup.sh
Remove no longer needed file from XtraDB.
storage/xtradb/srv/srv0start.c
Fix for MariaDB
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result2009-04-08 16:55:26 +
+++ b/mysql-test/r/information_schema.result2009-06-09 15:08:46 +
@@ -42,7 +42,7 @@ WHERE table_schema IN ('mysql', 'INFORMA
table_name<>'ndb_binlog_index' AND
table_name<>'ndb_apply_status' AND
NOT (table_schema = 'INFORMATION_SCHEMA' AND table_name LIKE 'PBXT_%');
-select * from v1;
+select * from v1 ORDER BY c COLLATE utf8_bin;
c
CHARACTER_SETS
COLLATIONS
@@ -54,6 +54,17 @@ EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
+INNODB_BUFFER_POOL_PAGES
+INNODB_
