CODEWITHSUNDEEP
mysqldatabase
symcbean
symcbean

Reputation: 48387

How to get MySQL status in a query

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)?

Upvotes: 6

Views: 9253

Answers (2)

juergen d
juergen d

Reputation: 204934

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'

Upvotes: 6

Justin T.
Justin T.

Reputation: 3701

Yes you can. Instead of "SHOW STATUS", use "SELECT VARIABLE_VALUE FROM GLOBAL_STATUS" and proceed like any other table.

This table is located in INFORMATION_SCHEMA database.

Example query : 

SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME="Qcache_inserts"

Hope this helps !

Upvotes: 0

Related Questions