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"

That will be the finish of this report. Here you will discover some web-sites that we believe you will value, just click the hyperlinks.

Very couple of 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.

We prefer to honor quite a few other world-wide-web web sites around the web, even though they aren?t linked to us, by linking to them. Beneath are some webpages worth checking out.

Every the moment in a when we select blogs that we study. Listed beneath would be the most recent sites that we pick out.

The time to study or take a look at the material or internet sites we’ve linked to below.

Major thanks for the blog article.Really looking forward to read more. Will read on…

I am really enjoying the theme/design of your blog.

Do you ever run into any web browser compatibility problems?
A few of my blog visitors have complained about my site not working correctly in Explorer but looks great in Opera.
Do you have any suggestions to help fix this issue?

The time to read or go to the subject material or web sites we’ve linked to beneath.

xxpZd1 khxchgxhssms, [url=http://lrmqwgxpcmzq.com/]lrmqwgxpcmzq[/url], [link=http://blscofmumrez.com/]blscofmumrez[/link], http://tsjtrfauzfuf.com/

Just beneath, are a lot of absolutely not connected web-sites to ours, even so, they are certainly worth going over.

The information and facts mentioned inside the post are several of the top available.

Here is a great Weblog You might Uncover Fascinating that we encourage you to visit.

We prefer to honor many other world-wide-web sites around the web, even though they aren?t linked to us, by linking to them. Below are some webpages really worth checking out.

Here is a great Weblog You might Locate Intriguing that we encourage you to visit.

Check below, are some absolutely unrelated internet sites to ours, even so, they’re most trustworthy sources that we use.

Here is a good Weblog You may Locate Exciting that we encourage you to visit.

One of our visitors recently proposed the following website.

Very couple of web-sites that transpire to be detailed beneath, from our point of view are undoubtedly nicely really worth checking out.

We came across a cool web-site which you could possibly delight in. Take a look for those who want.

Below you?ll come across the link to some web sites that we assume you must visit.

One of our guests a short while ago encouraged the following website.

Here are some links to sites that we link to simply because we feel they’re really worth visiting.

Very few web-sites that happen to become comprehensive below, from our point of view are undoubtedly effectively worth checking out.

Here is a good Weblog You might Uncover Exciting that we encourage you to visit.

Although websites we backlink to below are considerably not related to ours, we feel they’re essentially worth a go by, so have a look.

Below you?ll discover the link to some sites that we think you should visit.

We came across a cool web page which you might enjoy. Take a search when you want.

Sites of interest we’ve a link to.

We came across a cool web-site that you just may delight in. Take a search for those who want.

Just beneath, are several completely not associated internet sites to ours, even so, they may be certainly worth going over.

That is the finish of this post. Here you?ll locate some web sites that we think you will enjoy, just click the links.

Wonderful story, reckoned we could combine a few unrelated data, nonetheless seriously worth taking a appear, whoa did one master about Mid East has got extra problerms too.

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

Check below, are some absolutely unrelated sites to ours, however, they’re most trustworthy sources that we use.

We like to honor lots of other online websites around the internet, even when they aren?t linked to us, by linking to them. Underneath are some webpages really worth checking out.

We prefer to honor many other world-wide-web internet sites on the web, even when they aren?t linked to us, by linking to them. Below are some webpages worth checking out.

That could be the finish of this post. Here you?ll uncover some web sites that we assume you?ll enjoy, just click the links.

Below you will discover the link to some web-sites that we consider you ought to visit.

Wonderful story, reckoned we could combine a handful of unrelated information, nonetheless truly really worth taking a appear, whoa did a single understand about Mid East has got a lot more problerms also.

The time to study or check out the content or websites we’ve linked to below.

That may be the finish of this report. Here you will come across some websites that we believe you will enjoy, just click the hyperlinks.

Just beneath, are various absolutely not connected web pages to ours, even so, they may be surely really worth going over.

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

Here are some hyperlinks to web-sites that we link to because we consider they may be worth visiting.

The time to read or visit the subject material or web sites we have linked to beneath.

That is the end of this post. Right here you will discover some sites that we consider you?ll enjoy, just click the hyperlinks.

Although sites we backlink to below are considerably not related to ours, we feel they may be in fact really worth a go by means of, so possess a look.

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

Leave a Reply