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.
/usr/bin/mysql -h 10.0.0.1 -u root -p
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).
SHOW GLOBAL STATUS
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.
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.
show global variables;
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:
SHOW TABLE STATUS FROM database_name \G
Create_time: 2012-10-19 11:53:27
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.
SHOW SLAVE STATUS
To see all SHOW commands, not all of which are for monitoring, use the command HELP SHOW.
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.
log_slow_queries = /var/log/mysql-slow.log
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.
Allowed tags: <a> link, <b> bold, <i> italics