DevOps / Sys Admin Q & A #12 : Why Is the Database Slow?

bogotobogo.com site search:

Search Database Logs

One of the places we should look into when troubleshooting a database issue is the error log for that database (e.g. /var/log/mysql/error.log or /var/log/postgresql-9.3-main.log), particularly if there is a problem with the database starting.

The error log also often provides information about successful startup and syntax errors in queries sent to the database; these are particularly useful when we're debugging applications.

Is the Database Running?

We may also want to check whether the database is running and listening on the right port.

$ sudo service mysql status
mysql start/running, process 1472

$ ps -ef | grep mysql
mysql     1472     1  0 Nov28 ?        00:02:55 /usr/sbin/mysqld

$ sudo netstat -lnp | grep :3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1472/mysqld

Get Database Metrics

To pull metrics from MySQL, we'll use the mysqladmin tool:

$ mysqladmin -u root -p status
Uptime: 125776  Threads: 1  Questions: 146  Slow queries: 0  Opens: 100  Flush tables: 1  Open tables: 93  Queries per second avg: 0.001

The each value represents the following:

Uptime
The number of seconds the MySQL server has been running
Threads
The number of active threads (clients)
Questions
The number of questions (queries) from clients since the server was started
Slow queries
The number of queries that have taken more than long_query_time seconds
Opens
The number of tables the server has opened
Flush tables
The number of flush-*, refresh, and reload commands the server has executed
Open tables
The number of tables that currently are open
Queries per second avg
The average number of queries per second the database receives

To get more in-depth info:

$  mysqladmin -u root -p extended-status
Enter password: 
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 0           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 9964        |
| Bytes_sent                               | 12920       |
| Com_admin_commands                       | 2           |
...

Identify Slow Queries

Now, we may want to be able to identify any slow queries that take longer than a certain threshold.

When we can identify slow queries, we can then work on optimizing them to run faster on our database.

To enable slow query logging in MySQL, we will need to set two variables: log_slow_queries and long_query_time.

The log_slow_queries variable should be set to the file we want to log all of our slow queries and long_query_time should be set to the threshold in seconds for how long a query needs to take to be considered a slow query.

In the default /etc/mysql/my.cnf file that came with the MySQL installation, these settings were already present in the file; they were just commented out:

# Here you can see queries with especially long duration
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

Once we set both of these values, we will need to restart the MySQL process. When we do, it creates the slow query log.

Another way to identify problem MySQL queries is via the mysqladmin processlist command, which lists information about all the currently active MySQL processes.

$ mysqladmin -u root -p processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 52 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Loads - CPU, RAM, and I/O bound

If a database is generating high CPU-bound load, then we might be facing a bad SQL query that is using much more processing power than it should; if this is the case, we'll want to start tracking down slow queries.

If our load is RAM-bound, then we will either want to tune our database to take on fewer simultaneous queries or attempt to locate particularly expensive SQL queries that tie up RAM (or stop storing our database on our RAM disk).

If the load is IO-bound, then use tools like iotop to try to identify which specific process, and sysstat to locate which storage, is getting hit the most.