For those of you new to the idea of asynchronous clients, check out the Wikipedia pagefor an introduction. The basic idea is to be able to issue a query, have the function return immediately, do some other work (while the server processes the query), and then check for the query response and process the result (as normal). This may not be much of a gain (if any) for fast queries, but for those potentially sluggish queries that take a few hundred milliseconds, this could be significant (assuming you have something else to process during that time). This is especially so if you need to issue multiple queries that take a little time since they could all run in parallel connections.

Let me demonstrate with a simple use case, ignoring error checking for brevity:

...
$mysql = mysql_connect("localhost", "myuser", "mypass");
$result = mysql_query("...query that takes 500ms...");
...process result...
$result = mysql_query("...query that takes 500ms...");
...process result...
...

Imagine this is part of a PHP script for our new webpage, and it needs to issue two queries to the MySQL server. Each of these queries takes approximately 500ms, resulting in a total processing time of ~1 second. Now if the code uses asynchronous I/O:

...
$mysql[0] = mysql_connect("localhost", "myuser", "mypass", 1, MYSQL_CLIENT_ASIO);
$mysql[1] = mysql_connect("localhost", "myuser", "mypass", 1, MYSQL_CLIENT_ASIO);
mysql_query_start("...query that takes 500ms...", $mysql[0]);
mysql_query_start("...query that takes 500ms...", $mysql[1]);
$result = mysql_wait($mysql[0]);
...process result...
$result = mysql_wait($mysql[1]);
...process result...
...

(Note, this is psuedo code, it will just break if you try it currently!)

In this example, the “mysql_query_start” function calls will return immediately (send the packet out on the socket and return). This allows the server to process both of them in parallel, resulting in a total processing time closer to ~500ms. We just cut our page load time in half! (…well, ignoring network latency, but you get the idea)

You can of course expand on this by doing some other time consuming processing before waiting, say image manipulation. You can also issue more than two SQL queries (possibly to different servers) and then collect all the responses in the end. I also plan on adding a query pool so that you can add multiple pending queries to it and wait for the first one that returns. This way you would not need to order your “mysql_wait” calls in how you *think* they will return, you will always get them in the order in which they finish.

Again, this is still in it’s infancy, so please let me know if you have any comments or suggestions!

A few projects in particular that I’m going to start poking at are:

• Asynchronous I/O support in the client library.
• Connection pooling in the client library (need ASIO first).
• Multi-master replication (real multi, not just dual or circular).
• Synchronous replication for low-latency environments (building on Google’s patches to support ALL nodes).

I’ll be writing additional blog entries for each of these later. I know many (all?) of these are already on the radar within the MySQL development team, but hopefully there are ways to aid in some of this core development without being an employee of MySQl AB/Sun. I already have an email out to Jim Winstead on the ASIO support since I saw him mention it in a previous blog entry.

As a little background on myself, I’m a senior software engineer at Concentric (blog entries have the usual disclaimer: they are my opinions and do not express those of my employer). I have been writing high performance, high availability software in a clustered environment for some time now (including some proprietary clustered database projects). I’ve been a supporter of open source since my first Slackware v3 Linux install, and have been using MySQL since the late 90s. I’ve always been impressed by the stability and progress of the MySQL project, and when I spent some time to evaluate the NDB storage engine last year (very cool stuff), I decided to get myself up to speed so I could participate in the community. So, after front-loading a bunch of research, I’m excited to begin. :)