Introducing MariaDB S3 Storage Engine for database archiving and performance

MariaDB S3 storage engine

MariaDB S3 is a read only storage engine based on Aria code which allows you to archive MariaDB tables in Amazon S3 (on the size of s3_block_size) or any third-party public or private cloud that implements S3 API (of which there are many), but still have them accessible for reading in MariaDB. Internally, The S3 storage inherits from Aria code and hooks that change reads, So that instead if reading from local disk it reads from S3. We recommend S3 storage engine for our MariaDB 10.5 customers (if you are planning the update to MariaDB 10.5, We strongly recommend you to read our blogs MariaDB 10.5. New Features- Upgrade from MariaDB 10.4 to MariaDB 10.5 ) with very high volume tables which would become fairly inactive, but are still important so that they can not be removed. In that case, an option is to move such a table to an archiving service, which is accessible through an S3 API. So MariaDB S3 is a technically and commercially cost  efficient storage engine built for MariaDB archiving.

Installing MariaDB S3 Storage Engine

The S3 storage engine is available from MariaDB 10.5.4.

[mysqld]
plugin-maturity = alpha

Note: S3 storage engine is currently in alpha maturity   , So you cannot load by default S3 storage engine on MariaDB Server stable release ( this happens due to default value of system variable plugin_maturity ). To enable S3 storage engine you have to set plugin-maturity = alpha and restart the server.

Install plugin library:

INSTALL SONAME 'ha_s3';

How can you move data to S3 storage engine ?

If you want to move data from an existing table to S3 storage engine, Please run:

ALTER TABLE table_name ENGINE=S3

To move data back to a regular InnoDB table,  Please run:

ALTER TABLE s3_table_name ENGINE=INNODB

MariaDB S3 storage engine performance

The following are some best practices to consider for optimal performance on MariaDB S3 storage engine:

• MariaDB S3 Tables supports all the options (including ALTER TABLE) supported by Aria engine:
  • ALTER TABLE
  • DROP TABLE
  • SELECT 
  • SHOW TABLES
  • MariaDB S3 TABLES can also participate actively on Partitioning
• s3_block_size  is the default block size of the table, For most of the load the default size 4M is sufficient and we don’t recommend increasing this system variable
• Be conservative about querying information_schema tables as S3 has to check if there is new tables in S3.
• DROP statements on non existing tables are slower as S3 has to check if the table is in S3.
• MariaDB S3 Tables can use COMPRESSION_ALGORITHM=zlib to reduce the amount of data transferred from S3 to the local cache.
• If you are expecting high volume MariaDB S3 tables, Then we strongly recommend you to increase s3_pagecache_buffer_size ( sequel to innodb_buffer_pool_size of InnoDB and default value is 128M ) for optimal caching and better index handling. 
• MariaDB S3 Tables I/O performance also greatly depend on your connection speed to your S3 provider.

Conclusion

MariaDB powers database infrastructure for some of the largest planet-scale internet properties, This means handling database volume, performance, scalability, reliability, capacity planning / sizing and archiving are some of the most complex problems to solve. Thanks to MariaDB Server Engineering Team for coming up with S3 storage engine which is seamlessly addressing database archiving across multiple (vendor neutral and independent ) S3 providers.

☛ Want to engage MinervaDB for MariaDB Consulting, Enterprise-Class 24*7 Support and Remote DBA Services ?