MySQL Bugs: #14946: BENCHMARK() function returns strange results for stored functions

Bug #14946	BENCHMARK() function returns strange results for stored functions
Submitted:	15 Nov 2005 13:11	Modified:	2 Feb 2006 13:58
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.15-standard-log-i686-glibc23	OS:	Linux (SuSE Linux 2.4.21-273-smp4G)
Assigned to:		CPU Architecture:	Any

Description:
I wanted to use MySQL's BENCHMARK() function to measure the performance of stored functions versus inline code. The results returned by BENCHMARK() showed a huge overhead for the call of stored functions (factors from 10 to 50). Per-Erik Martin pointed out to me that this couldn't be possible and did the same tests with a custom-made benchmark in a stored procedure. His tests returned an overhead factor of only about 2.

So there seems to be something wrong in how BENCHMARK() handles calls to stored functions, which actually renders it useless for its purpose to do performance checks (and lets MySQL look much worse than it actually is when it comes to stored functions!).

How to repeat:
I used different stored functions (one-liners) to test this, tested it on different machines and ran all of the tests multiple times - with similar results.

The test presented here was done using this stored function:

CREATE FUNCTION ADD_TO_SET(`member` VARCHAR(64), `set` VARCHAR(4096))
    RETURNS VARCHAR(4096)
    DETERMINISTIC NO SQL
    RETURN CONCAT_WS(',', IF(`set` = '', NULL, `set`), `member`);

First benchmark the stored function versus its inline code using MySQL's built-in BENCHMARK() function (@member is set to 'd' and @set is set to 'a,b,c' for all tests):

mysql> DO BENCHMARK(100000, ADD_TO_SET(@member, @set));
Query OK, 0 rows affected (0.75 sec)

mysql> DO BENCHMARK(100000, CONCAT_WS(',', IF(@set = '', NULL, @set), @member));
Query OK, 0 rows affected (0.04 sec)

This shows an overhead factor of nearly 20 for the call to the stored function.

Now write your own benchmark procedures to compare times of inline versus stored function code (and an empty procedure to determine the overhead of the benchmark procedure itself):

CREATE PROCEDURE benchmark_stored_function(i INT)
    DETERMINISTIC NO SQL
BEGIN
    DECLARE s VARCHAR(4096);
    WHILE i > 0 DO
        SET s = ADD_TO_SET(@member, @set);
        SET i = i - 1;
    END WHILE;
END //

CREATE PROCEDURE benchmark_inline_code(i INT)
    DETERMINISTIC NO SQL
BEGIN
    DECLARE s VARCHAR(4096);
    WHILE i > 0 DO
        SET s = CONCAT_WS(',', IF(@set = '', NULL, @set), @member);
        SET i = i - 1;
    END WHILE;
END //

CREATE PROCEDURE benchmark_empty(i INT)
    DETERMINISTIC NO SQL
BEGIN
    DECLARE s VARCHAR(4096);
    WHILE i > 0 DO
        SET s = @set;
        SET i = i - 1;
    END WHILE;
END //

And do the tests again using these procedures:

mysql> CALL benchmark_stored_function(100000);
Query OK, 0 rows affected (2.27 sec)

mysql> CALL benchmark_inline_code(100000);
Query OK, 0 rows affected (1.25 sec)

mysql> CALL benchmark_empty(100000);
Query OK, 0 rows affected (0.75 sec)

Subtracting the overhead for the procedure itself (benchmark_empty) we get:

stored function: 2.27 - 0.75 = 1.52
inline code: 1.25 - 0.75 = 0.5

And thus an overhead factor of about 3 instead of the 20 measured with BENCHMARK().

Suggested fix:
-

Thank you for the bug report.

I've just seen this forums post from Felix Geerinckx in the discussion on this bug. He experienced a similar difference in execution times of stored functions versus inline code between SET and UPDATE statements:

http://forums.mysql.com/read.php?98,54177,54654#msg-54654

This could be related and probably mean that this is not a BENCHMARK()-only problem but could be somewhere deeper.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Marked this as a duplicate of Bug #15014.

> Please do not submit the same bug more than once. An existing
> bug report already describes this very problem. [...] Because of
> this, we hope you add your comments to the original bug instead.

Hi Jon, thanks for your friendly note, but I couldn't add my comment to the original bug, as this one was the original, just look at the dates. ;-)