June 2011 - developers - lists.mariadb.org

[Maria-developers] Philip please test: Duplicate Elimination for outer joins + semi joins
by Sergey Petrunya 30 Jun '11

30 Jun '11

Hello Philip, I'd like to get the tree at lp:~maria-captains/maria/5.3-subqueries-mwl90 tested for Duplicate Elimination with outer+semi-join processing. I'm interested in crashes, as well as wrong query result bugs. The code path that needs to be tested is selected with this @@optimizer_switch setting: semijoin=on,materialization=off,firstmatch=off,loosescan=off this set of switches disables all semi-join subquery strategies except DuplicateElimination, so you should have no problem with hitting the If you also set "semijoin=off" then you get the execution path that we assume will always produce correct results. Query pattern SELECT .... FROM complex_join WHERE top_where AND (SELECT ... FROM complex_join WHERE ...) where - subqueries must be "mergeable semi-joins": = no aggregates, unions, or ORDER BYs - both correlated and ucorrelated subqueries are OK. - complex_join is arbitrary join structure (comma join, "t1 JOIN t2", various kinds of outer joins - everything should work) - multiple sibling subqueries, as well as nested subqueries are covered by what you're testing. EXPLAIN - Should have id=='1' && select_type='PRIMARY' on all lines. - Should use Start Temporary/End temporary. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 2

Re: [Maria-developers] [Commits] Rev 2983: WL#77 - INSTALL PLUGIN * in http://bazaar.launchpad.net/~maria-captains/maria/5.2/
by Alexey Botchkov 29 Jun '11

29 Jun '11

I'm fine with this last patch. One thing i can think of is if we should add a new error instead of adding static english texts to the ER_CANT_OPEN_LIBRARY like 'API version for ... plugin ... not supported'. Second question if we should have INSTALL PLUGIN ... IF NOT INSTALLED; to be even more user-friendly. Regards HF 17.06.2011 21:49, serg(a)askmonty.org wrote: > At http://bazaar.launchpad.net/~maria-captains/maria/5.2/ >

1 0

[Maria-developers] MariaDB 5.3 docs
by Daniel Bartholomew 29 Jun '11

29 Jun '11

All, We're getting closer to the first beta release of MariaDB 5.3 and several of the new features have been documented in the Knowledgebase and linked to from this page: http://kb.askmonty.org/en/what-is-mariadb-53 What I don't know is if there are any features which have either: 1. not been documented in the Knowledgebase at all, -or- 2. been incompletely documented in the Knowledgebase but not added to the above page, -or- 3. been added to the page but incompletely documented. If you have worked on 5.3, please review the above page to make sure the items you worked on are present and fully documented. If they aren't please document them in the Knowledgebase and add them to the above page (or send me enough information about them so that I can document them). If they are present, but inadequately documented, please add what is missing or let me know what is missing. Don't worry about your documentation being perfect, I will clean it up. If you need any help, please contact me via email or on IRC. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 2

[Maria-developers] Proposal to split @@optimizer_switch
by Sergey Petrunya 28 Jun '11

28 Jun '11

