Following my recent blogg on the performance of MariaDB and PBXT on Windows showed that these new kids still has some work to do. Another question I was thinking testing was if the overhead of Transactions really matters, they do have a performance advantage also (like: You don't have to persist until the transaction is done), and so does row-level locking. And what about Linux? We could see that MariaDB on Windows was less than optimal, maybe this was a Windows problem. And finally, the Windows box I was testing on was a real old clunky box.
Hardware setup
So now, here are the results on a Linux box with a 4 Core AMD CPU and 4Gb of memory. Still not an exceptional box, but a decent one.
The first test on Linux
Again, all engines have the default settings, no tuning has been done at all. This time, with some more concurrent threads running (10 therads in total, as before, but now I have 4 running concurrently at full speed), row-level locking and increased concurrency support in PBXT and InnoDB pays off, they are both about the same speed, running in at 9216 and 8928 Operations per second respectively. Then we have MyISAM at 5615 Operations ser second, but I was expecting more here, and this is what I got, sonsistently over several runs. Last in, way behind after InnoDB, PBXT and MyISAM are already drinking beers at the local Storage Engine Pub, Maria gets in at 2285 Operations per second. So all in all, faster than in the case of my Windows test, but with a lot more Iron, and Maria way, way behind again.
Adding more threads
Wanting to try a bit more, I decided to change my test setup slightly. I insert 10000 rows per thread, instead of 1000, and run 40 simultaneous threads, to see what happens. And the results are pretty consistent with what I got before, maybe PBXT shines a bit more, at 11950 Operations / second, InnoDB comes in at 9796, MyISAM at 7665 and finally Maria, again at a sluggish 2092 Operations per second.
At this point, someone might say that Maria is still beta. But it is claimed to be RC quality, so it should be as close to GA as PBXT, but I don't really see that.
Thread impact and Transactions
Before I close the lid on this very simplistic benchmark, two more things. First, is it so that multiple-threads really cause less contention in InnoDB and PBXT than in the table-level locking Maria and MyISAM? Let's try with just 1 thread running, inserting 10000 rows. This time, MyISAM comes out best at 4468 Operations per second followed by PBXT (2170), InnoDB (2137) and last, as usual I was about to say, Maria at 1910. Now, this time Maria isn't THAT far behind, but note that Maria is not transactional, which PBXT and InnoDB is.
Lastly, let's see what good transactions can do, performance-wise. In this case, I will only persist (commit) every 100 Operations with PBXT and InnoDB. I am inserting 10000 rows in 10 threads each, and each thread is doing a commit every 100 rows then. For both PBXT and InnoDB this meant huge performance boosts, turing in at 28264 and 25773 Rows per second respectively, with MyISAM at 5540 and Maria at 2063.
Array inserts should help, but some simple tests shows that the relation stays the same, with PBXT and InnoDB on top, followed by MyISAM and Maria far behind (maybe even more behind).
Conclusion
Whereas PBXT seems good and ready for prime time, at least from a performance POV, and at least with this simple load, Maria is not there yet. This is a shame, I was hoping for Maria to be the crash-proof MyISAM we always wanted. Also, it seems that increased number of concurrent threads really do need more database concurrency (no big surprise there) in terms of row-level locking. And that transactions also may improve performance is also clear, at least to me.
And before I close, don't take this test too seriously, it is a very simplistic test, but Maria is so far behind even in this simple test (and data loads, as this is what this test is doing), is nearkly always important at some point (mysqldump recovery springs to mind :-).
/Karlsson
Thursday, January 7, 2010
Tuesday, January 5, 2010
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
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
Monday, January 4, 2010
An example why Open Source rocks... Take that, Apple!
My colleague Luca Olivari wrote about the excellent site GetApp.com, where you can find loads of good Open SOurce applications. Where Luca goes wrong though is when he compares this to AppStore for iPhone. Apple does encourage iPhone application development, but it is not Open Source, which is why one crucial Web component is missing: a Flash plug-in. Yes, for those of you without an iPhone, you can NOT view Flash-based pages with an iPhone. The reason? Apple will not allow one, at least not yet, and Adobe has not announced a player for the iPhone.
Although the Flash player in and of itself is not Open Source, this still shows what an active eco-system can do for you, it will encourage and promote more and innovative development. Looking at the competing Android platform (I'm on an Acer Liquid myself), a Flash player has been announced, but is not yet released. The eco-system around Android is much younger than that around iPhone, but it is just as large and is truly innovative. If we exclude that fact that tethering applications has been banned from Market (the Android app site), as a result of some telco companies realizing that this was not "in their best interest" (not that this will stop tethering in the end anyway, and excellent PdaNet, which I use right now, i close to as good, except that it is wired only (Bluetooth enabled PdaNet will be there with Android 2.0 though)).
All in all, comparing AppStore and Market will show you just what a true live Open Source environment can do in terms of eco-systems. That Android is less mature in terms of usability and partly in terms of features compared to an iPhone is true, but it is close enough, fully Open Source, intergrates with all the neat Google things (GPS with Google maps is just great, for example) and is about 1/2 the price (at least here in sweden) (I got mine on an auction, and paid even less than my old clunky Nokia E66 even).
So, all in all, if you want a true Open Source phone and is willing to accept that some assemply is required, then go for an Android. Take that, Apple!
/Karlsson
BTW. If you are wondering why I am not blogging as much on databases here as I used to, this is because the heated tone on the MySQL blog-front right now. Me being an evil person and all that.
Although the Flash player in and of itself is not Open Source, this still shows what an active eco-system can do for you, it will encourage and promote more and innovative development. Looking at the competing Android platform (I'm on an Acer Liquid myself), a Flash player has been announced, but is not yet released. The eco-system around Android is much younger than that around iPhone, but it is just as large and is truly innovative. If we exclude that fact that tethering applications has been banned from Market (the Android app site), as a result of some telco companies realizing that this was not "in their best interest" (not that this will stop tethering in the end anyway, and excellent PdaNet, which I use right now, i close to as good, except that it is wired only (Bluetooth enabled PdaNet will be there with Android 2.0 though)).
All in all, comparing AppStore and Market will show you just what a true live Open Source environment can do in terms of eco-systems. That Android is less mature in terms of usability and partly in terms of features compared to an iPhone is true, but it is close enough, fully Open Source, intergrates with all the neat Google things (GPS with Google maps is just great, for example) and is about 1/2 the price (at least here in sweden) (I got mine on an auction, and paid even less than my old clunky Nokia E66 even).
So, all in all, if you want a true Open Source phone and is willing to accept that some assemply is required, then go for an Android. Take that, Apple!
/Karlsson
BTW. If you are wondering why I am not blogging as much on databases here as I used to, this is because the heated tone on the MySQL blog-front right now. Me being an evil person and all that.
Subscribe to:
Posts (Atom)