How to use the SHOW STATUS command in MySQL

by Guest Contributor | May 21, 2004 7:00:00 AM

Tags: Guest Contributor

Takeaway: If you're trying to obtain real-time performance statistics on a MySQL database, this is where you start. Learn how to use SHOW STATUS to find the statistics you need.

MySQL records a plethora of data about how it is performing. In order to see a sample of the kind of information MySQL makes available, log in to the server using the MySQL client and issue the SHOW STATUS command:

mysql> SHOW STATUS;

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 1     |
| Aborted_connects | 0     |
...

| Threads_created          | 25    |
| Threads_connected        | 1     |
| Threads_running          | 1     |
| Uptime                   | 4495  |
+--------------------------+-------+
131 rows in set (0.00 sec)

If you're using MySQL 4.x, you'll see over 130 variables, a list which scrolls for many pages. To see a shorter list, filter the list by adding a LIKE clause:

mysql> SHOW STATUS LIKE 'threads%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_created   | 25    |
| Threads_connected | 1     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Each of the statistics has a special meaning in the MySQL context, so let's look at some of the more useful and important ones.

How long has your MySQL database been running?

The Uptime statistic shows you, in seconds, how long since the server was last restarted. The Questions statistic counts the total number of queries sent to the server, while Connections counts the total number of connection attempts. If you combine it with the Uptime statistic you can calculate arcane values like the average queries per day or connections per hour.

To obtain information on the number of INSERTs, UPDATEs, and DELETEs executed on the server, examine the values of the Handler_write, Handler_update, and Handler_delete statistics. Again, you can divide by the Uptime value to obtain per-hour or per-day statistics, if that's something you need.

To obtain information on the number of times a particular command was executed, look for the Com_* statistics, where * is the command name. For example, the Com_show_databases stores the number of times the SQL command SHOW DATABASES has been executed since the server was last restarted.

Some of this basic information can also be retrieved using the command-line mysqladmin status tool:

$ mysqladmin status

Uptime: 4661  Threads: 1  Questions: 200  Slow queries: 0  Opens: 16  Flush

tables: 1  Open tables: 6  Queries per second avg: 0.043

---

Read more about performance statistics in MySQL

"Understanding real-time performance statistics in MySQL"

"Understanding the performance statistics from SHOW STATUS"

"How to examine and kill MySQL client processes"

"Specialized status commands in MySQL"

---