MySQL Performance Blog

Why one may wonder, it’s just Swedish beer (State of Doplhin, MySQL UC 2006).
One week ago Jim Starkey sent message http://www.firebirdnews.org/?p=1742 so he will not work for MySQL anymore and starting new project. While that’s fully Jim Starkey’s personal decision, I expected some comments about Falcon future development from MySQL / Sun side. Jim was not just ordinary developer, but lead of project and main architect of Falcon and his leaving may change a lot. For now MySQL’s calm seems stunned or indifferent to Falcon’s destiny.
Falcon has being developed for about 2.5 years, and despite it named “beta” stage, it sill crashes in our quite simple benchmarks, so it’s very optimistic “beta”. That’s why I wonder if this project will be ever finished, especially when main architect left it.

We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that’s why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core is able to handle more queries per second than identical box, but with 4 CPU Cores.
[read more...]

Where does real power of MySQL Storage Engines, and pluggable storage engines as MySQL 5.1 lays ? It is very much advertised this allows third parties to create their own storage engines and we can see solutions as Solid and PBXT . Plus there is Falcon storage engine being developed inside MySQL.

All of these storage engines are however similar in their features and focus – all of them are transactional storage engines with multi versioning and row level locks so it is question of architecture implementation details of how it would compare to Innodb storage engine – old timer in this space. Does it add choice to the people ? Yes! but it also adds high level of complexity because it will not be easy to figure out which storage engine or storage engine mix is best match for you. Believe me people even often have hard time figuring out between MyISAM and Innodb and if they do they use some generic (and very wrong) ideas such as “MyISAM is always faster for reads”.

The main benefit I see from these general purpose storage engine is they apply pressure on each other and as books says competition leads to customer winning. When Falcon looses to Innodb in certain cases it makes Jim to try harder, if Heikki sees Innodb does not scale as well as PBXT it will be an extra pressure to do the fixes. While Innodb was only transactional storage engine there surely was less pressure – if you’re migrating from Oracle to MySQL you of course may consider to delay migration but if you’re already with MySQL and requiring transactions you’re stuck with Innodb if you you’re happy or not. (This is not to say Innodb is bad storage engine, but we all know nothing is perfect and has its problems).

The great Innovation I think happens with custom storage engines which are not designed to be run as core storage engines and are not match of functions which you’d find in Oracle, PostgreSQL and other databases. MEMORY storage engine is old timer of this type, ARCHIVE, CSV are other good examples. There are also storage engines out where to read sensor data, talk to memcache or external search engine . As MySQL 5.1 and plugable storage engine interface matures I hope we’ll see more storage engines which are great serving one particular talk but doing it in simple efficient and innovative way. NDB cluster is yet another example I’d put in here. I do not feel it is ready as main storage engine for most of applications just yet but for some cases it may be handy.

The fact which is limiting innovation at this point is the fact MySQL has only Storage Engine pluggable but SQL layer is kind of fixed same for all. This dramatically limits what you can do. Such limit is not a big surprise though as execution layer is much more complicated is constantly changed and different optimizations may change internal code paths dramatically.

I know number of projects being interested in special techniques applying to optimizes certain types of queries with MySQL, it is not easy to do however as there is no interface. We had the same problem with Sphinx – Sphinx can run sorting and group by operations much faster than MySQL can and it can do it in parallel, which means it can be 100 times faster in the end, but there is no way to hook it up to standard order by and group by functions so it is implemented in rather hackish way. Same applies to checking number of rows in result set and LIMIT implementation – these can optimized but not by use of MySQL Storage Engine interface, which means it is also has to be implemented through hacks.

In any case this is very interesting how it will be evolving and how more parts in MySQL will become pluggable and so hopefully more innovative tools and techniques will be developed.

Now as new MySQL Storage engine – Falcon is public I can write down my thought about its design, which I previously should have kept private as I partially got them while working for MySQL.

These thought base on my understanding, reading docs, speaking to Jim, Monty, Arjen and other people so I might miss something, something might be planned to be changed or not 100% correct but anyway you might find it interesting.

In many cases what I find good or bad would base of my MySQL use with existing applications – if you design new applications which are done specially for Falcon you might find those aspects positive.

[read more...]