How to find mysql query rate on Linux

Sometimes we want to know the current queries rate being sent to mysql server. This can be useful to get some idea on query load on mysql server. We can also use similar approach to find current rows read rate, etc. which can give an idea of current data being transferred from mysql server. Here are steps to find query rate on Ubuntu Linux Command line. We’ll use mysql “SHOW STATUS” command for the purpose of this article.

Find total queries executed since start of mysql

First we’ll use mysql status command to get the total query count since mysql start. Run the following command to find total queries executed so far:

$ mysql -u root  -e 'SHOW STATUS like "Queries"'
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Queries       | 47083727 |
+---------------+----------+

Note that here we are assuming you are running these queries on the mysql instance and your root password is empty. In case you have a root password, you can use --password=value. To get just the count value run the following:

$ mysql -u root --skip-column-names -e 'SHOW STATUS like "Queries"' | awk '{print $2}'
47083727

Calculate mysql query rate

Two find query rate, we’ll get the query count value at two different points of time. Save the following script to a file mysql_query_rate.sh:

#!/bin/bash
C1=$(mysql -u root --skip-column-names -e 'SHOW STATUS like "Queries"' | awk '{print $2}')
sleep 60
C2=$(mysql -u root --skip-column-names -e 'SHOW STATUS like "Queries"' | awk '{print $2}')
R=$(echo "($C2 - $C1)/60" | bc -l)
echo $R

Run the following code to get the query rate:

$ chmod a+x mysql_query_rate.sh
$ ./mysql_query_rate.sh
8.6666666666666666666

Other mysql stats

Note that you can also calculate few other types of query rate using a similar approach. Some of the stats you can consider are:

1. Innodb_rows_deleted
2. Innodb_rows_inserted
3. Innodb_rows_read
4. Innodb_rows_updated
5. Slow_queries