Statement-based replication is disabled for Falcon

Contrary to what I said earlier, Falcon has decided to deliberately disable statement-based replication using the same capabilities mechanism that InnoDB uses.

The reason is that isolation between concurrent transactions cannot be guaranteed, meaning that two concurrent transactions are not guaranteed to be serializable (the result of a concurrent transaction that has committed can "leak" into an ongoing transaction). Since they are not serializable, it means they cannot be written to the binary log in an order that produce the same result on the slave as on the master.

However, when using row-based replication they are serializable, because whatever values are written to the tables are also written to the binary log, so if data "leaks" into an ongoing transaction, this is what is written to the binary log as well, so that when the transaction commits, the values written to the table are the same as those written to the binary log.

It is a rational decision, but I hope that Falcon will support statement-based replication as well in the future.

MySQL Conference Replication tutorial at Monday 9:00am - 12:30pm

Lars and I will have the replication tutorial Monday 9:00am to 12:30pm in Ballroom H.

In order to make it easy to play around with replication, I threw together a little package with scripts that are available from the tutorial page at the forge. With this script, you can run several servers from a replication tutorial directory without interfering with existing installations of the server that you might have installed on your machine. In order to use this package, you need to download a binary distribution of the server without any installers, the replication tutorial package, and unpack them in the same directory.

It is, however, not necessary to download the package to be able to attend the tutorial, but it gives you an option to experiment with replication.

Statement-based replication for Falcon

I just read the post from Mark regarding some questions he had about the Falcon engine. One of the points that made me jump is the following:

Row-level replication (instead of statement-based replication) is required when replicating Falcon objects.

This might be enough to keep me from upgrading, but I am not sure if this is limited to Falcon. Will future MySQL releases require the use of row based replication? Having SQL statements in the binlog is invaluable to me and I am not willing to give that up.

Now, seriously, it is not trivial to disable statement-based replication for any engine so it is basically always on; whether it works as expected is a different story. So, in short: there is nothing specifically done to disable statement-based replication for the Falcon engine. It is just not supported by them (yet).

You can actually use statement-based replication to replicate any tables using any engine, Cluster tables as well. It is not a good idea to replicate Cluster tables for several reasons, but it is possible.

