Get MySQL Status - but only some values

Written by - 2 comments

Published on - 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

Show form to leave 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.


RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder