How to determine why a MySQL server has become slow and requests sometimes time out?

Question

I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having.

Most of the time, responses are returned quickly from the server, but when I have certain scripts that make a lot of queries (both reading and writing data) running in the background, I'm finding that other requests are slow to receive responses, or are timing out entirely.

As an example, yesterday when a PHP script that makes a lot database queries was running, requests to the server for pages that made a number of queries before returning a response were timing out -- I couldn't even connect to the database with Navicat, as it timed out as well.

In the above example, I ran top/htop in an SSH console and found that there was very low CPU usage and memory usage was only at about 50%. Because of this, I think the problem lies with the database, but I'm having trouble understanding what is causing the issues, such as whether they might be due to:

• Slow queries
• Too many connections
• Too many queries per second
• Other potential issues I'm not aware of

I know about the slow query log, but none of my queries are very slow.

What methods or tools can I use to determine what is causing a MySQL database to become slow, sometimes with requests even timing out?

Answer 1

You need to tune your database. This is a complicated process with many variables and decision trees. Without specifics we can only really get you started here.

If you have a script that runs lots of SQL and slows down your DB, and your cpu and memory usage are fine while it's running, then you are probably running into I/O problems. i.e. You are hammering the disk. You need to look through the SQL used in the script and tune it. For example, it might be generating lots of full table scans or creating too many transaction logs that are slowing down the DB. To start with, check if any unneccessary full table scans are taking place, and see if you can add indexes to the relevant columns to reduce the I/O. then report back and we can go from there.

The above linked MySQLtuner script is excellent but understanding the results may be challenging for you if you are inexperienced with databases. Do read the disclaimers on the main page before you get started: https://github.com/major/MySQLTuner-perl

Answer 2

You must find the real source of your bottleneck.

Memory usage only at 50%? Maybe you don't give mysql enough memory. Try the MySQLTuner Skript. It might point you out what is wrong with your server.

Check whether only one core is used and you have a lot of cores - this could lead to a false impression that the cpu usage is low.

Answer 3

To help you diagnose your the problem, Percona has the percona-toolkit collection which can be used for free. In particular, you might want to use pt-stalk to diagnose issues faced by your server. The output generated from these tools will help you determine what to tune or how to tune your server.

They also have Percona Cloud Tools which is a hosted service providing access to query performance insights for all MySQL uses. Although this service is in beta, I would recommend you try it.

Answer 4

Have you tried using the MySQL tools http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and running some of the options such as IO Reads/Writes, check buffer reads etc. As you say that the problems occur when running background processes can you check buffers or have a look at the possibility of file locks. It is difficult to answer not knowing what the background tasks do, straight selects compared to updates or deletes.

You mention a script running a lot of queries causing time outs. Does this script open and close connections properly, does it keep the queries tidy by completing one at a time or are you running multiple loops? Just because the memory/processor isn't working it doesn't mean that the PHP isn't running out of memory as well as the application, MySQL or your web server.

Just try one at a time until you get faster operations, and then implement any changes across all your work.

Answer 5

In my experience,

-You can try make a simple script. So If your script(PHP) Can solve the Problem, dont use database to do it.

-Dont To many create Procedure on your Mysql. Because it can decrease Performance.

SO you can try.