Post on replication poll was lost

My last post on the replication poll was apparently lost from Planet MySQL. If you're interested, I commented on the replication poll, our future plans, and how they were affected by the poll.

The replication poll and our plans for the future

We've been running replication poll and we've got some answers, so I thought I would comment a little on the results of the poll and what our future plans with respect to replication is as a result of the feedback. As I commented in the previous post, there are some items that require a significant development effort, but the feedback we got helps us to prioritize.

The top five items from the poll above stands out, so I thought that I would comment on each of them in turn. The results of the poll were (when this post were written):

Online check that Master and Slave tables are consistent	45.4%
Multi-source replication: replicating from several masters to one slave	36.3%
Multi-threaded application of data on slave to improve performance	29.2%
Conflict resolution: earlier replicated rows are not applied	21.0%
Semi-synchronous replication: transaction copied to slave before commit	20.3%

Online check that Master and Slave tables are consistent

The most natural way to check that tables are consistent is to compute a hash of the contents of the table and then compare that with a hash of the same table on the slave. There are storage engines that have support for incrementally computing a hash, and for the other cases, the Table checksum that was released by Baron "Xaprb" can be used. The problem is to do the comparison while the replication is running, since any change to the table between computing the hash on the master and the slave will indicate that the tables are different when they in reality are not. To solve this, we are planning to introduce support to transfer the table hash and perform the check while replication is running. By adding the hash to the binary log, we have a computed hash for a table at a certain point in time, and the slave can then compare the contents of the tables as it see the event, being sure that this is the same (relative) point in time as it were on the master. We will probably add a default hash function for those engines that do not have something, and allow storage engines to return a hash of the data in the table (probably computed incrementally for efficiency).

Multi-source replication: replicating from several masters to one slave

This is something that actually was started a while ago, but for several reasons is not finished yet. A large portion of the work is actually done, but since the code is a tad old (enough to be non-trivial to incorporate into the current clone), there is some work remaining to actually close this one. Since there seems to be a considerable interest in this, both at the poll and at the MySQL Conference, we are considering finishing off this feature sometime in the aftermath of 5.1 GA. No promises here, though. There's a lot of things that we need to consider to build a high-quality replication solution, and we're a tad strained when it comes to manpower in the team.

Multi-threaded application of data on slave to improve performance

This is something that we really want to do, but which we really do not have the manpower for currently. It is a significant amount of work, it would be a huge improvement of the replication, but it would utterly make us unable to do anything else for a significant period. Sorry folks, but however much I would like to see this happen, it would be irresponsible to promise that we will implement this in the near future. There are also some changes going on internally with the threading model, so it might be easier to implement in the near future.

Conflict resolution: earlier replicated rows are not applied

When multi-source comes into the picture, it is inevitable that some form of conflict resolution will be needed. We are currently working on providing a simple version of timestamp-based conflict resolution in the form of "latest change wins". This is ongoing work, so you will see it in a post-5.1 release in the near future.

Semi-synchronous replication: transaction copied to slave before commit

There is already a MySQL 4 patch for this written by folks at Google Code under the Mysql4Patches work. The idea is to not commit the ongoing transaction until the entire transaction has been successfully transferred to at least one slave. The reason for this is that it should be possible to switch to a slave in the event of a failure of the master, so it has to be certain that the transaction exists somewhere else (at least in disk). We consider this as very important for our ongoing work of being the best on-line database server for modern applications, so you will probably see it pretty soon. Compared to the patch above, we would like to generalize it slightly to allow it to be configurable how many slave should have received it before the transaction is committed. This will of course reduce performance of the master, but it will provide better redundancy in the case of a serious failure and it is a minor addition to the work anyway.

Binary log event checksum

In addition to the things we mentioned above, this is very important to both find and repair problems with replication. The relay log is a potential source of problem, as is the code that writes the events to the relay log, so it is prudent to add a simple CRC checksum to each event to check the integrity of the event. Sadly enough, this does not exist currently, so we're trying to make this get into the code base as soon as possible, maybe even for 5.1 (keep your fingers crossed). This is not a promise: we're doing what we can, but there are no guarantees.

The coolest future replication features...

...is something that you influence what it will be.

The problem with replication is that we have so many things that we want to do, but we are not that many people. What we do is what everybody does when the to-do list is to long: prioritize. Since the replication features are developed for you (yes, you), we have added a quickpoll on the http://dev.mysql.com/ where you can pick the three most important replication features that you would like to see us focus on next (after the 5.1 GA).

Do you think that on-line checks for table consistency is for weenies that cannot write a simple little script to do that? Please tell us that.

Do you prefer to live on the edge and think that semi-synchronous replication is for safety junkies? Well, we'll be glad to hear your opinion.

Do you think that the YouTube oracle algorithm hack is the coolest thing on earth and that we should make sure to have it in a release soon? In this case you should especially tell me, because I think it is a pretty cool idea as well.

We cannot promise that they will be done, and there are some features on the list that requires a substantial amount of work, so it might be that we decide to deliver many small features rather than one big feature... but we need your input, so please go and take the quickpoll on http://dev.mysql.com/, because you can be part of making MySQL the best on-line database for modern applications in the world.

Heading off to the MySQL Conference

After some long months of intensive bug fixing, it's time to pack up the stuff and head off to the MySQL Conference & Expo. Since this is actually my first MySQL (User's) Conference, it's bound to be interesting.

We've got a full schedule here, so it will definitely not be boring.

