- developers - lists.mariadb.org

[Maria-developers] Rev 12: Extracted system depended variables into configuration files, which in file:///Users/hakan/work/monty_program/mariadb-tools/
by Hakan Kuecuekyilmaz 03 Mar '10

03 Mar '10

At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 12 revision-id: hakan(a)askmonty.org-20100303132525-euyw1c8whdv8fkjl parent: hakan(a)askmonty.org-20100226170755-uucbsqe4n60b50c1 committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> branch nick: mariadb-tools timestamp: Wed 2010-03-03 14:25:25 +0100 message: Extracted system depended variables into configuration files, which are identified by hostname. === added directory 'sysbench/conf' === added file 'sysbench/conf/au0013.inc' --- a/sysbench/conf/au0013.inc 1970-01-01 00:00:00 +0000 +++ b/sysbench/conf/au0013.inc 2010-03-03 13:25:25 +0000 @@ -0,0 +1,28 @@ +# Configuration file for host: au0013 +# +# OS: Mac OS X 10.6.2 +# Platform: x86_64 +# CPU: Dual-core Intel Core 2 Duo @ 2.8GHz +# RAM: 4GB +# Disk(s): 1 x ST9500420ASG S-ATA + +# +# Adjust the following paths according to your installation. +# + +# We need sysbench >= v0.5. +SYSBENCH='/usr/local/bin/sysbench' + +# We need bzr >= 1.18.1 +BZR='/usr/local/bin/bzr' + +# OProfile binaries. +# There is no OProfile on Mac OS X. +# TODO: Find OProfile equivalent for Mac OS X. +OPCONTROL='' + +# System statistic binaries. +IOSTAT='/usr/sbin/iostat' +IOSTAT_DEVICE='disk0' +# Note: sar works different on Mac OS X. +SAR='/usr/bin/sar' === added file 'sysbench/conf/lu0012.inc' --- a/sysbench/conf/lu0012.inc 1970-01-01 00:00:00 +0000 +++ b/sysbench/conf/lu0012.inc 2010-03-03 13:25:25 +0000 @@ -0,0 +1,25 @@ +# Configuration file for host: lu0012 +# +# OS: Ubuntu 9.10 +# Platform: x86_64 +# CPU: Dual-core Intel Core 2 Duo @ 2.80GHz +# RAM: 4GB +# Disk(s): 1 x HITACHI HTS722016K9SA00 S-ATA + +# +# Adjust the following paths according to your installation. +# + +# We need sysbench >= v0.5. +SYSBENCH='/usr/local/bin/sysbench' + +# We need bzr >= 1.18.1 +BZR='/usr/bin/bzr' + +# OProfile binaries. +OPCONTROL='/usr/bin/opcontrol' + +# System statistic binaries. +IOSTAT='/usr/bin/iostat' +IOSTAT_DEVICE='/dev/sda' +SAR='/usr/bin/sar' === added file 'sysbench/conf/work.inc' --- a/sysbench/conf/work.inc 1970-01-01 00:00:00 +0000 +++ b/sysbench/conf/work.inc 2010-03-03 13:25:25 +0000 @@ -0,0 +1,25 @@ +# Configuration file for host: work +# +# OS: openSUSE 11.1 (x86_64) +# Platform: x86_64 +# CPU: Two socket quad-core Intel Xeon @ 3.00GHz +# RAM: 8GB +# Disk(s): 1 x WDC WD2500JS-00NCB1 S-ATA + +# +# Adjust the following paths according to your installation. +# + +# We need sysbench >= v0.5. +SYSBENCH='/usr/local/bin/sysbench' + +# We need bzr >= 1.18.1 +BZR='/usr/local/bin/bzr' + +# OProfile binaries. +OPCONTROL='/usr/bin/opcontrol' + +# System statistic binaries. +IOSTAT='/usr/bin/iostat' +IOSTAT_DEVICE='/dev/sda' +SAR='/usr/bin/sar' === modified file 'sysbench/run-sysbench.sh' --- a/sysbench/run-sysbench.sh 2010-02-26 16:24:13 +0000 +++ b/sysbench/run-sysbench.sh 2010-03-03 13:25:25 +0000 @@ -34,17 +34,24 @@ fi # -# Binaries. +# Read system dependent settings. +# +if [ ! -f conf/${HOSTNAME}.inc ]; then + echo "[ERROR]: Could not find config file: conf/${HOSTNAME}.conf." + echo " Please create one." + + exit 1 +else + source conf/${HOSTNAME}.inc +fi + +# +# Binaries used after building from source. You do not have to +# change these, except you exactly know what you are doing. # MYSQLADMIN='client/mysqladmin' # -# Adjust the following paths according to your installation. -# -SYSBENCH='/usr/local/bin/sysbench' -BZR='/usr/local/bin/bzr' - -# # Variables. # TEMP_DIR='/tmp'

