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:
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
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.
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');
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.
Post a Comment