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:
|Host OS||Ubuntu 18.04|
|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.
GRANT ALL ON *.* TO 'sysadmin'@'localhost' IDENTIFIED BY 'sunlight' WITH GRANT OPTION; FLUSH PRIVILEGES; exit
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
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)
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 r5.2xlarge + guaranteed IOPs||200||8||5302.2||47.47|
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.