MySQL Bugs: #89926: Performance degradation in libmysqlclient 20

Bug #89926	Performance degradation in libmysqlclient 20
Submitted:	6 Mar 2018 10:20	Modified:	12 Mar 2018 12:21
Reporter:	Bence Kiglics	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S5 (Performance)
Version:	5.7	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	libmysqlclient, performance

Description:
During an Ubuntu OS upgrade, we observed some serious performance degradation in one of our services. Our investigation revealed that the component affecting our performance is the newer version of MySQL connector.

The service is running on Amazon AWS HVM nodes and connects to an Amazon RDS database (MySQL 5.7.16).

The application's binding is linked against libmysqlclient.so.

The binding we used originally:
libmysqlclient18 shipped with MySQL 5.5.54

The binding in the new (under-performing) system:
libmysqlclient20 shipped with MySQL 5.7.21

We are certain that the latest version on the C binding causes the issue. We tried to rule out everything else that could affect the performance:

- We thought that it might be a thread management issue with Python, so we implemented a simple C application that opens several connections to MySQL.
- It seemed possible that the virtualized enviromnent causes the degradation, so we tested it on an older Dell XPS laptop to see how it performs on "bare metal".
- Meltdown and Spectre came up as a possible cause, we tested it with an older (unpatched) Linux kernel (3.13), also on bare metal.
- We tried it on several computers using a Docker-based environment (included in this bug report).

We built a test framework to test it with the two latest supported versions at the time, 5.6.39 and 5.7.21.

Some results:

Using the binding from mysql-5.6.39:
Opening 100 parallel connections took 0.024433 seconds
Opening 1000 parallel connections took 0.100703 seconds
Opening 100 individual connections took 0.024807 seconds
Opening 1000 individual connections took 0.235232 seconds

Using the binding from mysql-5.7.21:
Opening 100 parallel connections took 1.794089 seconds
Opening 1000 parallel connections took 2.796837 seconds
Opening 100 individual connections took 1.830826 seconds
Opening 1000 individual connections took 18.145618 seconds

How to repeat:
Try connecting to MySQL with both versions of the libraries several times and measure the time it takes. The example code we used to measure the performance difference can be found here: https://github.com/kbence/mysql-performance-tests/blob/287e15aa5dc2f736e9510b90fab20b53530...

This repository contains everything to reproduce the issue: https://github.com/kbence/mysql-performance-tests
To test it in a Docker environment, clone it, make sure Docker and Docker Compose is installed and issue the command `docker compose up`. It'll run the test with the included MySQL versions (client only, server compilation is excluded).

If you want to test it with other versions, you can change/add them in the script found at `scripts/compile_versions.sh`, then issue a `docker compose build && docker compose up`.

If you don't want to go through compiling Boost 1.59 and MySQL 5.6 & 5.7, replace `build: .` with `image: kbence/mysql-performance-tests` in `docker-compose.yaml`.

could be 5.7 using ssl by mistake...

Hello Bence,

Thank you for the report and test case.
Observed that:

-- Server 5.7.16 - ssl disabled
Not much difference in 5.6 and 5.7
-- Server 5.7.16 - ssl enabled
Noticeable performance drop when using 5.7 libraries

Could you please confirm whether ssl enabled on server? I'm joining details shortly.

Thank you Shane for all the helps on this.

Thanks,
Umesh

test results - 5.6/5.7

Attachment: 89926.results (application/octet-stream, text), 28.46 KiB.

Hi Umesh,

I can confirm that SSL is enabled and it can cause the issue we've been experiencing.
Is it possible that the 5.6 client falls back (or doesn't even initiate SSL connection) when 5.7 doesn't while connecting to a 5.7 server, causing us our performance issue?

Thanks,
Bence

Hello Bence,

Thank you for confirming that SSL is enabled on Server.
Quoting from official blog "With MySQL 5.7, libmysql will attempt SSL connection by default. If server supports encrypted connection, this change guarantees encrypted connection out-of-the-box.." https://mysqlserverteam.com/secure-by-default-in-mysql-5-7/

There will be *some* overhead in ssl connection compared to non-ssl. There are few related issues/feature requests raised in the past (Quoting my colleague Shane's note from Bug #89213) 

https://bugs.mysql.com/bug.php?id=86215
https://bugs.mysql.com/bug.php?id=89926
http://smalldatum.blogspot.co.za/2017/07/i-overstated-cpu-regression-in-mysql-57.html

I tried the work around suggested by my senior colleague Shane to disable at client side and this workaround works and performance is more or less same as 5.6 libmysql.

=== SSL enabled Server 5.7.16, disabled SSL mode at the client level

ushastry@ArtfulAardvark:~/Downloads/5_7$ vi test_mysql_connect.c 
ushastry@ArtfulAardvark:~/Downloads/5_7$ cat test_mysql_connect.c |grep -A2 -B2 'mysql_option'
    MYSQL *mysql = mysql_init(NULL);
    unsigned int helpme= SSL_MODE_DISABLED; 
    mysql_options(mysql,MYSQL_OPT_SSL_MODE,&helpme);
	if (!mysql_real_connect(mysql,hostname,username,password,NULL,3306,NULL,CLIENT_COMPRESS))
	{

ushastry@ArtfulAardvark:~/Downloads/5_7$ export LD_LIBRARY_PATH=/home/ushastry/Downloads/5_7/lib
ushastry@ArtfulAardvark:~/Downloads/5_7$ gcc -o test_mysql_connect test_mysql_connect.c `/home/ushastry/Downloads/5_7/bin/mysql_config --cflags --libs`
ushastry@ArtfulAardvark:~/Downloads/5_7$ ldd test_mysql_connect
	linux-vdso.so.1 =>  (0x00007ffc3f9b8000)
	libmysqlclient.so.20 => /home/ushastry/Downloads/5_7/lib/libmysqlclient.so.20 (0x00007fcabe443000)
	libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fcabe063000)
	libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fcabde5f000)
	libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fcabdc40000)
	libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fcabd8ba000)
	libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fcabd564000)
	libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fcabd34d000)
	/lib64/ld-linux-x86-64.so.2 (0x00007fcabec61000)
ushastry@ArtfulAardvark:~/Downloads/5_7$ ./test_mysql_connect 
Opening 100 parallel connections took 0.077748 seconds
Opening 1000 parallel connections took 0.813810 seconds
Opening 100 individual connections took 0.092595 seconds
Opening 1000 individual connections took 0.757946 seconds
Server host name and the connection type  127.0.0.1 via TCP/IP 
MySQL server version  5.7.16 
Currently used cipher of the TLS connection  DHE-RSA-AES256-SHA 
Client compile version: 50721
Client runtime version: 5.7.21

Regards,
Umesh

Hi Umesh,

I also got to the same conclusion (I turned off SSL as well in the example I sent). Unfortunately, our current Python binding doesn't let us to turn off that settings, but I noticed there's an official Python connector which can accept the `ssl_disabled` argument and also supports connection pooling, so we'll start looking around there.

Thank you for your help,
Bence