1 0

[Maria-developers] request for advice on managing a mysql patch in bzr
by MARK CALLAGHAN 28 Feb '10

28 Feb '10

I know this question is more about official MySQL than MariaDB, but it is also about sharing code we write at Facebook and all of the patches we publish can be reused by others as long as others accept BSD contributions. I am getting ready to publish facebook patches for mysql 5.1. We use git internally and are based on MySQL 5.1.44. We intend to stay current with 5.1 releases. What is the best way to manage the code in launchpad across new releases of official MySQL. My current plan is: 1) import MySQL 5.1.44, commit 2) apply patches from my git repo to my bzr repo, commit after each one 3) publish But there will soon be a release of 5.1.45 or 5.1.46 and I don't want to repeat the steps above each time that happens. Do I just publish a large patch for 5.1.44 -> 5.1.4X and get on with my work? Is there a better way? -- Mark Callaghan mdcallag(a)gmail.com

3 3

[Maria-developers] Updated (by Psergey): Subqueries backport: fix known semi-join subquery bugs (92)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries backport: fix known semi-join subquery bugs CREATION DATE..: Sun, 28 Feb 2010, 14:02 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 92 (http://askmonty.org/worklog/?tid=92) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:41)=-=- High Level Description modified. --- /tmp/wklog.92.old.24539 2010-02-28 16:41:06.000000000 +0000 +++ /tmp/wklog.92.new.24539 2010-02-28 16:41:06.000000000 +0000 @@ -1 +1,4 @@ We must fix known subquery semi-join bugs. +* outer join + semi join problem +* Duplicate Weedout + join caching problem. + -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- High Level Description modified. --- /tmp/wklog.92.old.22593 2010-02-28 15:06:23.000000000 +0000 +++ /tmp/wklog.92.new.22593 2010-02-28 15:06:23.000000000 +0000 @@ -1 +1 @@ - +We must fix known subquery semi-join bugs. -=-=(Psergey - Sun, 28 Feb 2010, 15:03)=-=- Title modified. --- /tmp/wklog.92.old.22572 2010-02-28 15:03:51.000000000 +0000 +++ /tmp/wklog.92.new.22572 2010-02-28 15:03:51.000000000 +0000 @@ -1 +1 @@ -Unused +Subqueries backport: fix known semi-join subquery bugs -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency deleted: 91 no longer depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- High Level Description modified. --- /tmp/wklog.92.old.22267 2010-02-28 14:57:52.000000000 +0000 +++ /tmp/wklog.92.new.22267 2010-02-28 14:57:52.000000000 +0000 @@ -1 +1 @@ -We must fix known semi-join subquery bugs. + -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- Title modified. --- /tmp/wklog.92.old.22249 2010-02-28 14:57:41.000000000 +0000 +++ /tmp/wklog.92.new.22249 2010-02-28 14:57:41.000000000 +0000 @@ -1 +1 @@ -Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Unused -=-=(Psergey - Sun, 28 Feb 2010, 14:51)=-=- High Level Description modified. --- /tmp/wklog.92.old.21961 2010-02-28 14:51:06.000000000 +0000 +++ /tmp/wklog.92.new.21961 2010-02-28 14:51:06.000000000 +0000 @@ -1,18 +1 @@ -Consider the following case: - -SELECT * FROM big_table -WHERE oe IN (SELECT ie FROM table_with_few_groups - WHERE ... - GROUP BY group_col) AND ... - -Here the best way to execute the query is: - - Materialize the subquery; - # now run the join: - for each record R1 in materialized table - for each record R2 in big_table such that oe=R1 - pass R2 to output - -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. - +We must fix known semi-join subquery bugs. -=-=(Psergey - Sun, 28 Feb 2010, 14:03)=-=- Observers changed: Psergey,Igor,Timour DESCRIPTION: We must fix known subquery semi-join bugs. * outer join + semi join problem * Duplicate Weedout + join caching problem. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries backport: fix known semi-join subquery bugs (92)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries backport: fix known semi-join subquery bugs CREATION DATE..: Sun, 28 Feb 2010, 14:02 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 92 (http://askmonty.org/worklog/?tid=92) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:41)=-=- High Level Description modified. --- /tmp/wklog.92.old.24539 2010-02-28 16:41:06.000000000 +0000 +++ /tmp/wklog.92.new.24539 2010-02-28 16:41:06.000000000 +0000 @@ -1 +1,4 @@ We must fix known subquery semi-join bugs. +* outer join + semi join problem +* Duplicate Weedout + join caching problem. + -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- High Level Description modified. --- /tmp/wklog.92.old.22593 2010-02-28 15:06:23.000000000 +0000 +++ /tmp/wklog.92.new.22593 2010-02-28 15:06:23.000000000 +0000 @@ -1 +1 @@ - +We must fix known subquery semi-join bugs. -=-=(Psergey - Sun, 28 Feb 2010, 15:03)=-=- Title modified. --- /tmp/wklog.92.old.22572 2010-02-28 15:03:51.000000000 +0000 +++ /tmp/wklog.92.new.22572 2010-02-28 15:03:51.000000000 +0000 @@ -1 +1 @@ -Unused +Subqueries backport: fix known semi-join subquery bugs -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency deleted: 91 no longer depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- High Level Description modified. --- /tmp/wklog.92.old.22267 2010-02-28 14:57:52.000000000 +0000 +++ /tmp/wklog.92.new.22267 2010-02-28 14:57:52.000000000 +0000 @@ -1 +1 @@ -We must fix known semi-join subquery bugs. + -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- Title modified. --- /tmp/wklog.92.old.22249 2010-02-28 14:57:41.000000000 +0000 +++ /tmp/wklog.92.new.22249 2010-02-28 14:57:41.000000000 +0000 @@ -1 +1 @@ -Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Unused -=-=(Psergey - Sun, 28 Feb 2010, 14:51)=-=- High Level Description modified. --- /tmp/wklog.92.old.21961 2010-02-28 14:51:06.000000000 +0000 +++ /tmp/wklog.92.new.21961 2010-02-28 14:51:06.000000000 +0000 @@ -1,18 +1 @@ -Consider the following case: - -SELECT * FROM big_table -WHERE oe IN (SELECT ie FROM table_with_few_groups - WHERE ... - GROUP BY group_col) AND ... - -Here the best way to execute the query is: - - Materialize the subquery; - # now run the join: - for each record R1 in materialized table - for each record R2 in big_table such that oe=R1 - pass R2 to output - -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. - +We must fix known semi-join subquery bugs. -=-=(Psergey - Sun, 28 Feb 2010, 14:03)=-=- Observers changed: Psergey,Igor,Timour DESCRIPTION: We must fix known subquery semi-join bugs. * outer join + semi join problem * Duplicate Weedout + join caching problem. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries backport: fix known semi-join subquery bugs (92)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries backport: fix known semi-join subquery bugs CREATION DATE..: Sun, 28 Feb 2010, 14:02 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 92 (http://askmonty.org/worklog/?tid=92) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:41)=-=- High Level Description modified. --- /tmp/wklog.92.old.24539 2010-02-28 16:41:06.000000000 +0000 +++ /tmp/wklog.92.new.24539 2010-02-28 16:41:06.000000000 +0000 @@ -1 +1,4 @@ We must fix known subquery semi-join bugs. +* outer join + semi join problem +* Duplicate Weedout + join caching problem. + -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- High Level Description modified. --- /tmp/wklog.92.old.22593 2010-02-28 15:06:23.000000000 +0000 +++ /tmp/wklog.92.new.22593 2010-02-28 15:06:23.000000000 +0000 @@ -1 +1 @@ - +We must fix known subquery semi-join bugs. -=-=(Psergey - Sun, 28 Feb 2010, 15:03)=-=- Title modified. --- /tmp/wklog.92.old.22572 2010-02-28 15:03:51.000000000 +0000 +++ /tmp/wklog.92.new.22572 2010-02-28 15:03:51.000000000 +0000 @@ -1 +1 @@ -Unused +Subqueries backport: fix known semi-join subquery bugs -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency deleted: 91 no longer depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- High Level Description modified. --- /tmp/wklog.92.old.22267 2010-02-28 14:57:52.000000000 +0000 +++ /tmp/wklog.92.new.22267 2010-02-28 14:57:52.000000000 +0000 @@ -1 +1 @@ -We must fix known semi-join subquery bugs. + -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- Title modified. --- /tmp/wklog.92.old.22249 2010-02-28 14:57:41.000000000 +0000 +++ /tmp/wklog.92.new.22249 2010-02-28 14:57:41.000000000 +0000 @@ -1 +1 @@ -Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Unused -=-=(Psergey - Sun, 28 Feb 2010, 14:51)=-=- High Level Description modified. --- /tmp/wklog.92.old.21961 2010-02-28 14:51:06.000000000 +0000 +++ /tmp/wklog.92.new.21961 2010-02-28 14:51:06.000000000 +0000 @@ -1,18 +1 @@ -Consider the following case: - -SELECT * FROM big_table -WHERE oe IN (SELECT ie FROM table_with_few_groups - WHERE ... - GROUP BY group_col) AND ... - -Here the best way to execute the query is: - - Materialize the subquery; - # now run the join: - for each record R1 in materialized table - for each record R2 in big_table such that oe=R1 - pass R2 to output - -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. - +We must fix known semi-join subquery bugs. -=-=(Psergey - Sun, 28 Feb 2010, 14:03)=-=- Observers changed: Psergey,Igor,Timour DESCRIPTION: We must fix known subquery semi-join bugs. * outer join + semi join problem * Duplicate Weedout + join caching problem. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries backport: fix known semi-join subquery bugs (92)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries backport: fix known semi-join subquery bugs CREATION DATE..: Sun, 28 Feb 2010, 14:02 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 92 (http://askmonty.org/worklog/?tid=92) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:41)=-=- High Level Description modified. --- /tmp/wklog.92.old.24539 2010-02-28 16:41:06.000000000 +0000 +++ /tmp/wklog.92.new.24539 2010-02-28 16:41:06.000000000 +0000 @@ -1 +1,4 @@ We must fix known subquery semi-join bugs. +* outer join + semi join problem +* Duplicate Weedout + join caching problem. + -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 15:06)=-=- High Level Description modified. --- /tmp/wklog.92.old.22593 2010-02-28 15:06:23.000000000 +0000 +++ /tmp/wklog.92.new.22593 2010-02-28 15:06:23.000000000 +0000 @@ -1 +1 @@ - +We must fix known subquery semi-join bugs. -=-=(Psergey - Sun, 28 Feb 2010, 15:03)=-=- Title modified. --- /tmp/wklog.92.old.22572 2010-02-28 15:03:51.000000000 +0000 +++ /tmp/wklog.92.new.22572 2010-02-28 15:03:51.000000000 +0000 @@ -1 +1 @@ -Unused +Subqueries backport: fix known semi-join subquery bugs -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency deleted: 91 no longer depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:58)=-=- Dependency created: 91 now depends on 92 -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- High Level Description modified. --- /tmp/wklog.92.old.22267 2010-02-28 14:57:52.000000000 +0000 +++ /tmp/wklog.92.new.22267 2010-02-28 14:57:52.000000000 +0000 @@ -1 +1 @@ -We must fix known semi-join subquery bugs. + -=-=(Psergey - Sun, 28 Feb 2010, 14:57)=-=- Title modified. --- /tmp/wklog.92.old.22249 2010-02-28 14:57:41.000000000 +0000 +++ /tmp/wklog.92.new.22249 2010-02-28 14:57:41.000000000 +0000 @@ -1 +1 @@ -Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Unused -=-=(Psergey - Sun, 28 Feb 2010, 14:51)=-=- High Level Description modified. --- /tmp/wklog.92.old.21961 2010-02-28 14:51:06.000000000 +0000 +++ /tmp/wklog.92.new.21961 2010-02-28 14:51:06.000000000 +0000 @@ -1,18 +1 @@ -Consider the following case: - -SELECT * FROM big_table -WHERE oe IN (SELECT ie FROM table_with_few_groups - WHERE ... - GROUP BY group_col) AND ... - -Here the best way to execute the query is: - - Materialize the subquery; - # now run the join: - for each record R1 in materialized table - for each record R2 in big_table such that oe=R1 - pass R2 to output - -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. - +We must fix known semi-join subquery bugs. -=-=(Psergey - Sun, 28 Feb 2010, 14:03)=-=- Observers changed: Psergey,Igor,Timour DESCRIPTION: We must fix known subquery semi-join bugs. * outer join + semi join problem * Duplicate Weedout + join caching problem. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 28 Feb '10

28 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0