Locations of visitors to this page Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Monday, June 02, 2008

MySQL 5.1: Measuring #queries/sec. using information_schema.GLOBAL_STATUS

MySQL 5.1 offers new information tables such as GLOBAL_STATUS. This can be used to report certain performance metrics, such as the number of queries processed per second:

SELECT MAX( -- use MAX to force aggregation
IF(variable_name='Questions' -- no. of queries sent to server
, CAST(variable_value AS unsigned) -- make integer value
, 0 -- ignore if not 'Questions'
)
)
/ -- divide by
MAX( -- use MAX to force aggregation
IF(variable_name='Uptime' -- no. of seconds the server is up
, CAST(variable_value AS unsigned) -- make integer value
, 0 -- ignore if not 'Uptime'
)
) AS queries_per_second
FROM information_schema.GLOBAL_STATUS
WHERE variable_name in ('Questions', 'Uptime');

(This snippet is maintained on MySQL Forge)

This query could have been solved in a number of other ways involving subqueries, for example like this:

SELECT (SELECT CAST(variable_value AS unsigned)
FROM information_schema.GLOBAL_STATUS
WHERE variable_name = 'Questions')
/ (SELECT CAST(variable_value AS unsigned)
FROM information_schema.GLOBAL_STATUS
WHERE variable_name = 'Uptime') as queries_per_second

For now I'm sticking to my original snippet. The disadvantage to this approach is that it requires a hack to force aggregation of all rows from GLOBAL_STATUS to one single row (using MAX), as well as some conditional expressions.

The disadvantage of a using subqueries is that it would be slighly more verbose, and it would also require more scanning of information_schema.GLOBAL_STATUS, although I am unsure if that would really matter that much.

What do you think?

4 comments:

Jay said...

Don't forget your CROSS JOIN-fu. :)

SELECT
(CAST(gs1.variable_value AS unsigned)
/ CAST(gs2.variable_value AS unsigned)
) AS queries_per_sec
FROM information_schema.GLOBAL_STATUS gs1
CROSS JOIN information_schema.GLOBAL_STATUS gs2
WHERE gs1.variable_name = 'Questions'
AND gs2.variable_name = 'Uptime';

Cheers!

Jay

Roland Bouman said...

Jay, thanks for the comment.

Yes - a join would have worked well too for this example.

However, I figured that I might want to add more metrics using perhaps more than two status variables, in which case you'd need more joins.

swanhart said...

Another approach would be to use a stored function to get the values from the information schema. Something like:

SELECT foo.GET_STATUS_VAR('GLOBAL', 'Questions') /
foo.GET_STATUS_VAR('GLOBAL',
'Uptime');

Roland Bouman said...

Justin,

thanks!

I think that for maximum reusability, your idea is great!

Currently, I'm leaning more and more to the idea of creating a view of all of my favorite metrics. I'll publish an example shortly that illustrates the idea.