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:
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

[28 Aug 2012 7:57] Yoshinori Matsunobu
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';
[28 Aug 2012 23:37] Ben Krug
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.
[29 Aug 2012 7:32] Valeriy Kravchuk
What exact sysbench commands you had used?
[29 Aug 2012 7:36] Yoshinori Matsunobu
[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
[29 Aug 2012 8:46] Valeriy Kravchuk
Difference in performance for the same test is clearly visible (5+ times in my case).
[13 Nov 2012 18:32] Paul Dubois
Noted in 5.6.8, 5.7.0 changelogs.

Random number generation during client authentication consumed
excessive CPU.