AWS Database Blog

Running sysbench on RDS MySQL, RDS MariaDB, and Amazon Aurora MySQL via SSL/TLS

sysbench is an ideal tool for running synthetic benchmarking on MySQL compatible databases. The Amazon Aurora Performance Assessment Technical Guide helps you assess the performance of Amazon Aurora MySQL by using sysbench. However, if you want to run sysbench on MySQL-compatible databases running on RDS or Aurora via SSL/TLS, you also need to consider some restrictions on the tool and AWS services.

This post discusses those consideration points and how you should prepare to run sysbench on RDS MySQL, RDS MariaDB, and Aurora MySQL.

Consideration points

The latest package release of sysbench is 1.0.17. If you install sysbench via package managers such as yum or RPM package, you get this version of sysbench. In this version, sysbench has the following restrictions when using SSL/TLS:

  • The --mysql-ssl option only accepts on or off, and SSL_MODE is fixed to REQUIRED.
  • A client private key, client public key, and CA certificate are all mandatory.
  • The client private key, client public key, and CA certificate paths are fixed to client-key.pem, client-cert.pem, and cacert.pem, respectively.

Because RDS doesn’t provide a client private key for SSL connections, but sysbench 1.0.17 requires a client private key, sysbench is unable to connect to RDS MySQL, RDS MariaDB, or Aurora MySQL via SSL/TLS. If you enable SSL/TLS on sysbench 1.0.17, you see the following error message:

$ sysbench oltp_read_write --db-driver=mysql ... --mysql-host=rds-mysql.***.ap-northeast-1.rds.amazonaws.com --mysql-ssl=on prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

FATAL: unable to connect to MySQL server on host 'rds-mysql.***.ap-northeast-1.rds.amazonaws.com', port 3306, aborting...
FATAL: error 2026: SSL connection error: SSL_CTX_set_default_verify_paths failed
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed

In version 1.1, sysbench has removed the restrictions thanks to this commit, allowing the following:

  • You can specify any SSL_MODE supported in MySQL client used to build the sysbench binary to --mysql-ssl option.
  • Certificate files are no longer mandatory.
  • You can specify each certificate file paths to --mysql-ssl-key, --mysql-ssl-cert and --mysql-ssl-ca options.

Therefore, if you use sysbench 1.1, you can connect to RDS MySQL, RDS MariaDB, or Aurora MySQL from sysbench via SSL/TLS with SSL_MODE = REQUIRED, as the following command line shows:

$ sysbench oltp_read_write --db-driver=mysql ... --mysql-host=rds-mysql.***.ap-northeast-1.rds.amazonaws.com --mysql-ssl=REQUIRED --mysql-ssl-ca=rds-combined-ca-bundle.pem prepare
sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...

However, because sysbench 1.1 has not been released as a package, you need to build it from source code.

Building sysbench 1.1

Installing the prerequisite packages

Firstly, you need to install prerequisite packages to install MySQL libraries and build sysbench. You can install them by using the yum command if you are using Amazon Linux AMI, Amazon Linux 2, or Red Hat Enterprise Linux AMI, as the following command line shows:

$ sudo yum install git gcc make automake libtool openssl-devel ncurses-compat-libs

Additionally, you need to install MySQL client libraries and header files. If you are using Red Hat Enterprise Linux AMI, you can use the MySQL official yum repository. Add the MySQL yum repository by using the RPM package available on the MySQL Community Downloads website. After installing the yum repository, get MySQL client libraries and header files via yum with the following command line:

$ sudo yum --enablerepo=mysql-80-community install mysql-community-devel mysql-community-client mysql-community-common

If you are using Amazon Linux AMI or Amazon Linux 2, you need to download MySQL 8.0.16 RPM packages from the MySQL Product Archives website, because MySQL 8.0.17 or newer requires OpenSSL 1.1.1 and glibc 2.28, which are not available in Amazon Linux repositories, as the following command lines show:

$ sudo yum install mysql-community-common-8.0.16-2.el7.x86_64.rpm
$ sudo yum install mysql-community-libs-8.0.16-2.el7.x86_64.rpm
$ sudo yum install mysql-community-client-8.0.16-2.el7.x86_64.rpm
$ sudo yum install mysql-community-devel-8.0.16-2.el7.x86_64.rpm

You need to install MySQL client libraries and header files of MySQL 8.0.12 or later because Aurora Serverless uses wildcard certificates and MySQL started supporting wildcard certificates from MySQL 8.0.12. You can see the change in the MySQL 8.0.12 release note as follows:

Previously, for the –ssl-mode=VERIFY_IDENTITY option, the client checked whether the host name that it used for connecting matched the Common Name value in the certificate but not the Subject Alternative Name value. Now, if the client uses OpenSSL 1.0.2 or higher, the client checks whether the host name matches either the Subject Alternative Name value or the Common Name value in the server certificate. Thanks to Daniël van Eeden for a patch on which this change was based. (Bug #16211011, Bug #68052, Bug #27511233, Bug #89578)

Downloading sysbench source code by cloning the sysbench GitHub repo

You can clone the sysbench GitHub repo by using git clone, as the following command line shows:

$ git clone https://github.com/akopytov/sysbench

Building sysbench

You can build sysbench with a script using automake and libtool in the source code directory, as the following command line shows:

$ cd sysbench
$ ./autogen.sh
...
$ ./configure
...
$ make
...
$ sudo make install
$ sysbench --version
sysbench 1.1.0-174f3aa

Running sysbench to RDS and Aurora MySQL via SSL/TLS

You can now run sysbench to RDS MySQL, RDS MariaDB, or Aurora MySQL via SSL/TLS for any engine version, as the following command lines show:

$ sysbench oltp_read_write --db-driver=mysql ... --mysql-host=rds-mysql.***.ap-northeast-1.rds.amazonaws.com --mysql-ssl=REQUIRED prepare
sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
...
$ sysbench oltp_read_write --db-driver=mysql ... --mysql-host=rds-mysql.***.ap-northeast-1.rds.amazonaws.com --mysql-ssl=REQUIRED run
sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!
...

To use SSL_MODE = VERIFY_IDENTITY or VERIFY_CA, download the rds-combined-ca-bundle.pem certificate for RDS and Aurora MySQL. For Aurora Serverless MySQL, download the AmazonRootCA1.pem certificate.

SSL_MODE = VERIFY_IDENTITY is not supported while connecting to cluster and reader endpoints of Aurora MySQL or Aurora Serverless MySQL from sysbench. The cluster and reader endpoints are CNAME record pointing to instance endpoint(s), then the host name, which is passed to sysbench, and the host name of the instance to connect is different. This shouldn’t be a problem if Subject Alternative Names (SAN) is used in the MySQL client library properly, because the server certificate installed in Aurora DB instances includes all endpoints in SAN. However, the MySQL client library doesn’t use SAN to validate VERIFY_IDENTITY, but validates equivalency to Common Name (CN) instead, as of MySQL 8.0.18, as the code in MySQL GitHub repo shows.

Therefore, you need to use VERIFY_CA for cluster and reader endpoints or use an instance endpoint.

Conclusion

Security is the most important thing while running your application, and SSL/TLS helps to keep your database workload secure. However, using SSL/TLS imposes an overhead and higher resource consumption. Therefore, benchmarking is very important to measure the baseline impact arising from SSL/TLS.

In this post, I explained why some preparations are necessary to run sysbench on RDS MySQL, RDS MariaDB, and Amazon Aurora MySQL via SSL/TLS, demonstrated how to build sysbench to cover the use case, and showed you some considerations when using SSL/TLS on RDS MySQL, RDS MariaDB, and Amazon Aurora MySQL.

You can now run sysbench on RDS MySQL, RDS MariaDB, and Amazon Aurora MySQL to measure the baseline impact from SSL/TLS by following this post and hopefully have a better understanding for how it all works. As always, AWS welcomes your feedback in the comments below.

 


About the Author

 

Yoshihiko Matsuzaki is a Database Engineer with the Relational Database Services (RDS) team at Amazon Web Services.