EmergencyEMERGENCY? Get 24/7 Help Now!

How MySQL ‘queries’ and ‘questions’ are measured

 | May 29, 2014 |  Posted In: Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

MySQL 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:

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…

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:

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.

PREVIOUS POST
NEXT POST
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

4 Comments

  • Fadi,

    Nope FLUSH STATUS does not resent them. This command BTW is quite confusing which resets local (connection) counters flushing them to global counters but when it also zeroes out some of the global counters but not all of them.

    As such it is much better to compute deltas in the application if you’re using it for monitoring.

  • Another interesting detail would be wether or not those include the queries/questions that have been handled by the query cache, or wether it’s only those that are actually parsed and executed.

    Looking for that very information now, because InnoTop gets it’s qps figure from the questions variable 🙂

  • I am using ipage hosting when I run some nested queries it shows that you have exhasted the limit. When I have checked the limit it is 300,000 questions set per hour. Please help me how it exhausts in just few queries.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.