X

Learn MySQL with the Curriculum Team

What do the Queries and Questions Status Variables Tell You?

Mark Lewin
MySQL Curriculum Developer

The two status variables Queries and Questions can sometimes cause confusion. On the face of it, both appear to count the number of times a query is executed on the server.

Each variable has both session and global scope, meaning that you can get statistics for both the current connection and for all connections. You can reset the session variables by executing FLUSH STATUS, but resetting the global variables requires a server restart.

All good so far. However, the way in which these variables are incremented is not always intuitive.

For one thing, everything in MySQL is a Question. Any operation you perform, including checking the status of a server variable is a Question. Even checking the number of Questions issued by your session is, in itself, a Question:

mysql> SHOW STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 4     |
+---------------+-------+
1 row in set (0.00 sec)

Another thing to be aware of is that MySQL increments these counters before executing the query. This doesn't always give you a very accurate indication of how many queries are executing concurrently, because these status variables tell you when the query was issued and not when it was run. So, if you rely on these variables for insight into your server workload you might well observe queries being executed at a pretty constant rate when in actual fact they are not completing until some required resource (such as an InnoDB row-level lock) becomes available.

A better indication of your server activity at any one time is the Threads_running status variable. This represents the total number of client processes (threads) currently executing on the database server. The server is holding these connections while the client is waiting for a reply. This is a much better indication of the load on your server than the Questions and Queries status variables.

# mysqladmin -i1  extended | grep Threads_running
| Threads_running                       | 33                   |
| Threads_running                       | 22                   |
| Threads_running                       | 28                   |
| Threads_running                       | 1                    |
| Threads_running                       | 21                   |
| Threads_running                       | 17                   |
| Threads_running                       | 2                    |
| Threads_running                       | 26                   |
| Threads_running                       | 25                   |
| Threads_running                       | 30                   |
| Threads_running                       | 27                   |
| Threads_running                       | 23                   |
| Threads_running                       | 1                    |
| Threads_running                       | 28                   |
...

The above output is from a server with 32 connections and you can see that on one occasion 33 threads are running. This indicates that the server is maxed out at that time. (The 33 includes the connection that is checking the value of the Threads_running status variable, so you must extract one from the value for a true picture of the number of active threads.)

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha