October 2010 - developers - lists.mariadb.org

[Maria-developers] request for feedback on live schema changes patch
by Zardosht Kasheff 18 Mar '11

18 Mar '11

Hello all, In another thread, there was a discussion about what it would take to allow the storage engine to support live schema changes in mysql and mariadb. I said that I was working porting this existing feature from MySQL Cluster to MySQL 5.1.46. I would like to show the work I have done so far and solicit feedback. Attached is the diff based off of 5.1.46. Before looking at the patch, please be aware of the following things: - I am not an expert in this code. As a result, I had to use an approach of that minimizes the risk of introducing regressions. \ - My main interest is to get something safe that works and can be integrated in 5.1. As a result, kept the current mysql_alter_table functionality in place (as old_mysql_alter_table), and added MySQL Cluster's as new_mysql_alter_table. For an engine to get new_mysql_alter_table to be used, the engine must expose an alter table flag HA_GENERAL_ONLINE. - Another reason why I kept old_mysql_alter_table was that I did not want to deal with engines that implement handler::add_index and make sure that they work properly under the new API. Also, I did not want to port alter table changes related to partitioned tables. - I have run the current 5,1 mysql test suite (that is in the mysql-test directory of source tarballs), and verified that using old_mysql_alter_table and new_mysql_alter_table works fine with them. That being said, I would love to hear feedback on this patch, and would also like to understand what (if anything) can be done to get this into MariaDB 5.1. I realize that may not be possible, but if it is, I would like to try. Thanks -Zardosht

2 5

[Maria-developers] Implementing new "group commit" API in PBXT?
by Kristian Nielsen 13 Dec '10

13 Dec '10

Hi Paul! I want to ask your opinion about implementing in PBXT an extension to the storage engine API that I am working on. There are lots of details in http://askmonty.org/worklog/Server-BackLog/?tid=116 (and even more details in other places), but I thought you would appreciate the short version :-) The idea is to get a well-defined ordering of commits in the server in an efficient way (eg. not break group commit like InnoDB does currently, Bug#13669). For this, two new (optional) storage engine methods are introduced: void (*prepare_ordered)(handlerton *hton, THD *thd, bool all); void (*commit_ordered)(handlerton *hton, THD *thd, bool all); The prepare_ordered() method is called after prepare(), as soon as commit order is decided. The commit_ordered() method is called before commit(), just after the transaction coordinator has made the final decision that the transaction will be durably committed (and not rolled back). The calls into commit_ordered() among different transactions will happen in the order that these transactions are committed, consistently across all engines and the binary log. Same for prepare_ordered(). The idea is that the storage engine should do the minimal amount of work in commit_ordered() necessary to make the commit visible to other threads. And to make sure commits appear to be done in the order of calls to these methods. Do you think either (or both) of these methods could be implemented in PBXT with reasonable effort (and if so, how)? ---- In InnoDB, this was trivial to do, as the InnoDB commit() method already had a "fast" part (which fixed the transaction log order (= "commit order") and made the transaction visible) and a "slow" part (which did the fsync() to make the transaction durable, and handled group commit). (It is necessary that commit_ordered() is fast, as it runs under a global lock. Ideally, it will just allocate an LSN in the transaction log to fix commit order, and perhaps whatever else already needs to happen serialised during engine commit). I hope my explanation was sufficiently clear for you to make a qualified answer. Maybe you can point me to where in the PBXT code a commit becomes visible and the commit order is fixed? In case you were wondering, here are some of the motivations for this feature: 1. For taking a hot backup, it is useful to have consistent commit order between binlog and storage engines. Without it, it can happen that the backed up state of the server has transaction A (but not B) committed in the storage engine, and transaction B (but not A) written to the binlog. Using such backup to provision a new master or slave would leave replication in an inconsistent state. 2. This feature implements working group commit for the binlog while still preserving consistent order as per (1). 3. This will allow to implement START TRANSACTION WITH CONSISTENT SNAPSHOT for multi-engine transactions that is truly consistent (currently is is possible for a transaction to be visible in one engine but not another in such "consistent" snapshot). 4. Galera relies on a consistent commit order, and I believe this feature will allow it to get this in a more engine-independent way. 5. We are planning to use consistent commit order to allow MySQL to recover after a crash transactions that were synced to disk in the binlog but not in the engine. This will allow to reduce the number of fsyncs() during prepare() / commit() from 3 to 1; it only needs to be done in the binlog (with group commit); the engine does not need to fsync(), as any lost transactions will be recovered from the binlog after crash. 6. The prepare_ordered() method is inspired by the Facebook patch to release InnoDB row-level read locks early (before syncing the binlog to disk) to improve performance in the presence of hot spots (probably does not apply to PBXT). - Kristian.

