Oracle Hugger Goes MySQL

I started working with Oracle databases in 1997. Ever since a former employer switched from Sybase to Oracle 8. Back then, databases were new and somewhat intimidating to me. Having just recently come up to speed with Sybase, after working with it for a few months, I now found myself suddenly transported into Oracle-land and told to forget about all things Sybase. I didn't like it, I didn't like it at all. I felt lost.

Oracle and me

At first, I was just an Oracle user. Then, I actually became an Oracle developer. I was a convert, an evangelist even: I found Oracle to be well-engineered and very satisfying to work with. I took up administration and design, and eventually became the Oracle go-to guy in the office (the Oracle oracle if you will).  I read all of Jonathan Lewis's books (such as this one and this one) for fun on Saturdays. And I ended up working with all versions between 8 and 11.2, and on big installations too, including RAC variants, at Schiphol, Boeing global, the Austrian government, NXP, etc.

Change scene, enter MySQL

So when I recently got tasked with analysing a performance problem on a MySQL installation for one of our customers, I found it to be quite a challenge. Up to that moment, my only experience with MySQL was as a user, not as a dev. I really wasn't sure if any of my Oracle knowledge would apply...

Though only a relatively small number of the customer's employees actually work with this ill-behaving MySQL application, the impact of the particular performance problem I was asked to tackle was rather large. This is because the application is so central to their daily work. With user frustration rising, it was very important to find a solution quickly.

Door number 1

The first thing I did was check out which MySQL version we were looking at. It turned out it was 5.1.51, a 32-bit version, released in september 2010 and running on 32-bit Linux. This was a bit of a surprise since it had been years since I last encountered a 32-bit Oracle version anywhere.

From a performance standpoint, 32-bit is quite limiting, mainly because of maximum addressable RAM and maximum file sizes. In cases where performance is limited by architectural choices in the application or data model, it helps when you can throw lots of RAM at it while you re-architect the app.

I therefore recommended to investigate upgrading to the latest 5.1 version (5.1.66, released in september 2012) or even to MySQL version 5.5, on a 64-bit platform.  Upgrading would give us the immediate benefits of bugfixes, performance enhancements, improved diagnostics and new features. But also possibly some broken functionality due to newly introduced bugs or changed functionality, which is why as always a thorough investigation is needed before any kind of upgrade, even a relatively minor one like from 5.1.51 to 5.1.66.

Door number 2

Next, I needed a tool to get a quick and concise overview of database health. In Oracle, you could use Enterprise Manager for that. For MySQL version 5.1, I found mysqltuner.pl. This Perl script is actually quite old, and apparently not actively maintained anymore, but it suited the purpose. It gave some concise database health parameters to work with and it served as a starting point for my forays into MySQL parameter and diagnostics information.

At this point though, I fell into a trap: mysqltuner.pl reported a problem concerning the "Query Cache", and I started attacking the problem based on my experience with the Oracle Query Cache.

Wrong! The Oracle Query Cache is something else entirely. I felt like a n00b when I realized it: even though the terminology is the same, the actual implementation of a concept can (and will) differ wildly between Oracle and MySQL.

I found out about the slow query log next. That was a happy moment! I was even happier when I found pt-query-digest, which I would now consider to be the MySQL logfile killer app. It aggregates the information it finds in the raw log file and presents the "top" queries (where you yourself can define what "top" means). It also has extensive filtering options...

So, I switched on the slow query log for a couple of days on the production server, while it ran its usual workload. Next, I ran the logfile though pt-query-digest and sent off the neatly formatted logfile to a developer colleague, saying: "These are the top 20 SQL queries we need to look at".

Well….no, not quite. Another n00b moment!

Looking closer at the report produced by pt-query-digest, my colleague noticed that all of these 20 queries ran at night (as part of batch processing and maintenance jobs) and could never cause the performance problems noted by the day-time users of the system…

At this point I should mention that the bulk of the application we were looking at was not written by our company. We knew a lot about its use, the business logic, but next to nothing about internal architecture and implementation.

The breakthrough

We decided to switch on the MySQL general log on a test system (never on production, it would just grind to a stop) and ran some of the slow user scenarios there. We ran the logfile through pt-query-digest again (yes: it groks a general log file as well) and found that a single scenario would result in hundreds of SQL INSERT and UPDATE statements. We also discovered that the application did not use prepared statements at all. Wow!

Conclusion

So going forward, we're now pursuing two parallel tracks. First, we're investigating how moving to 64-bit, adding extra RAM and allocating a bigger Query Cache might help the current application. Second, we're working with the app vendor, investigating how the introduction of prepared statements and the use of bulk 'insert and update' would benefit performance.

Improving nightly performance, while still important, has been postponed for now, even though we have some ideas already on how to tackle it (beter indexing and the use of materialized views).

Finally, I am now looking at improving our MySQL monitoring tooling. There are several Nagios plugins that deserve further explorations (see the Nagios Exchange and this blog post).

To summarize, I found MySQL to be a very interesting environment that I really need to dive into further. I also learned that, when working with MySQL, one must not make the mistake of approaching things the Oracle-way.