MySQL BENCHMARK() Explained

Posted on by Ian

In MySQL, BENCHMARK() is a built-in function that executes an expression repeatedly for a specified number of times.

It can be used to time how quickly MySQL processes the expression. Specifically, the function is intended for measuring the runtime performance of scalar expressions.

The result is always 0, or NULL for inappropriate arguments. The function is intended to be used within the mysql command line tool, which reports query execution times.

Syntax

The syntax goes like this: 

BENCHMARK(count,expr)

Where count is the number of times you want expr to run.

Example 1

Here’s an example to demonstrate:

SELECT BENCHMARK(2000000, FORMAT_BYTES( 27816604071000751325 ));

Result:

+----------------------------------------------------------+
| BENCHMARK(2000000, FORMAT_BYTES( 27816604071000751325 )) |
+----------------------------------------------------------+
|                                                        0 |
+----------------------------------------------------------+
1 row in set (0.53 sec)

As expected, 0 was returned. However, what we’re interested in is the query execution time.

Here I used the FORMAT_BYTES() function to format a numeric value into a user friendly format. I specified that it should execute 2000000 (2 million) times.

We can see that it took 0.53 seconds to execute this particular scalar expression 2 million times.

Here’s what happens if we add a zero to the first argument to make it 20 million:

SELECT BENCHMARK(20000000, FORMAT_BYTES( 27816604071000751325 ));

Result:

+-----------------------------------------------------------+
| BENCHMARK(20000000, FORMAT_BYTES( 27816604071000751325 )) |
+-----------------------------------------------------------+
|                                                         0 |
+-----------------------------------------------------------+
1 row in set (4.88 sec)

This time BENCHMARK() took just 4.88 seconds to execute the expression 20 million times.

Here’s what happens when I reduce the number of bytes in the FORMAT_BYTES() function:

SELECT BENCHMARK(20000000, FORMAT_BYTES( 278 ));

Result:

+------------------------------------------+
| BENCHMARK(20000000, FORMAT_BYTES( 278 )) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (1.43 sec)

It didn’t take as long to run this time.

Run BENCHMARK() Multiple Times

The MySQL documentation recommends that we run BENCHMARK() several times, and to interpret the result with regard to how heavily loaded the server machine is.

Here’s what happens when I run that last statement again:

SELECT BENCHMARK(20000000, FORMAT_BYTES( 278 ));

Result:

+------------------------------------------+
| BENCHMARK(20000000, FORMAT_BYTES( 278 )) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (1.37 sec)

This time it took less time to run.

Let’s run it one more time:

SELECT BENCHMARK(20000000, FORMAT_BYTES( 278 ));

Result:

+------------------------------------------+
| BENCHMARK(20000000, FORMAT_BYTES( 278 )) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (1.36 sec)

Slightly less time again.

Negative Iterations

If the first argument is a negative value, then null is returned, with a warning:

SELECT BENCHMARK(-20000000, FORMAT_BYTES( 278 ));

Result:

+-------------------------------------------+
| BENCHMARK(-20000000, FORMAT_BYTES( 278 )) |
+-------------------------------------------+
|                                      NULL |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1411 | Incorrect count value: '-20000000' for function benchmark |
+---------+------+-----------------------------------------------------------+

As expected, it tells us that the negative value is incorrect.

Null Argument

If the first argument is null, then null is returned:

SELECT BENCHMARK(null, FORMAT_BYTES( 27816604071000751325 ));

Result:

+-------------------------------------------------------+
| BENCHMARK(null, FORMAT_BYTES( 27816604071000751325 )) |
+-------------------------------------------------------+
|                                                  NULL |
+-------------------------------------------------------+
1 row in set (0.00 sec)

But if only the second argument is null, then 0 is returned:

SELECT BENCHMARK(2000000, null);

Result:

+--------------------------+
| BENCHMARK(2000000, null) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.01 sec)

Invalid Number of Arguments

Calling BENCHMARK() with no arguments, or with the wrong number of arguments, results in an error:

SELECT BENCHMARK();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'BENCHMARK'

And:

SELECT BENCHMARK(1, 2, 3);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'BENCHMARK'

More Information

The MySQL BENCHMARK() function is intended for measuring the runtime performance of scalar expressions, which has some significant implications for the way that we should use it and interpret the results. See the MySQL documentation for more information about this.