2 9

[Maria-developers] Volunteer my machine for buildbot and database with test results
by Haidong Ji 10 Dec '10

10 Dec '10

To whom it may concern, Per information shown on URL below, with relevant paragraph pasted, I'd love to volunteer to run a build slave and other tests. Let me know how to participate, such as: Will an old laptop work? If so, what do you need? What do I need to do on my side? Does it mean that I need to leave my machine on all the time? What if a reboot happens while it is being used? I am sure more questions as we proceed. Thanks, Haidong "Alex" Ji http://kb.askmonty.org/v/about-buildbot Volunteering to run a build slave Many of our build hosts are run by community members, and we are always looking for additional volunteers to help us cover additional platforms or build options in BuildBot. If you are able to provide a spare machine for this purpose, your help is greatly appreciated! This is a good way to get involved without having to spend a lot of time on it. Get started by writing an email to maria-developers(a)lists.launchpad.net with an offer to run a BuildBot slave. Database with test results Buildbot saves the results of test runs in a database, to be used for enhanced reporting on web pages without need to change the Buildbot code, and for data mining when investigating test failures. The database schema is documented under Buildbot Database Schema. The schema is likely to evolve as we gradually add more kinds of information. For now, the data is not externally available. But the plan is to set up a slave database to replicate the data, and provide access (eg. remote database accounts) to members of the community with interesting ideas about how to present or mine this data, or who are just curious to play with it. If anyone has an interest in this, or wants to volunteer a slave host for this purpose, please send a mail to maria-developers(a)lists.launchpad.net. The more people show interest in this, the faster it is likely to happen!

2 5

[Maria-developers] Transactional INFORMATION_SCHEMA / status variables?
by Kristian Nielsen 05 Nov '10

05 Nov '10

Would it make sense to have transactional behaviour for status variables, and/or information_schema tables? Or does this break the code and/or user expectations too much? The motivation is to follow up on MWL#116, group commit, which introduces consistent commit order between storage engines and binary log. I want to use this to get a consistent binlog position for START TRANSACTION WITH CONSISTENT SNAPSHOT, without taking any additional locks. Currently, I believe it is common (eg. mysqlbinlog --master-data --single-transaction) to do something like this: FLUSH TABLES WITH READ LOCK; START TRANSACTION WITH CONSISTENT SNAPSHOT; UNLOCK TABLES <take consistent backup by dumping tables using the consistent snapshot> and this takes a lock that can depending on circumstances severely affect the server: http://www.mysqlperformanceblog.com/2010/04/24/how-fast-is-flush-tables-wit… One idea is to let the binlog storage engine participate in START TRANSACTION WITH CONSISTENT SNAPSHOT, by installing a start_consistent_snapshot() method in its handlerton. And then do something like this: START TRANSACTION WITH CONSISTENT SNAPSHOT; SELECT variable_value FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE variable_name IN ('binlog_master_file', 'binlog_master_position'); <dump other transactional tables> If the SELECT of binlog_master_file and binlog_master_position could be transactional, then the binlog engine could return the correct values associated with the consistent snapshot, without blocking any other transactions. I like the simplicity of this idea, but I do not understand server transaction handling enough to be sure it will work well, some concerns: - Using SHOW STATUS / INFORMATION_SCHEMA.SESSION_STATUS like this could be surprising to people that do not expect different parts of the results to display different kinds of transactional behaviour. This could be helped by using instead a separate INFORMATION_SCHEMA table for the binlog position. - But this raises another concern, if an INFORMATION_SCHEMA can be transactional in this respect? - Also, it seems to me that merely selecting from such transactional INFORMATION_SCHEMA table would then start a new transaction inside the binlog engine. I wonder if this would cause any unpleasant side effects? Any suggestions or comments? ---- If this does not work, I have another idea, which I think is more general, but also more complicated to implement. The idea is that every transaction has a local transaction ID, assigned at the start (we already have this, in thd->transaction.xid_state.xid). Each engine will transactionally store the local transaction ID of the last transaction committed. The binary log will similarly store this ID along with every transaction that is binlogged. Then START TRANSACTION WITH CONSISTENT SNAPSHOT could optionally return the local transaction ID of the last committed transaction visible in the snapshot obtained. This local transaction ID could then be mapped to binlog position (with eg. mysqldump), and more generally any binlog plugin could provide a way to map such local transaction ID into its own global transaction ID. Similarly, after restore of InnoDB hot backup or LVM snapshot, one could ask the engine for last committed local transaction ID, and map this to binlog position / global transaction ID to be able to use the restored backup to provision a new slave. This would work with any storage engine and any binlog/replication implementation, without any need for FLUSH TABLES WITH READ LOCK. - Kristian.

