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 = 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.
See MySQL bugs 24289 and 41131.
I noticed this issue a while ago as well when I saw differing qps information reported from innotop. Turns out it was because newer versions of innotop use the Queries status variable.
I filed a bug for it:
http://code.google.com/p/innotop/issues/detail?id=27
It’s annoying that such a change was made since it means that monitoring/reporting tools can have inconsistent behavior across minor revisions of MySQL.
.. so for performance monitoring logic would have to be something like (in the famous PPPL language = *Peter’s Private Programming Language*)
CASE
version >= 5.0.75 and version use ‘queries’ status variable
version >= 5.1.31 and version use ‘queries’ status variable //see note
ELSE ===> use ‘questions’ variables
(according to docs all 5.4 versions have ‘queries’ – but not 100% sure about details with (the abandoned) 6.0 thing).
sshit .. what I psoted truncated.
try again:
version >= 5.0.75 and version = 5.1.31 and version < 6.0 …
What is the issue with this Blog system?? Doesn’t it understand plain text 😉
CASE
version GTorEq 5.0.75 and version LT 5.1.0: use ‘queries’ status variable;
version GTorEq 5.1.31 and version LT 6.0: use ‘queries’ status variable;
ELSE use ‘questions’status variables
aahhh .. success! 🙂
@Sigmund, thanks for the reference
@Peter, thanks; with patience, I would have formatted your comments 😉
There’s HTML strip for most tags.
It was initially fixed only in 5.1 and 6.0, but then backported to 5.0 upon a customer request. It’s impossible to please everyone, right, the only solution is to not GA a product with such obvious bugs around.
Hi Kostja,
I don’t mean to sound as if I know your business better than yourselves; but in my business I would not favor a single customer’s request by damaging all other customers; unless I’m willing to rely on this single customer as the source of my income.
Again, this is my personal view.
Regards
It was not a single customer request.
@Kostja
cool
[…] code.openark.org, Shlomi Noach is surprised by questions or queries. He writes, “I was used to checking for the ‘questions’ global status […]