Once again it may come as a surprise that the MySQL.com websites have been using memcached well before MySQL started providing support. We were starting to have growing pains, with now 20 million page impressions and 3 million visitors per month, and needed to figure out how to effectively scale without throwing heaps of hardware at the problem. Memcached was a perfect solution and gives us a toolkit that is easily integrated into our architecture and flexible enough to address a number of scaling issues.

If you want to find out more then why not register for the webinar? It will cost you nothing but around 45 minutes of your time and you get to fire all those difficult questions you've always wanted to ask at me!

I'll also be discussing Query Analyzer and how we used it to get orders of magnitude improvement in query performance on MySQL.com.

Why do we do this? Well for one thing the web team is like any team of highly motivated, intelligent computer geeks and we love new toys. More than that we like to try and break them. But we are also responsible and don't like software that breaks and brings our websites down. It is a delicate balancing act, but one which we are committed to in order to
improve the product and as a side benefit get to play with all the new features.

MySQL depends a lot on its web sites. The community depends a lot on the MySQL web sites. With 20 million page views per month and more than 3.5 million unique visitors each month we aren't talking about a small site either. If we expect people to use our product with confidence, we have to be confident in our products. What better way to instil confidence than to use our products in our own mission-critical applications. This has proven to be of immense benefit providing feedback to the development team and helping them to build a quality product.

The web team is currently responsible for nine servers running MySQL 5.1, some of which are production servers, some for reporting, and some for staging sites. In production we have a master server to handle all the write traffic and three slaves that handle read requests from the web servers. In our testing we have found that getting the best performance out of our site is more dependent upon the number of slaves available than the number of web front ends (we currently use three apache servers to handle the web requests). It doesn't matter how well performing your web server is and how many concurrent connections it can handle if each of these connections cannot get data back from the database. So replication and scale-out are important for us, and it is no surprise that the web team has logged bugs on replication, and had them fixed.

Another feature that I personally am excited about is the new MySQL Query Analyzer, available as part of the MySQL Enterprise offering. This is another area that the web team has been very active in providing feedback and suggestions, and anyone who needs to manage more than one or two servers is really going to find Query Analyzer invaluable. Query Analyzer has allowed us to improve performance of some of our core queries by orders of magnitude (link to previous blog post on quan). Its simple interface and defaults aimed at highlighting performance issues makes it very easy to use and understand.

None of this work stops with 5.1 going GA either. There are always constant improvements to stability, performance and security that are being built into the products and you can be assured that the web team will thrash them out on the MySQL.com websites to make sure they are ready for the enterprise.

Once installed, all you have to do is point your applications to port 4040 on the database server instead of the default of port 3306 and in a few minutes you'll start to see the queries being logged in the Query Analyzer tab. You can sort them by database, by execution time, by execution count, or any of the other table headings in the output. You can also filter by partial query as well as a variety of other filters.

"Ho hum" I hear you say? Yes you could probably just take a look at the processlist on your db server to see what it is doing now, but the statistics Query Analyzer provides can be extremely powerful. Two examples:

#1 - Badly performing query

I installed Quan (Query Analyzer) and within about 20 minutes it became clear I had a query that was averaging 14 seconds to run. This query doesn't get run all that often, every 5 minutes or so, but it uses a table that is the most active table on our system.

OK, click on the query and it pops up a full view, showing the query with literals replaced by place holders. If you have Example Queries configured, you can check to see if there is an example showing all of the data, and if you have Example Explain configured, you will also be able to get a full breakdown of what indexes will be used in resolving the query.

With a few minutes of checking the query and fine tuning it I was able to get it down to 0.15 seconds on average. That is almost a 100 fold improvement. Yes, I could probably have done something about it earlier, as I knew it was sub-optimal, but seeing it showing at the top of the list of queries hogging resources lifts the sense of urgency.

#2 - Excessive executions of query

While looking at the first one I noticed another query starting to work its way up the list. This one was a relatively simple query, but was clocking up more than 2,000 executions a minute. Now unlike the first I wasn't all that sure where this query was being run, but using the 'full' display format I could look for that exact string in the codebase, and was quickly able to identify where it was being called.

A little more searching and I found that the query was within a loop where it would get exactly the same information each time, because the variable data was set outside of the loop! Fix that and it now runs around 10-15 times a minute.

I could have discovered this one without Quan. Probably. If I'd done a code review that could have taken days if not weeks. If I wasn't so bored with the code review that I managed to understand the implications of the query within the loop. Instead I was able to find and fix it within minutes, and then be able to verify that the problem was resolved within a few more minutes.

Think on this one for a minute. To display the particular page in question there were up to 200 unnecessary queries. That is a lot of running around in circles. I'm now going hunting for the author of that gem with a big stick - and hoping that it wasn't me

Quan should be in every DBA's toolkit. It is an absolute must.

If I get my act together I'll put up some before and after screen shots.

*DISCLAIMER: I am an employee of Sun Microsystems, part of the team responsible for the MySQL.com websites, so I am almost certainly biased. MySQL.com is a pretty busy website though and we like to think that it hits our software pretty hard.

I'm not sure how often I'll be blogging, or what about, but I hope you will find it interesting.