SHOW STATUS WHERE….
Note: This article is about the WHERE extension to SHOW. I specifically use SHOW STATUS as an example, but WHERE is an extension to many SHOW statements.
Often DBAs will assess the health of a system by looking at some of the status variables returned by SHOW GLOBAL STATUS (specifying GLOBAL is important; remember that SHOW STATUS is the same as SHOW SESSION STATUS).
There are many status variables that SHOW GLOBAL STATUS returns. (SHOW GLOBAL STATUS on a Windows machine, MySQL version 5.0.67 returned 249, 5.1.22 returned 256 and 6.0.6-alpha returned 295 status variables!). I have used the SHOW STATUS LIKE syntax to help give me the output I really want, particularly when I forget the exact names of the status variables I am looking for.
But I did not know of a way to perform SHOW STATUS NOT LIKE or have any other means of filtering the information. Until today, when I was reading up on SHOW STATUS.
The 5.0 manual page for SHOW STATUS states:
The
WHEREclause can be given to select rows using more general conditions
and links to the manual page for extensions to SHOW.
Well, I immediately started testing this out. SHOW GLOBAL STATUS only returns two columns, Variable_name and Value, so the WHERE clause can only filter on those.
SHOW STATUS has over 100 Com_ variables, and over 20 ssl variables. I don’t often use those when looking at output, so I can run:
SHOW GLOBAL STATUS WHERE Variable_name NOT LIKE "%ssl%" AND Variable_name NOT LIKE "com\_%";
(note that I escaped the _ because _ is the wildcard character for one position. If I had not escaped _, the status variable Compression would have been excluded as well).
I can also find all the “important” numbers by running:
SHOW GLOBAL STATUS WHERE Value>0;
Or, even better, all the values that are not 0 – including the text values:
SHOW GLOBAL STATUS WHERE Value!='0';
Using the string ‘0′ will cast Value as a string and then compare to the string ‘0′. Your mileage may vary given different sql modes.
Category: Group Blog Posts, MySQL
Tags: extensions, show, show status, show variables
For the SHOW command, LIKE seems to be more efficient with the handler than WHERE.
Compare the incremental output of:
show global status like ‘Handler_read_rnd_next’;
show global status where Variable_name=’Handler_read_rnd_next’;
You can see by the output there that WHERE seems to act more like HAVING. Unlikely to matter when just ad-hoc investigating something, but might make a minor impact if you script something to run every second.
Thanx! This is very important information, so I’ll show some examples:
mysql> show global status like ‘Handler_read_rnd_next’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Handler_read_rnd_next | 13010 |
+———————–+——-+
1 row in set (0.00 sec)
mysql> show global status where Variable_name=’Handler_read_rnd_next’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Handler_read_rnd_next | 13012 |
+———————–+——-+
1 row in set (0.08 sec)
That’s a HUGE performance difference. So yes, using LIKE when you can is useful. WHERE gives you a lot more POWER than LIKE, but it comes at a cost.
No need for like or where, simply implement that as an information_schema view, and you’ll automagically have a standard synthax which can be handled also by reporting tools.
Too difficult?
p –
A view definition statement can only take a SELECT statement, so if you mean we should do
CREATE VIEW v_status AS SHOW STATUS;
then that won’t work. Or did you have something else in mind?
He meant to use the information_schema.global/session_status tables. They aren’t available until MySQL 5.1.12, but other DBMSs have had them for a while.
http://dev.mysql.com/doc/refman/5.1/en/status-table.html
There’s really no advantage over using SHOW STATUS … WHERE since you aren’t going to join this table into another table. It will probably be even slower.
Brian –
Thanx for the clarification! I knew other systems had tables, but 5.1 isn’t in widespread use yet, so I wasn’t exposed to this.
[...] del SHOW STATUS WHERE en Pythian (en [...]