MariaDB

Application Description

MariaDB is a fork of the MySQL relational database management system, so the database structure and indexes of MariaDB are the same as MySQL. MariaDB has a larger connection pool supporting up to 200,000+ connections whereas MySQL has smaller connection pool. MariaDB shows an improved speed when compared to MySQL.

Infrastructure Environment Tested

The MariaDB has been tested on the following setups:

Resource	Value
Host OS	Ubuntu 18.04
Kernel Version	4.15.0-45-generic
Package manager	apt
Application version
Environments tested	AWS r5.2xlarge, Sunlight 2xlarge,

Configuration and Setup Description

For the MariaDB the TPS and QPS as the key performance indicators 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 500GB 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 MariaDB 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 MariaDB server and deploy the tools required to run the test against this MariaDB server.

• mariadb_config.sql

GRANT ALL ON *.* TO 'sysadmin'@'localhost' IDENTIFIED BY 'sunlight' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

• mariadb_deploy.sh

sudo apt-get update
sudo apt-get install mariadb-server
sudo mysql_secure_installation
sudo mysql -h localhost -u root < mariadb_config.sql
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

MariaDB Tuning

sudo mysql -h localhost -u root

Increase the maximum number of connections allowed

MariaDB [(none)]> show global variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+

Increase it to 10 times of the current value

MariaDB [(none)]> set global max_prepared_stmt_count=163820;
Query OK, 0 rows affected (0.00 sec)

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 1200 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=1200 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	QPS	Latency in 95th percentile
AWS 2xlarge	200	8	2942.38	23.44
AWS r5.2xlarge + guaranteed IOPs	200	8	5302.2	47.47
Sunlight 2xlarge	200	8	13019.4	21.5

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 200 tables (~ 500 GB). The results with different number of tables are illustrated in the following figures.

Performance graphs