- developers - lists.mariadb.org

[Maria-developers] WL#253 New (by Igor): Take into account the selectivity of single-table range predicates on non-indexed columns
by worklog-noreply＠askmonty.org 06 Mar '13

06 Mar '13

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Take into account the selectivity of single-table range predicates on non-indexed columns CREATION DATE..: Wed, 06 Mar 2013, 17:25 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 253 (http://askmonty.org/worklog/?tid=253) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Description of the problem ========================== Currently the optimizer completely ignores any conditions on non-indexed columns when searching for the best execution plan. As a result in many cases it chooses a suboptimal plan. Let's consider the query: SELECT * FROM t1,t2 WHERE t1.a=t2.a and t2.b BETWEEN (1,3) assuming that: -table t1 contains 100 records -table t2 contains 1000 records -there is a primary index on t1(a) -there is a secondary index on t2(a) -there is no index defined on column t2.b -the selectivity of the condition t2.b BETWEEN (1,3) is high (~ 1%) Under these conditions currently the optimizer will choose the plan that accesses t1 using a table scan accesses t2 using index t2(a) checks the condition t2.b BETWEEN (1,3) This plan examines all rows of both tables and it performs 100 index look-ups. The alternative plan that: accesses table t2 in a table scan checks the condition t2.b BETWEEN (1,3) accesses t1 using index t1(a) also would examine all rows of t2, but it performs only ~10 look-ups to access ~10 rows of table t1. The second plan is expected to be more efficient and it is so. The plan to resolve the problem =============================== 1. Build single-table range conditions over different columns of all tables of the join query. 2. Calculate the selectivity of each such condition using persistent statistics on table columns. 3. Take this statistics into account when calculated the cardinality of each partial join that are evaluated by the optimizer. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] deadlock, rotate and prepared xids
by MARK CALLAGHAN 05 Mar '13

05 Mar '13

Kristian, Did you do anything special to avoid deadlock with ::new_file_impl and prepared XIDs? My memory is vague but I thought you handled it. I added code to the FB patch group commit implementation to handle it. Both Yoshi and I hit deadlocks with that code in official MySQL 5.6: http://bugs.mysql.com/bug.php?id=68569 http://bugs.mysql.com/bug.php?id=68251 -- Mark Callaghan mdcallag(a)gmail.com

2 3

[Maria-developers] C API for dynamic columns - in which versions?
by Honza Horak 01 Mar '13

01 Mar '13

Hi guys, I failed to find the info in which versions of MariaDB "C API for dynamic columns" is available as described here: https://kb.askmonty.org/en/dynamic-columns-api/ I think the information should be the best included directly on the page itself. Regards, Honza

2 2

[Maria-developers] How to copy IO_CACHE into a string?
by Lixun Peng 28 Feb '13

28 Feb '13

Hi Guys, I want to copy the contents of the IO_CACHE into a string. But I'm not found a function can do it, but I found "my_b_copy_to_file" can do the similar thing, so I implement a function "my_b_copy_to_string" by imitating "my_b_copy_to_file", to do this thing. Could you please review my code? char *my_b_copy_to_string(IO_CACHE *cache, size_t *bytes_in_cache) { char *buff; char *tmp_buff; size_t now_size; size_t inc_size; /* Reinit the cache to read from the beginning of the cache */ if (reinit_io_cache(cache, READ_CACHE, 0L, FALSE, FALSE)) return NULL; now_size= my_b_bytes_in_cache(cache); inc_size= 0; buff= (char *) my_malloc(now_size + 1, MYF(0)); tmp_buff= buff; do { now_size+= inc_size; if(inc_size > 0) { buff= (char *) my_realloc(buff, now_size + 1, MYF(0)); tmp_buff= buff + (now_size - inc_size); memcpy(tmp_buff, cache->read_pos, inc_size); } else { memcpy(tmp_buff, cache->read_pos, now_size); } cache->read_pos= cache->read_end; } while ((inc_size= my_b_fill(cache))); buff[now_size]= '\0'; reinit_io_cache(cache, WRITE_CACHE, 0, FALSE, TRUE); *bytes_in_cache= now_size; return buff; } Thanks, Lixun -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com

2 2

Re: [Maria-developers] GTID: clueless user experience, day 1
by Kristian Nielsen 27 Feb '13

27 Feb '13

[Cc: maria-developers@, in case others are interested or have comments] Elena Stepanova <elenst(a)montyprogram.com> writes: >> So if you agree, I will put on my ToDo to remove that omitted domain_id >> defaults to 0. > > It's fine with me. Pushed to lp:~maria-captains/maria/10.0-mdev26/ >> Clearly I need to give a better error message, this is highly >> confusing. Thanks for bringing this to attention! What do you think of: >> >> ERROR XXXX: Duplicate domain_id 0 for GTID 0-2-1 I made it like this: MariaDB [test]> change master to master_gtid_pos="0-1-2,1-1000-1000,2-3-1,0-2-100,4-4-4"; ERROR 1941 (HY000): GTID 0-2-100 and 0-1-2 conflict (duplicate domain id 0) > Sounds good. Only, if I understand correctly, it's nowadays not > domain_id, but gtid_domain_id ? Then it's better be named as such in > the error message (/me tried in vain to set domain_id, first on the > server command line, and then in a session). Well, these are different but related concepts. @@gtid_domain_id is a system variable. A "domain id" is a conceptual thing when taking about global transaction ID. It is like the InnoDB buffer pool. The system variable is "innodb_buffer_pool_size". The concept is "buffer pool size". But I agree the concept should be "domain ID", not domain_id (with an underscore). The reason I spell this out is not to nitpick, but rather that I would welcome suggestions for naming, I am terrible with naming. Do you think I should rename @@gtid_domain_id and @@gtid_seq_no to just @@domain_id and @@seq_no? I thought that was perhaps a bit too generic names, though I did keep plain @@server_id for backwards compatibility. Another option is @@binlog_domain_id and @@binlog_seq_no, this is perhaps better than the random "gtid" prefix? The variables are for controlling what goes into the binlog, after all. > But on the other hand, why should it be several rows? If > binlog_gtid_pos() returns a *single* comma-separated list of GTIDs for > the given position, as it does now, we can just as well add this list > to SHOW MASTER STATUS as a *column*, no? Sorry, it was me who was confused. I meant adding extra columns, not rows. I am still not sure if it is safe to add extra columns. It would seem useful, unless it breaks too many existing applications. >> Start after. GTID is the last event executed. > Hm... I must admit I don't quite understand the reasoning. I'll ask Let me give an example. Suppose server 1 is master. It binlogs an event with GTID 0-1-10. This event is currently the last in the binlog. Server 2 is a slave of server 1. When it catches up, GTID 0-1-10 is the last event applied on the slave. So now, what should be the replication state of slave server 2? If we want to use "start from" we could try GTID 0-1-11? But then suppose we now switch master to a new server 3. Then the event following 0-1-10 is 0-2-11, not 0-1-11. So if server 2 would try to connect as a slave to server 3 and start from 0-1-11, it will not work, this event does not exist anywhere. That is why it has to be "start after", as when we save the state on the slave, we know only the GTID of the last event applied, not the one to apply next. > There might be more in there for me to look into.. Indeed. This is a critical part of the design, I spent a _lot_ of thought to get this working, and I think I got it right, but I will feel a lot better knowing you spent your best efforts to break it :-). But please wait with this for a later stage, I have some planned development around this that is not ready yet. >> Gtid_Pos_Auto=1 means the new method, whether AUTO or explicit 'xxx' gtid, >> So what should we call it? How about just "Using_Gtid" ? > > Yep, Using_Gtid sounds fine. This is fixed in the tree now. > This brings a more conceptual question for me... Does Using_Gtid > indicate how the slave *replicates*, or just how it determines the > starting position while connecting to master? In other words, is there There is a difference. There are two ways a slave can connect. The old way using binlog file and position. And the new way using GTID. I think we need to have both. The Using_Gtid records which of the two is used. I made it this way so that users do not need to set some specific option in my.cnf, and so that with multi-source, one slave connection can use the old style (eg. master not supporting GTID?), while another can use new style. The main difference is finding the starting position. But this also implies difference for how the slave replicates. Because with GTID there can be *multiple* starting positions (one for each domain id). So after connecting, some events can be skipped during replication if one domain_id is ahead of another in the master binlog. > If not (and I couldn't yet figure the difference, since, as you said, > the slave reads the GTID state anyway when it connects with file > name/offset), then I start having doubts that we need the Using_Gtid > flag at all. I mean, if it only indicates what I provided last time in > CHANGE MASTER, how is this information useful? It is used when the slave connects (which happens at every START SLAVE, and every server restart, including mysqld_safe autostart). If Using_Gtid is false, then slave connects at the last file/offset found in relay-log.info. It also obtains the corresponding GTID position from the master, so that it is ready in case the user wants to switch to CHANGE MASTER TO ... MASTER_GTID_POS=AUTO. If Using_Gtid is true, slave connects at the GTID position read from the mysql.rpl_slave_state table, which is crash safe. And multiple start positions are used if there is more than one domain id in the GTID position. So you see, the information is needed, otherwise the slave and the master would not know how to handle slave connect. >> But frankly, I am not sure if the right place is in SHOW ALL SLAVES STATUS or >> in plain SHOW SLAVE STATUS. Can you help me understand what the difference is >> between putting the column in one or the other of these? > > I guess the only real reason was that Monty didn't want to change the > output format of existing SHOW SLAVE STATUS, not to break anything. Yes, this is an important issue. I will discuss it with Monty when I visit him next week. > Ideally, I'd expect the global values be shown both in SHOW SLAVE > STATUS, e.g. at the end of it, and once for all slaves in SHOW ALL > SLAVES STATUS, again at the end. But I don't know if it's doable, and > if it is, we need to think what it might break. Yes. The code actually seems to have been written for something like SHOW FULL SLAVE STATUS, which would include the extra data. I will need to ask Monty to know more. > Then I assumed you decided not to print current GTIDs in SHOW SLAVE > STATUS on purpose, to avoid the mess like MySQL has -- which was also > surprising since we wouldn't have that insanity anyway. > I didn't ask how I could obtain them on slave, as I figured it was > supposed to be done via mysql.rpl_slave_state. But it didn't occur to > me that it was printed in SHOW ALL SLAVES STATUS... Yes. In any case, the user interface is very much not finalised yet. Sorry for the rough experience, but hopefully we can use your experience to come up with a good interface for the final release. >> Maybe the problem is that if I add a column in SHOW SLAVE STATUS, and MySQL >> adds another column in the same position, then scripts will break because the >> column count for one or the other will change? > > eh.. MySQL's show slave status currently has like +13 columns > comparing to ours, I really don't think that scripts relying on such > level of detail stand any chance to survive. My main worry is not if one has more than the other. The worry is if some column is in position 25 (say) in MySQL but 28 (say) in MariaDB. Is there even any easy way to pick out a column by name, rather than numerical position? > Good thing you mentioned it, I actually totally forgot to tell the > tale of my *very* first experience. Well, I started when I mentioned > Then I followed the instruction again, and... it didn't work. It > didn't crash, replication didn't fail, but when I switched master and > slave, the new slave (old master) just wouldn't replicate the last > line, (6,3). > > I couldn't understand it until I compared the flow and configuration > with the MTR test that you have (it does basically the same, and it > works), and only then I figured that I have to have > log-slave-updates=on. It wasn't clear to me why, I mean, I had the > transaction in quesiton in the binlog, and GTID was there too, > so?.. That's when I switched from AUTO to explicit values and started > experimenting with them. Well, in my example, first server 1 is the master, then I switch around so that server 2 is the master. For a slave to also be able to serve as master, it needs --log-slave-updates=1, right? On the other hand, the design specifically allows for --log-slave-updates=0. I did fix one or two possibly related bugs lately, so please try again and if you can reproduce the problem I will look into it. (I was a bit confused how you could have the transaction in question in the binlog on the server that was changed from slave to master, if it did not have --log-slave-updates=1 ...) > > But nothing happens... No errors in the slave, but it isn't moving, either: > This looks like a bug, your commands look correct. I could reproduce, I will > investigate and fix ASAP and let you know. This should be fixed now and pushed to the tree. > > Getting the wrong mutex usage warnings. I saw a discussion on IRC the other day, but don't know the current status -- whether it's supposed to be fixed, or not fixed yet, or is considered innocent... This also should be fixed in the tree now. - Kristian. (who now has to run from the computer and hopes the above makes sense...)

