Re: [Libevent-users] mysql client with libevent

[William Ahern <william@xxxxxxxxxxxxxxxxxx>]

On Wed, Aug 31, 2011 at 11:58:32AM -0500, Nulik Nol wrote:
> Hi,
> I was wondering if someone has managed it to adapt mysql client's C
> API to work asynchronously ?
> As far as I have checked, MySQL (5.6.2) uses libevent for server, but
> not for the client. The only way to achieve sort of asynchronous
> behaviour is to use pthreads and spawn one thread per connection to
> the database in blocking mode. But truly event based mysql client not
> yet exist, is this correct? If so, what would be the best solution for
> fast MySQL query execution? (I use prepared statements)

I've written an async MySQL library. I used Ragel but it's certainly not
necessary as the protocol is fairly straight-forward, if ugly. Ragel just
makes it a quicker job to parse out all the little protocol elements in a
byte-oriented, restartable manner.

Most of the protocol is described here:

	http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol

The only real pain, other than debugging, was writing the password
"scambler". For that you'll need to study the C client library for the
hashing algorithm. Also, my notes mention that the documentation is wrong
about the atom size of the passwd token (or, alternatively, the
documentation is right but MySQL had a couple of buggy implementations).
Again, Ragel is useful here because it's easier to tweak the parsing. OTOH,
Ragel can make it more difficult to switch behavior on-the-fly according to
protocol version.

Beware: once you read thru the MySQL code you'll never want to use MySQL
again ;) The apparent robustness of MySQL has little to do with the quality
of the code and everything to do with the millions of people banging on it
every day.

Now, whether it will be worth it to write a MySQL library is another
question entirely. Just because you're using a thread prool to do the
querying doesn't mean you're wasting many resources. MySQL uses a thread per
client connection anyhow, so a 2x increase in threads isn't a big deal per
se. In the case of my project it helped, but that's because of the
particular scenario. YMMV. Also, I'm not familiar with prepared statements
in MySQL. They're new, and based on the quality of the MySQL code I wouldn't
imagine they bring has much benefit as on, say, Oracle. I highly doubt MySQL
does speculative read-aheads, etc, based on prepared statements. Parsing
cost is negligble anyhow, and there wouldn't be much latency to gain back by
using prepared statements because you still need the back-and-forth during
execution.
***********************************************************************
To unsubscribe, send an e-mail to majordomo@xxxxxxxxxxxxx with
unsubscribe libevent-users    in the body.