MySQL Database

Application description

MySQL is the world's most popular open source relational database management system (RDBMS). With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more. MySQL is based on a client-server model. The core of MySQL is MySQL server, which handles all of the database instructions (or commands).

Infrastructure Environment tested

The MySQL DB application has been tested on the following:

Resource	Value
Host OS	Ubuntu 18.04
Kernel Version	4.15.0-51-generic
Package manager	apt
Application version	Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)
Environments tested	AWS r5.2xlarge, Sunlight 2xlarge, VMWare 2xlarge

Configuration and Setup description

For the MySQL database the TPS as a key performance indicator could be affected by a number of factors, including the size of database, number of concurrent users and various database configurations, including buffer size as an important setting. The databases with difference size up to nearly 1 TB are generated and tested. The benchmarking tool sysbench is used to generate the databases and run the stress test against the generated databases. The steps to deploy MySQL server and run the tests are as follows.

Installation of MySQL server and utility tools

Running the following script in the host OS will install the MySQL server and deploy the tools required to run the test against this MySQL server.

• mysql_deploy.sh

sudo apt-get update
sudo apt-get install mysql-server
sudo service mysql status
sudo mysql -h localhost -u root < mysql_config.sql
sudo apt install apparmor-utils
sudo aa-complain /usr/sbin/mysqld
sudo apt-get install unzip
sudo apt-get install sysbench
sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial
sudo wget https://github.com/datacharmer/test_db/archive/master.zip
sudo unzip master.zip
cd test_db-master/
mysql -h localhost -u sysadmin -p -t < employees.sql

• mysql_config.sql

create user sysadmin identified by 'sunlight';
grant all on *.* to sysadmin;
quit;

Database preparation

The sysbench benchmarking tool with the following parameters will generate a database with 200 tables with 10,000,000 records in each table, which leads to around 500 GB data.

sysbench /usr/share/sysbench/oltp_read_write.lua --threads=6 --mysql-host=127.0.0.1 --mysql-user=sysadmin --mysql-password=sunlight --mysql-port=3306 --mysql-db=employees --db-driver=mysql --tables=200 --table-size=10000000 prepare

Running the test

The test is simulating a certain number of users to randomly read and write to the specified database for a period. The sysbench tool with the parameters below will act as 8 clients to read and write to the tables generated previously for 120 seconds.

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-user=sysadmin --mysql-password=sunlight --mysql-port=3306 --mysql-db=employees --db-driver=mysql --tables=200 --table-size=10000000 --report-interval=10 --threads=8 --time=120 run 2>&1

This test is performing read, write and other queries against the database with ratio of 70%, 20% and 10% respectively.

Data results table

Flavour	Number of Tables	Number of Threads	TPS	QPS	Latency in average	Latency in 95th percentile
Sunlight 2xlarge	20	8	930	18605	8.59	13.46
AWS r5.2xlarge + guaranteed IOPs	20	8	405	8095	19.76	39.65
VMware 2xlarge	20	8	341	6822	23.44	51.94

Each table has 10,000,000 records, which is around 2.5 GB data in storage. The results above is based on the sysbench test above against 20 tables (~ 50 GB). The results with different number of tables are illustrated in the following figures.

Performance graphs

^{As each table is about 2.5 GB, when the table size is less than 1, it means the test is just working against partial records of a table. The minimum size of table we tested is 0.0001, which is about 1000 records of a table. When the table dataset is less than 250 MB (i.e. 0.1 of a table with 10,000,000 records), Sunlight is generating worse results than AWS and VMware, because the CPU plays an important role when the storage path isn't the performance bottleneck. AWS and VMware has higher CPU frequency of 3.1 GHz, while Sunlight has a CPU with just 2.2 GHz. With the increase of number of tables, Sunlight is performing better than the others increasingly. When the dataset is about 500 GB with 200 tables, Sunlight performs nearly 5 times better than AWS and VMware.}

Impact of database size

Impact of concurrent users

Impact of database buffer size