I recently found myself wishing for an async library for MySQL. My goal is to be able to fire off queries to a group of federated servers in parallel and aggregate the results in my code.

With the standard client (DBD::mysql), I'd have to query the servers one at a time. If there are 10 servers and each query takes 0.5 seconds, my code would stall for 5 seconds. But by using an async library, I could fire off all the queries and fetch the results as they become available. The overall wait time should not be much more than 0.5 seconds.

While I found little evidence of anyone doing this in practice, my search led me to the perl-mysql-async project on Google Code. It's a pure-Perl implementation of the MySQL 4.1 protocol and an asyncronous client that uses Event::Lib (and libevent) under the hood.

The code contains little in the way of documentation or examples, aside from the simple bundled test script. After a bit of mucking around with it, I managed to cobble together a working example. It looks like this:

Sure enough, that code runs in just a bit more time than the longest query it executes, rather than the sum of all the query times.

What still surprises me is that this code doesn't appear to get a lot of use (or at least discussion) in the real world. In the PHP world, the mysqlnd driver offers async queries.

So count this as my contribution to demonstrating that Perl can do async MySQL queries too.

Posted by jzawodn at November 14, 2008 07:47 AM

Reader Comments
# Justin said:

Have you looked into DBSlayer (HTTP+JSON interface to MySQL)? You could then use cURL to issue multiple HTTP requests in parallel then re-assemble the results in your app.

on November 14, 2008 08:09 AM
# Jeremy Zawodny said:

Hadn't looked at DBSlayer yet. Very interesting. Might be worth playing with.

Thanks!

on November 14, 2008 08:22 AM
# Artem Russakovskii said:

Justin, what if the application is backend, which is what I suspect Jeremy does most of the time?

Jeremy, great hint, but what about the stability of their code? I've hit crash bugs even with existing and oh so tested DBI.pm before, so I'm afraid to even touch anything not as scrupulously tested by time and thousands of people.

on November 14, 2008 08:27 AM
# Jeremy Zawodny said:

Aretm:

I wish I knew about stability. Time will tell, I suspect.

Welcome to Open Source? :-)

on November 14, 2008 08:31 AM
# NM said:

Thanks Justin for the pointer to dbslayer, I was going to reimplement it for a XULRunner app I am writing, so this is a dogsend.
In fact I really wanted a Mysql API in Firefox, but it doesn't appear to exist. Jaxer (http://aptana.com/jaxer) links the libmysql into their mozilla-based Ajax server, but it doesn't look like it's reusable that easily as an XPCOM component into FF/XR.

on November 14, 2008 08:43 AM
# Tim Bunce said:

DBD::Gofer for Perl DBI is quite similar to DBSlayer but supports plugable transport layers. An ssh transport is included and a http transport is available as a separate module. There's a Gearman transport being developed. Implementing a new transport only takes a page or so of code.

on November 14, 2008 09:13 AM
# Mike Malone said:

There are facilities to do this in many languages, but it's worth noting that while running queries simultaneously is a performance boost, it doesn't really buy you anything in terms of scalability. With web apps, for example, it's usually not worth the trouble unless you're talking to some subsystem that is extremely slow and is independent of the rest of your code.

on November 14, 2008 10:31 AM
# Mike Schilli said:

When I want "asynchronous" calls, I automatically use POE. Ever looked at POE::Component::EasyDBI?

Sure, asynchronous or event-based programs take some time to wrap your head around the new paradigm, but POE is way easier than using threads in terms of ease of design and maintainability.

on November 14, 2008 09:50 PM
# Mike Schilli said:

When I hear "asynchronous" in Perl, I automatically use POE.

Have you looked into POE::Component::EasyDBI at all? It looks exactly like what you need.

Takes some time to wrap your head around the event based programming model, but once you're accomplished that, it's unbeatable in terms of ease of design and maintenance.

on November 14, 2008 10:45 PM
# Brian Aker said:

Take a look at libdrizzle. It speaks both Drizzle and MySQL, and has this sort of ability in it.

on November 15, 2008 08:35 AM
# Dmitry Karasik said:

You've implemented MySQL protocol? That's very cool, and I think is the way to go when there is no async API for mysql! However, doesn't that means that you will be continually racing towards compatibility with the latest mysql protocol version? One DBD::mysql is already bad enough...

I myself solved a similar problem with IO::Lambda::DBI, which is not that elegant because it uses forks/threads, but is DBD-agnostic.

on November 28, 2008 04:35 AM
# Jean-Christophe Zeus said:

If you are willing to use POE, as Mike Schilli suggested, I'd like to point to three more PoCo modules:

POE::Component::LaDBI
POE::Component::SimpleDBI
POE::Component::DBIAgent

I haven't looked at the last one, and PoCo::SimpleDBI requires installing half of CPAN. My personal favorite, for pure personal taste reasons, is PoCo::LaDBI.

on December 2, 2008 01:52 AM
# said:

I did some design work towards a general purpose asynchronizer for Perl, but YAPC turned down the grant application so I moved on to other things.

See http://davidnicol.com/perl/tpf%20async%20draft.txt for the proposal; the basic idea was to hide the socket communications and forking to asynchronous worker processes behind a simple-as-possible interface.

on December 22, 2008 01:29 PM
# said:

There is now Coro::Mysql, which allows you to use at least DBD::mysql asynchronously (using Coro::AnyEvent/AnyEvent)

on June 26, 2009 07:49 PM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.

 

Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.