2020-12-04

数据库

Test MariaDB's S3 storage engine on arm64 platform

Author: zhaorenhai

MariaDB has many other storage engines besides the default InnoDB storage engine, which are useful in some ways. Starting with this article, we’ll look at the functionality and performance of these non-default storage engines on the arm64 platform.

The first storage engine we studied was S3. The S3 storage engine is a new feature introduced since version 10.5. The S3 storage engine actually stores data in cloud storage that supports the S3 protocol. Tables stored on them are read-only . You can transfer data to S3 by changing the storage engine for tables, add and delete columns to tables of the S3 storage engine, or index them, but you cannot add, update or delete records directly. Readers should be wondering what the S3 storage engine does. In fact, it is useful for scenarios where some data is no longer updated, but the amount of data is large, and the data is important and cannot be deleted. At this time, you can change the storage engine of these tables to S3, because the S3 storage engine is cheaper and more reliable than local storage and is a good choice.

Here we will simply look at the functionality and performance of the S3 storage engine on the arm64 platform from several aspects.

First let’s run through the S3 Storage Engine test cases to see if they pass on the arm64 platform (MariaDB currently does not have the S3 Storage Engine-related test cases as the default required test cases, as mentioned in this document:Https://mariadb.com/kb/en/s3-storage-engine/), and then look at the functionality and performance of the S3 storage engine on arm64.

The test platform uses the Kunpeng Virtual Machine of Huawei Cloud and the OS is Ubuntu 18.04. Our S3 Storage Service uses Huawei Cloud’s OBS service. Created access key and secret key, bucket in advance. (Note that the storage area selected in OBS is best in the same area as the one used by the Kunpeng virtual machine for testing in order to reduce network latency. OBS-related guidance refers to the official guidance of Huawei Cloud, which is not detailed here.)

We created a new user on the OS adduser mariadb, and all subsequent work will be done under this user.

Download the source code for the latest version of MariaDB from GitHub and compile it

su - mariadb
sudo apt-get install build-essential libncurses5-dev gnutls-dev libcurl4-gnutls-dev 
sudo apt-get install zlib1g-dev ccache libnuma-dev libxml2-dev cmake bison
git clone https://github.com/mariadb/server
cd server
git submodule update --init
cd ..
mkdir build-mariadb-server
cd build-mariadb-server
cmake ../server -DCMAKE_BUILD_TYPE=RelWithDebInfo
cmake --build .

Test Cases

Edit the configuration file required for the test case:

1	vi ~/server/mysql-test/suite/s3/my.cnf

The configuration format is as follows:

!include include/default_mysqld.cnf
!include include/default_client.cnf

[mysqld.1]
#Currently, the maturity of S3 storage engine in version 10.6 is gamma, which is not stable. 
#It has not been loaded by default and needs to be configured manually
plugin-maturity = gamma
plugin-load-add=@ENV.HA_S3_SO
s3=ON
s3-host-name=obs.cn-north-4.myhuaweicloud.com
#The name of the bucket previously created on the OBS
s3-bucket=mariadb
s3-access-key=please replace with your access key
s3-secret-key=please replace with your secret key
#OBS area
s3-region=cn-north-4

Edit the following configuration file

1	vi ~/server/mysql-test/suite/s3/slave.cnf

The configuration format is as follows:

[mysqld.2]
plugin-maturity = gamma
plugin-load-add=@ENV.HA_S3_SO
s3=ON
s3-slave-ignore-updates=1
s3-host-name=obs.cn-north-4.myhuaweicloud.com
s3-bucket=mariadb
s3-access-key=please replace with your access key
s3-secret-key=please replace with your secret key
s3-region=cn-north-4

Start running test cases

1 2	cd ~/build-mariadb-server/mysql-test ./mysql-test-run --suite=s3

The output is as follows:

Logging: /home/mariadb/server/mysql-test/mysql-test-run.pl  --suite=s3
vardir: /home/mariadb/build-mariadb-server/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/mariadb/build-mariadb-server/mysql-test/var'...
Checking supported features...
MariaDB Version 10.6.0-MariaDB

 - SSL connections supported
   Using suites: s3
   Collecting tests...
   Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
s3.partition_create_fail                 [ skipped ]  Requires debug build
s3.encryption                            [ pass ]    777
s3.partition_move 'innodb'               [ pass ]   4059
s3.alter 'innodb'                        [ pass ]   5361
s3.innodb 'innodb'                       [ pass ]   1444
s3.alter2                                [ pass ]   1702
s3.partition                             [ pass ]  24858
s3.no_s3                                 [ pass ]      7
s3.arguments                             [ pass ]   1231
s3.basic                                 [ pass ]    830
s3.discovery                             [ pass ]   3288
s3.amazon                                [ skipped ]  Not connected to AWS
s3.backup                                [ pass ]    581
s3.mysqldump                             [ pass ]   3156
s3.select                                [ pass ]    600
s3.unsupported                           [ pass ]    329
s3.replication_delayed 'innodb,mix'      [ pass ]   3444
s3.replication_mixed 'mix'               [ pass ]   8435
s3.replication_partition 'innodb,mix'    [ pass ]  20100

s3.replication_stmt 'stmt'               [ pass ]   8967
--------------------------------------------------------------------------

The servers were restarted 10 times
Spent 89.169 of 107 seconds executing testcases

Completed: All 18 tests were successful.

2 tests were skipped, 1 by the test itself.

We can see that except one test case needs to be tested by debug version, and another needs to be connected to AWS (we use Huawei Cloud OBS) for testing, and these two are skipped, all the other test cases are successful. It shows that the basic function of S3 storage engine is no problem on arm64 platform.

Performance

Let’s take a look at performance of S3 storage engine.

First, configure the MySQL client path. We use the latest MySQL client just compiled and add the following code to ~/.bashrc

1	export PATH=~/build-mariadb-server/client:$PATH

Then

1	source ~/.bashrc

Edit configuration file:

1	vi ~/mariadb.cnf

Enter the content in the following format:

[mariadbd]
datadir = /home/mariadb/data/dir
lc_messages_dir = /home/mariadb/server/sql/share

innodb_buffer_pool_size = 8G
innodb_log_file_size = 1G
innodb_flush_method = O_DIRECT

plugin-maturity = gamma
plugin_dir = /home/mariadb/build-mariadb-server/storage/maria
plugin-load-add=ha_s3
s3=ON
s3-host-name=obs.cn-north-4.myhuaweicloud.com
s3-bucket=mariadb
s3-access-key=please replace with your access key 
s3-secret-key=please replace with your secret key
s3-region=cn-north-4

Then start the database:

mkdir -p /home/mariadb/data/dir
cd ~/build-mariadb-server
./scripts/mysql_install_db --srcdir=../server --defaults-file=~/mariadb.cnf
sql/mysqld --defaults-file=~/mariadb.cnf

After the database is successfully started, we need to create some test data.

Start a new shell window, login to MariaDB user and login to database.

1	mysql --socket=/tmp/mysql.sock

Create a test table by executing the following SQL.

1	create table test.s3test(id int, description varchar(40));

Then exit to the shell environment, ready to import a large amount of data into the table.

Using shell script to generate a 2.6G CSV file, about 60 million records, the first column is a number, the second column is a randomly generated string, this file named s3test.csv.

Import it to the database with the following command:

1	mysqlimport --socket=/tmp/mysql.sock --fields-terminated-by=, test /home/mariadb/s3test.csv

After importing it to the database, check the database file /home/mariadb/data/dir/test/s3test.ibd. The database file size is 4.4G.

Next, let’s test how long it takes to store this table to the S3 storage engine.

1	alter table test.s3test engine=s3;

Output:

1 2	Query OK, 63649280 rows affected (2 min 49.886 sec) Records: 63649280 Duplicates: 0 Warnings: 0

You can see that it took only two minutes and 49 seconds.

And the local data file /home/mariadb/data/dir/test/s3test.ibd no longer exists, indicating that the table has been saved to cloud storage.

Then let’s take a look at the files on the OBS.

Download an obsutil tool:

wget https://obs-community.obs.cn-north-1.myhuaweicloud.com/obsutil/current/obsutil_linux_arm64.tar.gz
tar -zxvf obsutil_linux_arm64.tar.gz
cd obsutil_linux_arm64_5.2.10
#Configure obsutil tool
./obsutil config -i=accesskey -k=secrectkey -e=obs.cn-north-4.myhuaweicloud.com

Check the data on OBS:

1	./obsutil ls obs://mariadb/test/s3test/

Query the data size on OBS, which is 3.22GB, even a little smaller than InnoDB storage:

1
2
3

Total size of prefix [test/s3test/] is: 3.22GB
Folder number is: 0
File number is: 826

Next, let’s query the data to see how fast it is. Log in to MariaDB database and enter the following SQL:

1	select * from test.s3test where id = 8;

the time is as follows:

1	512 rows in set (1 min 17.121 sec)

It took about 1 minute and 17 seconds to query 512 records out of 60 million non indexed records, and the speed was acceptable.

Next, let’s look at the speed of indexing and the speed of queries after indexing.

1
2
3

MariaDB [(none)]> alter table test.s3test add index idx_id(id);
Query OK, 63649280 rows affected (7 min 12.253 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

You can see that the creation of index took seven minutes.

Query again:

1
2
3

MariaDB [(none)]> pager md5sum; select * from test.s3test where id = 8;
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (25.146 sec)

You can see it’s a little bit faster this time, 25 seconds.

Take a look at the size of index

1	./obsutil ls obs://mariadb/test/s3test/

The output size is 3.81GB, which is 600M more than that of no index, indicating that the index takes up about 600M.

1
2
3

Total size of prefix [test/s3test/] is: 3.81GB
Folder number is: 0
File number is: 979

1	./obsutil ls obs://mariadb/test/s3test/index

1
2
3

Total size of prefix [test/s3test/index] is: 609.02MB
Folder number is: 0
File number is: 153

Let’s delete the index and see how long it takes.

1
2
3

MariaDB [(none)]> alter table test.s3test drop index idx_id;
Query OK, 63649280 rows affected (4 min 18.969 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

It took four minutes to delete the index.

Change the table storage engine to InnoDB to see how long it takes.

1
2
3

MariaDB [(none)]> alter table test.s3test engine=innodb;
Query OK, 63649280 rows affected (6 min 6.302 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

It took about six minutes.

Compare with InnoDB

Now the table is in the local database, and the storage engine is InnoDB. Let’s repeat the above operation and compare the performance of InnoDB engine and S3 engine.

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (30.218 sec)

MariaDB [(none)]> alter table test.s3test add index idx_id(id);
Query OK, 0 rows affected (2 min 1.309 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (0.009 sec)

MariaDB [(none)]> alter table test.s3test drop index idx_id;
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

It can be seen that the local InnoDB engine is still much faster than S3. However, except for index query and index deletion, there is no order of magnitude difference of no index query and create index operation, and the speed of S3 is acceptable.

Performance after increasing S3 page buffer

Let’s increase the page buffer size of S3 storage engine to see if the performance is improved.

1	vi ~/mariadb.cnf

Add the following configuration to set the page buffer size of S3 to 5G (the default is 128M)

1	s3_pagecache_buffer_size=5368709120

Restart the database.

Now let’s repeat the previous operation:

MariaDB [(none)]> alter table test.s3test engine=s3;
Query OK, 63649280 rows affected (5 min 8.340 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (39.110 sec)

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (10.019 sec)

MariaDB [(none)]> alter table test.s3test add index idx_id(id);
Query OK, 63649280 rows affected (5 min 45.067 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (17.479 sec)

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (0.015 sec)

MariaDB [(none)]> alter table test.s3test drop index idx_id;
Query OK, 63649280 rows affected (4 min 46.552 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

MariaDB [(none)]> alter table test.s3test engine=innodb;
Query OK, 63649280 rows affected (6 min 28.248 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

It can be seen that the operations such as changing the engine and creating and deleting indexes have not improved substantially. However, the speed of querying data, especially the second query, has an order of magnitude improvement, which is mainly due to the large buffer’s caching of data.

Performance with compression enabled

Next, let’s test the compression parameter COMPRESSION_ALGORITHM=zlib.

1
2
3

MariaDB [(none)]> alter table test.s3test engine=s3  COMPRESSION_ALGORITHM=zlib;
Query OK, 63649280 rows affected (6 min 21.659 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

It took more than 6 minutes, more than twice as long as it was not compressed.

Let’s see how much space can be saved.

1	./obsutil ls obs://mariadb/test/s3test/

1
2
3

Total size of prefix [test/s3test/] is: 1.51GB
Folder number is: 0
File number is: 826

You can see that more than half of the space is saved. However, our data is random, if it is regular data in reality, it is estimated that it can have higher compression rate. In addition, we use the default size of 4MB block, if the size of the block is changed to a larger point, the estimated compression rate should also be improved.

Let’s look at the query speed:

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (56.610 sec)

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (10.110 sec)

We can see that the first query is less than twice as slow as that without compression, and it is still acceptable. For the second query, due to the existence of large cache, the speed of compression and non compression is the same.

Let’s look at the indexing situation:

1
2
3

MariaDB [(none)]> alter table test.s3test add index idx_id(id);
Query OK, 63649280 rows affected (9 min 26.030 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

It took nine minutes, and it was less than twice as slow.

Look at the space occupied by the index:

1	./obsutil ls obs://mariadb/test/s3test/index

1
2
3

Total size of prefix [test/s3test/index] is: 261.54MB
Folder number is: 0
File number is: 153

You can see that after compression is enabled, the index is much smaller, which is smaller than half of that without compression.

Let’s look at the query:

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (27.094 sec)

MariaDB [(none)]> pager md5sum;select * from test.s3test where id = 8;
PAGER set to 'md5sum'
46ebf3c834a4023edec7fe311f50438d  -
512 rows in set (0.009 sec)

The first query is less than twice as slow as that without compression, and the second query is the same as that without compression due to the existence of large cache.

Finally, take a look at the time taken to delete the index and change the engine back to InnoDB:

MariaDB [(none)]> alter table test.s3test drop index idx_id;
Query OK, 63649280 rows affected (6 min 39.382 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

MariaDB [(none)]> alter table test.s3test engine=innodb;
Query OK, 63649280 rows affected (6 min 36.595 sec)
Records: 63649280  Duplicates: 0  Warnings: 0

Deleting indexes is less than twice as slow, and changing the engine takes as much time as without compression.

Summary

Through our test, we can find that S3 storage engine works well on arm64, and its performance is also good.

Let us summarize the results of the above tests in a table:

/	InnoDB	S3 with default options	S3 after setting pagecache buffer size to 5G	S3 after compression enabled and page buffer size 5G
Altering table to S3	/	2 min 49.886 sec	5 min 8.340 sec	6 min 21.659 sec
Query without index	30.218 sec	1 min 17.121 sec	39.110 sec	56.610 sec
Query without index 2nd time	28.628 sec	51.305 sec	10.019 sec	10.110 sec
Creating index	2 min 1.309 sec	7 min 12.253 sec	5 min 45.067 sec	9 min 26.030 sec
Query with index	0.009 sec	25.146 sec	17.479 sec	27.094 sec
Query with index 2nd time	0.002 sec	29.264 sec	0.015 sec	0.009 sec
Dropping index	0.009 sec	4 min 18.969 sec	4 min 46.552 sec	6 min 39.382 sec
Altering table to InnoDB	/	6 min 6.302 sec	6 min 28.248 sec	6 min 36.595 sec
Table size	4.4GB	3.22GB	3.22GB	1.51GB
Index size	1.1GB	609.02MB	609.02MB	261.54MB