how to get ‘Queries per second avg’ using SQL (not ‘mysqladmin status’)

Posted on

Question :

When I start mysqladmin, it prints out several statistics, including “Queries per second avg”:

# mysqladmin --defaults-file=../mylogin.cnf status
Uptime: 4568115  Threads: 859  Questions: 3703806462  Slow queries: 19415  Opens: 10300505  Flush tables: 247  Open tables: 2000  Queries per second avg: 810.795

Can I retrieve this “Queries per second avg” value within an SQL query? We use MariaDB 10.1.x

Answer :

For those running MariaDB and MySQL (If using 5.7, show_compatibility_56 enabled)

SELECT Q / T Queries_per_second_avg FROM
(SELECT variable_value Q FROM information_schema.global_status
WHERE variable_name = 'Questions') A,
(SELECT variable_value T FROM information_schema.global_status
WHERE variable_name = 'Uptime') B;

For those running MySQL 5.7, show_compatibility_56 disabled

SELECT Q / T Queries_per_second_avg FROM
(SELECT variable_value Q FROM performance_schema.global_status
WHERE variable_name = 'Questions') A,
(SELECT variable_value T FROM performance_schema.global_status
WHERE variable_name = 'Uptime') B;

Leave a Reply

Your email address will not be published. Required fields are marked *