We will continue to maintain statement-based replication for the foreseeable future, but it is just not possible to handle all the quirks that can occur in odd situations (for some examples, see BUG#31168, BUG#3989, and BUG#19630). If you know how to write queries that avoid problems like these, you will not have any problems, but if you are concerned about whether you're up to it, switch to use row-based replication.

SQLite table to read Atom feeds

Ah, Christmas Holidays! Time to take a break from the daily chores... to spend some time with family... but also time to catch up on reading and spend some hours on some fun hacking.

When catching up on my reading of Dr. Dobb's Journal, I came across an interesting article by Michael Owens about writing virtual tables for SQLite, which got me thinking about a small hack I've wanted to do for a while: a table that reads an RSS/Atom feed and presents the data to the query engine. Originally, I was planning to implement this as a MySQL Storage Engine, but since I was reading this article and the interface seems easy enough to work with, I decided to just whip together a simple prototype for SQLite instead. Since I currently don't have a good place to publish the repository, I have a distro available at http://www.kindahl.net/pub/sqlite-feedme-0.01.tar.gz.

After building and installing, the table can be created as simple as this:

mats@romeo:~/proj/feedme$ sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .load libfeedme.so
sqlite> create virtual table onlamp
   ...> using feedme('http://www.oreillynet.com/pub/feed/8');
sqlite> select title from onlamp;
PyMOTW: weakref
What the Perl 6 and Parrot Hackers Did on their Christmas Vacation
Least Appropriate Uses of Perl You've Seen
YAP6 Operator: Filetests?
WILFZ (What I Learned From Zope):  Buildout
TPT(Tiny Python Tip):  Watch Jeff Rush's Videos
PyCon 2008 Talks and Tutorials Finalized
TPT(Tiny Python Tip):  Python for Bash Scripters
What the X-Files Taught Us about Real Aliens
Python Web Framework Comparison:  Documentation and Marketing
Python Web Framework Comparison:  Documentation and Marketing
PyMOTW: mmap
Improving Test Performance
YAP6 Operator: Reduce Operators - Part II
WSGI:  Python Web Development's Howard Roark

Note that it is still a prototype. My plans are to at least:

• Read the entire feed into memory and parse it from there instead of writing the feed to disk before parsing it. Reading it to disk was the default for cURL, so I just stuck to that for the prototype (yeah, yeah. I know I'm lazy.)
• Allow the feed format to automatically be detected and set the parser accordingly. Right now, it can just handle Atom feeds, and does not do a great job at that either.
• Figure out a way to present multiple entries data in a useful way. For example, an entry can hold several links, but which one is really the interesting one?

Stopping the slave exactly at a specified binlog position

Catching up on some articles on the Planet MySQL feed, I just read the post by Dathan on how to promote a slave to be master by using MASTER_POS_WAIT(). The MASTER_POS_WAIT() is an excellent function that allows you to wait until the slave reaches a point at or after the given binlog position. Observe that after the statement issuing a MASTER_POS_WAIT() returns, the slave threads are still running, so this means that even if a STOP SLAVE is issued immediately after the statement with MASTER_POS_WAIT(), it is bound to move a little more before actually stopping. For Dathan's situation, this is not necessary, but wouldn't it be great if you could stop a slave at exactly the position that you want? Well, that is possible.

There is another command that does exactly what you want, and that is START SLAVE UNTIL. The problem with this command is that it can only be executed when the slave threads have stopped, but that is trivial to do by just issuing a STOP SLAVE. In other words, instead of doing:

SELECT MASTER_POS_WAIT('dbmaster1-bin.000002', 4);
SLAVE STOP;

like Dathan suggests, do:

STOP SLAVE;
START SLAVE UNTIL
    MASTER_LOG_FILE='dbmaster1-bin.000002', MASTER_LOG_POS=4;
SELECT MASTER_POS_WAIT('dbmaster1-bin.000002', 4);

Musings on MySQL Proxy

When seeing that the MySQL Proxy was released, I decided to try to experiment with it since I see strong potential with this tool, both for replication and for other uses (recall that I'm a replication guy, so this is my primary focus). I'm actually on vacation, but this will of course not stop me from tinkering with things (I know, I'm just a hopeless case in this aspect ;) ).

After reporting a minor bug, I managed to build and run it with some sample scripts. I'm using Kubuntu Feisty, and had some initial problems, but it was actually pretty straightforward. I'll repeat the steps anyway, in case anybody else have problems.

1. Get the source from the repository

   svn co http://svn.mysql.com/svnpublic/mysql-proxy/ mysql-proxy

2. Make sure you have all packages necessary. Several of the packages below were not installed for me.

   apt-get install pkg-config liblua5.1-0 liblua5.1-dev libevent-dev libevent1 libglib2.0 libglib2.0-dev libmysqlclient-dev

3. Switch to the directory where the source is.

   cd mysql-proxy/trunk

4. Set up all the stuff necessary for the autotools (Autoconf, Automake, and Libtool)

   ./autogen.sh

5. Run configure, but make sure to tell the configuration script that it should use Lua 5.1

   ./configure --with-lua=lua5.1

6. Build the proxy

   make

Some applications

After having experimented a little, I see some of the potential applications of the MySQL Proxy, but there are things missing to make these scenarios possible. Just to give some ideas, I will just present the ideas and last present what I see as missing pieces to make these possible.

Vertical and horizontal partitioning

If it was possible to parse the query and decompose it into it's fragments, it could be possible to separate the query into two queries and send them off to different servers. The result sets could then be composed to form a new result set that is then delivered to the final client. This can, of course, be accomplished thorough other means, but if you take a look at the next item, you have a variation that is not that simple to handle.

It could be interesting to handle horizontal partitioning in the case that data for, e.g., a user is stored in different machines depending on geographic location. This is something that is interesting for companies like Flickr, Google, and YouTube since contacting a server near yourself geographically significantly improves response times.

BLOB Streaming

In order to handle BLOB streaming, as I outlined in a previous post, it could be left to the proxy to build a final result set where the BLOB Locators (URIs in my example) are replaced with the actual BLOBs by contacting a dedicated BLOB server that holds the BLOB and building the final result set inside the proxy.

Pre-heating slave threads

By placing the proxy in between a master and a slave, it could be possible to pre-heat the slave by issuing a SELECT query through a client connection to the slave. This is the oracle algorithm presented by Paul Tuckfield from YouTube, but the solution in this case is simpler since it is not necessary to read the events from the relay log, but they can instead be caught "in the air" and acted upon.

This actually requires some parsing of the replication stream, so it might be better to handle this by embedding Lua or Perl into the slave threads. (Personally, I would prefer Perl. Not because it is a better language, or easier to embed, but because I've been using Perl on an almost daily basis since 1988. OTOH, Lua is designed to be efficient and map internal structures to the language and seems very easy to work with, so we'll see what happens.)

The missing pieces

Others have focused on what can be done with the MySQL Proxy, but I see some omissions that, if implemented, would turn the MySQL Proxy into an incredibly flexible tool.

• It should be possible to parse and rewrite a query before sending it to the server. This is already possible, but a library to parse and build SQL queries would help a lot here.
• It should be possible to rewrite the result set in a convenient manner. Jan just added the ability to rewrite the packet that is sent back, so the proxy is heading in that direction, but a more convenient interface would be an advantage here as well.
• It should be possible to keep connections to several servers active, and decide what server to send the query to on a per-query basis (even sending queries to all servers, or different queries to different servers). AIUI, there is some rudimentary support for it right now, but not to the extent that I describe here.
• It should be possible to send several query-result sequences to servers for each query-result sequence sent to the proxy. This will make it possible to act on the result of a response to one server, and dynamically decide, e.g., what server to contact next in order to get the data that forms the final result set.

All-in-all, the MySQL Proxy is showing incredible potential and I, for one, will see what I can contribute with in order to make it even better.

BLOB locators + BLOB streaming + Replication = Yeah!

On the MySQL Conference & Expo 2007, I had the chance of meeting up with Paul (the author of PBXT) and Mikael. We briefly touched the topic of the BLOB Streaming Protocol that Paul is working on, which I find really neat. On the way back home, I traveled with Anders Karlsson (one of MySQL:s Sales Engineers), who is responsible for the BLOB Locator worklog and he described the concepts from his viewpoint.

Since I work with replication, these things got me thinking on what the impact is for replication and how it affects usability, efficiency, and scale-out. Being a RESTful guy, I started thinking about URIs both when Paul described the BLOB Streaming Protocol and when Anders starting describing the BLOB Locators. Apparently, I wasn't the only one.

Combining BLOB Locators with the BLOB Streaming Protocol has a significant impact on the scalability and performance of replication, and I'm going to show how by giving a typical use of replication: scaling out reads from a installation by replicating from a single master to several slaves.

Now, when a client connects to get a blob from the database, the server delivers a result set containing one or more blob locators. Since we are using URI:s and the HTTP protocol, the blobs can be served by a normal web server, and the client can fetch the data in the Blobs using HTTP and build the real result set. The existence of blob locators is completely transparent to the client, who sees no difference from the previous implementation.

Now, what does this give us that make this setup so scalable?

• Instead of storing the actual blob data, we store a reference to the data (in the form of an URI). When working with the blob and copying it to another table, we will actually just copy the reference, which is a very quick operation compared to the size of most blobs. The use of the BLOB locator is entirely transparent to any operations on the blob: reading is not affected, and changing the blob can be accomplished using a copy-on-write semantics (which of course makes the operation slower).

• Since we have a unique reference to a blob it is possible to implement caching mechanisms to cache results of, e.g., fulltext searches in the blobs.

• The use of an URI makes the blob locator server-agnostic, which means that we can reliably replicate the URI instead of the blob and still expect any client that connects to the slave server to be able to fetch the blob using HTTP. There is no translation necessary when doing the replication, and the URI can be treated as just a string. This means that a scale-out strategy is trivial to implement. This is just a generalization of the recommended practice to store the blobs as files on a server, and save the file name in the tables instead: we just make it transparent to the user and simplify the deployment.

• By using an URI as reference, we can put the blob data on a separate server, which can be dedicated to delivering blob data to requesters. Since everything is going via this server, it is very likely that "hot" data is available immediately, and since we are using an URI, delivery over the Internet can rely on Web Caches to avoid re-sending data that is already cached somewhere.

  We do not lose the ability to count the number of deliveries of the data, since we can always count the number of blob locators that we have been delivered instead of the number of BLOBs that have been delivered.

• The HTTP protocol has support to both PUT and GET to read and write data to the server.

• We unload a significant amount of "dumb" job from the server, that of assembling result sets consisting of blob data and other data, and therefore allow the server to perform more of the "intelligent" job of doing database searches.

• The design is incredibly flexible since it is possible to, for example, allowing the blob server(s) to be placed anywhere, even in different towns, and can still keep the main operating site in one location.

scale