SHOW STATUS WHERE….
Sep 13, 2008 / By Sheeri Cabral
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.
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.
Pingback: ArtÃculos destacados de Septiembre | cambrico.net