Monday, April 23, 8:30am - 5:00pm, Ballroom D (with lunch 12:00am to 1:30pm). Lars, JDD and I am going to hold a replication tutorial for a full day where we'll go over some basics on how to set up replication to work properly, but also delve into some advanced stuff like row-based replication and cluster replication to see what it can do for you. This is a must for anybody that is seriously going to work with replication.

Tuesday, April 24, 10:45am - 11:45am, Ballroom F. Lars and I will keep a "Tips and Tricks" session on replication and bring a bunch of useful tricks for getting replication to work the way you want.

Tuesday, April 24, 7:30pm - 9:00pm, Bayshore. Lars, Jeremy Cole and I will have a BoF session where we'll have an open discussion about the future of replication. We will also bring some food for though and a digression into upcoming features like conflict handling.

Tuesday, April 24, 5:30pm - 6:15pm, Ballroom E. There will be a session on the replication roadmap where you will find Lars and me presenting some of the ideas that MySQL envision for the future. If you want to take part in making MySQL the best on-line database in the world, make sure to be there!

You will also find Chuck and me in the Guru bar from 2pm to 4:30pm the same day, so bring your problems and we'll give them a good beating!

On Friday, you will also find me at the Storage Engine Summit hosted by Brian.

The invisible I/O thread failures are no more

To get the status of the replication slave, it is possible to check the Last_Error and Last_Errno fields from SHOW SLAVE STATUS. Unfortunately, they only give information about the status of the SQL thread (and not always that either). If the I/O thread fails, for example, because the server configuration is not correctly set up, or if the connection to the master is lost due to a network outage, it is necessary to dig through the error log to find out the reason. This might be possible, although annoying, for a DBA to do since he has access to the files on the machine where the server is running, but when using automatic recovery applications that watch the status of the replication, this is not practical. It is also easier to see the status of the server through a normal client connection, compared to logging into the machine and starting to locate the files.

This is actually quite stupid, especially since it is possible to individually check if the threads are running, so to make it possible to check the status of the threads from a client (an application or a user connecting directly to the server), I just added four new fields to the output from SHOW SLAVE STATUS: Last_SQL_Error, Last_SQL_Errno, Last_IO_Error, and Last_IO_Errno. The new fields were added last, and the two old fields Last_Error and Last_Errno are just aliases for Last_SQL_Error and Last_SQL_Errno respectively. Adding the new fields last and keeping the two old fields intact allow old applications to work as normal since they either use positional arguments or find the column by name. New applications, however, can take advantage of these new fields.

Documentation for the unit tests API used by MySQL

For some time, MySQL has been using a unit test framework based on the Test Anything Protocol (TAP) used by Perl and PHP.

The framework consists of a C library that can be used to generate TAP output suitable for processing with, for example, the Test::Harness Perl module. In order to allow Test::Harness to execute the compiled programs, a simple wrapper called unit.pl exists in the unittest/ directory in the MySQL server tree.

The documentation for the MyTAP API is available at http://www.kindahl.net/mytap/doc/, until I can find another home for it. All comments are welcome.

Replication and the disappearing statements

After reading Todd Huss blog about the gotcha when using statement-based replication, where statements can "disappear" (that is, not be applied to the slave database), I believe that I can shed some light on the reason for this behavior.

Before that, some background.

Traditionally, MySQL has been using what is called statement-based replication. Statement-based replication replicates the changes to the slave by sending the actual statement that was executed on the master over to the slave, and the slave subsequently executes that statement. Of course, only statements that change something will be sent to the slave.

Sometimes, you don't want to send all changes to the slaves. So therefore it is possible to prevent the master from sending changes to some databases using the --binlog-do-db and --binlog-ignore-db switches, which will allow you to filter out statements that updates certain databases (this is not the whole story, more about that filtering later).

This works well for most queries, such as:

INSERT INTO products   SET name='Gizmo2000', price='$2000'

But suppose that we have two databases db1 and db2 and we decide to not replicate changes to db1 but will replicate changes to db2. Now, consider the following statement:

UPDATE db1.foo, db2.foo
SET db1.foo.a = db2.foo.a,
    db2.foo.b = db2.foo.b;

The statement updates both db1 and db2, so shall we replicate it or shall we not? Since we need to handle even this situation in a consistent manner, the current database is used to decide if the statement shall be replicated or not. (I didn't actually write the code, since it pre-dates me beginning at MySQL, but after being immersed in the code for almost two years, I'm pretty sure this is the reason.) This works well for most users, since one usually work with one database only, and set the current database to that before actually starting doing changes. However, for some special cases, like the one mentioned by Todd, it starts to look strange.

Recently (that is, in 5.1), MySQL released something called row-based replication, where the master sends the actual rows that were inserted/deleted/updated to the slave, and the slave then subsequently insert/delete/update those rows from the database. For each row, the database and table that the row belongs to is known, so if you are using row-based replication (option --binlog-format=row to the server, or use the SET GLOBAL BINLOG_FORMAT=ROW), the filtering will be done on the actual table being changed even if the statement updates several different tables in different databases.

I'll summarize with some general advice when using statement-based replication:

• Don't qualify your table names with a database name. If you do, you might have trouble with the replication, so this is something to look for.
• If you are going to make changes to tables in a database, always USE the database to set the current database correctly.
• Don't use multi-table updates (or other statements that manipulate several tables) unless the tables are all in the same database.
• Even if you are using multi-table updates on tables in the same database you might have problems. So watch out for any statement that manipulates several tables and make sure that they work by testing them.