MySQL Performance Consulting

MySQL is an extremely popular platform. It’s fast, easy to work with, and powerful enough to solve even tough business problems. It also has a very deep talent pool of experienced professionals, and technologies like Ruby on Rails have great MySQL support. It’s (relatively) easy and fast to develop web applications with MySQL.

Unfortunately, developing your application is half the battle – often, as our userbase and featureset grows, we end up having performance problems. Sometimes, your application works, but it’s generally sluggish – it’s just not as fast as it used to be. Sometimes, it’s just a part of your app – perhaps a once-loved feature becomes so slow that it’s unusable.

Othertimes, the performance is good when the site isn’t heavily used, but at peak times – forget it. Your site is slow, and your users complain.

At times, it can be even worse – maybe your sluggish periods aren’t clearly related to periods of heavy traffic, and they appear to happen randomly. You and your developers have tried to determine a cause, but it seems to be bafflingly inconsistent.

I can help.

I’ve seen many, many different causes of performance problems, and no matter what you are experiencing, I can get to the bottom of it.

Of course, every application is different, but the problems I tend to see fit into a few different categories.

Space Is Cheap

For one thing, space is cheap – hard drive capacities are larger than ever before. Therefore, users rarely think about archiving data. Tables swell to enormous size – sometimes including tables with unimportant or irrelevant statistics.

What’s the problem?, one may ask. You just said space is cheap. Quite right – it is cheap. However, it may be said, generally, that a larger data structure performs slower than a a smaller one – and, logically, therefore, a much larger data structure performs slower than a very small one. Tables, indexes, et. al. are all based on a relatively small set of computer science constructs – all of which perform better with less data.

Of course, we can’t throw away data completely – we need data for our applications to have some use. There are strategies, though, for reducing that – strategies for controlling and carefully migrating data to archive solutions, higher latencies storage, and so forth. These strategies can have a dramatic and rapid impact.

Installing Software Is Easy

There are other problems, of course. For example, MySQL (and other open source databases) are quite easy to install and configure. Stock MySQL can be installed out of the box with a package manager – apt-get or yum, and custom builds like Percona’s aren’t much harder to install. It’s easy to get MySQL to work on your server.

Of course, working and being properly configured are not the same.

Did you know, for example, that MySQL does not use all of your available memory by default? It has hardcoded memory limits, set in configuration files – so, unless it’s configured properly, you may only be getting a tiny portion of your server’s available memory being effectively used.

Even Small Queries Are Not Free

Another common problem is a lack of awareness of per query overheads. People often are careful about accessing large tables – they will often realize that accessing a large table with many rows will take a long time, so they do so infrequently. (At least, we hope!)

“Small” queries, though, pose less of a concern – lookup tables or other relatively small tables may be accessed with nary a thought. Your framework, such as Ruby on Rails, may automatically create queries for you – in some case, very many queries. Often, these queries execute very quickly – at least, on the developer’s test machine, anyway.

Unfortunately, MySQL *does* have a per-query cost – even if a query executes very quickly, many small queries can cause a performance issue. Although MySQL has relatively few locks – particularly for a system of it’s size and complexity – it does have some, and the overhead with each query does add up. Unfortunately, each developer’s machine likely has a lightly-loaded copy of MySQL, and therefore they may not notice the query overhead – they may not notice that a simple page has hundreds of small queries on it. It’s also likely a connection with extremely low latency – since your production machine’s application server and database server will likely be on separate machines, they will likely have higher latency. Even if the latency is still low – compared to say, web server to end user latency – it can still be significant when multiplied by a great many users.

Fortunately, there are ways to address this issue. There are tools available to make this sort of thing more visible to your developers – by making such mistakes instantly visible in an always-visible display tool in your development environment. This can also show related issues – high network usage caused by retrieving too many rows, unindexed queries, etc.

There’s an answer!

Of course, most developers are concerned with adding functionality, not speeding up code. That’s understandable, of course – many features will not experience severe performance issues, and don’t need extensive optimization. They don’t think about the issues I mention above very often.

What, though, can you do if you have performance problems? What do you do if you need someone who does think about these issues?

I can help with MySQL performance problems.

Give me a call today, at (603)-574-4766, and I can help you.