1 0

Re: [Maria-developers] Fwd: some question on bundled libedit in mysql
by Sergei Golubchik 25 Feb '13

25 Feb '13

Hi, AL13N! On Feb 25, AL13N wrote: > > > > In MariaDB we prefer to link with readline. We never link with bundled > > libedit at all. Either with system readline, if it's usable, or with > > system libedit, or with bundled readline. > > iinm, MariaDB is GPLv2 and libreadline (version 6) is GPLv3, so they don't > mix. but bundled libreadline (version 5) is GPLv2 and that one is > possible. That's why I said "with system readline, if it's usable". We only link with system readline if it's V5 or earlier. Regards, Sergei

1 0

[Maria-developers] Fwd: some question on bundled libedit in mysql
by xiaobing jiang 25 Feb '13

25 Feb '13

hi all: No reply from jonathan.perkin, so post to the maillist. can some one explain to me? Thanks! ---------- Forwarded message ---------- From: xiaobing jiang <s7v7nislands(a)gmail.com> Date: Fri, Feb 22, 2013 at 3:52 PM Subject: some question on bundled libedit in mysql To: jonathan.perkin(a)oracle.com hi jonathan: after read the README in source, I have some question about libedit. in recently, we find a bug. when using bundled libedit, we can't input the chinese using GBK. after debug, I find this code may be cause the bug. protected int terminal__putc(EditLine *el, Int c) { char buf[MB_LEN_MAX +1]; ssize_t i; mbstate_t state; memset(&state, 0, sizeof(mbstate_t)); if (c == (Int)MB_FILL_CHAR) return 0; i = ct_encode_char(buf, (size_t)MB_CUR_MAX, c, &state); // this should be: ct_encode_char(buf, (size_t)MB_LEN_MAX, c, &state); if (i <= 0) return (int)i; buf[i] = '\0'; return fputs(buf, el->el_outfile); } after change the code, I can fix the bug. and I find upstream also using MB_LEN_MAX. http://cvsweb.netbsd.org/bsdweb.cgi/src/lib/libedit/terminal.c protected int terminal__putc(EditLine *el, Int c) { char buf[MB_LEN_MAX +1]; ssize_t i; if (c == (Int)MB_FILL_CHAR) return 0; i = ct_encode_char(buf, (size_t)MB_LEN_MAX, c); if (i <= 0) return (int)i; buf[i] = '\0'; return fputs(buf, el->el_outfile); } so the merge from upstream may be wrong. And this bug may be the same http://bugs.mysql.com/bug.php?id=23097 and why mysql5.6 remove the bundled readline? I think readline is used more than libedit. and when using system readline, why perfer libedit to readline ? look at cmake/readline.cmake. FIND_SYSTEM_LIBEDIT(edit) -> FIND_SYSTEM_LIBEDIT(readline) thanks! xiaobing jiang

