SHOW STATUS WHERE….

Sep 13, 2008 / By Sheeri Cabral

Tags: ,

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.

7 Responses to “SHOW STATUS WHERE….”

  • ryan says:

    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.

  • Sheeri Cabral says:

    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.

  • p says:

    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?

  • Sheeri Cabral says:

    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?

  • Brian Papantonio says:

    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.

  • Sheeri Cabral says:

    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 […]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>