dcsimg

Quick and Dirty MySQL Performance Troubleshooting

What are the first things you should look at after learning of a sudden change in MySQL server performance?

Sooner or later, you’re going to get that phone call (or email, SMS, instant message, tweet, or whatever): The database is slow! Fix ASAP! And you’ll be expected to very quickly figure out what’s slowing things down–often wither very little context, background, or sense of what may have changed recently at the application layer.

It’s just a server. How hard could it be?

Well, as we all know “that depends.” It depends on a lot of things that you likely have little time to investigate in the middle of a crisis. So where should you focus your time and attention?

This article assumes that you have little to no good monitoring on the server and virtually no idea of what “typical” usage looks like either. Sadly, that’s a real-world situation for a lot of MySQL administators–especially those in smaller organizations who are expected to wear half a dozen different hats on any given day.

In other words, let’s go back to basics and talk about high-level performance troubleshooting–not fancy high end hardware or new releases of software that you haven’t had a chance to try yet.

Hardware Bottlenecks

The first thing I tend to do is check for hardware bottlenecks. That means logging into the box and running a small list of commands to get a quick idea of what’s happening. Mainly I’m looking for stress points. Which resources are most constrained right now? CPU? Memory? Disk I/O? Something else?

There are three main utilities I’ll run to in a situation like this:

  • top
  • vmstat
  • iostat

First I’m going to use top to see if anything is hogging CPU on the machine. If there are non-mysql processes using a substantial percentage of the CPU cores, I’m going to want to have a look at what that is and see about limiting its use or moving it a dedicated server. If I see mysqld using up a lot of CPU, I know it’s working hard and will have to drill into what’s happening inside of MySQL (maybe some poorly written queries). If nothing is apparently chewing up the CPU time, I know that the problem is likely elsewhere.

Next I’ll run vmstat over a fairly short interval–typically 5 or 10 seconds.

$ vmstat 5

‘ll generally run this for at least two or three minutes to get a sense of what the CPU and memory use are like. I’m also watching to see how much time the CPU is stalled waiting for I/O requests. Doing this for several minutes will make the occasional spikes really stand out and also allow for more time to catch those cron jobs that fire up every few minutes.

The last thing I’ll check before poking at MySQL itself is iostat. Just as with vmstat, I’m going to run it with a short interval (5 or 10 seconds) and do so for several minutes. I’ll likely filter the output so that I only see the output for the most active disk or array (the one where all of MySQL’s data lives).

$ iostat -x 5 | grep sdb

I’m looking closely at the % busy and tps to get a qualitative feel for how “busy” the I/O subsystem is, and I’ll watch both Blk_wrtn/s and Blk_read/s to figure out how many blocks are being written and read every second.

Once I have a basic feel for what the system is doing, I’ll start digging into MySQL itself a bit.

Probing MySQL

Looking inside MySQL is a sort of two-layer problem for me. First I want a high-level picture of what it seems to be doing and then I want to dig into the storage engine(s) doing all the I/O work, since I/O is a very common bottleneck.

For the highest level view, I want to see a number of things quickly:

  • how many queries per second is the server handling
  • how many clients are connected (and active)
  • are there many “slow” queries being executed
  • what, if any, unusual errors are being logged

The first few items can be answered by looking at the results of a few SHOW PROCESSLIST commands along with some SHOW GLOBAL STATUS. Or, better yet, by using a tool that is able to summarize and prevent that data in a more friendly and efficient manner. innotop and mytop both do that. (I wrote mytop but fully admit that innotop is more feature rich and frequently maintained. But either will handle the basics.) Sorting the running queries by execution time is often revealing.

To find out about slow queries I’m going hope that the slow query log is enabled and the server has a sane long_query_time. But even the default of 10 seconds is helpful in truly bad situations.

I’ll also want to glance through MySQL’s error log to make sure nothing bad-looking has started to appear.

With that out of the way, I’ll generally dive into the storage engine statistics. And nowadays that almost always means asking InnoDB to cough up some information with SHOW ENGINE INNODB STATUS. While there’s a wealth of information to process in that output, I’m mainly interested in a few high-level stats at the end.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 23904405026; in additional pool allocated 37084160
Buffer pool size   1310720
Free buffers       1
Database pages     1274443
Modified db pages  770518
Pending reads 1
Pending writes: LRU 0, flush list 1, single page 0
Pages read 733227814, created 65128628, written 1679994934
98.63 reads/s, 3.09 creates/s, 227.34 writes/s
Buffer pool hit rate 999 / 1000

