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
5 comments:
I too hope for a better MyISAM. But Maria is trying to do much more and be an alternative to InnoDB. It is much easier to be a better MyISAM than a great OLTP engine. So we must wait until someone decides to fix some of the problems in MyISAM and try to ignore all of the money that MySQL invested in Maria and Falcon.
Given the current state of MyISAM I am amused that they added support for hot backup to it. If you need hot backup, why would you use MyISAM?
It all depends on how you test, and different engines are suitable for different purposes but they also need to be handled differently for that purpose by the app.
For instance, I can insert 340,000 rows per second, sustainable, into a MyISAM table from a single thread on a basic 64bit Linux. Unmodified server code, just appropriate tuning and SQL approach.
Just to give a bit of perspective on your 4,000 operations with MyISAM ;-)
Benchmarks without context don't do much to advance the discussion. I can write many more rows per second to the /dev/null storage engine. I will guess that by 'sustained' you mean 'sustained' when there are no indexes.
Benchmarking with absolutely no tuning is an exercise in futility.
You can't expect an engine to have excellent settings for any arbitrary benchmark.
Swany: I agree, to an extent. But the idea was to compensate for this, to an extent, by testing something that is as little effected by tuning as possible, and for that I went for a mass-INSERT test.
Having said that, I still don't consider this to be conclusive at all, but in my mind, no benchmark is conclusive, and performance, per se, is not that interesting to me, once we get past a certain level. You can always add more machines, do your operations differently, shard your data etc. So I just wanted to test something silly out of the box.
Also, in this case, I found no good generic tuning samples for Maria or PBXT, no sample my.cnf that came with MariaDB contained any Maria or PBXT parameters at all. You could draw any kind of conclusion from that of course.
I'd be happy to do an optimal tuning test, but that will require a fair bit more effort, so I don't think I have the bandwidth for that. In the case of Maria though, I'll make an exception as it was so far behind the other guys.
Anyone who want to send me some optimal mass-INSERT tunings for the other engines are also welcome, and I'll test again, and do a third blogpost with the results next week.
/Karlsson
Post a Comment