- developers - lists.mariadb.org

[Maria-developers] Progress (by Bothorsen): Provide an " ORDER BY FIRST_JOIN.column" feature (59)
by worklog-noreply＠askmonty.org 14 Jan '10

14 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Provide an "ORDER BY FIRST_JOIN.column" feature CREATION DATE..: Thu, 22 Oct 2009, 12:33 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 59 (http://askmonty.org/worklog/?tid=59) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 5 ESTIMATE.......: 60 (hours remain) ORIG. ESTIMATE.: 60 PROGRESS NOTES: -=-=(Bothorsen - Thu, 14 Jan 2010, 15:24)=-=- Estimated by Igor to be 10 to 12 days after estimation. Worked 5 hours and estimate 60 hours remain (original estimate increased by 65 hours). DESCRIPTION: Question: How much effort would be required to provide an "ORDER BY FIRST_JOIN.column" feature? We often do self-join queries and want to order them by index, i.e.: create table t1 (a int, b int, unique key 'by_a_b' (a, b))); select * from t1 x inner join t1 y on (x.b = y.b) where x.a=1 and y.a=2 order by (x|y).b desc limit 10; but it is only ordered by index if you order by the first table in the optimized join order, which could be either x or y depending on which is more selective. we don't want to force the join order because often one is far more selective than the other, but the only way to know what table to order by then is to explain the query first. if there was some way to tell mysql to just order by the first join in the optimized order, that would help us. sometimes we do this across tables too. both of these can be solved by rewriting the query to say "using" instead of "on" in which case we don't have to specify the table name of the column, just "order by b desc". but, we also want to be able to do exclusions, in which case an "on" is required and therefore we run into ambiguous column names: Monty answered: MySQL has an optimization where it knows that if x.b = y.b is used then it can replace x.b with y.b and y.b with x.b in the WHERE part MySQL however doesn't do it for the ORDER BY part and I don't think that should be very hard to do. Question continues: create table t2 (c int, b int, unique key 'by_c_b' (c, b))); select * from t1 x inner join t1 y using (b) left join t2 on (t2.c = 3 and t1.b = t2.b) where x.a=1 and y.a=2 and t2.c is null order by (x| y).b desc limit 10; if we were ordering ascending, i think we could just leave off the order by entirely in this case and it would happen to work since it's reading in index order. but the combination of requiring an "on" and descending sort leaves us unable to use these tricks. if we had either an "ORDER BY FIRST_JOIN.column" or some way to tell the server that 'b' is in fact joined as being equivalent across tables (except where it may be null from left joining) and that we shouldn't have to specify the table name at all, that would save us having to figure out the table from the explain. Monty answers: It may be that the eq-replacment we have would solve this. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Provide key cache statistics (58)
by worklog-noreply＠askmonty.org 14 Jan '10

14 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Provide key cache statistics CREATION DATE..: Thu, 22 Oct 2009, 12:28 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 58 (http://askmonty.org/worklog/?tid=58) VERSION........: WorkLog-3.4 STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Thu, 14 Jan 2010, 15:15)=-=- Status updated. --- /tmp/wklog.58.old.19489 2010-01-14 13:15:35.000000000 +0000 +++ /tmp/wklog.58.new.19489 2010-01-14 13:15:35.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Cancelled -=-=(Monty - Fri, 27 Nov 2009, 12:36)=-=- High Level Description modified. --- /tmp/wklog.58.old.15108 2009-11-27 10:36:13.000000000 +0000 +++ /tmp/wklog.58.new.15108 2009-11-27 10:36:13.000000000 +0000 @@ -1,8 +1,7 @@ -Provide the key cache -statistics available for the default cache (key blocks used, -unflushed, etc.) for all named caches. +Provide the same key cache statistics for all MyISAM key caches as we provide +for the default key cache. -Monty answers in an email: +Background: Monty answers in an email to customer: This is a much simpler task and something that is important to get done. We would have to introduce a 'show keycache statistics' command @@ -11,3 +10,44 @@ The raw coding is probably 1-2 days, but on top if this we need testing, a test environment and building of biniaries for you. Henrik/Bo and Igor will come back to you with a more exact estimate. + +----------------- +Currently we have: + +show status like "key%"; ++------------------------+-------+ +| Variable_name | Value | ++------------------------+-------+ +| Key_blocks_not_flushed | 0 | +| Key_blocks_unused | 13389 | +| Key_blocks_used | 7 | +| Key_read_requests | 22 | +| Key_reads | 7 | +| Key_write_requests | 0 | +| Key_writes | 0 | ++------------------------+-------+ + +Sergei suggested we introduce for each new key cache a set of variables prefixed +with the keycache name: + +show status like "keycache1.%"; +Which should show: + ++----------------------------------+-------+ +| Variable_name | Value | ++----------------------------------+-------+ +| keycache1.Key_blocks_not_flushed | 0 | +| keycache1.Key_blocks_unused | 13389 | +| keycache1.Key_blocks_used | 7 | +| keycache1.Key_read_requests | 22 | +| keycache1.Key_reads | 7 | +| keycache1.Key_write_requests | 0 | +| keycache1.Key_writes | 0 | ++----------------------------------+-------+ + +The task would thus to be to automatically introduce new variables when we +create a new key cache and automatically remove these variables when the cache +is deleted. + +The other option would be to add an information schema where we populate the +information schema with data from all existing key caches. DESCRIPTION: Provide the same key cache statistics for all MyISAM key caches as we provide for the default key cache. Background: Monty answers in an email to customer: This is a much simpler task and something that is important to get done. We would have to introduce a 'show keycache statistics' command that would iterate over all keycaches and provide the statistics. The raw coding is probably 1-2 days, but on top if this we need testing, a test environment and building of biniaries for you. Henrik/Bo and Igor will come back to you with a more exact estimate. ----------------- Currently we have: show status like "key%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 13389 | | Key_blocks_used | 7 | | Key_read_requests | 22 | | Key_reads | 7 | | Key_write_requests | 0 | | Key_writes | 0 | +------------------------+-------+ Sergei suggested we introduce for each new key cache a set of variables prefixed with the keycache name: show status like "keycache1.%"; Which should show: +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | keycache1.Key_blocks_not_flushed | 0 | | keycache1.Key_blocks_unused | 13389 | | keycache1.Key_blocks_used | 7 | | keycache1.Key_read_requests | 22 | | keycache1.Key_reads | 7 | | keycache1.Key_write_requests | 0 | | keycache1.Key_writes | 0 | +----------------------------------+-------+ The task would thus to be to automatically introduce new variables when we create a new key cache and automatically remove these variables when the cache is deleted. The other option would be to add an information schema where we populate the information schema with data from all existing key caches. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Enable the use of libstdc++ in MariaDB (63)
by worklog-noreply＠askmonty.org 14 Jan '10

14 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Enable the use of libstdc++ in MariaDB CREATION DATE..: Wed, 11 Nov 2009, 13:19 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 63 (http://askmonty.org/worklog/?tid=63) VERSION........: Server-5.2 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 4 ESTIMATE.......: 10 (hours remain) ORIG. ESTIMATE.: 10 PROGRESS NOTES: -=-=(Knielsen - Thu, 14 Jan 2010, 13:46)=-=- Research and updated description Worked 4 hours and estimate 10 hours remain (original estimate increased by 14 hours). -=-=(Knielsen - Thu, 14 Jan 2010, 13:45)=-=- High-Level Specification modified. --- /tmp/wklog.63.old.13967 2010-01-14 11:45:49.000000000 +0000 +++ /tmp/wklog.63.new.13967 2010-01-14 11:45:49.000000000 +0000 @@ -1 +1,37 @@ +I did some investigation into this. + +The simple way to do this is to simply use g++ to link C++ objects. So this +issue is really restricted to GCC compilation where we by default prefer to +link with gcc even for C++ code. So this means building with + + CXX=g++ + +The consequences of doing this for the binaries is the addition of two +additional run-time .so dependencies: libstdc++.so and libgcc_s.so. + +It still needs to be investigated if these additional dependencies are a +problem for binary tarball packages, or if the ABI for those libraries are now +as stable as libc.so. + +The libgcc_s.so is needed as a dependency to support exceptions between +different object files, as they need to use the same code for stack unwinding. +libstdc++.so is of course needed for access to C++ runtime. + +I researched into the possibility to instead link only specific plugins with +g++, and continue to link the rest of the server with gcc. Unfortunately, this +seems really hard to do in a proper way due to the way autotools works. At +configure time, a script ./libtool is created with hardcoded compiler commands +derived from $CC and $CXX. This script is then used to do the actual linking +in Makefiles generated by Automake. I thus did not find a way to change the +linker command on a per-makefile basis, as libtool is global to the project. + +One option would be to use separate configure.in for plugins, but this is +quite an intrusive change. + +My conclusion is that the best way is to start using g++ for linking the +entire server. This is no problem for binaries made for a specific +distribution (Ubuntu, Debian, Centos), where the dependencies are handled by +the package manager. If it is a big problem for binary tarball releases, at +worst we can build multiple binary tarball releases for the different library +versions we need to support. -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Version updated. --- /tmp/wklog.63.old.19522 2010-01-12 16:26:23.000000000 +0200 +++ /tmp/wklog.63.new.19522 2010-01-12 16:26:23.000000000 +0200 @@ -1 +1 @@ -Connector/.NET-5.2 +Server-5.2 -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Category updated. --- /tmp/wklog.63.old.19506 2010-01-12 16:26:15.000000000 +0200 +++ /tmp/wklog.63.new.19506 2010-01-12 16:26:15.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog DESCRIPTION: Enable the use of libstdc++ in MariaDB. As time goes on, more and more plugins and external code/library will need linking to libstdc++ for stuff it uses. I have already seen this happen several times, with extra work needed to integrate things properly. It would be nice to have a general solution for this so that it is not necessary to spend time on individual solutions in each case. It also needs to be considered what the impact of this will be for the server in terms of binary compatibility, performance etc. I think it should be mostly ok, except that it might introduce a problem for bintar packages with an external dependency on libstdc++. HIGH-LEVEL SPECIFICATION: I did some investigation into this. The simple way to do this is to simply use g++ to link C++ objects. So this issue is really restricted to GCC compilation where we by default prefer to link with gcc even for C++ code. So this means building with CXX=g++ The consequences of doing this for the binaries is the addition of two additional run-time .so dependencies: libstdc++.so and libgcc_s.so. It still needs to be investigated if these additional dependencies are a problem for binary tarball packages, or if the ABI for those libraries are now as stable as libc.so. The libgcc_s.so is needed as a dependency to support exceptions between different object files, as they need to use the same code for stack unwinding. libstdc++.so is of course needed for access to C++ runtime. I researched into the possibility to instead link only specific plugins with g++, and continue to link the rest of the server with gcc. Unfortunately, this seems really hard to do in a proper way due to the way autotools works. At configure time, a script ./libtool is created with hardcoded compiler commands derived from $CC and $CXX. This script is then used to do the actual linking in Makefiles generated by Automake. I thus did not find a way to change the linker command on a per-makefile basis, as libtool is global to the project. One option would be to use separate configure.in for plugins, but this is quite an intrusive change. My conclusion is that the best way is to start using g++ for linking the entire server. This is no problem for binaries made for a specific distribution (Ubuntu, Debian, Centos), where the dependencies are handled by the package manager. If it is a big problem for binary tarball releases, at worst we can build multiple binary tarball releases for the different library versions we need to support. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Enable the use of libstdc++ in MariaDB (63)
by worklog-noreply＠askmonty.org 14 Jan '10

14 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Enable the use of libstdc++ in MariaDB CREATION DATE..: Wed, 11 Nov 2009, 13:19 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 63 (http://askmonty.org/worklog/?tid=63) VERSION........: Server-5.2 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 4 ESTIMATE.......: 10 (hours remain) ORIG. ESTIMATE.: 10 PROGRESS NOTES: -=-=(Knielsen - Thu, 14 Jan 2010, 13:46)=-=- Research and updated description Worked 4 hours and estimate 10 hours remain (original estimate increased by 14 hours). -=-=(Knielsen - Thu, 14 Jan 2010, 13:45)=-=- High-Level Specification modified. --- /tmp/wklog.63.old.13967 2010-01-14 11:45:49.000000000 +0000 +++ /tmp/wklog.63.new.13967 2010-01-14 11:45:49.000000000 +0000 @@ -1 +1,37 @@ +I did some investigation into this. + +The simple way to do this is to simply use g++ to link C++ objects. So this +issue is really restricted to GCC compilation where we by default prefer to +link with gcc even for C++ code. So this means building with + + CXX=g++ + +The consequences of doing this for the binaries is the addition of two +additional run-time .so dependencies: libstdc++.so and libgcc_s.so. + +It still needs to be investigated if these additional dependencies are a +problem for binary tarball packages, or if the ABI for those libraries are now +as stable as libc.so. + +The libgcc_s.so is needed as a dependency to support exceptions between +different object files, as they need to use the same code for stack unwinding. +libstdc++.so is of course needed for access to C++ runtime. + +I researched into the possibility to instead link only specific plugins with +g++, and continue to link the rest of the server with gcc. Unfortunately, this +seems really hard to do in a proper way due to the way autotools works. At +configure time, a script ./libtool is created with hardcoded compiler commands +derived from $CC and $CXX. This script is then used to do the actual linking +in Makefiles generated by Automake. I thus did not find a way to change the +linker command on a per-makefile basis, as libtool is global to the project. + +One option would be to use separate configure.in for plugins, but this is +quite an intrusive change. + +My conclusion is that the best way is to start using g++ for linking the +entire server. This is no problem for binaries made for a specific +distribution (Ubuntu, Debian, Centos), where the dependencies are handled by +the package manager. If it is a big problem for binary tarball releases, at +worst we can build multiple binary tarball releases for the different library +versions we need to support. -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Version updated. --- /tmp/wklog.63.old.19522 2010-01-12 16:26:23.000000000 +0200 +++ /tmp/wklog.63.new.19522 2010-01-12 16:26:23.000000000 +0200 @@ -1 +1 @@ -Connector/.NET-5.2 +Server-5.2 -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Category updated. --- /tmp/wklog.63.old.19506 2010-01-12 16:26:15.000000000 +0200 +++ /tmp/wklog.63.new.19506 2010-01-12 16:26:15.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog DESCRIPTION: Enable the use of libstdc++ in MariaDB. As time goes on, more and more plugins and external code/library will need linking to libstdc++ for stuff it uses. I have already seen this happen several times, with extra work needed to integrate things properly. It would be nice to have a general solution for this so that it is not necessary to spend time on individual solutions in each case. It also needs to be considered what the impact of this will be for the server in terms of binary compatibility, performance etc. I think it should be mostly ok, except that it might introduce a problem for bintar packages with an external dependency on libstdc++. HIGH-LEVEL SPECIFICATION: I did some investigation into this. The simple way to do this is to simply use g++ to link C++ objects. So this issue is really restricted to GCC compilation where we by default prefer to link with gcc even for C++ code. So this means building with CXX=g++ The consequences of doing this for the binaries is the addition of two additional run-time .so dependencies: libstdc++.so and libgcc_s.so. It still needs to be investigated if these additional dependencies are a problem for binary tarball packages, or if the ABI for those libraries are now as stable as libc.so. The libgcc_s.so is needed as a dependency to support exceptions between different object files, as they need to use the same code for stack unwinding. libstdc++.so is of course needed for access to C++ runtime. I researched into the possibility to instead link only specific plugins with g++, and continue to link the rest of the server with gcc. Unfortunately, this seems really hard to do in a proper way due to the way autotools works. At configure time, a script ./libtool is created with hardcoded compiler commands derived from $CC and $CXX. This script is then used to do the actual linking in Makefiles generated by Automake. I thus did not find a way to change the linker command on a per-makefile basis, as libtool is global to the project. One option would be to use separate configure.in for plugins, but this is quite an intrusive change. My conclusion is that the best way is to start using g++ for linking the entire server. This is no problem for binaries made for a specific distribution (Ubuntu, Debian, Centos), where the dependencies are handled by the package manager. If it is a big problem for binary tarball releases, at worst we can build multiple binary tarball releases for the different library versions we need to support. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Knielsen): Enable the use of libstdc++ in MariaDB (63)
by worklog-noreply＠askmonty.org 14 Jan '10

14 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Enable the use of libstdc++ in MariaDB CREATION DATE..: Wed, 11 Nov 2009, 13:19 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 63 (http://askmonty.org/worklog/?tid=63) VERSION........: Server-5.2 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Thu, 14 Jan 2010, 13:45)=-=- High-Level Specification modified. --- /tmp/wklog.63.old.13967 2010-01-14 11:45:49.000000000 +0000 +++ /tmp/wklog.63.new.13967 2010-01-14 11:45:49.000000000 +0000 @@ -1 +1,37 @@ +I did some investigation into this. + +The simple way to do this is to simply use g++ to link C++ objects. So this +issue is really restricted to GCC compilation where we by default prefer to +link with gcc even for C++ code. So this means building with + + CXX=g++ + +The consequences of doing this for the binaries is the addition of two +additional run-time .so dependencies: libstdc++.so and libgcc_s.so. + +It still needs to be investigated if these additional dependencies are a +problem for binary tarball packages, or if the ABI for those libraries are now +as stable as libc.so. + +The libgcc_s.so is needed as a dependency to support exceptions between +different object files, as they need to use the same code for stack unwinding. +libstdc++.so is of course needed for access to C++ runtime. + +I researched into the possibility to instead link only specific plugins with +g++, and continue to link the rest of the server with gcc. Unfortunately, this +seems really hard to do in a proper way due to the way autotools works. At +configure time, a script ./libtool is created with hardcoded compiler commands +derived from $CC and $CXX. This script is then used to do the actual linking +in Makefiles generated by Automake. I thus did not find a way to change the +linker command on a per-makefile basis, as libtool is global to the project. + +One option would be to use separate configure.in for plugins, but this is +quite an intrusive change. + +My conclusion is that the best way is to start using g++ for linking the +entire server. This is no problem for binaries made for a specific +distribution (Ubuntu, Debian, Centos), where the dependencies are handled by +the package manager. If it is a big problem for binary tarball releases, at +worst we can build multiple binary tarball releases for the different library +versions we need to support. -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Version updated. --- /tmp/wklog.63.old.19522 2010-01-12 16:26:23.000000000 +0200 +++ /tmp/wklog.63.new.19522 2010-01-12 16:26:23.000000000 +0200 @@ -1 +1 @@ -Connector/.NET-5.2 +Server-5.2 -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Category updated. --- /tmp/wklog.63.old.19506 2010-01-12 16:26:15.000000000 +0200 +++ /tmp/wklog.63.new.19506 2010-01-12 16:26:15.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog DESCRIPTION: Enable the use of libstdc++ in MariaDB. As time goes on, more and more plugins and external code/library will need linking to libstdc++ for stuff it uses. I have already seen this happen several times, with extra work needed to integrate things properly. It would be nice to have a general solution for this so that it is not necessary to spend time on individual solutions in each case. It also needs to be considered what the impact of this will be for the server in terms of binary compatibility, performance etc. I think it should be mostly ok, except that it might introduce a problem for bintar packages with an external dependency on libstdc++. HIGH-LEVEL SPECIFICATION: I did some investigation into this. The simple way to do this is to simply use g++ to link C++ objects. So this issue is really restricted to GCC compilation where we by default prefer to link with gcc even for C++ code. So this means building with CXX=g++ The consequences of doing this for the binaries is the addition of two additional run-time .so dependencies: libstdc++.so and libgcc_s.so. It still needs to be investigated if these additional dependencies are a problem for binary tarball packages, or if the ABI for those libraries are now as stable as libc.so. The libgcc_s.so is needed as a dependency to support exceptions between different object files, as they need to use the same code for stack unwinding. libstdc++.so is of course needed for access to C++ runtime. I researched into the possibility to instead link only specific plugins with g++, and continue to link the rest of the server with gcc. Unfortunately, this seems really hard to do in a proper way due to the way autotools works. At configure time, a script ./libtool is created with hardcoded compiler commands derived from $CC and $CXX. This script is then used to do the actual linking in Makefiles generated by Automake. I thus did not find a way to change the linker command on a per-makefile basis, as libtool is global to the project. One option would be to use separate configure.in for plugins, but this is quite an intrusive change. My conclusion is that the best way is to start using g++ for linking the entire server. This is no problem for binaries made for a specific distribution (Ubuntu, Debian, Centos), where the dependencies are handled by the package manager. If it is a big problem for binary tarball releases, at worst we can build multiple binary tarball releases for the different library versions we need to support. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Knielsen): Enable the use of libstdc++ in MariaDB (63)
by worklog-noreply＠askmonty.org 14 Jan '10

14 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Enable the use of libstdc++ in MariaDB CREATION DATE..: Wed, 11 Nov 2009, 13:19 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 63 (http://askmonty.org/worklog/?tid=63) VERSION........: Server-5.2 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Thu, 14 Jan 2010, 13:45)=-=- High-Level Specification modified. --- /tmp/wklog.63.old.13967 2010-01-14 11:45:49.000000000 +0000 +++ /tmp/wklog.63.new.13967 2010-01-14 11:45:49.000000000 +0000 @@ -1 +1,37 @@ +I did some investigation into this. + +The simple way to do this is to simply use g++ to link C++ objects. So this +issue is really restricted to GCC compilation where we by default prefer to +link with gcc even for C++ code. So this means building with + + CXX=g++ + +The consequences of doing this for the binaries is the addition of two +additional run-time .so dependencies: libstdc++.so and libgcc_s.so. + +It still needs to be investigated if these additional dependencies are a +problem for binary tarball packages, or if the ABI for those libraries are now +as stable as libc.so. + +The libgcc_s.so is needed as a dependency to support exceptions between +different object files, as they need to use the same code for stack unwinding. +libstdc++.so is of course needed for access to C++ runtime. + +I researched into the possibility to instead link only specific plugins with +g++, and continue to link the rest of the server with gcc. Unfortunately, this +seems really hard to do in a proper way due to the way autotools works. At +configure time, a script ./libtool is created with hardcoded compiler commands +derived from $CC and $CXX. This script is then used to do the actual linking +in Makefiles generated by Automake. I thus did not find a way to change the +linker command on a per-makefile basis, as libtool is global to the project. + +One option would be to use separate configure.in for plugins, but this is +quite an intrusive change. + +My conclusion is that the best way is to start using g++ for linking the +entire server. This is no problem for binaries made for a specific +distribution (Ubuntu, Debian, Centos), where the dependencies are handled by +the package manager. If it is a big problem for binary tarball releases, at +worst we can build multiple binary tarball releases for the different library +versions we need to support. -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Version updated. --- /tmp/wklog.63.old.19522 2010-01-12 16:26:23.000000000 +0200 +++ /tmp/wklog.63.new.19522 2010-01-12 16:26:23.000000000 +0200 @@ -1 +1 @@ -Connector/.NET-5.2 +Server-5.2 -=-=(Guest - Tue, 12 Jan 2010, 16:26)=-=- Category updated. --- /tmp/wklog.63.old.19506 2010-01-12 16:26:15.000000000 +0200 +++ /tmp/wklog.63.new.19506 2010-01-12 16:26:15.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog DESCRIPTION: Enable the use of libstdc++ in MariaDB. As time goes on, more and more plugins and external code/library will need linking to libstdc++ for stuff it uses. I have already seen this happen several times, with extra work needed to integrate things properly. It would be nice to have a general solution for this so that it is not necessary to spend time on individual solutions in each case. It also needs to be considered what the impact of this will be for the server in terms of binary compatibility, performance etc. I think it should be mostly ok, except that it might introduce a problem for bintar packages with an external dependency on libstdc++. HIGH-LEVEL SPECIFICATION: I did some investigation into this. The simple way to do this is to simply use g++ to link C++ objects. So this issue is really restricted to GCC compilation where we by default prefer to link with gcc even for C++ code. So this means building with CXX=g++ The consequences of doing this for the binaries is the addition of two additional run-time .so dependencies: libstdc++.so and libgcc_s.so. It still needs to be investigated if these additional dependencies are a problem for binary tarball packages, or if the ABI for those libraries are now as stable as libc.so. The libgcc_s.so is needed as a dependency to support exceptions between different object files, as they need to use the same code for stack unwinding. libstdc++.so is of course needed for access to C++ runtime. I researched into the possibility to instead link only specific plugins with g++, and continue to link the rest of the server with gcc. Unfortunately, this seems really hard to do in a proper way due to the way autotools works. At configure time, a script ./libtool is created with hardcoded compiler commands derived from $CC and $CXX. This script is then used to do the actual linking in Makefiles generated by Automake. I thus did not find a way to change the linker command on a per-makefile basis, as libtool is global to the project. One option would be to use separate configure.in for plugins, but this is quite an intrusive change. My conclusion is that the best way is to start using g++ for linking the entire server. This is no problem for binaries made for a specific distribution (Ubuntu, Debian, Centos), where the dependencies are handled by the package manager. If it is a big problem for binary tarball releases, at worst we can build multiple binary tarball releases for the different library versions we need to support. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2796: Merged patch from Percona to get proper fix for compilation issue of srv0srv.c on Solaris
by noreply＠launchpad.net 12 Jan '10

12 Jan '10

------------------------------------------------------------ revno: 2796 committer: Michael Widenius <monty(a)askmonty.org> branch nick: maria-5.1 timestamp: Tue 2010-01-12 19:31:11 +0200 message: Merged patch from Percona to get proper fix for compilation issue of srv0srv.c on Solaris modified: sql/sql_cache.cc storage/xtradb/srv/srv0srv.c -- 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

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2796)
by Michael Widenius 12 Jan '10

12 Jan '10

#At lp:maria based on revid:monty@askmonty.org-20100112102853-jc9hnlfg5o853oo5 2796 Michael Widenius 2010-01-12 Merged patch from Percona to get proper fix for compilation issue of srv0srv.c on Solaris modified: sql/sql_cache.cc storage/xtradb/srv/srv0srv.c per-file messages: sql/sql_cache.cc Fixed wrong comment storage/xtradb/srv/srv0srv.c Merged patch from Percona to get proper fix for compilation issue of srv0srv.c on Solaris === modified file 'sql/sql_cache.cc' --- a/sql/sql_cache.cc 2009-12-03 11:19:05 +0000 +++ b/sql/sql_cache.cc 2010-01-12 17:31:11 +0000 @@ -1306,8 +1306,8 @@ end: to the user. RESULTS - 1 Query was not cached. - 0 The query was cached and user was sent the result. + 0 Query was not cached. + 1 The query was cached and user was sent the result. -1 The query was cached but we didn't have rights to use it. No error is sent to the client yet. === modified file 'storage/xtradb/srv/srv0srv.c' --- a/storage/xtradb/srv/srv0srv.c 2010-01-06 21:27:53 +0000 +++ b/storage/xtradb/srv/srv0srv.c 2010-01-12 17:31:11 +0000 @@ -81,7 +81,6 @@ Created 10/8/1995 Heikki Tuuri #include "ut0mem.h" #include "ut0ut.h" #include "os0proc.h" -#include "os0sync.h" #include "mem0mem.h" #include "mem0pool.h" #include "sync0sync.h" @@ -103,6 +102,10 @@ Created 10/8/1995 Heikki Tuuri #include "row0mysql.h" #include "ha_prototypes.h" #include "trx0i_s.h" +#include "os0sync.h" /* for HAVE_ATOMIC_BUILTINS */ + +/* prototypes for new functions added to ha_innodb.cc */ +ibool innobase_get_slow_log(); /* This is set to TRUE if the MySQL user has set it in MySQL; currently affects only FOREIGN KEY definition parsing */ @@ -162,6 +165,7 @@ UNIV_INTERN ulint* srv_data_file_sizes = UNIV_INTERN ibool srv_extra_undoslots = FALSE; UNIV_INTERN ibool srv_fast_recovery = FALSE; +UNIV_INTERN ibool srv_recovery_stats = FALSE; UNIV_INTERN ibool srv_use_purge_thread = FALSE; @@ -1077,7 +1081,7 @@ UNIV_INTERN ulong srv_max_purge_lag = 0 Puts an OS thread to wait if there are too many concurrent threads (>= srv_thread_concurrency) inside InnoDB. The threads wait in a FIFO queue. */ -#ifdef INNODB_RW_LOCKS_USE_ATOMICS +#ifdef HAVE_ATOMIC_BUILTINS static void enter_innodb_with_tickets(trx_t* trx) { @@ -1175,7 +1179,7 @@ srv_conc_enter_innodb( return; } -#ifdef INNODB_RW_LOCKS_USE_ATOMICS +#ifdef HAVE_ATOMIC_BUILTINS if (srv_thread_concurrency_timer_based) { srv_conc_enter_innodb_timer_based(trx); return; @@ -1325,7 +1329,7 @@ srv_conc_force_enter_innodb( } ut_ad(srv_conc_n_threads >= 0); -#ifdef INNODB_RW_LOCKS_USE_ATOMICS +#ifdef HAVE_ATOMIC_BUILTINS if (srv_thread_concurrency_timer_based) { os_atomic_increment_lint(&srv_conc_n_threads, 1); trx->declared_to_be_inside_innodb = TRUE; @@ -1366,7 +1370,7 @@ srv_conc_force_exit_innodb( return; } -#ifdef INNODB_RW_LOCKS_USE_ATOMICS +#ifdef HAVE_ATOMIC_BUILTINS if (srv_thread_concurrency_timer_based) { srv_conc_exit_innodb_timer_based(trx); return;

1 0

[Maria-developers] Updated (by Psergey): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 12 Jan '10

12 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Tue, 12 Jan 2010, 18:39)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.31666 2010-01-12 18:39:43.000000000 +0200 +++ /tmp/wklog.66.new.31666 2010-01-12 18:39:43.000000000 +0200 @@ -4,3 +4,99 @@ To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. + + +<contents> +1. Scope of the task +2. Data structure used for the cache +3. Cache size +4. Interplay with other subquery optimizations +5. User interface +</contents> + +1. Scope of the task +-------------------- +This WL should handle all subquery predicates, i.e. it should handle these +cases: + + outer_expr IN (SELECT correlated_select) + outer_expr $CMP$ ALL/ANY (SELECT correlated_select) + EXISTS (SELECT correlated_select) + scalar-context subquery: (SELECT correlated_select) + +The cache will maintain + + (outer_expr, correlation_references)-> subquery_item_result + +mapping, where +- correlation_references is a list of tablename.column_name that are referred + from the correlated_select but tablename is a table that is ouside the + subquery. +- subquery_item_result is 'bool' for subquery predicates, and is of +some scalar or ROW(scalar1,...scalarN) type for scalar-context subquery. + +We dont support cases when outer_expr or correlation_references are blobs. + +2. Data structure used for the cache +------------------------------------ +There are two data structures available in the codebase that will allow fast +equality lookups: + +1. HASH (mysys/hash.c) tables +2. Temporary tables (the ones that are used for e.g. GROUP BY) + +None of them has any support for element eviction on overflow (using LRU or +some other policy). + +Query cache and MyISAM/Maria's key/page cache ought to support some eviction +mechanism, but code-wise it is not readily reusable, one will need to factor +it out (or copy it). + +We choose to use #2, and not to have any eviction policy. See subsequent +sections for details and reasoning behind the decision. + +3. Cache size +------------- +Typically, a cache has some maximum size and a policy which is used to +select a cache entry for removal when the cache becomes full (e.g. find +and remove the least [recently] used entry) + +For this WL entry we will use a cache of infinite size. The reasoning behind +this is that: +- is is easy to do: we have temporary tables that can grow to arbitrarily + large size while still providing the same insert/lookup interface. +- it suits us: unless the subquery is resolved with one index lookup, + hitting the cache would be many times cheaper than re-running the + subquery, so cache is worth having. + +4. Interplay with other subquery optimizations +---------------------------------------------- +* This WL entry should not care about IN->EXISTS transformation: caching for + IN subquery and result of its conversion to EXISTS would work in the same + way. + +* This optimization is orthogonal to <=>ANY -> MIN/MAX rewrite (it will + work/be useful irrespectively of whether the rewrite has been performed or + not) + +* TODO: compare this with materialization for uncorrelated IN-subqueries. Is + this basically the same? + A: no, it is not: + - IN-Materialization has to perform full materialization before it can + do the first subquery evaluation. This WL's code has almost no startup + costs. + - This optimization has temp.table of (corr_reference, predicate_value), + while IN-materialization will have (corr_reference) only. + +5. User interface +----------------- +* There will be an @@optimizer_switch flag to turn this optimization on and + off (TODO: name of the flag?) + +* TODO: how do we show this in EXPLAIN [EXTENDED]? The most easiest is to + print something in the warning text of EXPLAIN EXTEDED that would indicate + use of cache. + +* temporary table sizing (max size for heap table, whether to use MyISAM or + Maria) will be controlled with common temp.table control variables. + -=-=(Psergey - Mon, 11 Jan 2010, 13:25)=-=- As of today, there is code that - collects outside references - creates a temporary table with index that would allow for fast lookups. there is no code to - fill the temporary table - make lookups into it Reported zero hours worked. Estimate unchanged. -=-=(Sanja - Fri, 11 Dec 2009, 15:09)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.28164 2009-12-11 15:09:15.000000000 +0200 +++ /tmp/wklog.66.new.28164 2009-12-11 15:09:15.000000000 +0200 @@ -3,4 +3,4 @@ To check/discuss: -Are there sens to put subquery cache on all levels of subqueries of on highest. + To put subquery cache on all levels of subqueries or on highest level only. -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. <contents> 1. Scope of the task 2. Data structure used for the cache 3. Cache size 4. Interplay with other subquery optimizations 5. User interface </contents> 1. Scope of the task -------------------- This WL should handle all subquery predicates, i.e. it should handle these cases: outer_expr IN (SELECT correlated_select) outer_expr $CMP$ ALL/ANY (SELECT correlated_select) EXISTS (SELECT correlated_select) scalar-context subquery: (SELECT correlated_select) The cache will maintain (outer_expr, correlation_references)-> subquery_item_result mapping, where - correlation_references is a list of tablename.column_name that are referred from the correlated_select but tablename is a table that is ouside the subquery. - subquery_item_result is 'bool' for subquery predicates, and is of some scalar or ROW(scalar1,...scalarN) type for scalar-context subquery. We dont support cases when outer_expr or correlation_references are blobs. 2. Data structure used for the cache ------------------------------------ There are two data structures available in the codebase that will allow fast equality lookups: 1. HASH (mysys/hash.c) tables 2. Temporary tables (the ones that are used for e.g. GROUP BY) None of them has any support for element eviction on overflow (using LRU or some other policy). Query cache and MyISAM/Maria's key/page cache ought to support some eviction mechanism, but code-wise it is not readily reusable, one will need to factor it out (or copy it). We choose to use #2, and not to have any eviction policy. See subsequent sections for details and reasoning behind the decision. 3. Cache size ------------- Typically, a cache has some maximum size and a policy which is used to select a cache entry for removal when the cache becomes full (e.g. find and remove the least [recently] used entry) For this WL entry we will use a cache of infinite size. The reasoning behind this is that: - is is easy to do: we have temporary tables that can grow to arbitrarily large size while still providing the same insert/lookup interface. - it suits us: unless the subquery is resolved with one index lookup, hitting the cache would be many times cheaper than re-running the subquery, so cache is worth having. 4. Interplay with other subquery optimizations ---------------------------------------------- * This WL entry should not care about IN->EXISTS transformation: caching for IN subquery and result of its conversion to EXISTS would work in the same way. * This optimization is orthogonal to <=>ANY -> MIN/MAX rewrite (it will work/be useful irrespectively of whether the rewrite has been performed or not) * TODO: compare this with materialization for uncorrelated IN-subqueries. Is this basically the same? A: no, it is not: - IN-Materialization has to perform full materialization before it can do the first subquery evaluation. This WL's code has almost no startup costs. - This optimization has temp.table of (corr_reference, predicate_value), while IN-materialization will have (corr_reference) only. 5. User interface ----------------- * There will be an @@optimizer_switch flag to turn this optimization on and off (TODO: name of the flag?) * TODO: how do we show this in EXPLAIN [EXTENDED]? The most easiest is to print something in the warning text of EXPLAIN EXTEDED that would indicate use of cache. * temporary table sizing (max size for heap table, whether to use MyISAM or Maria) will be controlled with common temp.table control variables. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 12 Jan '10

12 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Tue, 12 Jan 2010, 18:39)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.31666 2010-01-12 18:39:43.000000000 +0200 +++ /tmp/wklog.66.new.31666 2010-01-12 18:39:43.000000000 +0200 @@ -4,3 +4,99 @@ To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. + + +<contents> +1. Scope of the task +2. Data structure used for the cache +3. Cache size +4. Interplay with other subquery optimizations +5. User interface +</contents> + +1. Scope of the task +-------------------- +This WL should handle all subquery predicates, i.e. it should handle these +cases: + + outer_expr IN (SELECT correlated_select) + outer_expr $CMP$ ALL/ANY (SELECT correlated_select) + EXISTS (SELECT correlated_select) + scalar-context subquery: (SELECT correlated_select) + +The cache will maintain + + (outer_expr, correlation_references)-> subquery_item_result + +mapping, where +- correlation_references is a list of tablename.column_name that are referred + from the correlated_select but tablename is a table that is ouside the + subquery. +- subquery_item_result is 'bool' for subquery predicates, and is of +some scalar or ROW(scalar1,...scalarN) type for scalar-context subquery. + +We dont support cases when outer_expr or correlation_references are blobs. + +2. Data structure used for the cache +------------------------------------ +There are two data structures available in the codebase that will allow fast +equality lookups: + +1. HASH (mysys/hash.c) tables +2. Temporary tables (the ones that are used for e.g. GROUP BY) + +None of them has any support for element eviction on overflow (using LRU or +some other policy). + +Query cache and MyISAM/Maria's key/page cache ought to support some eviction +mechanism, but code-wise it is not readily reusable, one will need to factor +it out (or copy it). + +We choose to use #2, and not to have any eviction policy. See subsequent +sections for details and reasoning behind the decision. + +3. Cache size +------------- +Typically, a cache has some maximum size and a policy which is used to +select a cache entry for removal when the cache becomes full (e.g. find +and remove the least [recently] used entry) + +For this WL entry we will use a cache of infinite size. The reasoning behind +this is that: +- is is easy to do: we have temporary tables that can grow to arbitrarily + large size while still providing the same insert/lookup interface. +- it suits us: unless the subquery is resolved with one index lookup, + hitting the cache would be many times cheaper than re-running the + subquery, so cache is worth having. + +4. Interplay with other subquery optimizations +---------------------------------------------- +* This WL entry should not care about IN->EXISTS transformation: caching for + IN subquery and result of its conversion to EXISTS would work in the same + way. + +* This optimization is orthogonal to <=>ANY -> MIN/MAX rewrite (it will + work/be useful irrespectively of whether the rewrite has been performed or + not) + +* TODO: compare this with materialization for uncorrelated IN-subqueries. Is + this basically the same? + A: no, it is not: + - IN-Materialization has to perform full materialization before it can + do the first subquery evaluation. This WL's code has almost no startup + costs. + - This optimization has temp.table of (corr_reference, predicate_value), + while IN-materialization will have (corr_reference) only. + +5. User interface +----------------- +* There will be an @@optimizer_switch flag to turn this optimization on and + off (TODO: name of the flag?) + +* TODO: how do we show this in EXPLAIN [EXTENDED]? The most easiest is to + print something in the warning text of EXPLAIN EXTEDED that would indicate + use of cache. + +* temporary table sizing (max size for heap table, whether to use MyISAM or + Maria) will be controlled with common temp.table control variables. + -=-=(Psergey - Mon, 11 Jan 2010, 13:25)=-=- As of today, there is code that - collects outside references - creates a temporary table with index that would allow for fast lookups. there is no code to - fill the temporary table - make lookups into it Reported zero hours worked. Estimate unchanged. -=-=(Sanja - Fri, 11 Dec 2009, 15:09)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.28164 2009-12-11 15:09:15.000000000 +0200 +++ /tmp/wklog.66.new.28164 2009-12-11 15:09:15.000000000 +0200 @@ -3,4 +3,4 @@ To check/discuss: -Are there sens to put subquery cache on all levels of subqueries of on highest. + To put subquery cache on all levels of subqueries or on highest level only. -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. <contents> 1. Scope of the task 2. Data structure used for the cache 3. Cache size 4. Interplay with other subquery optimizations 5. User interface </contents> 1. Scope of the task -------------------- This WL should handle all subquery predicates, i.e. it should handle these cases: outer_expr IN (SELECT correlated_select) outer_expr $CMP$ ALL/ANY (SELECT correlated_select) EXISTS (SELECT correlated_select) scalar-context subquery: (SELECT correlated_select) The cache will maintain (outer_expr, correlation_references)-> subquery_item_result mapping, where - correlation_references is a list of tablename.column_name that are referred from the correlated_select but tablename is a table that is ouside the subquery. - subquery_item_result is 'bool' for subquery predicates, and is of some scalar or ROW(scalar1,...scalarN) type for scalar-context subquery. We dont support cases when outer_expr or correlation_references are blobs. 2. Data structure used for the cache ------------------------------------ There are two data structures available in the codebase that will allow fast equality lookups: 1. HASH (mysys/hash.c) tables 2. Temporary tables (the ones that are used for e.g. GROUP BY) None of them has any support for element eviction on overflow (using LRU or some other policy). Query cache and MyISAM/Maria's key/page cache ought to support some eviction mechanism, but code-wise it is not readily reusable, one will need to factor it out (or copy it). We choose to use #2, and not to have any eviction policy. See subsequent sections for details and reasoning behind the decision. 3. Cache size ------------- Typically, a cache has some maximum size and a policy which is used to select a cache entry for removal when the cache becomes full (e.g. find and remove the least [recently] used entry) For this WL entry we will use a cache of infinite size. The reasoning behind this is that: - is is easy to do: we have temporary tables that can grow to arbitrarily large size while still providing the same insert/lookup interface. - it suits us: unless the subquery is resolved with one index lookup, hitting the cache would be many times cheaper than re-running the subquery, so cache is worth having. 4. Interplay with other subquery optimizations ---------------------------------------------- * This WL entry should not care about IN->EXISTS transformation: caching for IN subquery and result of its conversion to EXISTS would work in the same way. * This optimization is orthogonal to <=>ANY -> MIN/MAX rewrite (it will work/be useful irrespectively of whether the rewrite has been performed or not) * TODO: compare this with materialization for uncorrelated IN-subqueries. Is this basically the same? A: no, it is not: - IN-Materialization has to perform full materialization before it can do the first subquery evaluation. This WL's code has almost no startup costs. - This optimization has temp.table of (corr_reference, predicate_value), while IN-materialization will have (corr_reference) only. 5. User interface ----------------- * There will be an @@optimizer_switch flag to turn this optimization on and off (TODO: name of the flag?) * TODO: how do we show this in EXPLAIN [EXTENDED]? The most easiest is to print something in the warning text of EXPLAIN EXTEDED that would indicate use of cache. * temporary table sizing (max size for heap table, whether to use MyISAM or Maria) will be controlled with common temp.table control variables. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0