MySQL Bugs: #116393: Degrading performance of MD5(), SHA2() functions

Bug #116393	Degrading performance of MD5(), SHA2() functions
Submitted:	17 Oct 2024 12:46	Modified:	15 Jan 18:54
Reporter:	Eimantas Jatkonis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	BENCHMARK, hash, md5, regression, SHA2, speed

Description:
The speed of hashing functions MD5 and SHA2 is degrading with the MySQL version going up.

I used SAME config, SAME machine, and no databases.
#First function
SELECT BENCHMARK(10000000,md5(now()));
#Second function
SELECT BENCHMARK(1000000, CONCAT(SHA2(CONCAT('MySQL', 'BenchMark'), 256), POW(12345.6789, 2.71828), RAND(), MD5(NOW())));

Speed tests using different MySQL versions (duration in seconds)

                            First MD5    Second Misc
5.6.50 glibc 2.12           1.49         1.95
8.0.21 glibc 2.17 minimal   1.46         2.17
8.0.30 glibc 2.17 minimal   2.44         2.45
8.0.33 glibc 2.17 minimal   2.47         2.29
8.0.35 glibc 2.17 minimal   7.77         3.35
8.0.37 glibc 2.17 minimal   7.72         3.29
8.0.39 glibc 2.17 minimal   7.73         3.25
8.0.40 glibc 2.17 minimal   7.82         3.19
8.0.40 glibc 2.17           8.38         3.48
8.0.40 glibc 2.28           8.26         3.47

Such kinds of functions should be kept as fast as possible.

CPU:
Intel(R) Xeon(R) Gold 6348 CPU @ 2.60GHz

How to repeat:
#First function
SELECT BENCHMARK(10000000,md5(now()));

#Second function
SELECT BENCHMARK(1000000, CONCAT(SHA2(CONCAT('MySQL', 'BenchMark'), 256), POW(12345.6789, 2.71828), RAND(), MD5(NOW())));

Hi Mr. Jatkonis,

Thank you for your performance deterioration report.

We were able to repeat it.

For example, for MD5, these were the speeds:

5.7.49 :  3.27 seconds
8.0.35:  6.96 seconds
8.0.40: 10.37 seconds
8.4.3:   13.83 seconds

Verified as reported.

Posted by developer:
 
Hi Mr. Jatkonis,

can you please document what version of OpenSSL was used with these MySQL server tests?
Was OpenSSL 3.x used?

Thanks.

All tests with this version:
OpenSSL 1.1.1n  15 Mar 2022

Thank you, Mr. Jatkonis ......

Posted by developer:
 
The cryptography functions have been speedup via WL#16504 and this code will be part of MySQL 9.2.0 release.

To test (on one of the test servers), I've added two more SQLs that focus only on MD5() and SHA2() functions, i.e. removing parts like calling NOW() or similar functions.

So the following 4 functions were tested: 
SQL#1:  SELECT BENCHMARK(100000000,md5(now()));
SQL#2:  SELECT BENCHMARK(10000000, CONCAT(SHA2(CONCAT('MySQL', 'BenchMark'), 256), POW(12345.6789, 2.71828), RAND(), MD5(NOW())));
SQL#3:  SELECT BENCHMARK(300000000, MD5('MySQLBenchMark'));
SQL#4:  SELECT BENCHMARK(100000000, SHA2('MySQLBenchMark', 256));

And the results are (baseline being latest mysql-trunk code without WL code pushed):
    SQL#1:
        baseline: 26.10 sec
        WL:       23.59 sec
        8.0.21:   14.38 sec
    SQL#2:
        baseline: 22.37 sec
        WL:       21.13 sec
        8.0.21:   18.26 sec
    SQL#3:
        baseline: 49.13 sec
        WL:       41.89 sec
        8.0.21:   42.14 sec
    SQL#4:
        baseline: 23.77 sec
        WL:       23.39 sec
        8.0.21:   21.47 sec

The performance of MD5() and SHA2() functions themselves are now in the same ballpark as in MySQL 8.0.21 (results for SQL#3 and SQL#4), but there is still a difference in first two SQLs that add additional calls such as NOW().
The parts related to NOW() or similar functions may be improved upon in the future.

Good news is that with OpenSSL3.x builds, performance of the MD5() and SHA2() functions has been improved substantially, in some cases up to 4 times.

Thank you, Miroslav.

Fixed in MySQL 9.2.0 by WL#16504.

Added this bug # to existing changelog entry for easier reference.

Closed.

Thank you, Jon.