SHOW STATUS WHERE….

Posted in: Technical Track

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 WHERE clause 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.

Interested in working with Sheeri? Schedule a tech call.

7 Comments. Leave new

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.

Reply

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.

Reply

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?

Reply

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?

Reply
Brian Papantonio
September 17, 2008 12:59 pm

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.

Reply

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.

Reply
Artículos destacados de Septiembre | cambrico.net
October 6, 2008 1:30 pm

[…] del SHOW STATUS WHERE en Pythian (en […]

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *