[Problem Description]

Queries_per_sec (QPS) is two important performance counter metrics for databases.We often require development to inform this parameter to evaluate a load on the database.The following code connects to the server to make a simple query:

using (MySqlConnection conn = new MySqlConnection())

{

conn.ConnectionString = "Database=xx;Host=xx;Port=xx;User Id=xx; Password=xx; charset=utf8;pooling=true;";

MySqlCommand cmd = new MySqlCommand();

cmd.Connection = conn;

conn.Open();

cmd.CommandText = " select * from test where ID = 3";

cmd.ExecuteNonQuery();

conn.Close();

}

Let's add a loop and execute 2 million times.You can see that Queries_per_sec is about 930 times per second.

Questions_per_sec is approximately 620 times per second

The execution time ranges from 14:56:52 to 16:45:22 and lasts approximately 650 seconds, with 2 million executions.According to our understanding, QPS should be around 2 million/6510 = 310 times/second.However, Queries_per_sec or Questions_per_sec collected by our system do not match this data.Questions_per_sec is twice the expected value and Queries_per_sec is three times the expected value.That's why.

[Problem Analysis]

Performance counters are collected from show global status.Questions and Queries are defined as follows:

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.

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. 

This definition is a bit complicated.When you open General Log on the server, you can see that the query above actually contains the operations of Init DB:

3499308 Init DB testdb

3499308 Query select * from test where ID = 3

3499308 Init DB testdb

3499308 Query select * from test where ID = 3

So this explains why Questions_per_sec is twice as large as we expected because of the Init DB operation.
There should be another operation so that Queries_per_sec, the performance counter, is about three times what we expected for QPS.We tracked show global status like'com_%'; this command, you can see that the following three counts are increasing:

1,Com_admin_commands

2,Com_change_db

3,Com_select

The second and third explain that Com_Change_DB is equivalent to our Init DB, and COM_Select is our SELECT query.The first Com_admin_commands is odd.After checking the code, this is a collection of the counters below.Others are not used in general, the only thing you can use is COM_PING.

COM_CHANGE_USER

COM_SHUTDOWN

COM_PING

COM_DEBUG

COM_BINLOG_DUMP_GTID

COM_BINLOG_DUMP

So the problem is clearer.

[Conclusion]

Queries_per_sec is three times higher than our expected QPS due to a Ping check action on the connection by the driver.This action should also count as Queries.Not in Questions.