2 1

[Maria-developers] understanding commit and prepare APIs in MariaDB 5.5
by Zardosht Kasheff 21 Feb '13

21 Feb '13

Hello all, I am investigating what our storage engine, TokuDB, needs to do to achieve its best performance in MariaDB 5.5 with respect to preparing and committing transactions, and with respect to binary logging. As I understand, group commit has been added to the binary log in Maria 5.3, and is also in MariaDB 5.5. Additionally, new APIs prepare_ordered and commit_ordered are added. I am trying to understand how these fit together. >From reading http://kristiannielsen.livejournal.com/12408.html and comments in handler.h, here is what I THINK I know: - In MariaDB 5.5, we must fsync our recovery log on handlerton->prepare and handlerton->commit - If we want to ensure that the order we commit transactions is the same order that they appear committed in the binary log, then we should perform our commit in two phases, commit_ordered and commit, where commit_ordered is serialized under a global mutex that ensures the correct order - InnoDB must do this for hot backup to work Here is my big question: given TokuDB is not designed to work with InnoDB's hot backup solution, is there ANY reason why we should care about the commit order of our transactions? Are there any performance reasons related to group commit? >From everything I read, I think the answer is no, but I would appreciate confirmation. I think there are no performance gains to be made by implementing commit_ordered, and given we fsync on commit, there should be no issues with syncing our engine up with the binary log after a crash. Also, I assume the fsync after commit is necessary. Only in 10.0 will some new functionality be available that allows us to not fsync on commit. Is my understanding correct? Thanks -Zardosht

