Libmysqld performance. And a Webinar. And a talk at the UC!

As you might know by now, libmysqld, the deeply embedded version of MySQL, has reapperared in MySQL 5.1, having been gone in version 5.0. If you are curious about libmysqld in general, then feel free to pop by this free live webinar, hosted by noone else but yours truly:
http://www.mysql.com/news-and-events/web-seminars/index.html
(Look for the webex with the ISV and OEM ... Deeply embedded blurb, and you''ll find it). You need to preregister for it.

What I was going to say a few words about here though, was on the performance of libmysqld. The code in libmysqld is the same as in the mysqld server, basically, but there are some shortcuts, and above all, there is no overhead of Client / Server communication. I decided to try this out a bit, using a very simple application, doing INSERT, UPDATE and SELECT on a simple MyISAM table. Note that as the overhead of MyISAM itself is so small, the performance benefits here will be larger, in terms of percentage, than with, say, InnoDB. This doesn't mean I think there is something wrong with InnoDB (if you know me, you know this isn't my intention), just that I have choosen a setup to prove a point. The table I am using looks like this:
CREATE TABLE tab1(c1 INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
c2 CHAR(255),
c3 CHAR(255),
c4 CHAR(255),
c5 CHAR(255),
c6 INTEGER
) ENGINE=MyISAM;

Simple enough, right? So first I'm doing, using an empty table, 300.000 INSERTs, as 3 runs of 100.000 INSERT operations. Then I do the same thing with 400.000 UPDATEs, and I finish up with 300.000 SELECTs. I do this using embedded server, and the I restart and do the same thing using normal MySQL Client / Server. The numbers below is the mean time for 100.000 operations of each type.

EMBEDDED CLIENT/SERVER
INSERT 6.82 s 20.91 s
UPDATE 6.54 s 21.59 s
SELECT 6.92 s 21.73 s

Looking at the results in the table above, it looks like libmysqld is about 3 times as fast as mysqld. Can that be true? And the answer is no, it's not. Although the server and the libmysqld application are configured in the same way, run on the same hardware, and even use the same physical database, this is not a fair comparison. The reason is that what I am doing here are VERY simple operations, where the client / server overhead is significant. For more complex things, you will not see these differences, not at all. And note that libmysqld also has some disadvantages.

So, now I guess you ask yourself what in heavens name I have been smoking? What is my point? Really! And my point is this: For a singel user application, of low or medium complexity, the gain of using libmysqld isn't better performance, in and of itself (voice from the crowd: Can someone stop him. He's doing it again! Tell him to get to the point for heavens sake!). No, my point is that to achieve the same level of performance, less hardware resources is needed. And that IS an advantage. Right?

So no, performance isn't the thing, less overhead IS. To finish up, just a few more things: The benchmark, if that is what it should be called, above, was run on a small AMD 64-bit 2 core box running Linux (Fedora), and the application was a C-app using the MySQL C API, and MySQL version 5.1.32. And by the way folks, if you have been reading this far, welcome to my libmysqld talk at the MySQL User Conference which is up April 20 - 23.

And then, one big question remains: Where did I find that picture of myself that is on the MySQL UC website. Gosh!

/Karlsson
PS. My facebook and myspace pictures are better!
PPS. If you come to the UC and want me to wear a western style hat during the talk, tell me so. If you come to the UC only because of this, I owe you a beer!