How MySQL "Queries" and "Questions" are Measured
Join the DZone community and get the full member experience.
Join For FreeMySQL has status variables “questions” and “queries” which are rather close but also a bit different, making it confusing for many people. The manual describing it might not be very easy to understand:
Queries The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands. Questions The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.
In a nutshell if you’re not using prepared statements the big difference between those is what “Questions” would count stored procedure calls as a single statement where “Queries” will count all statements called inside of stored procedures as well.
There seems to be also more subtle difference between them:
Running on MySQL 5.6.17 having created a new connection I see…
mysql> show status like "questions"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Questions | 2 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show status like "queries"; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Queries | 21241470 | +---------------+----------+ 1 row in set (0.00 sec)
Which tells me what “queries” is a global status variable while “questions” is a session and can be used to see how many statements were issued to the server through the current connection.
There is also a global questions variable that shows the number for a server since the start:
mysql> show global status like "questions"; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Questions | 23375398 | +---------------+----------+ 1 row in set (0.00 sec)
When it comes to global values neither “queries” nor “questions” are reset when FLUSH STATUS is called.
What the manual does not describe in detail though is: When exactly those counters are incremented? This might look irrelevant but really it is not, especially if you’re capturing those values with high resolution and using them to diagnose non trivial MySQL performance incidents.
If you would count queries when they start – when a spike in the amount of queries in the given second could be due to the spike in the traffic, however, and if you measure queries at the completion – spikes could also mean that some critical resource became available which allowed for many queries to complete. Think for example of table-level locks or row-level locks with Innodb as very common cause.
So what is the case with MySQL? MySQL increments this counter Before executing the query so you may well see a very uniform rate of questions when actually a lot of queries were started but were not completing quickly due to waiting on some resource.
To check for unusual numbers of queries running concurrently and struggling to complete in time looking at threads_running status variable is a great idea.
Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Trending
-
How to LINQ Between Java and SQL With JPAStreamer
-
Extending Java APIs: Add Missing Features Without the Hassle
-
Auditing Tools for Kubernetes
-
The SPACE Framework for Developer Productivity
Comments