2 7

Re: [Maria-developers] Rev 3663: MDEV-3819 missing constraints for spatial column types
by Sergei Golubchik 20 Feb '13

20 Feb '13

Hi, Holyfoot! On Feb 20, holyfoot(a)askmonty.org wrote: > revno: 3663 > revision-id: holyfoot(a)askmonty.org-20130220195237-99gp9p29xemltqcm > parent: vvaintroub@pcbsd-20130219224652-t84v8wepdqvhxfwb > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-3819 > timestamp: Wed 2013-02-20 23:52:37 +0400 > message: > MDEV-3819 missing constraints for spatial column types. > Checks added to return and error when inappropriate > geometry type is stored in a field. > === modified file 'mysql-test/r/gis.result' > --- a/mysql-test/r/gis.result 2012-09-05 21:14:33 +0000 > +++ b/mysql-test/r/gis.result 2013-02-20 19:52:37 +0000 > @@ -1491,4 +1491,11 @@ SELECT 1 FROM g1 WHERE a >= ANY > (SELECT 1 FROM g1 WHERE a = geomfromtext('') OR a) ; > 1 > DROP TABLE g1; > +# > +# MDEV-3819 missing constraints for spatial column types > +# > +create table t1 (pt point); > +insert into t1 values(Geomfromtext('POLYGON((1 1, 2 2, 2 1, 1 1))')); > +ERROR HY000: Incorrect POLYGON value: 'POINT' I'm sorry, but you got it backwards :) The value above is POLYGON. The type is POINT. The error message should be ERROR HY000: Incorrect POINT value: 'POLYGON' Regards, Sergei

