Benchmark: DBS vs. MySQL vs. MariaDB vs. AWS Aurora (MySQL compatible)

1. To avoid potential "Oracle GPG package signature failure" problem:
   # Download MySQL GPG key sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
2. Add the MySQL yum repository:
   # 1. Download the MySQL yum repo. RPM sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm # 2. Install the MySQL yum repo. sudo rpm -ivh mysql80-community-release-el7-5.noarch.rpm
3. Install MySQL Community Server:
   # Install MySQL Community Server and its dependencies sudo yum install mysql-server -y
4. Enable & start MySQL Server service:
   # 1. Enable MySQL Server service sudo systemctl enable mysqld # 2. Start MySQL Server service sudo systemctl start mysqld
5. Verify that MySQL Server is running:
   # Check MySQL Server service status sudo systemctl status mysqld
6. Initialise the MySQL Server configuration:
   Before all, it is needed to retrieve the default root password that has been randomly generated during the installation process.
   # Retrieve temporary password generated for 'root@localhost' from MySQL Server log sudo grep 'temporary password' /var/log/mysqld.log
   Now the secure configuration tool can be launched:
   # Start MySQL Server secure configuration tool sudo mysql_secure_installation
   The following options are selected:
   # Start MySQL Server secure configuration tool -> Securing the MySQL server deployment. -> Enter password for user root: Enter the retrieved password -> The existing password for the user account root has expired. Please set a new password. -> New password: Enter the new password -> Re-enter new password: Confirm the new password -> Change the password for root ? ((Press y|Y for Yes, any other key for No) : y -> By default, a MySQL installation has an anonymous user, -> allowing anyone to log into MySQL without having to have -> a user account created for them. This is intended only for -> testing, and to make the installation go a bit smoother. -> You should remove them before moving into a production -> environment. -> Remove anonymous users? (Press y|Y for Yes, any other key for No) : y -> Normally, root should only be allowed to connect from -> 'localhost'. This ensures that someone cannot guess at -> the root password from the network. -> Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n -> ... skipping. -> By default, MySQL comes with a database named 'test' that -> anyone can access. This is also intended only for testing, -> and should be removed before moving into a production -> environment. -> Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y -> - Dropping test database... -> Success. -> - Removing privileges on test database... -> Success. -> Reloading the privilege tables will ensure that all changes -> made so far will take effect immediately. -> Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y -> Success. -> All done!
   Note: Despite answering N to the "Disallow root login remotely?" question, you may encounter an Access Denied error during the first remote connection with mysql client. If that happens, you will need to connect to the server locally and execute the following commands:

   # Solve 'access denied' issue for remote login mysql -h server_ip_address -u root -p -e "UPDATE mysql.user SET host = '%' WHERE user = 'root' AND host = 'localhost';" # Restart MySQL Server service sudo systemctl restart mysqld
7. It is more than likely that the MySQL server is listening on TCPIPv6 type addresses only. To verify the addresses bound by MySQL Server for listening:
   # List addresses & ports MySQL Server is listening sudo netstat -tulpn | grep LISTEN | grep mysqld -> tcp6 0 0 :::3306  :::* LISTEN pid/mysqld -> tcp6 0 0 :::33060 :::* LISTEN pid/mysqld
   If so, it is suggested to make MySQL Server also listen on TCPIPv4 type addresses to simplify connectivity from the client.
   To do this, edit the MySQL Server configuration file /etc/my.cnf and add the following under the [mysqld] section:
   [mysqld] bind-address=0.0.0.0
   Then, it is required to restart the MySQL Server to apply the changes:
   # Restart MySQL Server service sudo systemctl restart mysqld
   Check again the addresses bound by MySQL Server for listening:
   # List addresses & ports MySQL Server is listening sudo netstat -tulpn | grep LISTEN | grep mysqld -> tcp  0 0 0.0.0.0:3306 0.0.0.0:* LISTEN pid/mysqld -> tcp6 0 0 :::33060     :::*      LISTEN pid/mysqld

3. Install MySQL Community Client:
   # Install MySQL Community Client and its dependencies sudo yum install mysql -y
4. Verify that client is able to connect to the server:
   # Connect MySQL Server instance mysql -h host_ipv4_address -u root -p

