How the BENCHMARK() function works in Mariadb?

The BENCHMARK() function in MariaDB is a utility function used to measure the performance of a given operation by executing it a specified number of times. This function is particularly useful for database administrators and developers who need to assess the efficiency of expressions or functions.

Syntax

The syntax for the MariaDB BENCHMARK() function is as follows:

BENCHMARK(count, expression)

The count parameter is an integer that specifies how many times the expression should be executed. The expression can be any SQL expression or function. The BENCHMARK() function always returns 0, but its value lies in the side effect of executing the expression multiple times for performance testing.

Examples

Example 1: Basic Usage of `BENCHMARK()`

This example demonstrates the basic usage of the BENCHMARK() function by executing a simple expression 1,000,000 times.

SELECT BENCHMARK(1000000, 1+1);

+-------------------------+
| BENCHMARK(1000000, 1+1) |
+-------------------------+
|                       0 |
+-------------------------+

The output is 0, indicating the function has completed the execution.

Example 2: Measuring Function Performance

Measuring the performance of the MD5() function by running it 500,000 times.

SELECT BENCHMARK(500000, MD5('test'));

+--------------------------------+
| BENCHMARK(500000, MD5('test')) |
+--------------------------------+
|                              0 |
+--------------------------------+

The output is 0, but the execution time indicates the performance of the MD5() function.

Example 3: Comparing Two Functions

Comparing the performance of the SHA1() and MD5() functions by running each 100,000 times.

SELECT BENCHMARK(100000, SHA1('test')), BENCHMARK(100000, MD5('test'));

+---------------------------------+--------------------------------+
| BENCHMARK(100000, SHA1('test')) | BENCHMARK(100000, MD5('test')) |
+---------------------------------+--------------------------------+
|                               0 |                              0 |
+---------------------------------+--------------------------------+

The output is 0 for both, but the execution times can be compared to determine which function is faster.

Below are a few functions related to the MariaDB BENCHMARK() function:

MariaDB PROFILING is used to measure the resource usage of individual statements within a session.
MariaDB EXPLAIN provides information about how MariaDB executes a query, which can be used to optimize performance.

Conclusion

The BENCHMARK() function is a valuable tool for performance testing in MariaDB. It allows developers and database administrators to execute a specific operation multiple times and measure the time taken to complete, providing insights into the efficiency of SQL expressions and functions. While the function itself returns 0, the real benefit comes from observing the execution time and using this data to optimize database operations for better performance.