Mysql – How MySQL Queries and Questions status variables are calculated

MySQLstatistics

I have a mysql server running on 5.6.19 version and I got confused with the status variables Queries and Questions.

 show global status like 'Queries%';   
| Queries                 | 28058738 |

mysql> show global status like 'Questions%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Questions     | 9458886 |
+---------------+---------+

The server is executing so many preparing statements and as per MySQL manual I got an assumption that Questions will contain Com_stmt_execute count. So I found my counts as,

mysql> show global status like 'Com_stmt_execute';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Com_stmt_execute | 9356066 |
+------------------+---------+

So what are the other counters which dominates to the total Questions status value?

Please see my Select and Query cache hits and I have very less write on this server.

mysql> show global status like '%Com_select%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 2720855 |
+---------------+---------+

mysql> show global status like 'Qcache_hits';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Qcache_hits   | 2316541 |
+---------------+---------+

Also the mysqladmin status show a reverse status if we consider the variable names.

$ mysqladmin status
 Questions: 28079556

Best Answer

I answered an old question like this Feb 25, 2014 : Questions since MySQL Startup

In that post, I described how everything MySQL executes to retrieve info is a question. I also mentioned how a billion questions is no big deal (See my ServerFault post 1 billion mysql queries in 24 days? Can something be wrong?)

Here is the difference

  • Queries : The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.

  • Questions : The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

You should go through the MySQL Documentation and read up on all Server Status Variables.

Related Question