3 3

[Maria-developers] DS-MRR improvements patch r3 ready for review
by Sergey Petrunya 03 Nov '10

03 Nov '10

Hello Igor, Please find attached the combined patch that addresses all of the review feedback provided so far. The tree is in launchpad and buildbot also: https://code.launchpad.net/~maria-captains/maria/5.3-dsmrr-cpk BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 3

[Maria-developers] gcc -Wframe-larger-than
by Sergei Golubchik 01 Nov '10

01 Nov '10

Hi, Just noticed that gcc has a warning option -Wframe-larger-than=LEN that makes gcc to warn about functions with stack frames larger than LEN. Considering a recent issue when we had a 130K structure on the stack (which did overflow the stack in some cases), it may be a good idea to enable this warning with a reasonably conservative threshold, say, 16K or 32K. Regards, Sergei

2 1

Re: [Maria-developers] [Commits] Rev 2951: WL#12 - MariaDB User Feedback (a.k.a. Phone Home) plugin
by Sergei Golubchik 01 Nov '10

01 Nov '10

Hi, Michael! On Oct 27, Michael Widenius wrote: > >>>>> "Sergei" == Sergei Golubchik <serg(a)askmonty.org> writes: > > >> +++ b/plugin/feedback/sender_thread.cc 2010-09-30 14:24:31 +0000 > > ... > >> + if (thd) // for nicer SHOW PROCESSLIST > >> + thd->set_query(const_cast<char*>(url->url()), url->url_length()); > >> > >> Wouldn't it be better if url->url() would return const char * ? > > > It does return const char*, but thd->set_query() wants char* (for no > > good reason), so I need to cast const away. > > Should we change set_query() to take const char * ? I can do that, yes, but this plugin will need a cast anyway to work with MySQL sources. > >> +Url* http_create(const char *url, size_t url_length) > >> +{ > >> + const char *s; > >> + LEX_STRING full_url= {const_cast<char*>(url), url_length}; > >> + LEX_STRING host, port, path; > >> > >> Would it not be better to introduce LEX_CONS_STRING and use these to > >> avoid const away casts? > > > Perhaps, if I'd have more casts because of that. But just because of one > > or two - I'd rather keep the code uniform instead and use LEX_STRING > > everywhere. > > I tink there is many cases in the code where we have to cast const > away just becasue we don't have LEX_CONST_STRING. Don't you think it's > time to introduce it ? Not in the plugin. Btw, MariaDB already has LEX_CUSTRING (const unsigned) variant. Regards, Sergei

2 1

Re: [Maria-developers] [Commits] Rev 2840: Fixed LP bug #668290. in file:///home/igor/maria/maria-5.3-mwl128-bug668290/
by Sergey Petrunya 30 Oct '10

30 Oct '10

