The problem

The results of testing SQL with mysqlSLAP seem to be more stable than those of testing SQL with sysbench. Why?

The experiment

Take a random database,

We use the following SQL to perform the pressure test:

select sleep(@val := coalesce(@val, 0) + 1) from dual;

Each time this SQL is run, the sleep time is increased by one second. The first time was sleep(1), the second time was sleep(2),…

Let’s start the pressure test with mysqlSLAP:

View the results:

Read the result of mysqlslap:

  • Each ROUND of SQL runs for an average of 15s
  • A round of SQL runs an average of 5 SQL
  • It is deduced that each SQL runs an average of 3s

Note: there is a Max and Min in the result. It is often found that people divide Max by the number of SQL rounds to get the maximum latency of SQL. This calculation is wrong.

Max and Min refer to the maximum and minimum running time of each round if you iterations > 1 on mysqlSLAP. Is independent of the latency of a single SQL.

In this example, if we change the iterations to 3, the iterations to Max/Avg/Min are about 15s, but we know that the latency of these SQL is 1s,2s, and… The maximum/minimum value of a single SQL delay cannot be calculated with Max/Avg/Min.

Sysbench test:

Write a LUA script to sysbench. (if you don’t know how to write, you can copy sysbench’s bulk_insert. LUA script.)

Then run the sysbench test:

Looking at the results, you can see that the 95% quantile is far from the average, meaning that the latency of the same SQL varies greatly.

Through the above simple experiments, we found that:

  • Sysbench can reflect single SQL or local SQL latency exceptions
  • The measurement object of mysqlSLAP is a round and does not reflect the latency of a single SQL. If you use a simple division method to estimate SQL latency, the fluctuations in SQL latency will be much more stable than they actually are.

In other words, the reason mysqlSLAP results are more stable than sysbench results is that the error calculations were made when the mysqlslap results were processed, causing the fluctuations to be eliminated.

Tips:

In this example, we purposely use the same SQL. If business pressures are coming from different SQL types, you can use ps_Statement_AVG_latency_histogram to obtain a graph of latency differences between different SQL types.

—

Is there anything else you’d like to know about MySQL’s technical content? Leave a message and tell xiaobian!