MariaDB S3 Engine: Implementation and Benchmarking

MariaDB S3 Engine MariaDB 10.5 has an excellent engine plugin called “S3”. The S3 storage engine is based on the Aria code and the main feature is that you can directly move your table from a local device to S3 using ALTER. Still, your data is accessible from MariaDB client using the standard SQL commands. This is a great solution to those who are looking to archive data for future references at a low cost. The S3 engine is READ_ONLY so you can’t perform any write operations ( INSERT/UPDATE/DELETE ), but you can change the table structure.

In this blog, I am going to explain the details about the S3 engine’s implementation and aspects. And in the end, I compare the performance results from both Local and S3 engine tables.

S3 Engine Implementation

The S3 engine is alpha-level maturity and it will not load by default during MariaDB startup. You have to enable the S3 engine as follows:

[mysqld]
plugin-maturity = alpha

You also need to configure your S3 credentials in the MariaDB config file so that MariaDB can authenticate the connection and communicate with the S3 bucket. My config file looks like this:

[mysqld]
server-id = 101
plugin-maturity = alpha
log_error = /data/s3_testing_logs/mariadb.log
port = 3310

#s3
s3=ON
s3_access_key = xxxxxxxxxxxx
s3_secret_key = yyyyyyyyyyyyyyyyyyyyyyy
s3_bucket = mariabs3plugin
s3_region = ap-south-1
s3_debug = ON

Note: From a security perspective, your AWS credentials are plaintext. A new key pair should be created specifically for this plugin and only the necessary IAM grants be given.

After configuring the parameters, you need to restart MariaDB to apply the settings. After the restart, you can install the plugin as follows.

MariaDB [(none)]> install soname 'ha_s3';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> select * from information_schema.engines where engine = 's3'\G
*************************** 1. row ***************************
      ENGINE: S3
     SUPPORT: YES
     COMMENT: Read only table stored in S3. Created by running ALTER TABLE table_name ENGINE=s3
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
1 row in set (0.000 sec)

Now the S3 engine is ready to use.

How Do I Move The Table to The S3 Engine?

You can move the table to the S3 engine by using the ALTER. For testing, I have created the table “percona_s3” at my lab.

MariaDB [s3_test]> show create table percona_s3\G
*************************** 1. row ***************************
       Table: percona_s3
Create Table: CREATE TABLE `percona_s3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `c_date` datetime DEFAULT current_timestamp(),
  `date_y` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)

[root@ip-172-31-19-172 ~]# ls -lrth /var/lib/mysql/s3_test/* | grep -i percona_s3
-rw-rw----  1 mysql mysql 1019 Jul 14 01:50 /var/lib/mysql/s3_test/percona_s3.frm
-rw-rw----  1 mysql mysql  96K Jul 14 01:50 /var/lib/mysql/s3_test/percona_s3.ibd

Physically, you can see both .frm and .ibd files once the table is created (default InnoDB). I am going to convert the table “percona_s3” to the S3 engine.

#MariaDB shell

MariaDB [s3_test]> alter table percona_s3 engine=s3;
Query OK, 0 rows affected (1.934 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [s3_test]> show create table percona_s3\G
*************************** 1. row ***************************
       Table: percona_s3
Create Table: CREATE TABLE `percona_s3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `c_date` datetime DEFAULT current_timestamp(),
  `date_y` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=S3 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
1 row in set (1.016 sec)

#Linux shell

[root@ip-172-31-19-172 ~]# ls -lrth /var/lib/mysql/s3_test/* | grep -i percona_s3
-rw-rw----  1 mysql mysql 1015 Jul 14 01:54 /var/lib/mysql/s3_test/percona_s3.frm

Note: You will get the error “ERROR 3 (HY000):” if you enabled SELINUX, or if anything related to S3 access is misconfigured.

After converting to the S3 engine, you can see only the .frm file. The data has been migrated out of InnoDB and into the S3 engine storage format.

[root@ip-172-31-19-172 ~]# aws s3 ls s3://mariabs3plugin/s3_test/percona_s3/
                           PRE data/
                           PRE index/
2020-07-14 01:59:28       8192 aria
2020-07-14 01:59:28       1015 frm

[root@ip-172-31-19-172 ~]# aws s3 ls s3://mariabs3plugin/s3_test/percona_s3/data/
2020-07-14 01:59:29      16384 000001
[root@ip-172-31-19-172 ~]# aws s3 ls s3://mariabs3plugin/s3_test/percona_s3/index/
2020-07-14 01:59:28       8192 000001

Note: S3 will split the data and index pages and store them separately in respective folders.

S3 Engine Operation

For testing, I created the below table on S3. Let’s test the commands one by one.

MariaDB [s3_test]> select * from percona_s3;
+----+-----------------+---------------------+---------------------+
| id | name            | c_date              | date_y              |
+----+-----------------+---------------------+---------------------+
|  1 | hercules7sakthi | 2020-06-28 21:47:27 | 2020-07-01 14:37:13 |
+----+-----------------+---------------------+---------------------+
1 row in set (1.223 sec)

MariaDB [s3_test]> pager grep -i engine ; show create table percona_s3;
PAGER set to 'grep -i engine'
) ENGINE=S3 AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 |
1 row in set (0.798 sec)

S3 Engine with INSERT/UPDATE DELETE:

