Flexway Tablespace-Engine 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 ss -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 ss -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

1. Copy mysql_mariadb/Bench_MySQL8_DDL.sql to the server dedicated EC2-Instance.
2. 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
3. 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

Import the dataset sources in the following order:

1. dialogues.csv
   # Import 'dialogues' dataset LOAD DATA INFILE '/var/lib/mysql-files/dialogues.csv' INTO TABLE benchdb0.dialogues FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (uuid,dlg_en); -> Query OK, 120668 rows affected
2. regions.csv
   # Import 'regions' dataset LOAD DATA INFILE '/var/lib/mysql-files/regions.csv' INTO TABLE benchdb0.regions FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (region_code,@nparent_code,dlg_uuid) SET parent_code = NULLIF(@nparent_code, ''); -> Query OK, 31 rows affected
3. countries.csv
   # Import 'countries' dataset LOAD DATA INFILE '/var/lib/mysql-files/countries.csv' INTO TABLE benchdb0.countries FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (iso_2dg,iso_3dg,dlg_uuid,@npcountry_iso2dg,region_code) SET pcountry_iso2dg = NULLIF(@npcountry_iso2dg, ''); -> Query OK, 252 rows affected
4. cities.csv
   # Import 'cities' dataset LOAD DATA INFILE '/var/lib/mysql-files/cities.csv' INTO TABLE benchdb0.cities FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (uuid,country_iso2dg,dlg_uuid); -> Query OK, 141786 rows affected
5. ipaddresses.csv
   # Import 'ipaddresses' dataset LOAD DATA INFILE '/var/lib/mysql-files/ipaddresses.csv' INTO TABLE benchdb0.ipaddresses FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (network_cidr,network_start,network_end,country_iso2dg,city_uuid); -> Query OK, 4719740 rows affected
6. urls.csv
   # Import 'urls' dataset LOAD DATA INFILE '/var/lib/mysql-files/urls.csv' INTO TABLE benchdb0.urls FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (url_hash,url_src,country_iso); -> Query OK, 872238 rows affected

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 dedicated instance:
   # Connect MariaDB Server instance mariadb -h host_ipv4_address -u root -p

1. Copy mysql_mariadb/Bench_MySQL8_DDL.sql to the client dedicated EC2-Instance.
2. Copy the following 6 files to the client dedicated wherever you want as long as they are accessible from MariaDB Client, and to the server dedicated EC2-Instances, in the /var/lib/mysql/ directory:
   • datasets/cities.csv
   • datasets/countries.csv
   • datasets/dialogues.csv
   • datasets/ipaddresses.csv
   • datasets/regions.csv
   • datasets/urls.csv

Import the dataset sources in the following order:

1. dialogues.csv
   # Import 'dialogues' dataset LOAD DATA INFILE '/var/lib/mysql/dialogues.csv' INTO TABLE benchdb0.dialogues FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (uuid,dlg_en); -> Query OK, 120668 rows affected
2. regions.csv
   # Import 'regions' dataset LOAD DATA INFILE '/var/lib/mysql/regions.csv' INTO TABLE benchdb0.regions FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (region_code,@nparent_code,dlg_uuid) SET parent_code = NULLIF(@nparent_code, ''); -> Query OK, 31 rows affected
3. countries.csv
   # Import 'countries' dataset LOAD DATA INFILE '/var/lib/mysql/countries.csv' INTO TABLE benchdb0.countries FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (iso_2dg,iso_3dg,dlg_uuid,@npcountry_iso2dg,region_code) SET pcountry_iso2dg = NULLIF(@npcountry_iso2dg, ''); -> Query OK, 252 rows affected
4. cities.csv
   # Import 'cities' dataset LOAD DATA INFILE '/var/lib/mysql/cities.csv' INTO TABLE benchdb0.cities FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (uuid,country_iso2dg,dlg_uuid); -> Query OK, 141786 rows affected
