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:
|Host OS||Ubuntu 18.04|
|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.
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
create user sysadmin identified by 'sunlight'; grant all on *.* to sysadmin; quit;
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|
|AWS r5.2xlarge + guaranteed IOPs||20||8||405||8095||19.76||39.65|
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.
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