- developers - lists.mariadb.org

Re: [Maria-developers] LOCK_plugin in pbxt_init()
by Paul McCullagh 03 Mar '10

03 Mar '10

Hi Sergei, On Mar 3, 2010, at 10:06 AM, Sergei Golubchik wrote: > Hi, Paul! > > On Mar 01, Paul McCullagh wrote: >> >> Thanks for the info. That was a problem that caused me a lot of >> trouble until we finally made recovery asynchronous. >> >> The unlock hack can probably be removed altogether now. I have >> checked, and it has been removed in PBXT 1.1. Since recovery is >> asynchronous, waiting for LOCK_plugin to be released is no longer a >> problem. >> >> Now that you have fixed the problem in MariaDB, we just have to wait >> for it to be done in MySQL, and then we could consider making >> recovery >> synchronous again. > > Okay. So, what do you prefer me to do now, in Maria-5.2 ? > Comment out the mutex lock/unlock ? > Remove the whole #if ? > Use PBXT 1.1 ? You can just remove the #if, but keep the comments. I will do this for 1.0 as well. > >> This would make it possible to prevent startup of the server if >> recovery fails. But I am not so sure this is an advantage. > > It's not all black and white, all synchronous or all asynchronous. > Usually a user would like to see the database to be accessible as soon > as possible, that is as much of the recovery should be asynchronous as > possible, but without compromising data integrity. > > In ARIES, for example, Undo phase can be done asynchronously, allowing > users to connect and see the data even when recovery is still in > progress. > > Regards, > Sergei -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com

1 0

