MySQL Bugs: #66567: my_rnd_ssl() is very expensive for authentication

Bug #66567	my_rnd_ssl() is very expensive for authentication
Submitted:	28 Aug 2012 7:57	Modified:	13 Nov 2012 18:32
Reporter:	Yoshinori Matsunobu (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S5 (Performance)
Version:	5.6.6, 5.6.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	performance, regression

Description:
In MySQL5.6.6-m9, establishing connection consumes much more system CPU resources than previous (5.1/5.5) MySQL.

You can easily repeat by running sysbench with "--num-threads=100 --oltp-reconnect-mode=query --oltp-connect-delay=0". On my machine (24 logical cpu cores), I got 8000 conns/s in 5.6.6-m9, 24000 conns/s in 5.5.27, 21000 conns/s in 5.1.65. In 5.6.6, %sys was above 75%.

I verified that in MySQL 5.6.6-m9 my_rnd_ssl() function called by create_random_string() (sql/password.c line 325) spent lots of resources. By replacing with my_rnd(), I could get 35500 conns/s.

How to repeat:
Run sysbench

Suggested fix:
--- sql/password.c
+++ sql/password.c.new
@@ -322,7 +322,7 @@ void create_random_string(char *to, uint
   char *end= to + length;
   /* Use pointer arithmetics as it is faster way to do so. */
   for (; to < end; to++)
-    *to= (char) (my_rnd_ssl(rand_st) * 94 + 33);
+    *to= (char) (my_rnd(rand_st) * 94 + 33);
   *to= '\0';
 }

Connecting to MySQL is now using OpenSSL or yaSSL, as these have greater
cryptographic strength than my_rnd().  

Other than reverting the code in your build, another
option would be to build without yaSSL, so that
#if defined(HAVE_YASSL)
would return 0.  In that case, OpenSSL is used, which is somewhat faster (in our tests, 3 times faster),
although my_rnd() will still be much faster than that.

What exact sysbench commands you had used?

[remove_client]$ sysbench --test=oltp  --oltp-table-size=2000000 --max-time=60 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --oltp-read-only --oltp-skip-trx --oltp-dist-type=special --oltp-reconnect-mode=query --oltp-connect-delay=0 --db-driver=mysql --mysql-user=root  --mysql-host=$server_host --mysql-port=$server_port --num-threads=100 run

Difference in performance for the same test is clearly visible (5+ times in my case).

Noted in 5.6.8, 5.7.0 changelogs.

Random number generation during client authentication consumed
excessive CPU.