VIEWS on INFORMATION_SCHEMA tables - Useful stuff

Views in MySQL really has quite a bad reputation, for bad performance mainly, but also there were some stability issues at some point. Now they are pretty stable, but I don't see them used that much. One place where I like to use them myself is in combination with INFORMATION_SCHEMA tables. The I_S tables are really useful and contain a lot of information, and by using VIEWs we can massage the data a bit.

I often have a database specifcally for DBA needs, so that is what we will use here.
CREATE DATABASE IF NOT EXISTS dba;
USE dba;

OK, now we have a database to play with. Lets solve a minor problem first. Being able to use the TABLES table in INFORMATION_SCHEMA is great, as it allows standard SQL filtering and processing, in difference to the output from SHOW TABLES (largely at least, some filtering is available in the SHOW commands also of course). But the TABLES table contains the tables in ALL databases in the MySQL instance, not just the current database. Also, although the INFORMATION_SCHEMA tables are neat, typing INFORMATION_SCHEMA to access these tables all the time is tiresome. This is something that is easily fixed with a view. In the dba database, create a VIEW like this:
CREATE OR REPLACE VIEW dba.tables AS
SELECT * FROM information_schema.tables
WHERE table_schema = database();

Note that this is "dynamic", i.e. the database() function is evaluated when a query runs against the view, not when the view is created. So if we change to the test database and issues the command:
USE test;
SELECT * FROM dba.tables;

You will get the tables in the test database. Pretty neat and useful. Another issue with the I_S tables is that there is a whole lot of data in them, the data is often at a low level and sometimes they are not organized in a way to make using them easy. Case in point is the GLOBAL_STATUS table. This has a LOT of values, 300+ in the version of MySQL I use, and often you are only interested in some values, which is solved by filtering. But some values are missing, like cache hit ratios. Which is not to say that cache hit ratios cannot be computed from values in the GLOBAL_STATUS table. But we can make things easier with a view:
CREATE OR REPLACE VIEW dba.global_status(
variable_name, variable_value)
AS SELECT gs1.variable_name, gs1.variable_value
FROM information_schema.global_status AS gs1
UNION SELECT 'INNODB_CACHE_HIT_RATIO', ROUND((1 - (gs1.variable_value
/ gs2.variable_value)) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'INNODB_BUFFER_POOL_READS'
AND gs2.variable_name = 'INNODB_BUFFER_POOL_READ_REQUESTS'
UNION SELECT 'MYISAM_CACHE_HIT_RATIO', ROUND((1 - (gs1.variable_value
/ gs2.variable_value)) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'KEY_READS'
AND gs2.variable_name = 'KEY_READ_REQUESTS'
UNION SELECT 'QCACHE_CACHE_HIT_RATIO', ROUND((gs1.variable_value
/ gs2.variable_value) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'QCACHE_HITS'
AND gs2.variable_name = 'COM_SELECT'
ORDER BY variable_name;

Now, we have cache hit ratio values added to the global status. All the existing status values are still there. Also, note an interesting MySQL extension to VIEWs here: You can add an ORDER BY to them!

/Karlsson

Monitoring MySQL SQL statements the way it SHOULD be done!

You may have read a previous post of mine, back in April this year, where I wrote about using the MySQL 5.5 Audit interface to SQL Statement monitoring. There was a bunch of comments and some ideas, but not much happened. Until now that is.

Hereby I release the first version of SQLStats, which is a much enhanced version of what I described in the previous post. This is a MySQL Plugin for MySQL 5.5 that allows you to monitor the most recently executed, and the most frequently executed statements using 2 INFORMATION_SCHEMA tables. The thing is not very complex, to be honest, but it does do the job. So what was the job then? Well, looking at what this plugin does, it goes something like this:

• Allows you to monitor ALL SQL statements executed by the server.
• The SQL statements are "normalized", meaning that literals / constants are removed before comparison.
• Data is saved in memory. No disk access and very little overhead.
• Data is retrieved from INFORMATION_SCHEMA tables, just a simple SELECT and you know what is going on in the server.

I have done some basic testing of the overhead of this plugin, and it wasn't even noticable. If you have used the general query log for this stuff before, know what overhead I'm talking about. With SQLStats, the overhead is close to 0.

There is no need for MySQL Proxy or anything like that. There is no need to change something in the Client or in the Connector. To be honest, there are a couple of things I want to add to the plugin eventually, but this is a starting point at least. To use it: download it, install the tomcat / mysql monitoring server, install the monitoring agent and ... No wait, that was MySQL Enterprise Monitor, this is how you do it: download, build, install and use it. That's it.

So where can I get it, you ask (or maybe you don't, but I'm gonna tell you anyway). It's on sourceforge, and you can download it from here:
http://sourceforge.net/projects/sqlstats/

There are two files to download: A simple PDF documents with some basic usage and configuration tips, and a source-code package (which also includes the same PDF).

What would I want from you? Ideas for future development, bug reports and a few beers, that's it, not too much to ask, eh?

/Karlsson
Also, did I mention that the overhead is VERY low...