- developers - lists.mariadb.org

[Maria-developers] WL#190 New (by Yakatz): Optional parameters support in stored procedures
by worklog-noreply＠askmonty.org 28 Mar '11

28 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Optional parameters support in stored procedures CREATION DATE..: Mon, 28 Mar 2011, 20:26 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 190 (http://askmonty.org/worklog/?tid=190) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: This bug has been open on MySQL since 2005. In a nutshell, support PROCEDUREs like this: CREATE PROCEDURE ListUsers ( @userID int, @authorized bit = NULL ) AS.... ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Review of replication/group commit work
by Kristian Nielsen 28 Mar '11

28 Mar '11

Hi Monty, Thanks for offering to review my replication worklogs. Here are pointers to the various worklogs and patches, and suggestions for order to things in etc. Some background information is in this series on my blog: http://kristiannielsen.livejournal.com/12254.html http://kristiannielsen.livejournal.com/12408.html http://kristiannielsen.livejournal.com/12553.html http://kristiannielsen.livejournal.com/12810.html There is a tree on launchpad based on 5.2 with all the patches merged, if you want to look at the actual code in one piece: lp:~maria-captains/maria/mariadb-5.2-rpl ---- The main worklog, and the place to start, is MWL#116: http://askmonty.org/worklog/Server-Sprint/?tid=116 I think you'll have to read through the worklog/design first (it already had architecture review by Serg) in order to properly review the code. The patch for MWL#116 is here: https://lists.launchpad.net/maria-developers/msg03693.html It also has links to a lauchpad tree with just this patch. This worklog extends the storage engine API with a facility to efficiently coordinate commit order among transaction participants, and also implements group commit for binlog. ---- The next worklog is MWL#136: http://askmonty.org/worklog/Server-RawIdeaBin/?tid=136 Here is the commit mail: https://lists.askmonty.org/pipermail/commits/2010-November/000735.html (also this one, but it just renames some variables to bettter names): https://lists.askmonty.org/pipermail/commits/2010-November/000741.html This worklog makes START TRANSACTION WITH CONSISTENT SNAPSHOT, well, consistent. It also uses this for the binlog to provide non-blocking mysqldump --single-transaction --master-data. ---- The last worklog is MWL#163: http://askmonty.org/worklog/Server-Sprint/?tid=163 Here is the commit mail: https://lists.askmonty.org/pipermail/commits/2010-November/000711.html This is a re-write of a Facebook patch. Mark has mentioned this on several occasions as being important for them to fix a hot-spot. It implements an option to release InnoDB row locks earlier, before committing to binlog and storage engine (which is not safe in general, but can be safe in certain applications such as Facebook presumably). ---- That's it for now! - Kristian.

2 1

[Maria-developers] review: [Commits] Rev 2863: MWL#163: release of row locks in InnoDB during prepare in http://bazaar.launchpad.net/~maria-captains/maria/5.1
by Michael Widenius 28 Mar '11

28 Mar '11

Hi! >>>>> "knielsen" == knielsen <knielsen(a)knielsen-hq.org> writes: knielsen> At http://bazaar.launchpad.net/~maria-captains/maria/5.1 knielsen> ------------------------------------------------------------ knielsen> revno: 2863 knielsen> revision-id: knielsen(a)knielsen-hq.org-20101103160708-88w8p8ibe9iaoggj knielsen> parent: knielsen(a)knielsen-hq.org-20101102074027-jlnx0rnbdpovl122 knielsen> committer: knielsen(a)knielsen-hq.org knielsen> branch nick: work-5.1-mwl163 knielsen> timestamp: Wed 2010-11-03 17:07:08 +0100 knielsen> message: knielsen> MWL#163: release of row locks in InnoDB during prepare knielsen> Port the Facebook patch for releasing InnoDB row locks early to the knielsen> MWL#116 framework. knielsen> === modified file 'storage/xtradb/handler/ha_innodb.cc' knielsen> --- a/storage/xtradb/handler/ha_innodb.cc 2010-11-01 14:41:09 +0000 <cut> Spent 30 minutes checking the innodb code and things looked ok. Regards, Monty

1 0

[Maria-developers] Writeup of how to run my group commit benchmark
by Kristian Nielsen 28 Mar '11

28 Mar '11

Hi, For anyone interested in trying to redo my group commit benchmarks on more interesting hardware than what I have available, here is a writeup of the steps needed to run it. The benchmarks are described here: http://kristiannielsen.livejournal.com/15739.html http://kristiannielsen.livejournal.com/15515.html 1. Compile gypsy. This is used to generate the load. I use a specific version as the latest in trunk seemed to not build on non-windows last I tried: bzr branch '-rrevid:shane@mysql.com-20100224065101-jk3s5sro2p914nvp' lp:gypsy cd gypsy make This requires `mysql_config` in the path to get includes/libraries for libmysqlclient. It also needs openssl headers/libraries (eg. install libssl-dev package). 2. Install MariaDB with group commit enabled. Packages/sources are available for this here: http://kb.askmonty.org/v/mariadb-52-replication-feature-preview These packages are however missing the --binlog-optimize-thread-scheduling option that I used to compare the two ways to schedule threads in the above blog posts. To get that option, grab this bzr tree and build: bzr branch lp:~knielsen/maria/mariadb-5.1-mwl116-threadscheduling cd mariadb-5.1-mwl116-threadscheduling BUILD/compile-pentium64-max 3. Configure and start mysqld This involves `make install` and `mysql_install_db` when building from source, check README/INSTALL for details. For configuration, set whatever parameters are interesting to test. Eg. set InnoDB buffer pool size and log file/buffer size to something sensible, set innodb_flush_log_at_trx_commit=1 and sync_binlog=1. Also create a mysql account and a database with suitable access permissions. 4. Create the table to use: mysql --host=127.0.0.1 -utest -ptestpass test MariaDB [test]> create table t (a int primary key, b int) engine=innodb; 5. Create the script for Gypsy to generate the load. Just create a file simpleload.gypsy containing this single line: ----------------------------------------------------------------------- p|1|REPLACE INTO t (a,b) VALUES (? MOD 1000000, ? MOD 1000000)|int,int ----------------------------------------------------------------------- 6. Run the bemchmark. I run it with `script` to capture the output (yeah, I know it's crude). The `gypsy` binary is found in the directory where it was compiled in step (1). script for i in 1 4 8 12 16 24 32 48 64 96 128 ; do gypsy --host=127.0.0.1 --user=test --password=testpass --database=test --queryfile=simpleload.gypsy --duration=180 --threads=$i ; done Adjust host/user/password as appropriate for the installation of mariadb. 7. That should be it! This is obviously a rather crude benchmark, sorry about that, I did not have experience with something like sysbench or tcp-c, would be interesting to see results from such benchmarks also. - Kristian.

1 0

Re: [Maria-developers] Bundle more relevant cnf files in MariaDB package
by Arjen Lentz 28 Mar '11

28 Mar '11

Hi Haidong On 12/03/2011, at 7:56 AM, Haidong Ji wrote: > I alluded to this in IRC maybe a couple of weeks back. That is, it is > really to have intelligent default for MariaDB servers. there already is a baseline my.cnf which is installed by the DEB (Debian/Ubuntu) packages. You'll find it in the debian build subdirs. It can a) be updated with more parameters b) also be put into RPM, tarball and other builds. I would recommend not redoing it from scratch, as a number of practical issues have to be considered for this kind of default config, and reinventing the wheel there is just not necessary. Simply extend what's already there! > In particular, the bundled sample my.cnf files, under > /usr/local/mysql/support-files, such as my-small.cnf, my-medium.cnf, > my-large.cnf, and my-huge.cnf, are laughable in today's world. yep they are crap and should be removed. However, since they've already been replaced in the real world this is a separate issue. The my-*.cnf are not proof of "we need to create a good sample config" but rather a "this is old junk that should be removed from the bzr tree." The hindrance there is that some builds currently use them, so simply deleting the files is not sufficient. I'll see if I can find those dependencies, and create a modified tree for merging. Cheers, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Remote expertise & maintenance for MySQL/MariaDB server environments. Follow us at http://openquery.com/blog/ & http://twitter.com/openquery _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-developers] Pushing/merging sample my.cnf
by Haidong Ji 25 Mar '11

25 Mar '11

All, I've created my trunk on launchpad for MariaDB, and am ready to make some updates to the sample my.cnf file(s) packaged with MariaDB. I have a few questions regarding how to proceed: 1. I've seen work log creation and assignment in MariaDB developer mailing list. Is work log creation necessary for changes like this, where no C/C++ source code is touched? If yes, any guidelines will be appreciated; 2. As this is more packaging related, what are the testing requirement? 3. What about peer review? I've blogged this and also just sent another email for more input. 4. After I make the change on my machine, build it, and push the changes back to Lauchpad, I will do a merge request. 5. Anything else? Comments/suggestions? Thanks, Haidong "Alex" Ji

2 4

[Maria-developers] review: [Commits] Rev 2946: Fix LP BUG#715738 in file:///home/tsk/mprog/src/5.3/
by Michael Widenius 23 Mar '11

23 Mar '11

Hi! >>>>> "timour" == timour <timour(a)askmonty.org> writes: timour> At file:///home/tsk/mprog/src/5.3/ timour> ------------------------------------------------------------ timour> revno: 2946 timour> revision-id: timour(a)askmonty.org-20110322132154-moeuyey4saatnm5x timour> parent: igor(a)askmonty.org-20110315193048-akp4yq0y079dkkx2 timour> committer: timour(a)askmonty.org timour> branch nick: 5.3 timour> timestamp: Tue 2011-03-22 15:21:54 +0200 timour> message: timour> Fix LP BUG#715738 timour> Analysis: timour> A query with implicit grouping is one with aggregate functions and timour> no GROUP BY clause. MariaDB inherits from MySQL an SQL extenstion timour> that allows mixing aggregate functions with non-aggregate fields. timour> If a query with such mixed select clause produces an empty result timour> set, the meaning of aggregate functions is well defined - either timour> NULL (MIN, MAX, etc.), or 0 (count(*)). However the non-aggregated timour> fields must also have some value, and the only reasonable value in timour> the case of empty result is NULL. <cut> === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2011-03-09 13:47:59 +0000 +++ b/sql/sql_lex.cc 2011-03-22 13:21:54 +0000 @@ -1667,6 +1667,11 @@ void st_select_lex::init_select() cond_value= having_value= Item::COND_UNDEF; inner_refs_list.empty(); full_group_by_flag= 0; + n_sum_items= 0; + n_child_sum_items= 0; + select_n_sum_item= 0; + select_n_nonsum_item= 0; + ok, but I am not totally happy with that we get even more things that needs to be initialized for every select (we already initialize a lot of things...) Please change the names of 'n_' to something more understandable. 'number_of_' or 'use the _count' prefix that we use in other places. === modified file 'sql/sql_yacc.yy' --- a/sql/sql_yacc.yy 2011-03-09 13:47:59 +0000 +++ b/sql/sql_yacc.yy 2011-03-22 13:21:54 +0000 @@ -1338,7 +1338,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** ev_alter_on_schedule_completion opt_ev_rename_to opt_ev_sql_stmt %type <ulong_num> - ulong_num real_ulong_num merge_insert_types + ulong_num real_ulong_num merge_insert_types remember_n_sum_func %type <ulonglong_number> ulonglong_num real_ulonglong_num size_number @@ -7019,31 +7019,43 @@ select_item_list: ; select_item: - remember_name select_item2 remember_end select_alias + remember_n_sum_func remember_name select_item2 remember_end select_alias { THD *thd= YYTHD; - DBUG_ASSERT($1 < $3); + DBUG_ASSERT($2 < $4); - if (add_item_to_list(thd, $2)) + if (thd->lex->current_select->n_sum_items > $1) + ++thd->lex->current_select->select_n_sum_item; + else + ++thd->lex->current_select->select_n_nonsum_item; Not sure if the counting of select_n_nonsum_item is correct. For example, we may have an item: SELECT MAX(field)+const_field from t1 ... In this case 'MAX(field)+const_field' will be counted as select_n_sum_item even if it should be counted also as select_n_nonsum_item. Not sure we can do this test properly at the parsing stage... Regards, Monty

1 0

[Maria-developers] WL#189 New (by Psergey): Pending bulk renames in server codebase
by worklog-noreply＠askmonty.org 22 Mar '11

22 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Pending bulk renames in server codebase CREATION DATE..: Tue, 22 Mar 2011, 16:31 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 189 (http://askmonty.org/worklog/?tid=189) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: This task is to file things that we intend to rename in the server codebase but can't do it just yet because of the merge problems it will create. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#188 New (by Knielsen): Using --log-slave-updates to ensure crash safe/transactional slave state
by worklog-noreply＠askmonty.org 21 Mar '11

21 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Using --log-slave-updates to ensure crash safe/transactional slave state CREATION DATE..: Mon, 21 Mar 2011, 12:56 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 188 (http://askmonty.org/worklog/?tid=188) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Overview -------- A replication slave needs to preserve certain state between slave server restarts to be able to correctly resume replication from where it left off. In current MySQL replication, this state is kept in multiple simple files (master.info, relay-log.info). This is a big problem if the slave server crashes, as there is no guarantee that these files will be in a consistent state with what is in the table data (and binlog if using --log-slow-updates), or even with each other. The Google patch rpl_transaction_enabled has a partial solution for this, by duplicating the state inside InnoDB in a transactional way. At slave server startup, InnoDB can then decide to overwrite the files keeping the slave state with its own, hopefully more correct, information. There are some remaining problems with this approach, some of which may be fixable, some not. However, the basic problem here is the need to maintain state in a transactional/crash-safe way across multiple subsystems of the server (eg. replication and storage engine(s)). And we already have such a mechanism, in the form of the two-phase commit between engines and binlog. This worklog describes how we could use this existing mechanism to keep the replication slave state across server restarts in a crash-safe way, rather than introduce new complex mechanisms for every new piece of state to be kept. Idea ---- The main state we need to make crash-safe is the binlog position (filename,offset) of the next event from the master to execute on the slave. There is more state stored current, but there is less need for that to be transactional: - relay-log.info also stores the position in the relay log files on the slave which event execution has reached. In the case of normal shutdown this can be used file as-is. In case of recovery after a crash, it is possible that the relay logs are not consistent with the master and/or slave SQL thread, so it is probably better to just discard any existing relay log and re-fetch all necessary events from the master. - master.info mainly stores the connection information from CHANGE MASTER TO, which does not change often. The basic idea is that on slave server start, we get the required information from the binlog on the slave, rather than from these files (this requires that --log-slave-updates is enabled). This also has the advantage that we avoid the need to constantly update the state files after every transaction, saving some execution cost. When we shut down the server normally, we close the binlog in a way that allows at startup to detect if we are recovering from a crash or not. As part of this close, we can write whatever state we need to recover at the end of the binlog. Recovering the state then depends on whether we crashed or not. If we did not crash, then all we need is to able to find the position of whatever event was written at the end of the binlog with the state. One way is to just have a fixed offset from the end where this event starts; however this is not too robust against finding wrong data there, especially with respect to binlogs from different versions of the server possibly with different events (or event sizes) at the end of the log. Another way is that when we close the binlog, we seek back to the start of the log and write the position of the last event there (either in the format_description event, which already has version information, or a new event written just after format_description event). We already do this seek anyway to overwrite the flag in format_description event which signals that the binlog has been closed properly (not crashed). We just need to be sure to fsync() the binlog _before_ overwriting the crashed-or-not flag, so we are sure all data will be there in the not-crashed case. If we did crash, then we can not rely on any information at the end of the binlog. In this case, we can instead build on top of the already existing crash recovery mechanism. This mechanism scans the last binlog to build a list of all committed transactions, then uses this list to tell storage engines which previously prepared transactions to commit and which to roll back. As part of this scan, we can determine the last event executed, and from this we get the necessary state to continue replication in terms of the binlog position (filename,offset) on the master. Note that if something like group ID (MWL#175) or global transaction ID is implemented, then the state to preserve is the last executed ID rather than binlog position; however, the basic mechanism remains the same. Discussion ---------- The main disadvantage I see with this approach is that in order for this to be really crash safe, we need to run with innodb_flush_log_at_trx_commit=1 and sync_binlog=1. This requires 3 fsync() calls per commit, and group commit is not possible since the slave is single-threaded. This is likely to be too expensive for many installations to be able to use it. However, it may be possible to reduce this overhead sufficiently by using MWL#185 (grouping multiple commits together on the slave to reduce fsync() overhead), or by implementing parallel replication to utilise group commit (MWL#169, MWL#184, MWL#186). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#187 New (by Knielsen): Consistent (but non-durable) crash recovery from binlog 2-phase commit
by worklog-noreply＠askmonty.org 21 Mar '11

21 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Consistent (but non-durable) crash recovery from binlog 2-phase commit CREATION DATE..: Mon, 21 Mar 2011, 11:54 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 187 (http://askmonty.org/worklog/?tid=187) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Overview -------- Current MySQL needs no less than three fsync() calls for every commit in order to ensure the ability to recover into a consistent state (between master binlog and storage engine(s)). Without this, after a master crash, the binlog may be inconsistent with the data in tables on the master, and replication slaves will diverge from the master, possibly causing replication to eventually break. The MWL#116 implements group commit, so that each of the three fsync() calls can be shared among multiple transaction commits. The MWL#164 describes a way to reduce this to a single fsync() call shared among multiple commits, by recovering any transactions lost in storage engines from the binlog, thereby avoiding the need for fsync() calls in the engines. Both MWL#116 and MWL#164 improve performance without reducing functionality or ability to do crash recovery. This worklog describes a method to reduce the overhead to *zero* fsync() calls per commit. Though one will still want to have say one fsync() per second or whatever of the binlog. After a master crash, crash recovery is still guaranteed to recover the system into a consistent state, where every transaction committed in the storage engine is also committed in the binlog, and vice versa. However, the cost of this is that durability is no longer guaranteed: after a crash, a number of transactions committed before the crash may be lost. Thus this worklog is similar to the InnoDB feature innodb_flush_log_at_trx_commit=2 (or 0). That feature makes InnoDB not call fsync() after every commit, possibly loosing some transactions in case of crash, but still guaranteeing that the table data can be recovered into a consistent state. This worklog would do the same, but for the binlog and InnoDB together, whereas innodb_flush_log_at_trx_commit=2/0 only guarantees consistency for InnoDB in isolation. Idea ---- This worklog builds on top of the group commit framework implemented in MWL#116. The basic idea is similar to MWL#163, "release of row locks in InnoDB during prepare() phase". As soon as we have successfully run the prepare step in InnoDB, we make the transaction committed in memory so that it is visible to other transactions from that point on and all row locks are released. However, at this point we go further than MWL#163, and return successful commit to the client connection. The rest of the group commit work is delegated to a separate backgroud thread: writing and fsync()'ing to the binlog, running commit_ordered() and commit() inside the storage engine. The client is thus free to continue with new transactions without waiting for any fsync() delay. The background thread will do an fsync() call, but since no client is waiting for it, the background thread is free to do a long wait (eg. 1 second) between fsync() calls, potentially collecting lots of transtions for a single huge group commit. Note that any replication slave _will_ be waiting for the background thread; we must wait for fsync() of the binary log before sending events to the slaves, or we could in case of crash end up with more transactions on the slaves than on the master, which would break replication. So there will be a cost of extra replication latency. Discussion ---------- A main complication with this worklog is that the main part of the commit will run in a background thread, which is different from the thread that ran the main transaction. This requires that storage engines are checked, and modified as needed, so that they do not depend on thread local storage in that part of the code, and also do not do things like release a mutex that was aquired from another thread, etc. We also need to handle the issue that we need the THD object of the original transaction/thread to be able to finish the commit in the background thread. I think we will need to remove the THD object from the original thread before returning to the client and keep a reference to it in the queue of transactions waiting to group commit in the background thread. The background thread must then use this THD for the last part of commit and afterwards return the THD object to a pool of spare THDs; when/if the client starts a new transaction, it will then need to obtain a new THD from this pool of spare THDs. ---- Currently, the MWL#116 API has prepare_ordered() being called _after_ prepare(). I am not sure if there is any particularly good reason for this. The only reason I know is that this is how the Facebook innodb_release_locks_early patch did things, however when I asked I did not get any answer from Facebook why they did it in this order. If we switched this, we should be able to return to the client even earlier, already before prepare(), so we could keep the fsync() in prepare(). Alternatively, we should use MWL#164 to not have to call fsync() in prepare(); then we can return to the client only after prepare(), and still avoid any fsync() latency visible to the client. Note that we have to make the transaction committed to memory and visible to other transactions before returning to the client, just as with innodb_release_locks_early in MWL#163. The client would be quite confused if COMMIT returns ok, yet the committed transaction is not visible to following SELECT statements! ---- The first transaction to enter the group commit queue becomes the group commit leader, and would just signal the background thread to continue the commit sequence and then return to the client. Subsequent transactions that enqueue after would not signal the background thread; instead they would set a flag in their entry in the queue, so that the background thread knows that they are asynchroneous and should not be woken up after commit is done (the original thread may no longer exist at that point, or may be doing something else). ---- The main benefit of this worklog would be that we could get crash safe replication state on the master without any fsync() overhead visible to the client. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0