I’m usually keen to see that the buffer pool hit rate is good. The closer to 1000 / 1000 things get, the happier I am. I also want to see how mange pages are being read and written per second. If either of those seems high, it should correspond to a high volume of I/O seen earlier in iostat.

The Culprit?

In nine out of ten cases, the culprit is obvious by now–or was obvious half way through this exercise. The reality is that most of the time changes that are very easy to spot are responsible for a sudden decline in performance. The trick is this: you have to be looking in order to see them!

Silly as that sounds, it’s been my experience that most problems go undetected until someone complains–especially in smaller IT groups where there isn’t someone regularly looking after the MySQL server that runs just fine 99.9% of the time. And since it fails so infrequently, nobody bothers to setup decent monitoring or performance logging infrastructure to detect problems before end users are impacted.

Sound familiar?

Just a Start…

The tasks I’ve presented here are just the beginning. Once you’ve got a good sense of what’s happening on a MySQL server, you can really start to dig in and think about how to improve the situation. Do you add or change hardware? Modify the application? Adjust some of MySQL or InnoDB’s run-time or start-time parameters?

Comments on "Quick and Dirty MySQL Performance Troubleshooting"

Just beneath, are quite a few entirely not related web sites to ours, having said that, they are surely worth going over.

That could be the end of this report. Here you will obtain some web sites that we feel you will enjoy, just click the links.

Every as soon as inside a while we decide on blogs that we read. Listed below would be the most current sites that we select.

Below you will uncover the link to some web-sites that we consider you’ll want to visit.

Although internet websites we backlink to below are considerably not connected to ours, we really feel they’re basically really worth a go by, so have a look.

Here is a good Weblog You might Locate Interesting that we encourage you to visit.

Here are a few of the internet sites we recommend for our visitors.

The time to study or stop by the content or websites we have linked to below.

The info mentioned within the report are several of the most effective accessible.

Here are some links to sites that we link to simply because we believe they may be really worth visiting.

Here are a few of the web-sites we suggest for our visitors.

Just beneath, are various entirely not connected web pages to ours, on the other hand, they may be surely really worth going over.

Here are some links to sites that we link to for the reason that we consider they’re really worth visiting.

Please take a look at the web sites we stick to, including this 1, as it represents our picks in the web.

Please pay a visit to the web-sites we adhere to, such as this a single, as it represents our picks through the web.

We like to honor many other online web sites around the net, even when they aren?t linked to us, by linking to them. Underneath are some webpages really worth checking out.

The time to study or take a look at the content or internet sites we have linked to beneath.

Hi there, just became aware of your blog through
Google, and found that it’s really informative. I’m going to watch out
for brussels. I will be grateful if you continue this in future.
Numerous people will be benefited from your writing.
Cheers!

We like to honor quite a few other net sites on the internet, even when they aren?t linked to us, by linking to them. Under are some webpages really worth checking out.

Excellent read, I just passed this onto a colleague who was doing a little research on that. And he actually bought me lunch since I found it for him smile Thus let me rephrase that: Thanks for lunch!

One of our guests not too long ago proposed the following website.

We came across a cool internet site that you could delight in. Take a search in the event you want.

Here are some hyperlinks to web-sites that we link to due to the fact we assume they may be really worth visiting.

Here are some links to internet sites that we link to for the reason that we think they are worth visiting.

Very handful of sites that come about to become detailed beneath, from our point of view are undoubtedly effectively really worth checking out.

Here are some hyperlinks to web-sites that we link to for the reason that we believe they may be worth visiting.

Very few internet sites that come about to be in depth below, from our point of view are undoubtedly nicely really worth checking out.

Here are several of the web-sites we advocate for our visitors.

Here are some of the web-sites we advocate for our visitors.

Always a huge fan of linking to bloggers that I love but really don’t get quite a bit of link adore from.

Wonderful story, reckoned we could combine a few unrelated information, nonetheless seriously really worth taking a search, whoa did 1 discover about Mid East has got far more problerms as well.

Usually posts some really fascinating stuff like this. If you?re new to this site.

Here are several of the websites we suggest for our visitors.

Leave a Reply