Skip to content

# questions or queries?

MySQL Monitoring mycheckpoint November 13, 2009

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
17
Leave a Reply

17 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
7 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Peter Laursen

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! 🙂

Guest
Peter Laursen

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.

Guest
Baron

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.

Guest
Mark Callaghan

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.

Author
shlomi

@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.

Guest
Sigmund Arthur

See MySQL bugs 24289 and 41131.

Guest
Dan Rogart

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.

Guest
Peter Laursen

.. 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).

Guest
Peter Laursen

sshit .. what I psoted truncated.

try again:
version >= 5.0.75 and version = 5.1.31 and version < 6.0 …

Guest
Peter Laursen

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

Guest
Peter Laursen

aahhh .. success! 🙂

Author
shlomi

@Sigmund, thanks for the reference

@Peter, thanks; with patience, I would have formatted your comments 😉
There’s HTML strip for most tags.

Guest
Kostja

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.

Author
shlomi

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

Guest
Kostja

It was not a single customer request.

Author
shlomi

@Kostja

cool

Log Buffer #170: a Carnival of the Vanities for DBAs | The Pythian Blog

[…] code.openark.org, Shlomi Noach is surprised by questions or queries. He writes, “I was used to checking for the ‘questions’ global status […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.