S3 is introduced in MariaDB 10.5.
The S3 storage engine is read only and allows one to archive MariaDB tables in Amazon S3, 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.
To move data to S3 one can do
ALTER TABLE old_table ENGINE=S3
To get data back to a 'normal' table one can do:
ALTER TABLE s3_table ENGINE=INNODB
S3_BLOCK_SIZE
: Set to 4M as default. This is the block size for all index and data pages stored in S3. COMPRESSION_ALGORITHM
: Set to 'none' as default. Which compression algorithm to use for block stored in S3. Options are: none
or zlib
. To be able to use S3 for storage one *must* define how to access S3 and where data are stored in S3:
Other, less critical options, are:
Last some options you probably don't have to ever touch:
[mysqld-10.5] s3=ON s3-bucket=mariadb s3-access-key=xxxx s3-secret-key=xxx s3-region=eu-north-1 #s3-host-name=s3.amazonaws.com #s3-protocol-version=Auto [aria_s3_copy] s3-bucket=mariadb s3-access-key=xxxx s3-secret-key=xxx s3-region=eu-north-1 #s3-host-name=s3.amazonaws.com #s3-protocol-version=Auto
The typical use case would be that there exists tables that after some time 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.
Notice that S3 means the Cloud Object Storage API defined by Amazon AWS. Often the whole of Amazon’s Cloud Object Storage is referred to as S3. In the context of the S3 archive storage engine, it refers to the API itself that defines how to store objects in a cloud service, being it Amazon’s or someone else’s. OpenStack for example provides an S3 API for storing objects.
The main benefit of storing things in an S3 compatible storage is that the cost of storage is much cheaper than many other alternatives. Many S3 implementations also provide reliable long-term storage.
The S3 storage engine supports full MariaDB discovery. This means that if you have the S3 storage engine enabled and properly configured, the table stored in S3 will automatically be discovered when it's accessed with SHOW TABLES, SELECT or any other operation that tries to access it. In the case of SELECT, the .frm file from S3 will be copied to the local storage to speed up future accesses.
When an S3 table is opened for the first time (it's not in the table cache) and there is a local .frm file, the S3 engine will check if it's still relevant, and if not, update or delete the .frm file.
This means that if the table definition changes on S3 and it's in the local cache, one has to execute FLUSH TABLES
to get MariaDB to notice the change and update the .frm file.
Discovery of S3 tables is not done for tables in the mysql databases to make mysqld boot faster and more securely.
aria_s3_copy is an external tool that one can use to copy Aria tables to and from S3. Use aria_s3_copy --help
to get the options of how to use it.
mysqldump
is run with the --copy-s3-tables
option, the resulting file will contain a CREATE statement for a similar Aria table, followed by the table data and ending with an ALTER TABLE xxx ENGINE=S3
. FLUSH TABLES
for the MariaDB to notice that the definition has changed if the S3 table is in the table cache. ALTER TABLE ... ENGINE=S3
with an error that the table already exists in S3. Depending on your connection speed to your S3 provider, there can be some notable slowdowns in some operations.
As S3 is supporting discovery (automatically making tables available that are in S3) this can cause some small performance problems if the S3 engine is enabled.
There are no performance degradations when accessing existing tables on the server. Accessing the S3 table the first time will copy the .frm file from S3 to the local disk, speeding up future accesses to the table.
If you have performance problems with the S3 engine, here are some things you can try:
Try also to execute the query twice to check if the problem is that the data was not properly cached. When data is cached locally the performance should be excellent.
If you get errors such as:
ERROR 3 (HY000): Got error from put_object(bubu/produkt/frm): 5 Couldn't connect to server
one reason could be that your system doesn't allow MariaDB to connect to ports other than 3306. The fix is to change /etc/selinux/config
to SELINUX=disabled
.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/using-the-s3-storage-engine/