Hello, Please find below descriptions of problems with current @@optimizer_switch variable, as well as a proposal on how to fix it. <contents> Problems with @@optimizer_switch Proposed solution Details about hooking this into parser Alternate approach1: grouping </contents> Problems with @@optimizer_switch -------------------------------- @@optimizer_switch variable use has outgrown its syntax. There are too many settings to fit on the single line: MariaDB [(none)]> select @@optimizer_switch; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@optimizer_switch | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) The line is now 510 characters long. It's hard to find the flag you're looking for, and one needs to write regular expressions if they want to check the value of a particular flag in some program. There is no easy way to get settings for a subset of flags (which means we've banned "select @@optimizer_switch" from almost all .test files, because one had to change .result file all the time) The extra-long line also gets into "SHOW VARIABLES" output, and makes it hard-to-read, too. The situation is going to get worse, as we've already committed to adding even to add even more switches (Igor's patch adds some, then we've decided that two semi-join optimizations that do not have their switches, should have them) The benefits of having all settings grouped together are: - It was easy to implement - One can easily see all optimizer switches without having to fish them out of the list of all server variables. - One can reset all optimizer settings with a single statement: SET @@optimizer_switch=default Proposed solution ----------------- Break optimizer_switch into multiple variables, with names having the dots in them. That is, current @@optimizer_switch flags will change into this set of variables: optimizer.index_merge=on optimizer.index_merge.union=on optimizer.index_merge.sort_union=on optimizer.index_merge.intersection=on optimizer.index_merge.sort_intersection=off optimizer.index_condition_pushdown=on optimizer.join_cache.bka=on optimizer.join_cache.hashed=on optimizer.join_cache.incremental=on optimizer.join_cache.optimize_buffer_size=on optimizer.join_cache.outer_join=off optimizer.join_cache.semijoin=off optimizer.mrr=on optimizer.mrr.cost_based=off optimizer.mrr.sort_keys=on optimizer.semijoin=on optimizer.semijoin.firstmatch=on optimizer.semijoin.loosescan=on optimizer.subquery.cache=on optimizer.subquery.in_to_exists=on optimizer.subquery.materialization=off optimizer.subquery.partial_match_rowid_merge=on optimizer.subquery.partial_match_table_scan=on optimizer.table_elimination=on The dots are there so that - the structuring is more apparent - it is possible to reset groups of variables to their defaults. Current idea is to use syntax like this: SET optimizer.semijoin.*= default; - in the future we may also allow to select and set groups of settings. Details about hooking this into parser ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Currently, dotted syntax is allowed to some extent: we allow - GLOBAL/SESSION/DEFAULT prefix for any variable - key_cache_name prefix for multiple named key caches. There is one key cache named default, it is not possible to make a key cache named "session". To make variable lookup rules easy, we will diallow creation/use of key caches named 'optimizer'. Overall, system variables can be encountered - as part of @@variable syntax when their value is selected - as part of SET statement. in both cases, bison can easily see it's parsing a system variable name, so I was able to add support for dotted variable syntax into the parser without introducing any extra conflicts. Alternate approach1: grouping ----------------------------- (Suggested by Igor (or was it Timour?)) Replace one @@optimizer_switch variable with multiple: @@optimizer_switch_semijoin @@optimizer_switch_mrr .. and so forth. The advantage of this approach is that it's easy and we don't need to change the parser. The disadavantage is that it may turn out that groups of flags may be still too big, and so either look ugly or require further splits. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

4 9

Re: [Maria-developers] [Commits] Rev 3062: Fixed bug lp:800679 in file:///home/tsk/mprog/src/5.3-mwl89/
by Sergey Petrunya 28 Jun '11

28 Jun '11

Hi Timour, Ok to push. On Mon, Jun 27, 2011 at 05:40:25PM +0300, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3-mwl89/ > > ------------------------------------------------------------ > revno: 3062 > revision-id: timour(a)askmonty.org-20110627144014-r14ahro6uzf77423 > parent: igor(a)askmonty.org-20110625210227-kdneawayskx5jl4q > fixes bug(s): https://launchpad.net/bugs/800679 > committer: timour(a)askmonty.org > branch nick: 5.3-mwl89 > timestamp: Mon 2011-06-27 17:40:14 +0300 > message: > Fixed bug lp:800679 > > Analysis: > The failed assert ensured that the choice of subquery strategy > is performed only for queries with at least one table. If there > is a LIMIT 0 clause all tables are removed, and the subquery is > neither optimized, nor executed during actual optimization. However, > if the query is EXPLAIN-ed, the EXPLAIN execution path doesn't remove > the query tables if there is a LIMIT 0 clause. As a result, the > subquery optimization code is called, which violates the ASSERT > condition. > > Solution: > Transform the assert into a condition, and if the outer query > has no tables assume that there will be at most one subquery > execution. > > There is potentially a better solution by reengineering the > EXPLAIN/optimize code, so that subquery optimization is not > done if not needed. Such a solution would be a lot bigger and > more complex than a bug fix. > === modified file 'mysql-test/r/subselect_mat_cost_bugs.result' > --- a/mysql-test/r/subselect_mat_cost_bugs.result 2011-06-05 02:56:06 +0000 > +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2011-06-27 14:40:14 +0000 > @@ -295,3 +295,24 @@ id select_type table type possible_keys > select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; > c1 > drop table t1, t2; > +# > +# LP BUG#800679: Assertion `outer_join->table_count > 0' failed in > +# JOIN::choose_subquery_plan() with materialization=on,semijoin=off > +# > +CREATE TABLE t1 ( f1 int); > +insert into t1 values (1),(2); > +CREATE TABLE t2 ( f1 int); > +insert into t2 values (1),(2); > +SET @@optimizer_switch='materialization=on,semijoin=off'; > +EXPLAIN > +SELECT * FROM t1 > +WHERE (f1) IN (SELECT f1 FROM t2) > +LIMIT 0; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where > +SELECT * FROM t1 > +WHERE (f1) IN (SELECT f1 FROM t2) > +LIMIT 0; > +f1 > +drop table t1, t2; > > === modified file 'mysql-test/t/subselect_mat_cost_bugs.test' > --- a/mysql-test/t/subselect_mat_cost_bugs.test 2011-05-23 07:56:05 +0000 > +++ b/mysql-test/t/subselect_mat_cost_bugs.test 2011-06-27 14:40:14 +0000 > @@ -326,3 +326,26 @@ select c1 from t1 where c1 in (select kp > select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; > > drop table t1, t2; > + > +--echo # > +--echo # LP BUG#800679: Assertion `outer_join->table_count > 0' failed in > +--echo # JOIN::choose_subquery_plan() with materialization=on,semijoin=off > +--echo # > + > +CREATE TABLE t1 ( f1 int); > +insert into t1 values (1),(2); > +CREATE TABLE t2 ( f1 int); > +insert into t2 values (1),(2); > + > +SET @@optimizer_switch='materialization=on,semijoin=off'; > + > +EXPLAIN > +SELECT * FROM t1 > +WHERE (f1) IN (SELECT f1 FROM t2) > +LIMIT 0; > + > +SELECT * FROM t1 > +WHERE (f1) IN (SELECT f1 FROM t2) > +LIMIT 0; > + > +drop table t1, t2; > > === modified file 'sql/opt_subselect.cc' > --- a/sql/opt_subselect.cc 2011-06-21 13:00:41 +0000 > +++ b/sql/opt_subselect.cc 2011-06-27 14:40:14 +0000 > @@ -4358,7 +4358,7 @@ bool JOIN::choose_subquery_plan(table_ma > by the IN predicate. > */ > outer_join= unit->outer_select() ? unit->outer_select()->join : NULL; > - if (outer_join) > + if (outer_join && outer_join->table_count > 0) > { > /* > The index of the last JOIN_TAB in the outer JOIN where in_subs is > @@ -4371,7 +4371,6 @@ bool JOIN::choose_subquery_plan(table_ma > JOIN_TAB, and their join_tab_idx remains MAX_TABLES. Such predicates > are evaluated for each complete row of the outer join. > */ > - DBUG_ASSERT(outer_join->table_count > 0); > max_outer_join_tab_idx= (in_subs->get_join_tab_idx() == MAX_TABLES) ? > outer_join->table_count - 1: > in_subs->get_join_tab_idx(); > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] can "set read_only" be non-blocking?
by MARK CALLAGHAN 27 Jun '11

27 Jun '11

It can be dangerous for us to run "set read_only" on a production server because it can block in close_cached_tables. More details about the pain this caused at a previous job are at: http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html Per the code in set_var.cc: /* Perform a 'FLUSH TABLES WITH READ LOCK'. This is a 3 step process: - [1] lock_global_read_lock() - [2] close_cached_tables() - [3] make_global_read_lock_block_commit() [1] prevents new connections from obtaining tables locked for write. [2] waits until all existing connections close their tables. [3] prevents transactions from being committed. */ Can there be a variant that doesn't do #2? My workload doesn't use MyISAM and I don't know if #2 is done because of MyISAM. Calling close_cached_tables seems like a heavy way to force LOCK TABLEs to be unlocked. Any long running queries will cause #2 to block. -- Mark Callaghan mdcallag(a)gmail.com

2 4

Re: [Maria-developers] [Commits] Rev 3062: Fixed LP bug #801536. in file:///home/igor/maria/maria-5.3-merge-tmp/
by Timour Katchaounov 27 Jun '11

27 Jun '11

Igor, Ok to push in the sense that the new patch cannot any more result in overflow. Both the old and new code are not equivalent. Given there is no comment, I cannot fully understand whether the change produces at least as good estimate of the record length as the old patch. Could you please add some general comment to the whole function how do we compute the field length. Timour On 27.06.2011 07:55, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-merge-tmp/ > > ------------------------------------------------------------ > revno: 3062 > revision-id: igor(a)askmonty.org-20110627045532-663o9eih0flql0n6 > parent: igor(a)askmonty.org-20110625210227-kdneawayskx5jl4q > committer: Igor Babaev<igor(a)askmonty.org> > branch nick: maria-5.3-merge-tmp > timestamp: Sun 2011-06-26 21:55:32 -0700 > message: > Fixed LP bug #801536. > Ensured valid calculations of the estimates stored in JOIN_TAB::used_fieldlength. >

1 0

[Maria-developers] WL#201 New (by Knielsen): Multi-source replication
by worklog-noreply＠askmonty.org 23 Jun '11

23 Jun '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Multi-source replication CREATION DATE..: Thu, 23 Jun 2011, 12:50 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 201 (http://askmonty.org/worklog/?tid=201) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: This worklog is about implementing a way for a MariaDB replication slave to replicate from more than one master simultaneously. Each master is handled by a specific replicator instance in the slave server. Each replicator instance consists of separate I/O thread, SQL thread, and associated state and configuration. In efffect, several replication slaves are running at the same time, each replicating from a separate master, but all replicating into a common data store (typically, but not necessarily to separate databases/tables). A replicator instance is identified with a user-chosen name used in replication SQL statements such as CHANGE MASTER TO ... This name is also included in file names to distinguish the files that keep the replication state (relay logs, master.info, relay-log.info). This way, each separate instance can be configured separately, but otherwise the same way as existing single-source replication. In order to remain backwards-compatible with existing third-party scripts etc., the replicator instance name is made optional in all existing replication statements. If it is omitted, the name "default" is used, and for this particular name, master.info and the other files retain their old names to allow seamless upgrades of slaves. In this worklog, there is no extra conflict resolution proposed. The effect of updates from one master on replication from another master will be the same as the effect of direct user SQL queries on the slave server, ie. it is the responsibility of the user/DBA to ensure no conflicts occur. If a conflict causes replication of an event to fail (with duplicate key violation eg.), the corresponding slave SQL thread will stop, requiring manual intervention to fix and start. An easy and typical way to avoid conflicts would be to eg. to use separate databases for each master->slave replication channel. RBR idempotent slave application can also be used to help resolve conflicts, for example. See also MySQL WL#1697: http://forge.mysql.com/worklog/task.php?id=1697 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] 5.5 merge: sql_select.cc
by Sergei Golubchik 23 Jun '11

23 Jun '11

Hi. Here I'll list changesets that cause conflicts and ask what to do. 1. http://tinyurl.com/63tey39 vs. http://tinyurl.com/5v2pluf it's a fix for bug#49322. both changes are similar, return is moved down. But Igor put it after the while() loop, while Ole John - in the loop, as soon a non-match is found. What difference does it make, I mean the earlier return, what consequences? 2. http://tinyurl.com/69lwrp9 - a big chunk is moved around in test_if_skip_sort_order() that's all, the rest of the file I've merged already. Regards, Sergei

3 2

[Maria-developers] forwarded message from Kristian Köhntopp
by Michael Widenius 22 Jun '11

22 Jun '11

Hi! Here is booking.com's wish list! Regards, Monty

3 2