Peering Under the Hood, Part One

On a busy server, it's often hard to keep track of what's running and when, so from time to time, you may find yourself wondering what MySQL is doing. Luckily, MySQL provides a degree of transparency that makes it relatively easy to peer inside and see what's up.

On a busy server, it’s often hard to keep track of what’s running and when, so from time to time, you may find yourself wondering what MySQL is doing. Luckily, MySQL provides a degree of transparency that makes it relatively easy to peer inside and see what’s up.

Who and What

One of the most basic questions that you’re likely to ask is, “Who’s connected to MySQL and what are they doing?” Now, you could fall back on classic Unix tools such as netstat to figure out which clients have connections open to TCP port 3306, but that would tell you only which machines are connected — not necessarily who is connected. The best way to track down who’s connected to MySQL is to use MySQL’s own SHOW PROCESSLIST command. Like most of MySQL’s SHOW commands, it produces easy to read tabular output, as shown in Figure One.




Figure One


+——–+——————+———————-+————+————————–+
| Id | User | Host | db | Command | Time | State | Info |
+——–+——————+———————-+————+————————–+
| 17 | jzawodn | localhost | NULL | Query | 0 | NULL | show |

In Figure One, you’ll see one “record” for each connected user, detailing who the user is, what they’re doing, and so on. Here’s what the columns mean:

* ID. This is the “thread id” for the given connection. This uniquely identifies the connection, much like a process id does for a process on Linux.

* USER. The name of the user (from MySQL’s point of view) using this connection.

* HOST. This is either the IP address or the hostname from which the user is connected.

* DB. The name of the database the user is connected to. NULL indicates that no database has been selected yet.

* COMMAND. This is the internal “command” or function that signifies what MySQL is doing in response to the user’s request. Look below for a full command list.

* TIME. The number of seconds that the connection has been in the current state. For example, if this value is 10 and MySQL is running a query, that means the query has been running for 10 seconds.

* STATE. This field contains extra information about what the thread’s doing.

* INFO. When executing a query, this field contains the first 100 characters of the query being executed. (You can get the full query by executing SHOW FULL PROCESSLIST).

Okay, when asked, MySQL spits out a bunch of information about who’s connected, which database they’re using, and what query (if any) they’re running. But what can you really do with this information?

Playing Big Brother

As it turns out, monitoring tools find information like this quite useful. For instance, if you wanted to provide a chart that depicted the relative popularity of a database on a given MySQL server over time, the information might come from the output of SHOW PROCESSLIST. Given a bit of time, you can probably come up with a number of other useful bits that’d be worth monitoring in some way.

Beyond passive monitoring, MySQL’s process list can come in very handy when it’s time to figure out who’s hogging the database. When MySQL seems to slow down for no apparent reason, login as root and check the process list. There’s a good chance you’ll either see a lot of activity, or a very small number of running queries that have been running more than a few seconds. In the former case, you simply have a lot of users banging on your database. This can happen during busy times of the day. In the latter, you have someone running a very intensive query.

Unfortunately, this isn’t as easy as it could be. Though they produce tabular output, MySQL’s SHOW commands aren’t like normal SQL commands in other ways. You cannot apply most filtering or sorting options. In other words, you cannot do…


SHOW PROCESS LIST ORDER BY TIME DESC LIMIT 5;

… to get the top five longest running queries. Instead, you’ll either have to manually scan the list, write a script to do it, or find some other tool to help you. Try mytop (http://jeremy.zawodny.com/mysql/mytop), because it acts a lot like the Unix top utility, which should be familiar to any Linux administrator. Whatever tool you choose, you’ll ultimately be able to find out which queries are hogging resources and who’s running those.

Counters

Speaking of monitoring MySQL servers, network and system administration geeks are fond of using tools like Cricket (http://cricket.sourceforge.net) or RRDTool (http://people.ee.ethz.ch/~oetiker/webtools/rrdtool) to monitor the various statistics that SNMP-capable network devices produce. While MySQL doesn’t natively speak SNMP natively, it does expose a wealth of internal counters and values, many of which are worth collecting and charting over time.

MySQL’s SHOW STATUS command produces a two column list of name/value pairs that represent a variety of different “events” that occur within MySQL. This is shown in Figure Two.




Figure Two


mysql> SHOW STATUS;
+———————————-+——————+
| Variable_name | Value |
+———————————-+——————+
| Aborted_clients | 0 |
| Aborted_connects| 17 |
| Bytes_received | 105618 |
| Bytes_sent | 7321730 |

In modern versions of MySQL, the list is quite extensive.

Let’s focus in on a few groups of the counters that tend to be most useful. (It’s worth noting that these counters are unsigned integers. On 32-bit hardware, that means they’ll “wrap” at just over 4.2 billion. Beware.)

* Bytes_received and Bytes_sent track bytes that MySQL has sent and received on the network.

* Connections, Max_used_connections, and Threads_ connected count connection statistics. The first variable is the total number of connection requests the server has received. The second is the high water mark, indicating the maximum number of simultaneous connections the server has handled. The final counter tracks the current number of connections.

* Questions counts the number of “questions” that MySQL has answered. In a sense, you can think of this as queries, including SET, INSERT, UPDATE, DELETE, REPLACE, LOAD DATA, and all of the less frequently used commands, such as GRANT and SHOW.

* Uptime is uptime of the server given in seconds. This statistic turns out to be quite convenient. For example, by dividing Questions by Uptime you can compute the average number of queries per second. By doing this over shorter time intervals (every 1 or 5 minutes perhaps), you can easily chart how “busy” a server is over time and easily spot patterns in that data.

MySQL also tracks the number of times that it executes every command or query in counters that begin with Com_.

Again, this is just a small sampling of the useful data MySQL provides about what’s going on inside.

A full list of the status counters (sometimes call status variables) is available in the latest MySQL documentation online at http://dev.mysql.com/doc/mysql/en/Server_status_variables.html, as well as in the recently released book High Performance MySQL.



Jeremy Zawodny plays with MySQL by day and spends his spare time flying gliders in California. He is the author of High Performance MySQL, published by O’Reilly Media. Reach him at Jeremy@Zawodny.com.

Fatal error: Call to undefined function aa_author_bios() in /opt/apache/dms/b2b/linux-mag.com/site/www/htdocs/wp-content/themes/linuxmag/single.php on line 62