- developers - lists.mariadb.org

[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

[Maria-developers] WL#186 New (by Knielsen): Parallel replication using interleaved binlog events to detect potential parallelism
by worklog-noreply＠askmonty.org 21 Mar '11

21 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Parallel replication using interleaved binlog events to detect potential parallelism CREATION DATE..: Mon, 21 Mar 2011, 10:45 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 186 (http://askmonty.org/worklog/?tid=186) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Similar to MWL#169 and MWL#184, this worklog describes a way to discover binlog events that can be executed in parallel on the slave, improving scalability with increasing CPU and I/O resources. This worklog only deals with parallel replication of row-based events. Statement-based events should still be executed in serial order. We also require that commits on the slave still happen in the same order as on the master (this guarantees that applications can never see a state on a slave that did not exist on the master, among other nice properties). Basic idea ---------- The idea is to work with row-based events. Suppose that on the master, we have two parallel transactions running, T_1 and T_2. Suppose that we have the following sequence of events: 1. T_2 modifies a row R_1, resulting in row event E_1 2. T_1 commits 3. T_2 commits. In this case, it is safe on a slave to apply the event E_1 of T_2 in parallel with any events of transaction T_1. Since we know T_1 committed before T_2, we know that T_1 did not have to wait for T_2 to release any row lock of R_1 in commit, so T_1 could not have modified R_1 after T_2 did. Similarly, since we know that T_2 executed E_1 before T_1 committed, we know E_1 did not have to wait for T_1 to release any row locks on R_1, so T_1 could not have modified R_1 before T_2 did. Thus, we know that T_1 and T_2 are not in conflict on this particular row R_1 and row event E_1. Thus, on a slave, with this knowledge we can start transaction T_2 and execute E_1 (in a separate thread from T_1 events) while T_1 is still running. Refinement: optimistic apply ---------------------------- Suppose now that instead we have the following sequence of events: 1. T_3 commits 2. T_4 modifies a row R_2, resulting in row event E_2 3. T_4 commits In this case, we can not be sure that event E_2 is independent of transaction T_3. It could well be that T_3 also modified R_2, and E_2 was waiting for T_3 to commit and release row locks. However, on a slave, we can nevertheless try to apply E_2 (in a separate thread) while T_3 is still running. However, then we have to deal with the situation that T_3 gets stuck waiting for T_4 to commit and release the lock on R_2. In this situation, we have a deadlock; T3 is waiting for T_4, but T_4 also needs to wait for T_3 (as we want to commit in the right order, and even more so want the end result to have the changes of T_4 in row R_2, not those of T_3). So we need to abort and restart one of the transactions. As long as we choose always to abort the transaction with the later commit time (T_4 in this case), things will work out: We will abort T_4, commit T_3, then re-start and commit T_4. Using optimistic apply has the potential to increase parallelism on the slave. However, this must be carefully weighted against the extra cost of having to roll back and retry some transactions. So it needs suitable status variables to be able to monitor number of transactions and number of events that were rolled back and re-started (as well as number of transactions and events successfully applied). And user options to control if and to what extend to try optimistic apply. But note that since we always have at least one transaction running that will not be aborted due to optimistic apply (the one with earliest commit), and since all other transactions run in parallel, the cost of restart may not be too bad, as long as we do not start to starve the CPU(s). Any extra I/O reads done in the aborted transaction to bring in pages to the buffer pool will in any case benefit us when we immediately re-start the transaction. Requirements ------------ Implementing this worklog will require several non-trivial extensions of the server and storage engine code (these should eventually be filed as separate worklog items): 1. Ability for server layer to detect and control what to do in case of conflicts between two transactions. Currently, the storage engine (eg. InnoDB/XtraDB) will just let the latter access block waiting for the first transaction to commit, and in the case of a deadlock the storage engine will choose an arbitrary transaction to roll back. For this worklog, the server layer (replication slave SQL thread(s)) must be able to (a) cause abort of the former transaction rather than wait of the second, and (b) choose which of several possible transactions to abort in case of deadlock. Note that such a mechanism is also needed for Galera replication, and the Galera wsrep patch implements something like this for InnoDB already. 2. Interleaving of events from different transactions in the binlog. Currently, the master buffers events during a transaction and writes them all out sequentially during COMMIT; this destroys any knowledge about which events and transactions ran in parallel on the master. We need to change this so that events are tagged with a (local) transaction ID and written out to the binlog as they occur, interleaved with events from other transactions. We will probably also want to add "begin transaction" events and "prepare" event, to increase the knowledge available on the slave about in which order events occured. We will still want to do _some_ buffering of events to avoid heavy contention on writing to the binlog. We can buffer a small number of normal events; The cost of such buffering is we may write a row event E_r _after_ a commit event E_c where it could have been written before, which loses some opportunity for (non-optimistic) parallel apply on the slave. We may however _not_ buffer commit events; such buffering could wrongly put E_r ahead of E_c where it must be after, causing an unexpected conflict in parallel apply on the slave. 3. A mechanism on the slave for distributing binlog events among multiple threads and coordinating execution and commit among the threads. Such mechanism should ideally be the same as used in MWL#184 and MWL#169. Discussion ---------- One nice property of this worklog is that the parallelism exposed to the slave is derived from whatever parallelism was there already on the master. We know E_1 in T_2 ran in parallel with T_1 on the master, and use this to similarly run E_1 in parallel with T_1 on the slave. This increases the likelyhood that if parallelism is available on the master, it will also be available on the slave (note that we will need parallelism on both master and slave to be able to scale an entire replication topology). ---- For short OLTP-like transactions, it seems likely that this method will be particularly suited to be combined with the method from MWL#184, which allows to run in parallel on the slave all transactions that participate in a single group commit on the master. Eg. for single-row transactions T_1 (with row event E_1) and T_2 (with row event E_2) that run in parallel on the master, we can have the following two situations (among others): 1. E_1 and E_2 execute start 1. E_1 and E_2 execute start 2. E_1 execute finish 2. E_1 execute finish 3. E_2 execute finish 3. T_1 commit 4. T_1 commit 4. E_2 execute finish 5. T_2 commit 5. T_2 commit In the scenario to the left, on a busy master the two commits are likely to be done as a single group commit, and MWL#184 will allow parallelism on the slave. In the scenario on the right, optimistic apply as per this worklog will allow to run T_1 and T_2 in parallel on the slave. ---- Note that by interleaving events from different transactions in the binlog, we will be writing events before they are committed, and so will need to issue ROLLBACK statements in case of rollback. Similarly, on the slave, we can start applying the events from a transaction even before we have received the commit event from the master (in fact even before such commit event has even occured on the master). This can potentially reduce master->slave delay. However, there is a need to be careful, as on the slave we need to keep the correct commit order. And such order is not known until the commit events are received. For example, if we had a limited number N of slave execution threads and they all have started running T_1, ..., T_N, and we then receive a commit event for T_(N+1), we would be deadlocked. It might be a good idea to only start executing new transactions when we have at least one pending commit in the relay log, or at least keep one spare thread available for execution of one more transaction once we receive such commit event. Similarly, if we get a conflict in opportunistic apply of transactions T_1 and T_2, neither of which we have received the commit event from, then we will need to wait for either commit event to arrive before we can decide which of T_1 and T_2 to abort. Note that in any case what we need to do is no worse than what we do in currentl MySQL replication. There, we never do any execution until we have not only seen but also executed any prior commit. Similarly, whatever buffering of events we need to do on the parallel slave will be no worse than what we currently have to do on the master (where we buffer _all_ events until commit). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#185 New (by Knielsen): Group commits on slave to reduce fsync() load
by worklog-noreply＠askmonty.org 18 Mar '11

18 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Group commits on slave to reduce fsync() load CREATION DATE..: Fri, 18 Mar 2011, 14:25 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 185 (http://askmonty.org/worklog/?tid=185) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: MWL#116 introduces group commit for the binary log; this can reduce the number of fsync() calls needed when running with sync_binlog=1. But this does not currently help on the slave. The reason is that the slave SQL thread is single-threaded, so there will never be any opportunity for group commit. However, an alternative way to reduce fsync() on the slave when sync_binlog=1 is to combine multiple transactions from the binlog into one transaction on the slave. If we have N transactions T_1, ..., T_N in the binlog, we can combine them simply into one transaction on the slave like this: - Change COMMIT in T_1, ..., T_(N-1) into SAVEPOINT T_i - Change any ROLLBACK into ROLLBACK to T_i This will reduce the number of fsync() calls, since it results in fewer COMMITs. The downside is that the latency between a transaction being visible on the master and on the slave will increase. There should probably be some option to limit how much transactions may be delayed on the slave. It would also be useful to have some kind of timestamps in the binlog to be able to estimate the duration of a transaction on the slave before starting to apply it; this would allow to avoid combining a later long transaction with a previous short transaction, which could cause undue delay for the short transaction to become visible on the slave. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] mysql_upgrade issue for libmysqld and amarok
by Kristian Nielsen 18 Mar '11

18 Mar '11

Ian Monroe <ian(a)monroe.nu> writes: > On Fri, Mar 18, 2011 at 05:44, Kristian Nielsen > <knielsen(a)knielsen-hq.org> wrote: >> The issue is how to upgrade libmysqd from MySQL version 5.1 to version 5.5 (or >> more generally any major version upgrade), in particular as relates to >> Amarok's use of libmysqld. >> One idea is to also build mysql_upgrade_embedded; this would be a variant of >> mysql_upgrade linked with libmysqld, so it does not depend on the mysqld >> server. Similarly, we would need mysqlcheck_embedded (mysqlcheck is called >> from mysql_upgrade). These two binaries could be included in the libmysqld >> package (they are small). > > wouldn't they be huge? libmysqld is a static library, so anything > linking to them is going to be at least 50mb. In MariaDB, we have both static and dynamic libmysqld. So we should link with the dynamic libmysqld.so, and binaries should be small. >> The upgrade procedure for amarok would then be to stop amarok linked with 5.1, >> then run mysql_upgrade_embedded (from 5.5) against the amarok libmysqld data >> directory, then start amarok linked with 5.5. > > There can't be any procedure that the user has to do. Agree. > But if there is some way for us to determine if mysql_upgrade_embedded > needs to be run, I don't see why this would a problem. Amarok could > run it before initializing the database. I would suggest to write a small file in the user's data directory with the version last upgraded to. If this is < 5.5 at startup (eg. "5.1"), then Amarok can run the upgrade and afterwards update the file to say "5.5". This will also allow to detect and warn about downgrading 5.5->5.1. Debian does something similar for the main MySQL server packages. >> The other idea that came up was to add this as extra calls in the library. So >> that the amarok application could just call some mysql_upgrade() function >> instead of having to run an external upgrade binary. Would this be more useful >> for amarok? > > This sounds much more straightforward. Ok, I will see if we can do this. Probably we could do both (then mysql_upgrade_embedded will just be a stub calling into mysql_upgrade() in the library, might be useful in other applications). >> We could offer to implement this (either solution I think) in MariaDB (a >> version of MariaDB that includes MySQL 5.5 is in the works). This would allow >> to use the above to upgrade Amarok to using MariaDB 5.5. Would this work? > > Sounds good. > We really could care less about maria vs. mysql, we just want > something that works. Ok, great :-) > Since I talked to you last time, I've had reports of people going from > mysql 5.1 to 5.5 without needing mysql_upgrade. mysql_upgrade seemed > to fix things for me, but maybe it was just a coincidence. Yes. The main issue I can think of now is that when character sets/collations change, the indexes of affected tables may need to be rebuilt. Otherwise they may contain incorrect ordering or indexing of affected characters. Since such changes are rare, and tables with affected characters probably even rarer, a missing upgrade is likely to go ok or at least unnoticed for some time. > Either way though, relying on newer versions of mysql simply not > needing to do any upgrade procedures seems like poor planning. Agree, even if it works for 5.5 it will be sure to break sooner or later. Monty said he would look into implementing this. He is quite busy, so it may be a few weeks, but I will try to follow up with him to see if we can move forward with it reasonably quickly. - Kristian.

1 0

[Maria-developers] WL#184 New (by Knielsen): Parallel replication of group-committed transactions
by worklog-noreply＠askmonty.org 18 Mar '11

18 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Parallel replication of group-committed transactions CREATION DATE..: Fri, 18 Mar 2011, 14:06 SUPERVISOR.....: Sergei IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 184 (http://askmonty.org/worklog/?tid=184) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Overview -------- Parallel replication is the idea of executing multiple transactions (or more precisely event groups from the binlog) in parallel in multiple threads on the slave, in order to make the slave applier more scalable to multiple CPU cores and disk spindles. The main problem with parallel replication is identifying transactions that are *independent*, meaning that they can be safely executed in parallel on the slave without dead-locking each other or producing different results than on the master. This worklog describes one way of identifying such independence. MWL#169 describes another. Apart from discovering independent transactions, it is also necessary to implement a mechanism for actually distributing events to different execution threads, coordinating commit order, handling errors and crash recovery, etc. It is expected that different ways of determining independent transactions, such as this worklog and MWL#169, will share the same mechanism for actual parallel execution. It should even be possible to combine different ways to discover independence at the same time. Idea ---- MWL#116 introduces group commit, where multiple transactions are committed together and also written to the binlog together. This works by collecting parallel transactions that are waiting inside COMMIT (or autocommit) for access to the binary log. The idea in this worklog is that all transactions participating in a single group commit must necessarily be independent, and can be safely applied in parallel on a slave. Suppose we are about to write N transactions T_1, ..., T_N into the binlog in group commit. Then all statements for each transaction have completed, but none of them are committed yet. This means that (1) there can be no conflicting row locks between any two T_i and T_j, and (2) no transaction T_i was able to see any changes from T_j (for i<>j and assuming isolation level greater than read uncommitted). Another way to see this is to note that on the master, the order of T_1, ..., T_N is essentially random and depends only on how the kernel decided to schedule the threads during the COMMIT. Any ordering would have been possible just by having different thread scheduling delays when the transactions are put in the ordered list used for group commit; there is no inter-thread dependencies at that point in the code. So any order of the transactions in the binlog would have been possible, and thus any order of slave execution. Therefore, as long as COMMIT cannot change what modifications the transaction already did, any execution order of the transactions on the slave is permissible. Implementation -------------- I suggest that when this feature is enabled, we introduce two new binlog events, group_commit_begin and group_commit_end. We write a group_commit_begin event just before all the events for the transactions in a group commit, and a group_commit_end event just after. We can use the "ignorable events" from MySQL 5.5 for these events; this will allow a slave that does not implement these events to safely ignore them. If needed, we can further increase compatibility by - Having a server option to enable/disable the writing of these two events. - Add a flag that the slave sets to request the sending of these events, and only send them if requested (similar to MWL#47). It still needs to be decided how much of this to do. The slave can then collect all the event groups between the group_commit_begin and group_commit_end pair, and schedule them in parallel. Limitations ----------- The main limitation of this approach is that on the slave, it can only run transactions in parallel if they happened to commit at roughly the same time on the master. This means that there will be little opportunity for parallelism on the slave if 1. The master has low write load 2. The master is mainly running few but long transactions 3. The master has fast fsync() performance for the commit In the case (1), there is in any case less need for scaling the event execution. In the case (2), another method (for example MWL#169) will be needed to achieve efficient parallel replication. For (3), we can improve parallelism by deliberately delaying commits on the master. By inserting a short sleep just before group commit, we allow more independent transactions time to participate, giving more opportunity for parallelism on the slave. At the same time, we reduce fsync() load on the master, potentially freeing more I/O capacity for other work. For example, we might add an option to rate-limit COMMIT fsync() to 25 per seconds or whatever. Care will be needed when using this option, as if the master load has insufficient parallelism, such rate-limitation will reduce throughput on the master. The MWL#163 innodb_release_locks_early option causes transactions to effectively be committed early in COMMIT during the prepare phase. This allows another transaction to see and/or modify rows also modified by the first transaction, and still participate in the same group commit. This means that when innodb_release_locks_early is set, the transactions cannot be safely used for parallel replication (eg. in this case we must disable the writing of group_commit_begin / group_commit_end). Events that modify non-transactional tables or do DDL causes more complications, as they are written directly to the binlog and make changes visible immediately to other transactions (basically they are not transactional). To simplify things, we will not declare any such events independent with respect to any other transactions according to this worklog. There are many useful applications of parallel replications where such events are rare. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] mysql_upgrade issue for libmysqld and amarok
by Kristian Nielsen 18 Mar '11

18 Mar '11

This is a follow up on a discussion I had with eean on Freenode IRC #maria. The issue is how to upgrade libmysqd from MySQL version 5.1 to version 5.5 (or more generally any major version upgrade), in particular as relates to Amarok's use of libmysqld. The procedure for upgrading a normal MySQL server is to run the mysql_upgrade command. However, this requires a running mysqld server, so it is not appropriate for libmysqld, or at least very inconvenient. We discussed this briefly at our MariaDB meeting in Lisbon, and here is what we came up with: One idea is to also build mysql_upgrade_embedded; this would be a variant of mysql_upgrade linked with libmysqld, so it does not depend on the mysqld server. Similarly, we would need mysqlcheck_embedded (mysqlcheck is called from mysql_upgrade). These two binaries could be included in the libmysqld package (they are small). The upgrade procedure for amarok would then be to stop amarok linked with 5.1, then run mysql_upgrade_embedded (from 5.5) against the amarok libmysqld data directory, then start amarok linked with 5.5. Does that sound useful? The other idea that came up was to add this as extra calls in the library. So that the amarok application could just call some mysql_upgrade() function instead of having to run an external upgrade binary. Would this be more useful for amarok? Either way, we then need to think on how to implement this change. We could offer to implement this (either solution I think) in MariaDB (a version of MariaDB that includes MySQL 5.5 is in the works). This would allow to use the above to upgrade Amarok to using MariaDB 5.5. Would this work? (I think it would even work to keep Amarok using MySQL 5.5 libmysqld, but using MariaDB 5.5 mysql_upgrade_embedded, though that is kind of a strange solution. Or we could provide the patch for MariaDB and you could patch libmysqld similarly to provide the same functionality in relevant distros.) Does this sound like a feasible way forward? One question about how Amarok uses libmysqld, which will help understand what is needed to solve the upgrade issue: Does Amarok use libmysqld built with the --with-embedded-privilege-control option, or use stored procedures, or other things that require the `mysql' database schema to be available in MySQL? If this does not make sense, the answer is probably "no" :-) The background: When a normal standalone mysql database server is installed, it is necessary to create the `mysql' database schema using mysql_create_db. This database schema contains such things as user accounts and passwords, stored procedure code, etc. Normally, libmysqld does not do any access control on accounts, and it is possible to use it without running mysql_install_db. In fact it's a bit hard to run mysql_install_db, as it has the same issue as mysql_upgrade. So I assume that Amarok does not do this and just does not use account privileges or stored procedures/functions, but just wanted to check? This affects how much mysql_upgrade_embedded has to do, as part of the upgrade is to add any missing columns in tables in the `mysql' database schema. - Kristian.

1 0

Re: [Maria-developers] [All] How are we going to handle incompatible changes in MariaDB?
by Kristian Nielsen 18 Mar '11

18 Mar '11

"Philip Stoev" <pstoev(a)askmonty.org> writes: > We should discuss how do we handle new developments in MariaDB that cause > our behavior to diverge from the one in MySQL. Since we claim to be a Yes, agree. Thank you for bringing it up! Note that this is a general MariaDB discussion, not a Monty Program specific one. So I move the thread to maria-developers@. So far, we have generally been ok at handing incompatible changes. The basic approach is to shape new features to be backwards compatible as much as possible, and to add any extra needed code to handle interoperability. For example, if an option is changed due to a feature, we should keep the old option around and emulate the bahaviour, eventually deprecating it in a future version. Another example, with replication, we check the version of the master or slave at the other end, and take care to only send what the other end understands, and likewise interpret what it sends correctly based on its version. However, this has been mostly up to the individual developer (or often reviewer), and if we can get some more structure/process to this to avoid it being forgotten, then that would be very good. > The particular situation we are facing now is the microsecond precision > patch from Serg. As part of introducing precision date and time datatypes, > he made some refactoring around the Item_* tree. > > As a result, various corner cases, e.g. invalid dates, partially valid > dates, invalid conversions between date and time, etc. are now handled > differently, with either different warnings being printed (or no warnings at > all), or with the result of the expression being different, or with a NULL > or 0000-00-00 returned. > > For example, mysql reports that DATE ( SEC_TO_TIME( 8 ) ) is equal to > "2000-00-08" whereas serg's patch returns "0000-00-00" . In other words, > MySQL chose to place the "8" literal into the days portion of the date > value, whereas Serg's patch filtered the literal somewhere along the way. > There are numerous other such examples. > > While in most cases Serg's new behavior is more intuitive and a program that > is written to conform to postgresql-level type safety will not be affected > either way, it entirely possible that a program written under mysql's > relaxed type safety rules, and with MySQL's behavior in mind, would start > behaving unexpectedly. It sounds like in this case, the changes in behaviour could be considered improvements, or even bug fixes, when seen in isolation. And the behaviour they change is not something that an application would deliberately be written to rely on. But since it is a change in behaviour, it has the potential to break applications that by accident happen to depend on such behaviour. My opinion on this is that we should do the change in a major version (eg. 5.2->5.3), but not in a minor version (eg. 5.2.5->5.2.6). And document it in release notes as an incompatible change. Basically the same way we did in MySQL (and still do, I suppose). It is ok to change behaviour in a major version, if it is necessary, and documented. I believe users anticipate such changes, and consequently are more careful about going eg. 5.1->5.5 than 5.1.x->5.1.y. We should not needlessly change things. Every behaviour change should be carefully considered. If it has the potential to seriously break major applications, we should not change it (or use deprecation, compatibility mode, etc). If the change is not necessary or useful, it should probably be left. But if the change makes sense and the new behaviour is much more reasonable, then we should be ready to make the change. We cannot afford to be stuck forever on old poor decisions. Another example is optimiser changes. In my opinion, we cannot totally avoid that improvements to the optimiser will make some corner case queries slower in some cases. The optimiser has only limited knowledge of the data and application. If, based on this knowledge, execution plan A is better than execution plan B, then we should be prepared to change (in a major version) the code to select A over B. This will invaribly cause slowdown in some cases, where the knowledge is incomplete. But it is important to be able to make improvements. But again, we need to consider each case carefully. We should not do things that are obviously stupid (eg. do not select table scan over range scan unless _really_ sure it will be better), and we should avoid breaking major applications or use patterns, even if it theoretically makes sense. So summary of my opinion: We should be very careful about incompatible changes. We should generally avoid them unless there is a very good reason for them. We should create backward compatibility where possible and where it makes sense. And if we do change behaviour, we should only do it in a major version and document it clearly in an "incompatible changes" list. And being able to continually improve the server long-term is important. > So, how we should handle this particular case, and more importantly, how do > we handle future cases like that in a stable and predictable manner? I > understand that in the early days of MySQL it would have been a no-brainer > to change everything as needed, we are by now dealing with a huge installed > base and powerful competitors, so we can not simply apply the happy hacking > approach that was used in the 1990s. Agree. No "happy hacking" approach to incompatible changes! - Kristian.

1 0

[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] WL#182 New (by Mdcallag): Explain running statements
by worklog-noreply＠askmonty.org 13 Mar '11

13 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Explain running statements CREATION DATE..: Sun, 13 Mar 2011, 09:44 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 182 (http://askmonty.org/worklog/?tid=182) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: I want to know the query plan for running statements -- something like "SHOW PLAN FOR <connection_id>". This requires running statements to generate a string that describes their plan in case another thread needs to examine it. For me it would be good enough to know the join order and indexes. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0