Hello Igor, Plese find the comments below. On Sat, Oct 30, 2010 at 12:56:10PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mwl128-bug668290/ > > ------------------------------------------------------------ > revno: 2840 > revision-id: igor(a)askmonty.org-20101030195609-h53be2cdzjlz38bz > parent: igor(a)askmonty.org-20101030130745-563ypxtkcwv4vbfg > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-mwl128-bug668290 > timestamp: Sat 2010-10-30 12:56:09 -0700 > message: > Fixed LP bug #668290. > Prohibited to use hash join algorithm BNLH if join attributes > need non-binary collations. It has to be done because BNLH does > not support join for such attributes yet. > Later this limitations will be lifted. > > Changed default collations for the schemes of some test cases > to preserve the old execution plans. ... > === modified file 'sql/field.h' > --- a/sql/field.h 2010-09-23 22:00:32 +0000 > +++ b/sql/field.h 2010-10-30 19:56:09 +0000 > @@ -585,6 +585,12 @@ > } > /* Hash value */ > virtual void hash(ulong *nr, ulong *nr2); > + > + virtual bool hash_join_is_possible(bool globally_allowed) > + { > + return globally_allowed; > + } Is there any partiular reason why have this globally_allowed parameter? In my understanding, a field either allows hash or not, and that is regardless of whether it was "globally allowed", so the check of whether hash join is globally allowed should be done outside of the function. Also, it would be nice to have here a comment specifying the meaning of this function. (I know all this is supposed to be temporary, but expirience shows that often supposedly temporary things stay for a long time, that's why it's nice to put a comment). > + > friend bool reopen_table(THD *,struct st_table *,bool); > friend int cre_myisam(char * name, register TABLE *form, uint options, > ulonglong auto_increment_value); > @@ -760,6 +766,12 @@ > my_decimal *val_decimal(my_decimal *); > virtual bool str_needs_quotes() { return TRUE; } > uint is_equal(Create_field *new_field); > + > + bool hash_join_is_possible(bool globally_allowed) > + { > + /* TODO: support hash joins for non-binary collations */ > + return globally_allowed && (flags & BINARY_FLAG); > + } > }; > > > @@ -1904,6 +1916,7 @@ > uint size_of() const { return sizeof(*this); } > int reset(void) { return !maybe_null() || Field_blob::reset(); } > geometry_type get_geometry_type() { return geom_type; }; > + bool hash_join_is_possible(bool globally_allowed) { return FALSE; } > }; > #endif /*HAVE_SPATIAL*/ > > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2010-10-30 13:07:45 +0000 > +++ b/sql/sql_select.cc 2010-10-30 19:56:09 +0000 > @@ -3547,7 +3547,7 @@ > /* > Additional optimization: if we're processing > "t.key BETWEEN c1 AND c1" then proceed as if we were processing > - "t.key = c1". > + "t.key = c1".sis > TODO: This is a very limited fix. A more generic fix is possible. Is the above a meaningful change? > There are 2 options: > A) Make equality propagation code be able to handle BETWEEN > @@ -5946,6 +5946,44 @@ > } > > > +/** > + @brief > + Check whether hash join algorithm can be used to join this table > + > + @param is_allowed usage of hash join is allowed > + > + @details > + This function finds out whether the ref items that have been chosen > + by the planner to access this table can be used for hash join algorithms. > + The answer depends on a certain property of the the fields of the > + joined tables on which the hash join key is built. If hash join is > + allowed for all these fields the answer is positive unless hash join > + algorithms are not allowed for the query at all. > + > + @note > + The function is supposed to be called now only after the function > + get_best_combination has been called. > + > + @retval TRUE it's possible to use hash join to join this table > + @retval FALSE otherwise > +*/ > + > +bool JOIN_TAB::hash_join_is_possible(bool is_allowed) > +{ > + if (!is_allowed) > + return FALSE; Same as with globally_allowed. I think the parameter is redundant and rather confusing. > + if (type != JT_REF && type != JT_EQ_REF) > + return FALSE; > + KEY *keyinfo= &table->key_info[ref.key]; > + for (uint i= 0; i < ref.key_parts; i++) > + { > + if (!(keyinfo->key_part[i].field->hash_join_is_possible(TRUE))) > + return FALSE; > + } > + return TRUE; > +} > + > + > static uint > cache_record_length(JOIN *join,uint idx) > { > @@ -7649,8 +7687,10 @@ > &bufsz, &flags, &cost); > > if ((cache_level <=4 && !no_hashed_cache) || no_bka_cache || > - (flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6) > + ((flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6)) > { > + if (!tab->hash_join_is_possible(TRUE)) > + goto no_join_cache; > if (cache_level == 3) > prev_cache= 0; > if ((tab->cache= new JOIN_CACHE_BNLH(join, tab, prev_cache)) && > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2010-10-30 13:07:45 +0000 > +++ b/sql/sql_select.h 2010-10-30 19:56:09 +0000 > @@ -391,6 +391,7 @@ > return max_used_fieldlength; > } > double get_partial_join_cardinality() { return partial_join_cardinality; } > + bool hash_join_is_possible(bool is_allowed); > } JOIN_TAB; > > > B8R Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] support-files/my-xxx.cnf templates cause warning when used
by Oli Sennhauser 30 Oct '10

30 Oct '10

