COM_QUIT and Questions
In the pursuit of intricate details, we always want 2 + 2 to equal 4. In the case of
the status value Questions, 2 + 2 seems to equal much less than 4. On a production
server this discrepancy can often be a magnitude in the millions. Thanks to a poster
named bwakem on dbforums.com, the primary discrepancy was found to be the status
value Connections, which usually accounts for the missing Questions.
In an effort to understand why, beyond simply knowing that this is, I dove into
the MySQL source code (version 5.0.10 beta) to see what was going on and where. This
document is part detailed explanation of why Connections allows Questions to add up
evenly, and part journey through the MySQL source code. Numbers in brackets like 
scattered throughout the document are source code line references.
Adding Up Questions
If you've read this article before, you'll remember I formerly thought the formula
for the status value Questions was:
Questions = Com_* + Qcache_hits + ((Connections - 2) - (Aborted_clients / 2))
However, since mysqlreport
v2.2 and its new --dtq
(Distribution of Total Questions) option, I've found that this is not always 100%
accurate. Meaning, there are yet other things which increment Questions, but cannot
be directly counted with the values from SHOW STATUS. Consequently, when mysqlreport
notices such "hidden" values, it lists a line in the DTQ report for these called "Unknown."
(Ironically, the last sentence of this article, as preserved, mentions
"the satisfaction of knowing MySQL isn't hiding something from us"—apparently I was wrong.)
On some servers, as one example report
shows, the value of Unknown Questions as a percentage of Total Questions can be quite large.
However, it is possible to have no Unknown Questions. For this reason, I leave this article
as-is until I can do the laborious task of tracking down all the hidden values. For now, here
follows the original article; just be aware there's more to be discovered...
What increments Connections is not actually what increments Questions in turn.
The MySQL protocol command COM_QUIT is actually what increments Questions; Connections
is the closest status available to us to quantify how many COM_QUIT commands MySQL has
handled. Naturally this works because every connection is eventually going to quit
(forgetting, for the sake of simplicity, persistent connections). Two is subtracted
from Connections first for the current connection (the one in which SHOW STATUS is
being executed, which obviously hasn't quit yet), and again because MySQL starts
Connections at 1 instead of 0 so the very first connection shows Connections is 2
(I don't why; an issue for another document perhaps). Half of Aborted_clients is
subtracted from Connections because an aborted client causes Connections to increment
but doesn't send a COM_QUIT command (hence why it's considered aborted). Aborted_clients
is divided by 2 because MySQL apparently increments it in twos for even a single
aborted client (another issue for another document). What would increment Com_select
increments Qcache_hits instead if MySQL is able to get the query from the query
cache. The sum of all Com_* status values is the primary contribution to Questions.
(Although I haven't tested more particular Com_ values like Com_slave_start, looking
at the source code I believe it's reasonable to say all Com_ values increment Questions.)
COM_QUIT and Questions In Action
An important part of understanding the MySQL source code is the global and per-thread
variables query_id (sql/mysql_priv.h [47-49] and sql/sql_class.h [1233-1241]). For clarity,
and because it appears this way in the code, query_id refers to the global query_id and
thd->query_id refers to the per-thread query_id (where thd is an instantiation of class
THD, sql/sql_class.h [1028-1490]). query_id starts at 1. Every question MySQL handles
is given a number which comes from query_id. Therefore, if you start MySQL, login with
the mysql cli, and quit (\q), when the cli sends the COM_QUIT command, this being the
first question is given the number 1. Naturally, after each question is given its
number, query_id is incremented by 1. This process happens in sql/sql_parse.cc, function
dispatch_command() [1507-1509]. First thd->query_id is assigned the current value of
query_id . If the question (i.e., command, query, etc.) is not COM_STATISTICS or
COM_PING , then query_id is incremented by 1 .
Knowing this, it's easy to understand why Questions is simply thd->query_id (sql/sql_show.cc
). Although each thread (thd) will have a different value for its query_id,
when you tell MySQL "SHOW STATUS;", at that instant that question (which results in
a COM_QUERY command) gets the next global query_id number. Since all threads have been
taking their numbers from query_id, this is an accurate count of all questions for all
Since COM_QUIT is sent by every civilized MySQL interface when the script or program
using that interface is done, this explains why COM_QUIT, seen by us as Connections,
counts toward Questions. In a sense you could say the formula for COM_QUIT is:
COM_QUIT = (Connections - 2) - (Aborted_clients / 2)
Perhaps MySQL AB will add a Com_quit status value. Then the formula for Questions
would be really simple and intuitive:
Questions = Com_* + Qcache_hits
Until then, we at least have the satisfaction of knowing MySQL isn't hiding something
from us; it's just a little obfuscated.