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:

Anonymous 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

rpbouman 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.

Anonymous 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');

rpbouman 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.

DuckDB bag of tricks: Processing PGN chess games with DuckDB - Rolling up each game's lines into a single game row (6/6)

DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB . This post is the sixth installment of a s...