Use MySQL monitoring to optimize performance

  

 Email Article  

Tweet  

  

MySQL monitoring is one of the most complex and important tasks a database administrator has to perform. Problems in MySQL can affect entire business processes. MySQL data is vulnerable to nonrecoverable errors, so DBAs must check status variables from the server and examine logged events. Based on this data, administrators and developers can also optimize server variables and databases accordingly. Here are some basic ways to keep an eye on your installation's data integrity.

You can connect to MySQL using many different text-based and graphical tools. For this article I'll use the native MySQL terminal client, which is available on admin stations and servers without a graphical environment and provides the fastest and most reliable way to connect to a MySQL server. In Centos the MySQL terminal is available through the MySQL client package, which is always installed as a dependency along with the MySQL server. Some of my examples use the Employees Test DB on Launchpad, which is frequently referred to in the official MySQL documentation. It contains tables with enough varying data to allow you practicing MySQL administration and development.

You can start the MySQL client either remotely or locally. You have to provide a username, password, and hostname (necessary for remote administration) when you start the client. For example, to start the client as the admin user (root) and initiate a connection to a server with an IP address of 10.0.0.1, the command would be /usr/bin/mysql -h 10.0.0.1 -u root -p. To avoid security problems and misconfigurations you may also create a separate user with no privileges only for executing this command.

Monitoring from the MySQL terminal is built around the SHOW command. SHOW GLOBAL STATUS, for example, shows all the common statistical information from the server. Using SHOW you can read more than 290 global server indicators for anything from incorrectly closed connections (Aborted_clients) to the server's uptime (Uptime).

Every server status indicator could be useful in meeting your monitoring and troubleshooting needs, so you should acquaint yourself with them all in the official MySQL manual. Some of the most important ones are as follows.

• Aborted_clients shows incorrectly closed connections, which indicate badly written or poorly functioning applications that exceed the server's timeout limit or do not close connections properly. This may lead to various application problems such as slowness or incomplete presentation of data. Start investigating it from the application's code in the part where the MySQL connection is closed.
• Aborted_connects shows when a connection could not be established because of incorrect login information. This indicator usually means that an application is not configured properly. A very high number may indicate someone trying to brute-force authenticate to the MySQL server.
• Bytes_received and Bytes_sent show the traffic to and from all the clients. Network bottlenecks are not common problems in MySQL, but it is always worth knowing how much bandwidth the MySQL service consumes.
• Com_* indicators are the counters of the common MySQL data commands such as select, delete, and update. The values show how many times each operation has been performed since the server startup. These counters are useful for analyzing what kind of operations are most common for the server so that you can optimize it properly. For example, if select commands dominate, you should optimize the server for faster reads, create indexes, and especially increase its query cache.
• Innodb_* indicators show specific InnoDB-related statistics that can be used for tweaking the InnoDB server engine and InnoDB table settings.
• Max_used_connections shows the highest number of MySQL connections, successful and unsuccessful, which may indicate bottlenecks and a need to increase the server variable max_connections.
• Slow_queries is the number of queries whose execution time has exceeded the server's variable long_query_time, which is 10 seconds by default. Even a few slow queries are alarming and should be investigated.

To obtain server variable values such as the ones I just mentioned, run the command show global variables;. If needed, you can change variable values permanently by editing the file /etc/my.cnf. Place configuration directives and values in the [mysqld] section of the file.

To dive deeper into MySQL monitoring and find information about specific tables, use the command SHOW TABLE STATUS FROM database_name \G. An example result row for the employees test database looks like this:

           Name: salaries
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2844513
 Avg_row_length: 35
    Data_length: 100270080
Max_data_length: 0
   Index_length: 36241408
      Data_free: 22020096
 Auto_increment: NULL
    Create_time: 2012-10-19 11:53:27
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 

Two indicators here are especially important for monitoring: Data_length shows size of the table in bytes, while Index_length shows the index's size in bytes. Querying these two values is the most precise way to find out how large your data and its indices are at any given time so you can follow their expansion later.