[Maria-developers] Rev 13: Merge. 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: 13 [merge] revision-id: hakan(a)askmonty.org-20100303132558-2v3ni7cq5z3me245 parent: hakan(a)askmonty.org-20100303132525-euyw1c8whdv8fkjl parent: sergii(a)pisem.net-20100303125349-tpo8hreru2kugu3k committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> branch nick: mariadb-tools timestamp: Wed 2010-03-03 14:25:58 +0100 message: Merge. added: dgcov.pl dgcov.pl-20100303111408-fyazt0jc6o2obwsh-1 === added file 'dgcov.pl' --- a/dgcov.pl 1970-01-01 00:00:00 +0000 +++ b/dgcov.pl 2010-03-03 12:53:49 +0000 @@ -0,0 +1,648 @@ +#! /usr/bin/perl + +# Copyright (C) 2003,2008 MySQL AB +# Copyright (C) 2010 Sergei Golubchik and Monty Program Ab +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# Run gcov and report test coverage on only those code lines touched by +# a given list of revisions. + +use strict; +use warnings; + +use Getopt::Long; +use File::Find; +use Cwd qw/realpath/; +use File::Basename; + +my $verbose; +my $all_opt; +my $context= 3; +my $help; +my $purge_opt; +my $only_gcov_opt; +my $skip_gcov_opt; +my $local_opt; +my $uncommitted_opt; + +my $result= GetOptions + ("context=i" => \$context, + "all" => \$all_opt, + "verbose" => \$verbose, + "help" => \$help, + "purge" => \$purge_opt, + "only-gcov" => \$only_gcov_opt, + "skip-gcov" => \$skip_gcov_opt, + "local" => \$local_opt, + "uncommitted" => \$uncommitted_opt, + ); + +usage() if $help; + +# +# In verbose mode we output to STDERR as well as to STDOUT. +# Avoid misplaced output due to buffering. +# +if ($verbose) { + select STDERR; $| = 1; # make unbuffered + select STDOUT; $| = 1; # make unbuffered +} + +my $troot= `bzr root`; +chomp $troot; +if (!$troot || !chdir $troot) { + die "Failed to find tree root (this tool must be run within\n" . + "a bzr work tree).\n"; +} + +my $res; +my $cmd; +if ($purge_opt or not $skip_gcov_opt) +{ + # One cannot create a file with empty name. But empty argument with -f + # makes 'rm' silent when there is no file to remove. + $cmd= "find . " .($purge_opt ? "-name '*.da' -o -name '*.gcda' -o " : ""). + "-name '*.gcov' -o -name '*.dgcov' | grep -v 'README\.gcov' | ". + "xargs rm -f ''"; + print STDERR "Running: $cmd\n" if $verbose; + $res= system($cmd); + exit ($res ? ($? >> 8) : 0) if $res or $purge_opt; + + # gcov is difficult. source files might be in different places: + # 1. in the same directory where the .o file is + # 2. in include/ for headers + # 3. elsewhere, symlinked + # 4. elsewhere, if Makefile specifies a file from a different directory + # + # because of 2/3/4 one source file may have more than one .gcov file, + # and even more than one .gcov file with the same name (like, include/my_sys.h + # will have as many files with the name "sql/my_sys.h.gcov" as there are + # .o files in the sql directory. these "sql/my_sys.h.gcov" files are + # _different_ files with the same name, and different content) + # + # that's what we'll do here: delete all .gcov and .dgcov files (already done, + # see above). run gcov once per every .gcda file, grab all generated .gcov + # files and aggregate them with the already existing .dgcov files for the + # corresponding source files. + # + find(\&gcov_one_file, "."); + + exit 0 if $only_gcov_opt; +} + +my @revisions = @ARGV; +if(@revisions == 0 && !$uncommitted_opt) { + $local_opt= 1; +} + +if($local_opt) { + # Add revisions present in this tree only. + my $cmd= "bzr missing --this"; + print STDERR "Running: $cmd\n" + if $verbose; + for $_ (`$cmd`) + { + next + unless /^revno: (.*)/; + push @revisions, $1; + print STDERR "Added revision $1\n" + if $verbose; + } +} +die "No revision differences to check.\n" + if (@revisions == 0 && !$uncommitted_opt); + +my $filemap= {}; +# First find all files and their revisions included in the list of revisions. +for my $cs (@revisions) { + # getting the list of revisions + my $cmd="bzr log --line -r '$cs'"; + $cs="$cs..$cs" unless $cs =~ /\.\./; + my @revs=(); + print STDERR "Running: $cmd\n" if $verbose; + print STDERR "." if !$verbose and -t STDERR; + + open PIPE, '-|', $cmd + or die "Failed to spawn '$cmd': $!: $?\n"; + while(<PIPE>) { + die "unexpected output from '$cmd': $_\n" unless /^(\d+):/; + push @revs, $1; + } + close PIPE or die "subcommand '$cmd' failed: $!: $?\n"; + + $cmd= "bzr status --short -r before:'$cs'"; + print STDERR "Running: $cmd\n" if $verbose; + print STDERR "." if !$verbose and -t STDERR; + + open PIPE, '-|', $cmd + or die "Failed to spawn '$cmd': $!: $?\n"; + while(<PIPE>) { + die "unexpected output from '$cmd': $_\n" unless /^[- +RX?CP][ NDKM][ *] /; + next unless /^( M|\+N). (.*)$/; + my $file = $2; + next unless -r "$file.dgcov"; + $filemap->{$file}{$_} = 1 for (@revs); + printf STDERR "Added file $file for @revs\n" if $verbose; + } + close PIPE or die "subcommand '$cmd' failed: $!: $?\n"; +} +print STDERR "\n" unless $verbose; + +my $uncommitted_changes= { }; +if($uncommitted_opt) { + $uncommitted_changes= get_uncommitted_changes_unified(); +} + +# Next, run 'bzr annotate' and 'gcov' on the source files. +my $missing_files= 0; +my $total_lines= 0; +my $numfiles= 0; +my $uncovered= 0; +my $bad_anno_lines= 0; + +for my $file (sort keys %$filemap) { + my $cmd; + my $lines = [ ]; + + if (@revisions != 0) { + $cmd= "bzr annotate --all '$file'"; + print STDERR "Running: $cmd\n" if $verbose; + open PIPE, '-|', $cmd or die "Failed to spawn '$cmd': $!"; + my $linenum= 1; + while(<PIPE>) { + die "Unexpected source line '$_'\n" + unless /^([.0-9]+)\??\s+[^|]+ \| (.*)$/; + my ($rev, $text)= ($1, $2); + # Push line number on list of touched lines if revision matches. + if($filemap->{$file}{$rev}) { + push @$lines, $linenum; + ++$total_lines; + } + ++$linenum; + } + close PIPE + or die "command '$cmd' failed: $!: $?\n"; + } + $numfiles++; + + my $dgcov_file= "$file.dgcov"; + + $lines= apply_diff_to_file($uncommitted_changes->{$file}, $lines) + if -r $dgcov_file and $uncommitted_changes->{$file}; + + # Skip if no lines actually touched in the file. + next unless @$lines; + + # Remember previous N lines to be able to print context. + my @prev= ( ); + # Print N more lines of context. + my $pending= 0; + + $res= open FH, '<', $dgcov_file; + if(!$res) { + warn "Failed to open gcov output file '$dgcov_file'\n". + "The file was never run yet ?\n"; + $missing_files++; + die; # can that happen now ? + next; + } + + my ($mark, $lineno, $src, $full); + my $did_header= undef; + my $last_lineno= undef; + + my $printer= sub { + unless($did_header) { + print "\nFile: $file\n", '-' x 79, "\n"; + $did_header= 1; + } + print $_[0]; + $last_lineno= $lineno; + }; + + my $annotation= undef; + + while(<FH>) { + next if /^function /; # Skip function summaries. + die "Unexpected line '$_'\n" + unless /^([^:]+):[ \t]*([0-9]+):(.*)$/; + ($mark, $lineno, $src, $full)= ($1, $2, $3, $_); + + # Check for source annotation for inspected/dead/tested code. + if($src =~ m!/\*[ \t]+purecov[ \t]*:[ \t]*(inspected|tested|deadcode)[ \t]+\*/!) { + $annotation= 'SINGLE'; + } elsif($src =~ m!/\*[ \t]+purecov[ \t]*:[ \t]*begin[ \t]+(inspected|tested|deadcode)[ \t]+\*/!) { + $annotation= 'RUNNING'; + } elsif($src =~ m!/\*[ \t]+purecov[ \t]*:[ \t]*end[ \t]+\*/!) { + warn "Warning: Found /* purecov: end */ annotation " . + "not matched by begin.\n" . + " At line $lineno in '$file'.\n" + unless defined($annotation) && $annotation eq 'RUNNING'; + $annotation= undef; + } else { + $annotation= undef if defined($annotation) && $annotation eq 'SINGLE'; + } + + shift @prev if @prev > $context; + + if(@$lines == 0 || $lineno < $lines->[0]) { + # This line was not touched by any revision. But we might need + # to print it as context. + + # For lines printed as context (not touched by any revision) + # that are not covered, we make the ##### marker a little less + # prominent. + $full=~ s/^([ \t]*)\#\#\#\#\#:/"$1+++++:"/e; + + if ($pending > 0) { + # Print as context for a previous line included in our revisions. + die "Internal error: pending context to print, but \@prev non-empty" + if @prev; + $pending--; + $printer->(".$full"); + } else { + # Not printed now, so save it as context which may be needed later. + push @prev, ".$full"; + } + } else { + # The line is included in our revision list. + shift @$lines; + + # We need to print the line (and any previous context lines) + # either if this line is not covered, or if it should be shown as + # context for a previous printed line. + # However, a purecov: annotation reverses this logic, so we will warn + # about an annotated line that is actually covered by the test. + if($mark =~ /\#\#\#\#\#/ && !defined($annotation)) { + $uncovered++; + # Make sure we print this line and following context lines. + $pending= $context + 1; + } + if($mark =~ /^[ \t]*[0-9]+$/ && defined($annotation)) { + $bad_anno_lines++; + # Make sure we print this line and following context lines. + $pending= $context + 1; + } + if($all_opt) { + # In all_opt mode, all lines modified in revisions are printed. + $pending= $context + 1; + } + if($pending > 0) { + if(defined($last_lineno) && $last_lineno < $lineno - 1) { + # Mark a gap in the printed file with an empty line. + print "\n"; + } + $printer->($_) for @prev; + @prev = ( ); + $pending--; + $printer->("|$full"); + } else { + # Not printed now, so save it as context which may be needed later. + push @prev, "|$full"; + } + } + } + close FH; + print "\n" + if ($did_header); +} + +print '-' x 79, "\n\n"; +print "$total_lines line(s) in $numfiles source file(s) modified in revision(s).\n"; +print "$uncovered line(s) not covered by tests.\n"; +print "$bad_anno_lines line(s) with redundant purecov: annotations.\n" + if $bad_anno_lines > 0; +print "$missing_files file(s) not processed with gcov.\n" + if $missing_files; +print "For documentation, see http://forge.mysql.com/wiki/DGCov_doc\n"; + +exit 0; + +############################################################################### + +sub get_uncommitted_changes_simple { + my $cmd= "bzr diff"; + print STDERR "Running: $cmd\n" + if $verbose; + open PIPE, '-|', $cmd + or die "Failed to spawn '$cmd': $!"; + + my $x= { }; + my $c= undef; + + while(<PIPE>) { + if(/^===== (.*) [0-9]+\.[0-9]+(\.[0-9]+\.[0-9]+)? vs edited =====$/) { + $c= [ ]; + $x->{$1}= $c; + $filemap->{$1}{UNCOMMITTED}= 1 + unless exists($filemap->{$1}); + printf STDERR "Added file %-14s %s\n", "UNCOMMITTED", $1 + if $verbose; + } elsif(/^([0-9]+)a([0-9]+),([0-9]+)$/) { + # Append new lines $2-$3 after old line $1. + push @$c, [a => $1, $2, $3]; + } elsif(/^([0-9]+)a([0-9]+)$/) { + push @$c, [a => $1, $2, $2]; + } elsif(/^([0-9]+),([0-9]+)d([0-9]+)$/) { + # Delete old lines $1-$2 after new line $3. + push @$c, [d => $1, $2, $3]; + } elsif(/^([0-9]+)d([0-9]+)$/) { + push @$c, [d => $1, $1, $2]; + } elsif(/^([0-9]+),([0-9]+)c([0-9]+),([0-9]+)$/) { + # Change old lines $1-$2 to new lines $3-$4 + push @$c, [c => $1, $2, $3, $4]; + } elsif(/^([0-9]+)c([0-9]+),([0-9]+)$/) { + push @$c, [c => $1, $1, $2, $3]; + } elsif(/^([0-9]+),([0-9]+)c([0-9]+)$/) { + push @$c, [c => $1, $2, $3, $3]; + } elsif(/^([0-9]+)c([0-9]+)$/) { + push @$c, [c => $1, $1, $2, $2]; + } elsif(/^([<>]|---)/) { + # We are not interested in the actual diff content, just the + # line numbers that were changed. + } else { + die "Unexpected output from '$cmd':\n$_"; + } + } + + return $x; +} + +sub get_uncommitted_changes_unified { + my $cmd= "bzr diff --diff-options=-U0"; + print STDERR "Running: $cmd\n" + if $verbose; + open PIPE, '-|', $cmd + or die "Failed to spawn '$cmd': $!"; + + my $x= { }; + my $c= undef; + + while(<PIPE>) { + # Ignore directories. + if(/^=== added directory '(.*)'$/) { + + # Collect files. + } elsif(/^=== (modified file|added file) '(.*)'$/) { + $c= [ ]; + $x->{$2}= $c; + $filemap->{$2}{UNCOMMITTED}= 1 + unless exists($filemap->{$2}); + printf STDERR "Added file %-14s %s\n", "UNCOMMITTED", $2 + if $verbose; + + # Ignore removed files. + } elsif(/^=== (removed file) '(.*)'$/) { + + # Ignore file names. + } elsif(/^(---|\+\+\+) ./) { + + # Collect changed lines. Ignore those with 0 lines changed. + # Change old lines $1-$2 to new lines $3-$4 + } elsif(/^@@ [+-](\d+),(\d+) [+-](\d+),([1-9]\d*) @@/) { + push @$c, [c => $1, $1+$2, $3, $3+$4]; + } elsif(/^@@ [+-](\d+) [+-](\d+),([1-9]\d*) @@/) { + push @$c, [c => $1, $1, $2, $2+$3]; + } elsif(/^@@ [+-](\d+),(\d+) [+-](\d+) @@/) { + push @$c, [c => $1, $1+$2, $3, $3]; + } elsif(/^@@ [+-](\d+) [+-](\d+) @@/) { + push @$c, [c => $1, $1, $2, $2]; + + } elsif(/^@@ /) { + # Ignore diffs with 0 lines changed. + + } elsif(/^[ +-]|^$/) { + # We are not interested in the actual diff content, just the + # line numbers that were changed. + } else { + die "Unexpected output from '$cmd':\n$_"; + } + } + + return $x; +} + +sub apply_diff_to_file { + my ($c, $l)= @_; + my $i= 0; + my $shift= 0; + my $l_new= [ ]; + + # Copy over line numbers, applying the diffs on the way. + for my $d (@$c) { + my $t= shift @$d; + if($t eq 'a') { + my ($old, $from, $to)= @$d; + # Find the place to insert the lines. + push @$l_new, $l->[$i++] + $shift && ++$total_lines + while $i< @$l && $l->[$i] <= $old; + push @$l_new, ($from .. $to); + ++$total_lines; + $shift+= ($to - $from + 1); + } elsif($t eq 'd') { + my ($from, $to, $new)= @$d; + push @$l_new, $l->[$i++] + $shift && ++$total_lines + while $i< @$l && $l->[$i] + $shift <= $new; + # Skip any deleted lines. + $i++ + while $i< @$l && $l->[$i] <= $to; + $shift-= ($to - $from + 1); + } elsif($t eq 'c') { + my ($ofrom, $oto, $nfrom, $nto)= @$d; + push @$l_new, $l->[$i++] + $shift && ++$total_lines + while $i< @$l && $l->[$i] < $ofrom; + $i++ + while $i< @$l && $l->[$i] <= $oto; + push @$l_new, ($nfrom .. $nto); + ++$total_lines; + $shift= $shift - ($oto-$ofrom) + ($nto-$nfrom); + } else { + die "Internal?!?"; + } + } + push @$l_new, $l->[$i] + $shift + while $i< @$l; + return $l_new; +} + +sub usage { + print <<END; +Usage: $0 --help + $0 [options] [revisionspec [revisionspec ...]] + +The dgcov program runs gcov for code coverage analysis, and reports missing +coverage only for those lines that are changed by the specified revision(s). +Revisions are specified in any bzr supported format, as invidual revisions or +ranges. +If no revisions are specified, the default is to work on all unpushed +revisions (bzr missing --this). + +Options: + + -h --help This help. + -v --verbose Show commands run. + -a --all All lines modified in revisions are printed. + -c N --context=N Show N (default 3) lines of context around reported lines. + -p --purge Delete all test coverage information, to prepare for a + new coverage test. + -g --only-gcov Stop after running gcov, don't run bzr + -s --skip-gcov Do not run gcov, assume .dgcov files are already in place + -l --local Add revisions from 'bzr missing --this' (default if no + revisions given and not using -u). + -u --uncommitted Also consider changes not committed (slow). + +Prior to running this tool, the analyzed program should be compiled with +-fprofile-arcs -ftest-coverage (for MySQL, BUILD/compile-pentium-gcov script +does just that), and the testsuite should be run. dgcov will report +all lines that are modified in the specified revisions and that are reported +as not covered by gcov. + +Lines not covered are marked by '#####', lines without generated code are +marked with '-', and other lines are marked with the number of times they +were executed. See 'info gcov' for more information. + +Lines modified by revisions are pre-fixed by '|', context lines not included +in the specified revisions are prefixed by '.'. Non-modified context lines +that are not covered by tests are marked with '+++++' instead of '#####'. + +Reports of non-covered lines may be suppressed by 'purecov' annotations: + + inspected For code that cannot be covered (like out of memory conditions), + but which has been reviewed and is considered correct. + deadcode Unreachable code. + tested Code that is not covered by automatic tests, but which has been + manually tested. + +Annotations may be for a single line: + + if((p= malloc(10)) == NULL) return 0; /* purecov: inspected */ + +or for a span of lines: + + /* purecov: begin deadcode */ + tmp= x; + x= y; + y= tmp; + /* purecov: end */ + +Note that if annotated lines are actually covered, they will be reported as +errors as well (since the annotations are then clearly wrong). +END + + exit 1; +} + +sub suck_in { + no warnings 'numeric'; + my ($acc, $fh) = @_; + while (<$fh>) + { + die "not a gcov file?" unless /^\s*(-|#+|\d+):\s*(\d+):/; + my ($cnt, $line) = ($1, $2); + next if $cnt eq '-'; + $acc->[$line]+=$cnt; + } +} + +my $file_no=0; +sub gcov_one_file { + return unless /\.gcda$/; + my $ofile="$`.o"; + my $sourcepath; + my $lastfile; + + $cmd= "gcov '$_'"; + print STDERR ++$file_no, "\r" if !$verbose and -t STDERR; + print STDERR "Running: $cmd\n" if $verbose; + my $res= system "$cmd 2>/dev/null >/dev/null"; + if($res) { + warn "Failed to spawn '$_': $res: $!: $?\n". + "The gcov report may be incomplete.\n"; + $missing_files++; + die; # can that happen now ? + return; + } + # now, read all generated files + for my $file (<*.gcov>) { + open FH, '<', $file; + $_=<FH>; + chomp; + # first, we read the name or the source file from the .gcov file + # that works pretty well for included headers + warn "$File::Find::dir/$file does not start from a Source line ? Weird " + unless /^\s+-:\s+0:Source:/; + my $sourcefile=$'; + # remove .libs from the end of the path + # for building dynamic libraries libtool puts .o files in the .libs/ + my $up=($File::Find::dir =~ /\/\.libs$/ ? "../" : ""); + # and resolve symlinks, we love symlinking sources so much! + my $source=realpath($up.$sourcefile); + unless ($source and -r $source) { + # Hm, let's try to find the file in the same directory where the last + # file was. + # the only file that needs it is libmysqld/sql_yacc.yy.gcov + $source=dirname($lastfile)."/".$sourcefile if $lastfile; + } + unless ($source and -r $source) { + # still no cookie, time to try something new. + # sometimes files are not symlinked, but specified in the Makefile with + # a path, like file.o: ../foobar/file.c + # in that case the ../foobar part is recorded in the .o file + unless (defined $sourcepath) { + $_=`readelf -wi $ofile|grep -m1 'DW_AT_name.*/' 2>/dev/null`; + m!DW_AT_name\s*:\s*(?:$.*$: )?(\S.*)/[^/]+\n! or + die "error running 'readelf -wi $File::Find::dir/$ofile', no 'DW_AT_name.*/' found"; + print STDERR "Got the path '$1' with 'readelf -wi $File::Find::dir/$ofile'\n" if $verbose; + $sourcepath=$1; + } + $source=realpath("$up$sourcepath/$sourcefile"); + } + die "A source file $source for $File::Find::dir/$up$file does not exists" + unless -r $source; + + unless ($source =~ /^$troot/o) { + warn "Skipping $source\n"; + unlink $file; + next; + } + $lastfile=$source; + + my @acc=(); + print STDERR "Reading: $File::Find::dir/$file\n" if $verbose; + suck_in(\@acc, *FH); + close FH; + + my $dgcov_file="$source.dgcov"; + if (-r $dgcov_file) { + open (FH, '<', $dgcov_file); + print STDERR "Adding: $dgcov_file\n" if $verbose; + suck_in(\@acc, *FH); + close FH; + } + + open (F, '<', $source) or die "cannot read $source"; + open (FH, '>', $dgcov_file) or die "cannot write to $dgcov_file"; + print STDERR "Writing: $dgcov_file\n" if $verbose; + while (<F>) { + printf FH '%9s:%5s:%s', + defined ($acc[$.]) ? $acc[$.] || '#####' : '-', + $., $_; + } + close FH; + close F; + unlink $file; + } +} +

1 0

[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 01 Mar '10

01 Mar '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