Menu
Is it possible to use the server status variables in a MySQL query?
I can see various metrics from 'show status' but how do I calculate derived values (e.g. the query cache hit ratio)
show global status like 'Qcache_inserts';
show global status like 'Qcache_not_cached';
show global status like 'Qcache_hits';
Ho do I get Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached)?
530
You're correct that STATUS is a keyword, likely owing to SHOW STATUS . If renaming the column isn't much of a chore for you, changing the name is the best choice.
The active or total connection can be known with the help of threads_connected variable. The variable tells about the number of currently open connections. mysql> show status where `variable_name` = 'Threads_connected'; Here is the output.
The DESC is the short form of DESCRIBE command and used to dipslay the information about a table like column names and constraints on column name. The DESCRIBE command is equivalent to the following command − SHOW columns from yourTableName command.
generally you can access that info due this select:
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM
INFORMATION_SCHEMA.GLOBAL_STATUS
You could make the calculation you want like this:
select (g1.VARIABLE_VALUE / (g1.VARIABLE_VALUE + g2.VARIABLE_VALUE + g3.VARIABLE_VALUE)) as result
FROM INFORMATION_SCHEMA.GLOBAL_STATUS g1
inner join INFORMATION_SCHEMA.GLOBAL_STATUS g2
inner join INFORMATION_SCHEMA.GLOBAL_STATUS g3
where g1.VARIABLE_NAME = 'Qcache_hits'
and g2.VARIABLE_NAME = 'Qcache_inserts'
and g3.VARIABLE_NAME = 'Qcache_not_cached'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