1. Copy mysql_mariadb/Bench_MySQL8_DDL.sql to one of the client dedicated EC2-Instances, wherever it is accessible from MySQL Client application.
2. Copy mysql_mariadb/Bench_MySQL8_url_locX.sql, from 1 through 4, to their respective client dedicated EC2-Instance, wherever they are accessible from MySQL Client application.
3. Copy the following 6 files to the server dedicated EC2-Instance, in the /var/lib/mysql-files/ directory:
   • datasets/cities.csv
   • datasets/countries.csv
   • datasets/dialogues.csv
   • datasets/ipaddresses.csv
   • datasets/regions.csv
   • datasets/urls.csv
4. Change copied files ownership to ensure mysqld process is allowed to read them:
   # Change dataset sources ownership chown mysql:mysql /var/lib/mysql-files/*.csv

1. Edit the MariaDB yum repository by either replacing the entire content of the configuration file or adding to it with the following:
   # Create/Edit /etc/yum.repos.d/MariaDB.repo [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/11.rc/centos7-amd64 gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
2. Install MariaDB Community Server:
   # Install MariaDB Community Server and its dependencies sudo yum install mariadb-server -y
3. Enable & start MariaDB Server service:
   # 1. Enable MariaDB Server service sudo systemctl enable mariadb # 2. Start MariaDB Server service sudo systemctl start mariadb
4. Verify that MariaDB Server is running:
   # Check MariaDB Server service status sudo systemctl status mariadb
5. Initialise the MariaDB Server configuration:
   When MariaDB Server is installed, there is no root password defined, it is then required to set it.
   # Connect to local MariaDB Server sudo mariadb -u root
   Once locally connected to the server, execute the following 3 commands:
   # Clean up unused DELETE FROM mysql.user WHERE host <> 'localhost' OR TRIM(user) = ''; -> Query OK, 5 rows affected (0.000 sec) # Set root password SET PASSWORD = PASSWORD('your_password'); -> Query OK, 0 rows affected (0.000 sec) # Allow remote login UPDATE mysql.user SET host = '%' WHERE user = 'root' AND host = 'localhost'; -> Query OK, 1 row affected (0.000 sec)
   Restart the server:
   # Restart MariaDB Server service sudo systemctl restart mariadb

2. Install MariaDB Community Client:
   # Install MariaDDB Community Client and its dependencies sudo yum install mariadb -y
3. Verify that client is able to connect to the server:
   # Connect MariaDB Server instance mariadb -h host_ipv4_address -u root -p

1. Copy mysql_mariadb/Bench_MySQL8_DDL.sql to one of the client dedicated EC2-Instances, wherever it is accessible from MariaDB Client application.
2. Copy mysql_mariadb/Bench_MySQL8_url_locX.sql, from 1 through 4, to their respective client dedicated EC2-Instance, wherever they are accessible from MariaDB Client application.
3. Copy the following 6 files to the server dedicated EC2-Instance, in the /var/lib/mysql/ directory:
   • datasets/cities.csv
   • datasets/countries.csv
   • datasets/dialogues.csv
   • datasets/ipaddresses.csv
   • datasets/regions.csv
   • datasets/urls.csv
4. Change copied files ownership to ensure mariadbd process is allowed to read them:
   # Change dataset sources ownership chown mysql:mysql /var/lib/mysql/*.csv

1. Select the Databases menu and click on Create database button. Then:
   • Select Standard create as Database creation method option.
   • Select Aurora (MySQL compatible) as Engine type option.
   • Select Aurora MySQL 3.08.2 (compatible with MySQL 8.0.39) as Engine version option, which is currently the latest one.
   • Select Dev/Test as Template option.
   • Optionally, change the default name of the cluster identifier.
   • Carefully set your Master username. This user is the database server's primary user which compulsorily replaces the default MySQL/MariaDB root user.
   • Select Self managed as Credentials management option and define carefully your password.
   • Select Aurora standard as Configuration option.
   • Select Serverless v2 as DB instance class option.
   • Set 2 ACUs as Minimum capacity and 8 ACUs as Maximum capacity options. The reason is that it is not possible to precisely define the number of CPUs and memory separately in this type of configuration. That said, by specifying the proposed values ranging from 2 to 8 ACUs, Aurora starts with approximately the equivalent of 4 CPUs and 4 GiB of memory, and automatically scales up to a maximum of 16 CPUs and 16 GiB of memory in case of load increase; this allows for a configuration that closely matches the other tested technologies.
   • Select Don't create an Aurora Replica as Multi-AZ deployment option. It is useless to configure a full replicated cluster in current benchmark context.
   • Select Don't connect to an EC2 compute resource as Compute resource option.
   • Select IPv4 as Network type option.
   • Select the VPC and the DB subnet group according to the ones where your client dedicated EC2-Instances reside.
   • Select No as Public access option.
   • Select Choose existing as VPC security group option and select the Security Group you previously defined.
   • Select the Availability zone according to the ones where your client dedicated EC2-Instances reside.
   • Uncheck the RDS Proxy checkbox.
   • Uncheck the Enable Performance insights checkbox.
   • Check the Enhanced Monitoring checkbox, select 60 seconds and select default as respectively OS metrics granularity and Monitoring role for OS metrics options.
   • Check the Error Log checkbox as Log exports option.
   • Leave all other options at their default settings.
