Asynchronous MySQL client library

Our de facto method for asynchronous programming at Meebo is to use a single thread with non-blocking sockets and some sort of socket watching/readiness notification (poll, select, epoll, libevent, glib’s mainloop, etc). This is what Pidgin does, too. It is by far my favorite approach to asynchronous programming. (There are some other approaches listed in Dan Kegel’s article The C10K problem.)

It works especially well for clients. For servers, if you want to take advantage of multiple processors or multiple cores then you either have to spawn threads or use multiple processes.

We use MySQL a lot at work. And we use C a lot. Unfortunately the C API for MySQL is synchronous. This means if you perform a query then the entire application sits idle until the database server responds. This is extremely inconvenient. There are various hacks and 3rd party libraries you can use to perform asynchronous queries, but they’re a bit ugly and don’t work that well. We ended up writing a proxy server that runs on the local machine and proxies queries to the database. So the client connects to the proxy and submits a query, control is returned to the client immediately, the client’s event loop watches the socket connected to the proxy server and calls a callback function once the proxy has returned the result from the database.

It works well enough, but I feel like the world would benefit from a solid asynchronous MySQL C API. Or maybe we should just switch to PostgreSQL.

P.S. epoll is ridiculously sweet. If you’re concerned about the performance of a network-heavy application then use epoll (or kqueue if you’re on BSD)! The difference is night and day.

Update 2010-02-26:
People suggested some projects in the comments. Also see:
https://launchpad.net/myconnpy
http://cv.arpalert.org/mysac.html

This entry was posted in All, Computers and tagged . Bookmark the permalink.

7 Responses to Asynchronous MySQL client library

  1. modified says:

    Mark, who all reads your LJ? I feel like I’m one of your few nerdy readers and but I only hack java and I’m of little use to you. Greetings from Iceland btw. For a country with about as many people as Raleigh, they have suprisingly good wifi.

  2. Anonymous says:

    just got the same issue

    however, i did not create a proxy for that. looks like too much overhead, too.

    I initially wrote a C++ API atop of the mysql C API for my own convinience.
    Later i noticed that some administrative queries may freeze your server for just too long (about 5 seconds in my case) so I tried to find out how to get async’d mysql queries.

    http://code.ninchens.net/repositories/diff/libnuggad?rev=2a73707061a4476b43cdf76c0e4c01cb591dba68

    However, looks like i ran into a couple of issues:
    * you can’t mix sync and async quries within the same mysql connection.
    * you cannot send multiple async queries in a row (without waiting/processing the result first).

    This is really ugly. Has anyone an idea how to solve this?
    Otherwise it looks like I’m forced to do internal connection pooling in order to keep the outside api clean.

    Cheers,
    trapni.

  3. Nir says:

    can “ribify” the default libmysqlclient (and any other “blocking” library, as long as it provides static library) to become async. This way new features will always be supported. In fact, if you use ribs2, your code does not need to worry about events OR threads and forks (except for utilizing multiple CPU/cores as mentioned) Enjoy.

Leave a Reply to modified Cancel reply

Your email address will not be published. Required fields are marked *