Hello all, The support-files/my-xxx.cnf templates have an old value in it and thus give a nasty Warning message when used: 101029 21:06:04 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. So I have changed it. The patch should work for MariaDB 5.1 AND 5.2... Can somebody please put it into the trees when acceptable? Thanks, Oli --- support-files/my-huge.cnf.sh.orig 2010-10-29 21:09:24.000000000 +0200 +++ support-files/my-huge.cnf.sh 2010-10-29 21:10:47.000000000 +0200 @@ -25,7 +25,7 @@ socket = @MYSQL_UNIX_ADDR@ [mysqld] port = @MYSQL_TCP_PORT@ socket = @MYSQL_UNIX_ADDR@ -skip-locking +skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 --- support-files/my-large.cnf.sh.orig 2010-10-29 21:09:22.000000000 +0200 +++ support-files/my-large.cnf.sh 2010-10-29 21:10:33.000000000 +0200 @@ -25,7 +25,7 @@ socket = @MYSQL_UNIX_ADDR@ [mysqld] port = @MYSQL_TCP_PORT@ socket = @MYSQL_UNIX_ADDR@ -skip-locking +skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 --- support-files/my-medium.cnf.sh.orig 2010-10-29 21:09:20.000000000 +0200 +++ support-files/my-medium.cnf.sh 2010-10-29 21:10:05.000000000 +0200 @@ -26,7 +26,7 @@ socket = @MYSQL_UNIX_ADDR@ [mysqld] port = @MYSQL_TCP_PORT@ socket = @MYSQL_UNIX_ADDR@ -skip-locking +skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 --- support-files/my-small.cnf.sh.orig 2010-10-29 21:09:17.000000000 +0200 +++ support-files/my-small.cnf.sh 2010-10-29 21:09:45.000000000 +0200 @@ -26,7 +26,7 @@ socket = @MYSQL_UNIX_ADDR@ [mysqld] port = @MYSQL_TCP_PORT@ socket = @MYSQL_UNIX_ADDR@ -skip-locking +skip-external-locking key_buffer_size = 16K max_allowed_packet = 1M table_open_cache = 4 -- FromDual - Vendor independent and neutral MySQL consulting. Oli Sennhauser Rebenweg 6 CH - 8610 Uster / Switzerland Phone +41 44 940 24 82 Mobile +41 79 830 09 33 e-Mail oli.sennhauser(a)fromdual.com Website http://www.fromdual.com/ Skype fromdual Jabber fromdual(a)swissjabber.ch Yahoo IM fromdual Xing https://www.xing.com/profile/Oliver_Sennhauser LinkedIn http://ch.linkedin.com/in/shinguz Twitter http://twitter.com/shinguz

2 1

[Maria-developers] On possible bug in get_partial_join_cost()
by Sergey Petrunya 29 Oct '10

29 Oct '10

Hi Timour, Answers to irc questions: <timour> spetrunia, I found a problem in get_partial_join_cost(), could you please look at this: <timour> for (uint i= join->const_tables; i < n_tables + join->const_tables ; i++) <timour> spetrunia, why "i < n_tables + join->const_tables" ? isn't the number of tables always the same? <timour> That is, shouldn't we just skip the constant tables assuming they are in the beginning of join->best_positions ? Yes we should skip the constant tables, and they are at the beginning of best_positions. The number of tables passed as n_tables varies across different invocations of get_partial_join_cost(): uint n_tables= my_count_bits(sj_nest->sj_inner_tables & ~join->const_table_map); SJ_MATERIALIZATION_INFO* sjm; if (!(sjm= new SJ_MATERIALIZATION_INFO) || !(sjm->positions= (POSITION*)join->thd->alloc(sizeof(POSITION)* n_tables))) DBUG_RETURN(TRUE); /* purecov: inspected */ sjm->tables= n_tables; sjm->is_used= FALSE; double subjoin_out_rows, subjoin_read_time; get_partial_join_cost(join, n_tables, &subjoin_read_time, &subjoin_out_rows); Here one can see that n_tables is "number of non-constant tables within an SJ-Materialization nest". Different nests will contain different numbers of tables. <timour> spetrunia, I changed the loop upper boundary to n_tables, then I get few improved EXPLAINS, and got a crash, then I had to leave. I think the change is wrong: if you stop at n_tables, that means that partial_join_cost() has done optimization for the first (n_tables - join->const_tables) non-constant tables, which is not right. If you need further help, could you give a concrete example that causes the problem? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0