Some recent blog posts by Markus and Frank got me thinking about some important things when considering an upgrade to MySQL 5. Did adding much-needed features — such as stored procedures, views, and triggers — to the MySQL server lead to poorer performance than previous server version without these additional features? This is a question that, over the next few months leading up to the MySQL User’s Conference, I plan on writing quite a bit about, and detailing in a number of webinars and blog entries about performance tuning, index selection strategies, and coding techniques for optimal performance. The first of these webinars is tentatively scheduled for March 1st; check back here in the next few days for a definite date and time.

Some Background On MySQL Guiding Principles

For those of you who don’t know, Monty Widenius and David Axmark, founders of MySQL, sought to create a database server that:

• was incredibly fast
• was incredibly reliable
• was incredibly easy to use

An early decision was made that features would not be added to the server unless it could be proven that the feature, when implemented in the server, would have minimal impact on these guiding principles.

Many in the OSS database community have lauded (quite rightfully) version 5 of MySQL as a “feature catch-up release”. Indeed, the addition of stored procedures, views, triggers, INFORMATION_SCHEMA, and more have brought the feature-set of MySQL more inline with the features included with Oracle, PostgreSQL, DB2, SQL Server, and other RDBMSs.

But does this mean that the development of MySQL 5’s new features necessitated a digression from the above guiding principles? Did MySQL, in order to combat the feature-criticism, put aside performance, reliability, and ease-of-use?

Peter Zaitsev, Senior Performance Engineer at MySQL, will be giving at least three (possibly more?) sessions on Performance Tuning at the MySQL User’s Conference. I expect him to be covering some of the interesting results found in recent benchmarks comparing the performance of a MySQL 4.1 with MySQL 5.0. Many of these results are quite promising; other results show that in certain circumstances, slowdowns did occur in the MySQL 5.0 codebase. I will be going over many of these results over the next few months, and Peter, who knows a trillion times more than I do on these issues, will of course be covering them at the UC.

Peter recently sent me the DBT2 (the Open Source version of the TPC-C benchmarks) benchmarks run against 4.1 and 5.0. The benchmarks for the CPU-bound test are shown in the table below. The tests were run against a QuadXeon server — 4*2.0 Ghz 32bit Xeon with HT, 4GB RAM, 8 SATA hard drives in RAID10. The results below are in transactions per second, so the higher the number, the better.

So, what can we deduce from the information above? Well, the raw data shows performance degradation of the bechmark code from 4.0 to 4.1 and 4.1 to 5.0. Indeed, if one were to simply upgrade the MySQL Server from 4.0 to 5.0, without any change in SQL code, one would see a performance degradation if the application tended to be CPU-bound (as opposed to disk-bound).

Before you go throwing your hands up saying that MySQL 5 goes against everything that MySQL’s first guiding principle states, consider two points:

1. The CPU efficiency of the server source code is very dependent on how many decisions and calculations the code must make. Clearly, as features are added to the code base, a certain increase in decisions and calculations is inevitable. However, algorithms can (and have) been improved, storage formats made more compact, and streamlined design choices made to offset these increases in calculation complexity.
2. Notice that if instead of simply upgrading the MySQL server from 4.1 to 5.0, the developer actually took advantage of the stored procedures inherent in MySQL 5, then performance jumps 22%! That’s right. The results show that you can’t just sit and be complacent and expect a 4.1 → 5.0 upgrade to automatically increase performance. No. You have to actually take advantage of the features that MySQL 5 is giving you.

Take Advantage of MySQL 5 Stored Procedures!

Let this last point sink in a bit. Use these new features in order to get significant performance boons from your server upgrade! If ever there was a reason to start (if you haven’t already) mapping your company’s migration path to MySQL 5, this is your reason.

Tomorrow, I’ll be covering some more performance benchmark results and we’ll chat about your opinions on the results shown above. Thoughts?