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.
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:
--mysql-ssloption only accepts on or off, and
SSL_MODEis fixed to
- 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
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_MODEsupported in MySQL client used to build the sysbench binary to
- Certificate files are no longer mandatory.
- You can specify each certificate file paths to
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
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! ...
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.
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.