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

database-tuningMySQLperformance

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?

Best Answer

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

Related Solutions

Mysql innoDB write operations are extremely slow

innodb_flush_log_at_trx_commit = 2 for better speed (=1 for better safety)
sync_binlog = 0 if you are not using the binlog.
"Batch" INSERTs — many rows in a single INSERT statement. (If you have lots, do 100 at a time.) LOAD DATA is also excellent for loading.
innodb_buffer_pool_size = 70% of available RAM.
auto_commit = 1, or use BEGIN ... COMMIT
thread_stack = 256K I'm surprised it did not crash with your small value.

Still, the times (1s-6s) are grossly unreasonable. Even with everything set "wrong", 0.1s for a simple INSERT should be expected.

Is this running in a "cloud"? Is something else going on?

Mysql – How to determine MySQL queries per day

For SELECTs:

show global status like "Com_select";

UPDATEs:

show global status like "Com_update";

INSERTs:

show global status like "Com_insert";

DELETEs:

show global status like "Com_delete";

ALl values are "cumulativ" since MySQL last restart.

So to get your SELECTs in one hour:

At 9pm:

[21:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 671664 |
+---------------+--------+
1 row in set (0.00 sec)

At 10pm:

[22:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 672363 |
+---------------+--------+
1 row in set (0.00 sec)

The number of SELECT in the past hour : 672363 - 671664 = 699

Best Regards

Best Answer

Related Solutions

Mysql innoDB write operations are extremely slow

Mysql – How to determine MySQL queries per day

Related Question