5. ipaddresses.csv
   # Import 'ipaddresses' dataset LOAD DATA INFILE '/var/lib/mysql/ipaddresses.csv' INTO TABLE benchdb0.ipaddresses FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (network_cidr,network_start,network_end,country_iso2dg,city_uuid); -> Query OK, 4719740 rows affected
6. urls.csv
   # Import 'urls' dataset LOAD DATA INFILE '/var/lib/mysql/urls.csv' INTO TABLE benchdb0.urls FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (url_hash,url_src,country_iso); -> Query OK, 872238 rows affected

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 installation can be retained and thus reused.

Since RDS doesn't allow access to the underlying system, the files must be uploaded from client application.
It is therefore recommended to ensure that the server allows local file upload:

Import the dataset sources in the following order:

1. dialogues.csv
   # Import 'dialogues' dataset LOAD DATA LOCAL INFILE 'path_to_uploaded_file/dialogues.csv' INTO TABLE benchdb0.dialogues FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (uuid,dlg_en); -> Query OK, 120668 rows affected
2. regions.csv
   # Import 'regions' dataset LOAD DATA LOCAL INFILE 'path_to_uploaded_file/regions.csv' INTO TABLE benchdb0.regions FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (region_code,@nparent_code,dlg_uuid) SET parent_code = NULLIF(@nparent_code, ''); -> Query OK, 31 rows affected
3. countries.csv
   # Import 'countries' dataset LOAD DATA LOCAL INFILE 'path_to_uploaded_file/countries.csv' INTO TABLE benchdb0.countries FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (iso_2dg,iso_3dg,dlg_uuid,@npcountry_iso2dg,region_code) SET pcountry_iso2dg = NULLIF(@npcountry_iso2dg, ''); -> Query OK, 252 rows affected
4. cities.csv
   # Import 'cities' dataset LOAD DATA LOCAL INFILE 'path_to_uploaded_file/cities.csv' INTO TABLE benchdb0.cities FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (uuid,country_iso2dg,dlg_uuid); -> Query OK, 141786 rows affected
5. ipaddresses.csv
   # Import 'ipaddresses' dataset LOAD DATA LOCAL INFILE 'path_to_uploaded_file/ipaddresses.csv' INTO TABLE benchdb0.ipaddresses FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (network_cidr,network_start,network_end,country_iso2dg,city_uuid); -> Query OK, 4719740 rows affected
6. urls.csv
   # Import 'urls' dataset LOAD DATA LOCAL INFILE 'path_to_uploaded_file/urls.csv' INTO TABLE benchdb0.urls FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (url_hash,url_src,country_iso); -> Query OK, 872238 rows affected

1. Copy fw/Bench_FW_DDL.sql to the server dedicated EC2-Instance.
2. Copy the following 6 files to the server dedicated EC2-Instance, wherever you want as long as they are accessible from the CLI:
   • datasets/cities.sql
   • datasets/countries.sql
   • datasets/dialogues.sql
   • datasets/ipaddresses.sql
   • datasets/regions.sql
   • datasets/urls.sql

Import the dataset sources in the following order:

1. dialogues.sql
   # Import 'dialogues' dataset fwtsmcli -w benchmark.fwts < path_to_uploaded_file/dialogues.sql
2. regions.csv
   # Import 'regions' dataset fwtsmcli -w benchmark.fwts < path_to_uploaded_file/regions.sql
3. countries.csv
   # Import 'countries' dataset fwtsmcli -w benchmark.fwts < path_to_uploaded_file/countries.sql
4. cities.csv
   # Import 'cities' dataset fwtsmcli -w benchmark.fwts < path_to_uploaded_file/cities.sql
5. ipaddresses.csv
   # Import 'ipaddresses' dataset fwtsmcli -w benchmark.fwts < path_to_uploaded_file/ipaddresses.sql
6. urls.csv
   # Import 'urls' dataset fwtsmcli -w benchmark.fwts < path_to_uploaded_file/urls.sql