- developers - lists.mariadb.org

[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

[Maria-developers] MariaDB limits
by Marian Marinov 21 Jun '11

21 Jun '11

Hello guys, I have a lot of clients requesting some kind of per user limiting based on the information from the STATISTICS tables. I'm thinking of creating a patch that will add this functionality by creating two more tables in the INFORMATION_SCHEMA: USER_LIMITS CLIENT_LIMITS They will have some parts of the structure of USER_STATISTICS and CLIENT_STATISTICS and will keep only values for limits. By default a 0 would mean no limit. Do you have any comments about my aproach? Would you be willing to accept a patch that adds such functionality? I want to know before I start work on this project if the community is interested in such enhancement of MariaDB. Regards, Marian

3 4

Re: [Maria-developers] Notes from the two IT Day Sessions
by Michael Widenius 20 Jun '11

20 Jun '11

Hi! Follow up to last email. I will hope to have the worklog items for all the wishes done by Thursday. Regards, Monty

1 0

[Maria-developers] MariaDB in EPEL
by Marian Marinov 20 Jun '11

20 Jun '11

Hello, I wish to package MariaDB for the EPEL repository. I have searched a bit to see if there were any efforts in that direction but I saw none. In 2009 there was some talk about MariaDB and Fedora, but that was it, only some talk. I have the time to prepare and test the MariaDB RPMs and to continue support them later. My idea is to have MariaDB RPMs as replacements of the default mysql installation. In order to do so, I first want the approval of the MariaDB developers. Best regards, Marian Marinov

2 3