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:

#!/usr/bin/perl -w
use strict;

use Event::Lib;
use Data::Dumper;

use MysqlAsync;
use AsyncCaller qw/schedule/;

$Data::Dumper::Terse = 1;

$|=1;

my $expected_results = 25;
my $results = 0;

my $dbh;

for (1..$expected_results) {
        # my $secs  = int(rand(5));
        my $secs  = rand(5);
        my $query = qq[select sleep($secs)];
        schedule(0.001, sub{
            my $dbh = MysqlAsync->new(
            database => {
                host => "localhost",
                port => 3306,
                database => "mysql",
                passwd => "xxxxxx",
                user => "root",
            },
            connect_timeout => 1,
            max_requests    => 25,
            db_timeout      => 10,
            # logfile         => "/tmp/mysqllog",
        );

        $dbh->get_array($query, \&result );
        });
}

event_mainloop();

exit;

sub result
{
    my ($result) = @_;
    if (defined $result) {
        print "result: " . Dumper($result);
    } else {
        print "error: " . Dumper($dbh->error());
    }
    $results++;

    # all done?
    if ($results == $expected_results) {
        exit;
    }
}

__END__

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.

(comments)