With all three statements, the query will return the “ERROR 1036: read only”.

Sample output:

MariaDB [s3_test]> insert into percona_s3 (name) values ('anti-hercules7sakthi');
ERROR 1036 (HY000): Table 'percona_s3' is read only

S3 Engine with SELECT:

MariaDB [s3_test]> select * from percona_s3;
+----+-----------------+---------------------+---------------------+
| id | name            | c_date              | date_y              |
+----+-----------------+---------------------+---------------------+
|  1 | hercules7sakthi | 2020-06-28 21:47:27 | 2020-07-01 14:37:13 |
+----+-----------------+---------------------+---------------------+
1 row in set (1.012 sec)

Adding Index to S3 Engine Table:

MariaDB [s3_test]> alter table percona_s3 add index idx_name (name);
Query OK, 1 row affected (8.351 sec)               
Records: 1  Duplicates: 0  Warnings: 0

Modifying the Column on S3 Engine Table:

MariaDB [s3_test]> alter table percona_s3 modify column date_y timestamp DEFAULT current_timestamp();
Query OK, 1 row affected (8.888 sec)               
Records: 1  Duplicates: 0  Warnings: 0

S3 Engine with DROP:

MariaDB [s3_test]> drop table percona_s3;
Query OK, 0 rows affected (2.084 sec)

Note: DROP TABLE will completely remove the data and index pages from S3 as well.

In short, the S3 will allow the read commands and the structure modification commands. Changing or adding any data into the S3 is restricted. MariaDB community is planning to allow the BATCH UPDATE (single user) on S3. Right now, if you need to change any data on S3 tables, you need to follow the below procedure:

Convert table from S3 to local (Engine = InnoDB)
Modify the data
Convert table from Local to S3 (Engine = S3)

You can also query the metadata from INFORMATION_SCHEMA and retrieve the metadata using the SHOW commands.

Comparing the Query Results on Both S3 and Local

In this section, I am going to compare the query results on both the S3 engine and Local. We need to consider the below points before going to the test results.

I have disabled the parameters “innodb_buffer_pool_dump_at_shutdown” and “innodb_buffer_pool_load_at_startup”.
MariaDB server has restarted before and after executing every single SQL query shown below.
MariaDB server and S3 are in the same zone.
The ping time between the MySQL and s3 is 1.18 ms

S3 vs Local ( Count(*) )

At S3:

MariaDB [s3_test]> select count(*) from percona_perf_compare;
+----------+
| count(*) |
+----------+
| 14392799 |
+----------+
1 row in set (0.16 sec)

At local:

MariaDB [s3_test]> select count(*) from percona_perf_compare;
+----------+
| count(*) |
+----------+
| 14392799 |
+----------+
1 row in set (18.718 sec)

Count(*) is faster on S3engine. S3 tables are read_only, and it might display the stored value like MyISAM.

S3 vs Local (Entire Table Data)

At S3:

MariaDB [s3_test]> pager md5sum; select * from percona_perf_compare;
PAGER set to 'md5sum'
1210998fc454d36ff55957bb70c9ffaf  -
14392799 rows in set (16.10 sec)

At Local:

MariaDB [s3_test]> pager md5sum; select * from percona_perf_compare;
PAGER set to 'md5sum'
1210998fc454d36ff55957bb70c9ffaf  -
14392799 rows in set (11.16 sec)

S3 vs Local (PRIMARY KEY based lookup)

At S3:

MariaDB [s3_test]> pager md5sum; select * from percona_perf_compare where id in (7196399);
PAGER set to 'md5sum'
13b359d17336bb7dcae344d998bbcbe0  -
1 row in set (0.22 sec)

At Local:

MariaDB [s3_test]> pager md5sum; select * from percona_perf_compare where id in (7196399);
PAGER set to 'md5sum'
13b359d17336bb7dcae344d998bbcbe0  -
1 row in set (0.00 sec)

S3 engine is pretty good with COUNT(*). And, if we retrieve the actual data from S3, we can see little delay compared to local.

I have conducted the above tests with the default S3 settings. As per the MariaDB document, we can consider the below things to increase the performance on S3:

Decreasing s3_block_size. This can be done both globally and per table.
Use COMPRESSION_ALGORITHM=zlib when creating the table. This will decrease the amount of data transferred from S3 to the local cache.
Increasing the size of the s3 page cache: s3_pagecache_buffer_size

I would say the performance also depends on the disk access speed and network health between server and S3. Consider the below points:

Having a low-performance disk and a good network between servers and S3 will favor S3.
Having a good performance disk and poor network between servers and S3 will favor Local.

Conclusion

This is a very good solution for data archival from MariaDB community. You can query the historical data without restoring.
The table is completely read-only.
COUNT(*) is pretty fast like MyISAM.
Pt-online-schema change will not work on both scenarios (S3 to Local & Local to S3). It will fail because of the INSERT (when copying the data) and CREATE (the S3 table options will not support for InnoDB).
CREATE TABLE, DROP TABLE, INFORMATION_SCHEMA tables are slower as those operations need to check the S3.
For copying the Aria tables, you need to use the tool aria_s3_copy

I am working with Agustin on our next blog on this, covering the S3 engine compression. Stay tuned!

by Sri Sakthivel via Percona Database Performance Blog

Search This Blog

Small Business News