New kids on the block doin a stupid benchmark on Windows

There are now some interesting Storage Engines out there, beyond the usual MyISAM, InnoDB, NDB etc. The question is then, how do they perform? And as usual, I was a bit curious on the feasibility of these new guys on Windows?

I decided to try out MariaDB first, based on MySQL 5.1.39, and this also includes PBXT, which was convenient, now I got a chance to try, with one installation, InnoDB, MyISAM, Maria and PBXT. The MariaDB Windows download was no fun at all, though. This includes among many other things, the text setup (300Mb just that), pdb files for libraries and executables, embedded versions and debug and optimized libraries, adding up to a whopping 869 Mb on disk (but you have to exclude my current data which amounts to 94 Mb). Standard 5.1.39 is 134 Mb on disk, excluding data.

What is more annoying is that there is no installer, no MSI no nuthin', just a zip. Which is sort of fine for me, but goes to show that this really isn't even close to RC.

As for Maria with this build, if you do this:
SELECT engine, transactions, xa FROM INFORMATION_SCHEMA.ENGINES;
What is returned is this:
+------------+--------------+------+
| engine | transactions | xa |
+------------+--------------+------+
| CSV | NO | NO |
| MRG_MYISAM | NO | NO |
| PBXT | YES | NO |
| MARIA | YES | NO |
| BLACKHOLE | NO | NO |
| MyISAM | NO | NO |
| ARCHIVE | NO | NO |
| MEMORY | NO | NO |
| InnoDB | YES | YES |
+------------+--------------+------+
9 rows in set (0.00 sec)
Note here that Maria claims to be transactional, which isn't really true. Looking at the Maris docs, this is because they plan to make it transactional.

Benchmark setup
Now, for the benchmark, I decided for something really simple. No engines has been tuned at all, i.e. no engine specific parameters are set. The tables for the different engines were created like this:
create table t1_maria(c1 int not null primary key auto_increment, c2 char(100)) engine=maria;
create table t1_pbxt(c1 int not null primary key auto_increment, c2 char(100)) engine=pbxt;
create table t1_innodb(c1 int not null primary key auto_increment, c2 char(100)) engine=innodb;
create table t1_myisam(c1 int not null primary key auto_increment, c2 char(100)) engine=myisam;

As you can see, again no engine special settings, just out of the box stuff. What I do in the actual benchmark is this:
Start 10 threads, each thread executes 1000 operations like this:
INSERT INTO t1_maria VALUES(NULL, 'xxxxxxxxxx');
Just changing the table name. This isn't anything complex, hardly scientific, but there applications out there that does something like this, no more, no less.

The hardware used was my crappy old Lenovo Laptop running Windows XP. This is not the hottest computer since Osborne-1, but all Storage Engines shared the same hardware.

Expectations
OK, what was I expecting here? MyISAM was expected to be the fastest, no surprise, there are just too many shortcuts taken for it not to be. But if I had increased the # of threads, performance would slow down, due to concurrency issues, but not even that might be true, as the table was all clean now, and MyISAM has some tricks to deal with that. InnoDB was expected to be slower, and I was expected the usual InnoDB slowdown in some threads (I always see a few InnoDB operations being slow, although performance is good overall). As for Maris, I expected something along the lines of in the middle between InnoDB and MyISAM. Maria being crash proof would add to the overhead, but on the other hand, no transaction support and MyISAM heritage would speed it up. PBXT I didn't know what to expect from, but as this should be the kind of thing that PBXT is expected to excel at, but on the other hand being transactional (and this time it really IS transactional :-), I was expecting InnoDB'ish performance, maybe even better at this PBXT oriented load.

So the results then?
MyISAM fast as expected (but not crashproof), performed on average 8553 operations / s.

InnoDB on the other end of the scale, transactional and crash proof and all that, achieved 105 Operations / s. Not bad for an untuned InnoDB. Also, note that autocommit was used all over the place, had pumped some more data per transaction or relaxed the durability, things would sure be better.

PBXT performed real well, despite not being tuned at all. 181 Operations per second, and pretty consistent throughput across the threads.

So what about Maria then? Well, I don't know, apparently performance will be addressed in a later release (personally, I think performance first and foremost is a result of design, but what do I know) according to the docs. Also, this is a beta release (but PBXT is RC I think, and Monty et all claims that Maria 1.5 "we think it's good enough for RC"). In any case, I got 20 Operations per second from Maria. Less than 1/5 of InnoDB, MyISAM being more than 4000 as fast as Maria. Hmmm, I was a bit disappointed here, I was expecting more. And where is the Windows installer?

Conclusion
PBXT looks promising to me. I've always liked the Primebase focus on smart technology and thinking outside-the-box. Here it seems it may have paid off (I write "may have", as this benchmark is far from conclusive). InnoDB works as expected (here I was using the built-in InnoDB by the way, not the plug-in). MyISAM is fast, but takes too many shortcuts for my taste. And Maria seems not ready for prime time. I admit that it wasn't tuned, but none of the other engines were tuned either, and the difference was just too big, 1/5 of InnoDB performance without transaction support. And the lack of a Windows installer is another sign of this. Too bad.

/Karlsson