2 1

Re: [Maria-developers] Rev 3663: MDEV-3819 missing constraints for spatial column types
by Sergei Golubchik 20 Feb '13

20 Feb '13

Hi, Holyfoot! On Feb 20, holyfoot(a)askmonty.org wrote: > revno: 3663 > revision-id: holyfoot(a)askmonty.org-20130220172419-j5sad8p7t3191kii > parent: vvaintroub@pcbsd-20130219224652-t84v8wepdqvhxfwb > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-3819 > timestamp: Wed 2013-02-20 21:24:19 +0400 > message: > MDEV-3819 missing constraints for spatial column types. > Checks added to return and error when inappropriate > geometry type is stored in a field. > > === modified file 'sql/field.cc' > --- a/sql/field.cc 2013-01-28 12:36:05 +0000 > +++ b/sql/field.cc 2013-02-20 17:24:19 +0000 > @@ -7580,6 +7580,12 @@ int Field_geom::store(const char *from, > if (wkb_type < (uint32) Geometry::wkb_point || > wkb_type > (uint32) Geometry::wkb_last) > goto err; > + > + if (geom_type != Field::GEOM_GEOMETRY && > + geom_type != Field::GEOM_GEOMETRYCOLLECTION && > + (uint32) geom_type != wkb_type) > + goto err_wrong_type; why goto err_wrong_type? there's no common cleanup or any common code at all at the end, you can put it right here in the if(). > + > Field_blob::store_length(length); > if (table->copy_blobs || length <= MAX_FIELD_WIDTH) > { // Must make a copy > @@ -7595,6 +7601,12 @@ int Field_geom::store(const char *from, > my_message(ER_CANT_CREATE_GEOMETRY_OBJECT, > ER(ER_CANT_CREATE_GEOMETRY_OBJECT), MYF(0)); > return -1; > + > +err_wrong_type: > + bzero(ptr, Field_blob::pack_length()); > + my_message(ER_WRONG_GEOMETRY_TYPE, > + ER(ER_WRONG_GEOMETRY_TYPE), MYF(0)); > + return -1; > } > > #endif /*HAVE_SPATIAL*/ > > === modified file 'sql/share/errmsg-utf8.txt' > --- a/sql/share/errmsg-utf8.txt 2013-02-08 21:24:06 +0000 > +++ b/sql/share/errmsg-utf8.txt 2013-02-20 17:24:19 +0000 > @@ -6588,3 +6588,5 @@ ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT > ER_NO_SUCH_TABLE_IN_ENGINE 42S02 > eng "Table '%-.192s.%-.192s' doesn't exist in engine" > swe "Det finns ingen tabell som heter '%-.192s.%-.192s' i handlern" > +ER_WRONG_GEOMETRY_TYPE > + eng "Unsuitable geometry type for the field" Ouch, is that really needed? Can you use ER_WRONG_VALUE instead? Like Incorrect POINT value: 'POLYGON((1 1, 2 2, 2 1, 1 1))' Regards, Sergei

1 0