Saturday, August 02, 2008

New PBXT Release 1.0.04 Improves Performance

Lets face it, when it comes to storage engines, performance is everything. But then again, so is stability and data integrity!

So as a developer of an engine, which should you concentrate on first: performance, stability or data integrity?

I know there are not many that have to deal with this stuff, but here is my advice anyway: go for performance first.

The reason is simple, significant performance tuning can have a serious affect on both stability and data integrity. And this means you need to repeat a lot of the debugging and testing you did before.

For example one of the optimizations I made for 1.0.04 required a number of changes to the index cache. One thing was to make the LRU (least recently used) list global, it was segment based before. During the change I copy-pasted a "lru" pointer instead of a "mru" pointer :(

The result was not a crash, but the engine lost cache pages! So I only noticed the problem when a test just ran to slowly. When I got it up in the debugger, I noticed that the engine was flushing the index constantly, and this was because it was running on only 4 cache pages! All-in-all that typo cost me a half a day of debugging.

Anyway, there is still more to be done in way of optimization, but so far I am happy with the results. Here is a comparison between 1.0.04 and the previous version of PBXT:

This test was done on a 2-core machine using sysbench-0.4.7 running various selects on a table with 1M rows.

As you can see performance of the 1.0.03 version breaks completely at 4 threads. However, although 1.0.04 performance is significantly better (10 times faster at 4 threads), it also degrades substantially.

So why is this?

Well that is the thing that prompted me to have a look at the performance of MySQL itself, which I reported here: Mutex contention and other bottlenecks in MySQL.

Suffice to say that at 16 threads, MySQL is hanging 43% of the time in a mutex in open_table(), and 45% of the time in a mutex in lock_table(). And the solution is ... on its way down ... Drizzle :)

As usual you can download the latest version from www.primebase.org/download or checkout using svn directly from SourceForge.net. Give it a spin...