- developers - lists.mariadb.org

[Maria-developers] accelerating CREATE TABLE
by Toshikuni Fukaya 13 Mar '13

13 Mar '13

Hi, I made a patch to accelerate CREATE TABLE on the innodb plugin. To zero table spaces, I used fallocate instead of normal writes and sync. I compared MariaDB 5.5 trunk and the patched one by our own application, which creates > 1000 tables in series, with ext4 filesystem on Ubuntu 12.04. Results are shown below. Original MariaDB 5.5 trunk: 64.9 seconds (5 times avg.) Patched MariaDB 5.5 trunk: 36.0 seconds (5 times avg.) Any comments or suggestions? Thanks, Toshikuni Fukaya

3 8

[Maria-developers] MariaDB 5.5.30 Release Prep
by Daniel Bartholomew 12 Mar '13

12 Mar '13

All, I've prepped changelog and release notes pages for MariaDB 5.5.30. https://kb.askmonty.org/en/mariadb-5530-release-notes/ https://kb.askmonty.org/en/mariadb-5530-changelog/ If there is anything I should be sure to mention in the release notes, please let me know ASAP. Unless something comes up, I plan on activating and announcing the release tomorrow (12 Mar 2013). Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

1 0

[Maria-developers] issue with Index Condition Pushdown in MariaDB 5.5.28a
by Zardosht Kasheff 11 Mar '13

11 Mar '13

Hello, I have found an interesting issue with index condition pushdown. I do not know if it is a bug, or if I am misusing the feature. I have found that in one scenario, when performing a join, an index condition is pushed down, but because handler->end_range is not set, handler_index_cond_check never returns ICP_OUT_OF_RANGE, even though we are out of range. Here is the relevant stack trace, I cannot yet send the entire trace as it may include customer information, and I need to double check it is ok to send: #15 0x00002aaac4f33593 in ha_tokudb::index_next_same (this=0x2fb33d8, buf=0x2fb53b8 "\302\177\254\353/\036", key=0x31b36d0 "Y\001", keylen=395) ---Type <return> to continue, or q <return> to quit--- at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/storage/tokudb/ha_tokudb.cc:4768 #16 0x00000000005e782c in handler::ha_index_next_same (this=0x2fb33d8, buf=0x2fb53b8 "\302\177\254\353/\036", key=0x31b36d0 "Y\001", keylen=395) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_class.h:4246 #17 0x000000000065d103 in join_read_next_same (info=0x31b2f68) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16929 #18 0x000000000065b54a in sub_select (join=0x319f588, join_tab=0x31b2eb8, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16142 #19 0x000000000065bb5f in evaluate_join_record (join=0x319f588, join_tab=0x31b2b98, error=0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16342 #20 0x000000000065b628 in sub_select (join=0x319f588, join_tab=0x31b2b98, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16162 #21 0x000000000065bb5f in evaluate_join_record (join=0x319f588, join_tab=0x31b2878, error=0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16342 #22 0x000000000065b498 in sub_select (join=0x319f588, join_tab=0x31b2878, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16123 #23 0x000000000065bb5f in evaluate_join_record (join=0x319f588, join_tab=0x31b2558, error=0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16342 #24 0x000000000065b628 in sub_select (join=0x319f588, join_tab=0x31b2558, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16162 #25 0x000000000065ad52 in do_select (join=0x319f588, fields=0x0, table=0x31bf368, procedure=0x0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:15793 #26 0x0000000000639c5d in JOIN::exec (this=0x319f588) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:2396 In this stack, we are performing an index_next_same, and a condition has been pushed down. When we call handler_index_cond_check. we always get ICP_NO_MATCH and never get ICP_OUT_OF_RANGE, even though we go past the key that we should be retrieving. Is this by design? Is this a bug? Thanks -Zardosht

2 2

[Maria-developers] Spam in KB?
by Sergei Petrunia 07 Mar '13

07 Mar '13

Hi Daniel, I am looking at https://kb.askmonty.org/en/mariadb-5529-release-notes/ and I see at the top in the breadcrumbs: [Home] [ Why Shared Web Hosting Service Is Ideal For Small Businesses] [Development] and when I click on the shared hosting I get to: https://kb.askmonty.org/en/why-shared-web-hosting-service-is-ideal-for-smal… In my understanding this text has nothing to do with MariaDB? Is it link spam? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] Rev 3633: MDEV-4241 fix
by Sergei Golubchik 06 Mar '13

06 Mar '13

Hi, Sanja! On Mar 06, sanja(a)askmonty.org wrote: > revno: 3633 > revision-id: sanja(a)askmonty.org-20130306191058-d293nik3l66vn4tp > parent: psergey(a)askmonty.org-20130301042335-x16j7uo1dwxxgqs9 > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-MDEV-4241 > timestamp: Wed 2013-03-06 21:10:58 +0200 > message: > MDEV-4241 fix. > > Field_enum incorrectly inherited decimals() from Field_string. > Field_enum should be always integer in numeric context. Ok to push! Regards, Sergei

1 0

[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