One must-know SHOW command that could save your day is SHOW PROCESSLIST, which gives essential information about the current MySQL tasks:

+----+---------+---------------------+-----------+---------+------+-------------------+-----------------------------------------------+
| Id | User    | Host                | db        | Command | Time | State             | Info                                          |
+----+---------+---------------------+-----------+---------+------+-------------------+-----------------------------------------------+
|  8 | root    | localhost           | employees | Query   |   64 | Locked            | insert into salaries (select * from salaries) |
| 13 | root    | localhost           | NULL      | Query   |    0 | NULL              | show processlist                              |
| 15 | anatoli | 192.168.1.100:37383 | employees | Query   |   85 | copy to tmp table | ALTER TABLE `salaries` DROP PRIMARY KEY       |
+----+---------+---------------------+-----------+---------+------+-------------------+-----------------------------------------------+

The meaning of most of the values above is self-explanatory, but the State column requires special attention. The process with id 8 is in Locked state, which means it is blocked by another process and waiting for it to finish. In this case we know the locking process has id 15, because the statement ALTER TABLE always acquires READ-LOCK on a table, which means other processes can read data from the table but cannot alter it in any way. In applications, locked tables lead to timeouts, errors, and downtime.

To avoid downtime and application problems you may be tempted to kill a long-running process with the command KILL Id. However, this is not always a good idea, because data can be corrupted by killing a process that's manipulating it, and that's especially true with an ALTER TABLE process. Before manually killing a MySQL process you should find out the impact of its premature termination.

Many SHOW commands are not commonly used but may be useful depending on your monitoring needs. For example, if you run MySQL replication, you'll be interested in monitoring its state. The most important command for monitoring replication is SHOW SLAVE STATUS, which shows the slave's current state and the indicator Seconds_Behind_Master. Ideally, that indicator is equal to zero, meaning the slave is fully synchronized with the master and there are no replication errors. You should analyze any other value you see.

To see all SHOW commands, not all of which are for monitoring, use the command HELP SHOW.

Monitoring the MySQL slow log

The MySQL slow log gives you information about processes whose execution times exceeded the server's variable long_query_time. By default, the queries are not logged to a file; to have MySQL do so, edit the /etc/my.cnf configuration file and add the directive log_slow_queries = /var/log/mysql-slow.log. Make sure that the file exists and the mysql user can write to it. For this change to take effect you must restart the MySQL service with the command service mysqld restart.

A sample excerpt from the slow log looks like this:

# Time: 121028 13:17:24
# User@Host: root[root] @ localhost []
# Query_time: 194.052534  Lock_time: 65.396478 Rows_sent: 0  Rows_examined: 5687748
insert into salaries (select * from salaries);

In this log entry, Query_time indicates in seconds how long a query has run; the more time, the worse for performance. Lock_time shows how much time the query has waited to acquire a lock on the table – in this case more than a minute. This can have an extremely negative impact on performance and stability. The final line shows the query that resulted in the log entry.

The above slow query is just a test one that I specially crafted to be slow, so there's not much to optimize in it, but real-world slow production queries should be investigated and taken care of. How do you start to do that?

Dealing with the slow queries is a complex process, but here are a few directions to explore. Start with the application code. Perhaps a slow query can be limited to a subset of the original data, especially by using an indexed data column. After that try examining the table data, its structure and state, and perform normalization, add indexing, and defragment. Next, try tweaking the MySQL service configuration with a readily available tool such as the MySQL tuner. Finally if nothing else helps, enhance the server's hardware – add more RAM, faster media, or more CPUs.

Once you've mastered the above basic MySQL monitoring techniques you'll have a good start on building your own customized MySQL monitoring strategy. It's not enough to check these indicators once in a while or when there is a problem; instead, record the values and graph the results over a period of time. This will allow you to follow trends, understand tendencies, and evaluate the results of your optimization work.