2. Review the various settings and click on Create database button underneath.
3. Once the Aurora instance is created, it is necessary to collect both the Reader and the Writer endpoints in order to be able to connect them with the client applications.

If you followed the order of the technologies presented here, Aurora being based on MariaDB, the client dedicated installations can be retained and thus reused.

If you didn't follow the order of the technologies presented here, the procedure II. MariaDB Clients is the one that can be carried out.

Since RDS doesn't allow access to the underlying system, the files must be uploaded from client application, which intrinsically has a slight impact on the measurements.
So, once MariaDB Client is installed, it is recommended to ensure that the server allows local file upload:

1. Copy mysql_mariadb/Bench_MySQL8_DDL.sql to one of the client dedicated EC2-Instances, wherever it is accessible from MariaDB Client application.
2. Copy mysql_mariadb/Bench_MySQL8_url_locX.sql, from 1 through 4, to their respective client dedicated EC2-Instance, wherever they are accessible from MariaDB Client application.
3. Copy the following 6 files to the one of the client dedicated EC2-Instances, wherever it is accessible from MariaDB Client application:
   • datasets/cities.csv
   • datasets/countries.csv
   • datasets/dialogues.csv
   • datasets/ipaddresses.csv
   • datasets/regions.csv
   • datasets/urls.csv

1. On the server dedicated instance, once the RPM is available:
   # Install server RPM sudo rpm -i dbsd-x64-0.99-beta-rc-1.rpm
2. When DBS Server is installed, it is configured with default restrictions for security reasons. To waive these restrictions, you must edit the /etc/dbs/dbs.conf file and change the following settings, as the purpose of this benchmark is not to evaluate security aspects:
   # 1. Disable 'password policy' enforcement [Instance] ... passwordpol=false # 2. Bind all system TCPIPv4 addresses svctcpipv4addr=0.0.0.0 # 3. Allow up to 5 simultaneaous sessions (Concurrent write test) svcmaxsessions=5
3. Enable & start DBS Server service:
   # 1. Enable DBS Server service sudo systemctl enable dbsd # 2. Start DBS Server service sudo systemctl start dbsd
4. Verify that DBS Server is running:
   # Check DBS Server service status sudo systemctl status dbsd
5. Verify the addresses bound by DBS Server for listening:
   # List addresses & ports DBS Server is listening sudo netstat -tulpn | grep LISTEN | grep dbsd -> tcp  0 0 0.0.0.0:4404 0.0.0.0:* LISTEN pid/dbsd -> tcp6 0 0 ::1:4404     :::*      LISTEN pid/dbsd

1. On each client dedicated instance, once the RPM is available:
   # Install client RPM sudo rpm -i dbs-x64-0.99-beta-rc-1.rpm
2. Verify DBS Server connection:
   # Connect DBS Server using 'dbsa' (Default password = dbsa) dbscli -h server_ip_address -u dbsa -p

1. Copy dbs/Bench_DBS_DDL.sql to one of the client dedicated EC2-Instances, wherever it is accessible from DBS Client application.
2. Copy dbs/Bench_DBS_url_locX.sql, from 1 through 4, to their respective client dedicated EC2-Instance, wherever they are accessible from DBS Client application.
3. Copy the following 6 files to the server dedicated EC2-Instance, in the /var/dbs/data/lsf/ directory:
   • datasets/cities.csv
   • datasets/countries.csv
   • datasets/dialogues.csv
   • datasets/ipaddresses.csv
   • datasets/regions.csv
   • datasets/urls.csv
4. Change copied files ownership to ensure dbsd process is allowed to read them:
   # Change dataset sources ownership chown dbs:dbs /var/dbs/data/lsf/*.csv

Note: By running the following commands, the 'bench' schema is automatically mounted; this allows skipping this stage, which usually takes 0.5 to 2 seconds, during the test sequences below.