Get MySQL Status - but only some values

Written by Claudio Kuenzler - 2 comments

Published on November 28th 2012 - Listed in Database Linux Shell MySQL

MySQL has an embedded status information, which can easily be checked by launching:

mysql> show status;

This command outputs a lot of information (381 rows in my case) about ongoing and historical data. The only problem is, that most of the information is not the relevant one. For example if I want to check for the amount of connections since start, I would have to go through the whole output.

Of course there are two (or more) solutions. The first one is to output the mysql command to shell and grep it from there:

mysql -e "show status" | grep Connections
Connections     274026146
0m0.016s

This is not that smart though because the MySQL query itself still needs to query the whole table, instead of only one value. This can be done with a LIKE in the statement:

mysql -e "show status LIKE 'Connections'"
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| Connections   | 274062338 |
+---------------+-----------+
0m0.014s

To combine multiple status variables, a query like the following can be used:

mysql -e "show status WHERE Variable_name LIKE 'Innodb_rows%' OR Variable_name LIKE 'open%' OR Variable_name LIKE 'Connections'"
+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| Connections              | 274088826    |
| Innodb_rows_deleted      | 684775       |
| Innodb_rows_inserted     | 150929209    |
| Innodb_rows_read         | 646484380358 |
| Innodb_rows_updated      | 11352849     |
| Open_files               | 301          |
| Open_streams             | 0            |
| Open_table_definitions   | 400          |
| Open_tables              | 512          |
| Opened_files             | 8807760      |
| Opened_table_definitions | 0            |
| Opened_tables            | 0            |
+--------------------------+--------------+
0m0.013s

It's definitely faster than the grep solution:

mysql -e "show status" | egrep "(Innodb_rows|open|Connections)"
0m0.023s

Add a comment

Comments (newest first)

alexs77 from Zürich wrote on Jan 11th, 2013:

All those "short queries" (like eg. "show status" or "show processlist") are nowadays "legacy shortcuts" to queries dealing with the "INFORMATION_SCHEMA" meta database.

That's good to know, because "show processlist" cannot be used together with a "WHERE" statement, limiting output to eg. some user.

mysql> SHOW PROCESSLIST where User = 'root';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where User = 'root'' at line 1

# mysql --version
mysql Ver 14.14 Distrib 5.1.66, for portbld-freebsd8.3 (amd64) using 5.2

If you use queries dealing with INFORMATION_SCHEMA directly, it's no problem at all, because they are basically normal queries.

# mysql -e "select * from INFORMATION_SCHEMA.PROCESSLIST where USER = 'root';"
+---------+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+---------+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
| 7634817 | root | localhost | NULL | Query | 0 | executing | select * from INFORMATION_SCHEMA.PROCESSLIST where USER = 'root' |
+---------+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+

C from wrote on Dec 2nd, 2012:

With MySQL there's also REGEXP:

$ time mysql -e "show status where Variable_name regexp '^(connections|innodb_rows|open)';"
[...]
real 0m0.011s

The query string is shorter, but it's only 0.002s faster than the egrep solution.