I’ve hit a recent change which took me by surprise.
I was used to checking for the ‘questions‘ global status variables to see the total amount of queries the server performs. So, for example, I could run com_select/questions to learn the SELECT ratio out of all queries.
Apparently, as of 5.0.72–5.0.76 & 5.1.31 this has changed. A new status variable was introduced, called ‘queries‘.
The change being? questions does not any longer indicate the number of queries the server has executed: only the number of queries requested by the client (so, calling on a stored routine only counts as 1, regardless of how many queries the routine executes). The new queries variable indicates the amount of server queries issued (see the 5.0 and 5.1 docs for details).
So, as of 5.0.72 or 5.1.31, the calculation should be com_select/com_queries (or com_select_diff/com_queries_diff) to learn the SELECT ratio of all queries. I learned this due to a bug report on mycheckpoint, which presented some 10265% SELECT ratio…
My take on this is that it could have been worked out differently: instead of changing the meaning of an existing variable, questions could have remained as it was, with the introduction of, say, client_questions, which would only indicate client number of issued queries.
I believe changing the meaning of status variables at such late versions (5.0.76 is quite late!) invites trouble: code that used to work on already then-stable versions (e.g. 5.0.51) would behave differently after upgrade. Such changes should best take place while still in BETA phase.
server 5.1.40 .. from a freshly connected client.
SHOW GLOBAL STATUS LIKE ‘questions’; — returns 3354
SHOW GLOBAL STATUS LIKE ‘queries’; — returns 3355
(also if I revert the two the LAST will return FIRST+1)
.. so I cannot reproduce this. Maybe rather a ‘stored program’ issue? I think you rushed to conclusions here! 🙂
you are right as regards SP’s:
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
Queries: The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. This variable was added in MySQL 5.0.76.
Questions: The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs, unlike the Queries variable.
I do not really understand why they did this.
Grr. I didn’t realize the meaning of Questions had changed — I thought it remained the same and Queries added the new information. That is SO annoying. Smells like the old SHOW STATUS versus SHOW GLOBAL STATUS garbage, except even harder to work around.
I had the same question as you when I first noticed this. Pardon my pun.
I think another difference is that one of this has a per-session counter and one does not. So if you forget to type SHOW GLOBAL STATUS because you were raised on MySQL 4.0 and assume SHOW STATUS == SHOW GLOBAL STATUS, then you will get very different results.
@Mark – that sounds even worse! I’ll check it out later on (will be disconnected from the network for a week).
To be honest, I don’t really care about < = 4.1 (don't encounter many of these around); I'd settle for anything that's consistent for >= 5.0.
@Baron – to solve it with mycheckpoint I now force the existence of “queries” variable (even if not found on SHOW GLOBAL STATUS), then check ratio like:
com_select_diff/IFNULL(queries_diff, questions_diff)
@Peter – I suspect there are more scenarios. Will check them out.