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
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.
Hadn't looked at DBSlayer yet. Very interesting. Might be worth playing with.
Thanks!
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.
Aretm:
I wish I knew about stability. Time will tell, I suspect.
Welcome to Open Source? :-)
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.
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.
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.
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.
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.
Take a look at libdrizzle. It speaks both Drizzle and MySQL, and has this sort of ability in it.
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.
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.
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.
There is now Coro::Mysql, which allows you to use at least DBD::mysql asynchronously (using Coro::AnyEvent/AnyEvent)
