Showing posts with label mysql administration. Show all posts
Showing posts with label mysql administration. Show all posts

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?

DuckDb Performance: min, max, and median vs quantile

I was playing with some classical statistics in DuckDB and I ran into something I'd like to share. It's about the measures minimum,...