questions or queries?

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.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

17 Comments to "questions or queries?"

  1. Peter Laursen wrote:

    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! :-)

  2. Peter Laursen wrote:

    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.

  3. Baron wrote:

    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.

  4. Mark Callaghan wrote:

    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.

  5. shlomi wrote:

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

  6. Sigmund Arthur wrote:

    See MySQL bugs 24289 and 41131.

  7. Dan Rogart wrote:

    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.

  8. Peter Laursen wrote:

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

  9. Peter Laursen wrote:

    sshit .. what I psoted truncated.

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

  10. Peter Laursen wrote:

    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

  11. Peter Laursen wrote:

    aahhh .. success! :-)

  12. shlomi wrote:

    @Sigmund, thanks for the reference

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

  13. Kostja wrote:

    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.

  14. shlomi wrote:

    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

  15. Kostja wrote:

    It was not a single customer request.

  16. shlomi wrote:

    @Kostja

    cool

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

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

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org