DBAs of all countries, unite!

I’m observing the process of most awesome SHOW commands being abolished, destroyed and some weird information_schema tables are introduced instead.

Say, even though you can select configuration variables using @@syntax, you can’t do same for much more interesting to DBAs status variables in any more interesting logic.

Apparently instead of doing

SHOW STATUS LIKE "questions"

one has to do this now (I’m being dramatic here, above hasn’t been removed yet, but hasn’t been expanded for better usage either):

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME="QUESTIONS"

Do note, those SQL standard followers will get caps-lock button swapped with space bar soon.

Of course, we, DBAs, know that one can simplify stuff by creating stored routines:

CREATE FUNCTION `gstatus`(v varchar(64)) returns varchar(1024)
return
( SELECT variable_value
  FROM information_schema.global_status
  where variable_name=v LIMIT 1
)

So we can do such simple things as:

mysql> select m.gstatus("questions");
+------------------------+
| m.gstatus("questions") |
+------------------------+
| 140                    |
+------------------------+
1 row in set (0.00 sec)

Of course, this leads to solution of one of most common DBA problems, how to get decent status variable values per time:

CREATE PROCEDURE m.report(in timer float)
begin

DROP TEMPORARY TABLE IF EXISTS status_old;
CREATE TEMPORARY TABLE status_old
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS;

SELECT SLEEP(timer) into @x;
SELECT
    s.variable_name status,
    (s.variable_value-o.variable_value)/timer value
FROM INFORMATION_SCHEMA.GLOBAL_STATUS s
    JOIN status_old o USING (variable_name)
WHERE s.variable_value>0;

DROP TEMPORARY TABLE status_old;

end

So, the “show me changes-per-second for values in last 0.5s” would look like this:

ysql> call m.report(0.5) //
+-----------------------------------+---------+
| status                            | value   |
+-----------------------------------+---------+
| ABORTED_CLIENTS                   |       0 |
| ABORTED_CONNECTS                  |       0 |
| BYTES_RECEIVED                    |  532662 |
| BYTES_SENT                        | 1140894 |
...
| QUERIES                           |    2884 |
| QUESTIONS                         |    2878 |
| SELECT_FULL_JOIN                  |       2 |
| SELECT_RANGE                      |     196 |
| SELECT_SCAN                       |     146 |
...
| THREADS_CACHED                    |      12 |
| THREADS_CONNECTED                 |     -28 |
| THREADS_CREATED                   |       4 |
| THREADS_RUNNING                   |      -2 |
| UPTIME                            |       2 |
| UPTIME_SINCE_FLUSH_STATUS         |       2 |
+-----------------------------------+---------+
125 rows in set (0.53 sec)

Query OK, 0 rows affected, 1 warning (0.54 sec)

So, by spending five minutes on writing very simple INFORMATION_SCHEMA procedure we can resolve one of usual nightmares in MySQL DBA environments.

I can get back now to the initial idea of this post – if one DBA can write such small neat thing in few minutes, would you imagine how useful can a collaboratively built repository of DBA-assisting stored procedures in functions, and how we can spit at all the SQL standard verbosity, and make our systems easy to manage? :) I think we shouldn’t allow not to share such utilities, as widespread use and “expect it already there” would make overall work much much easier. Let’s use and reuse (and someone should set up framework for building such thing ;-))

This entry was posted in mysql and tagged , . Bookmark the permalink.

5 Responses to DBAs of all countries, unite!

  1. Anders Karlsson says:

    One reason I tend to like the I_S views better than the SHOW commands is that it just allows me to do more things. Like saving the status in another table with a simple INSERT INTO … SELECT FROM. And to do some more advanced filtering.
    Be that as it may, to explore the new features that the I_S schema allows, some programming is necessary. I have used this on my Joomla site, exploring this, and combining it with EVENTS so that I can easily save of status values at regular intervals.
    I blogged about it here:

    http://karlssonondatabases.blogspot.com/2009/01/putting-5x-features-to-good-use-of.html

    /Karlsson

  2. Mark Leith says:

    Ever heard of MySQL Forge? :P

    http://forge.mysql.com

    I hear it’s pretty handy for this stuff! :)

  3. forge doesn’t facilitate release management and file deliveries too well.
    it has to come as big package of stuff, not some random SPs!

  4. Mark Callaghan says:

    MySQL 5.0 forever! Or in your case, MySQL 4.0 forever!

    I appreciate the ability to use SQL when accessing this data and do more. But I also like to type less when doing simple things.

  5. well, in one way or another, shortcuts will be mandatory for most of information_schema or performance_schema data. it is up for DBA community to come up with those shortcuts, and maybe even CLI tools will evolve to better facilitate them…

Comments are closed.