Reloading into battle-Graviton2 improves Aurora's price/performance ratio

Foreword

From October 2020, Amazon Graviton2 based databases will be gradually launched. You can start these database instances when using Amazon RDS for MySQL, Amazon RDS for PostgreSQL, Amazon RDS for MariaDB, and Amazon Aurora compatible with PostgreSQL and MySQL.

The Amazon Graviton2 processor is customized by Amazon Web Services using the 64-bit Arm Neoverse core, and a variety of performance optimizations have been performed on the first-generation Amazon Graviton processor. This includes 7 times the performance, 4 times the number of computing cores, 2 times the private memory per core, 5 times the memory speed, and 2 times the floating point performance per core. In addition, the Amazon Graviton2 processor also has a fully encrypted DDR4 memory function that runs around the clock, and the encryption performance per core is increased by 50%. These performance improvements make Graviton2 R6g database instances an excellent choice for database workloads.

This article will show you the performance enhancement of Graviton2 R6g database instance to R5 database instance and the process of migrating from self-built or managed database to Graviton2 R6g database instance. Through testing, we can clearly see that regardless of the workload and concurrency conditions, the performance of R6g instances is significantly better than that of R5 instances with the same resource configuration, but the unit price per hour has dropped. Therefore, R6g instances are more important to customers. That said, there is a better price/performance ratio.

Environmental preparation

1. Environmental information

Note: Aurora's parameters basically use the default parameters. Because sysbench 1.0.18 will have a large number of prepared statements, it is necessary to set max_prepared_stmt_count to the maximum value of 1048576 to ensure that the test proceeds smoothly, otherwise an error may be encountered during initialization FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382) )"

2. Install and configure

Install sysbench1.0.18 on the test client

Download sysbench from git

sudo yum install gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql
git clone https://github.com/akopytov/sysbench.git

Open the sysbench directory

cd sysbench

Switch to sysbench version 1.0.18 and run autogen.sh

git checkout 1.0.18
sudo ./autogen.sh

Compile

sudo ./configure --prefix=/usr --mandir=/usr/share/man
sudo make
sudo make install

3. Open the client resource limit

No matter which sysbench software is installed, you need to perform the following configuration on the Sysbench client to tell the Linux kernel that all CPU cores can be used to process packets (only two can be used by default), and to reduce context switching between cores. These two This setting is to achieve the throughput goal with fewer Sysbench clients. (ffffffff means using 32 cores. Please modify according to the actual configuration. For example, if there are only 8 cores, enter ff)

sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ffffffff> $x/rps_cpus; done'

sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"

sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"

sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"

vim /etc/security/limits.conf

*                              soft    nofile  65536

*                              hard    nofile  65536

*                              soft    nproc  65536

*                              hard    nproc  65536

test

1. Read-only load test

test data injection

First, generate test tables on the test database through the sysbench client. Here, 250 tables are generated, and each table has 25,000 rows. You can also adjust the number and size of the tables according to your goals. Please replace the in <> Various connection information, and then execute the command, if you copy the command directly from the blog, please pay attention to the format. The precautions for subsequent commands are the same and will not be repeated

sysbench --db-driver=mysql --mysql-host=--mysql-port=--mysql-user=--mysql-password=--mysql-db=--table_size=25000 --tables=250 --events=0 --time=600  oltp_read_only prepare

test

Execute commands in the sysbench client to simulate the load, each lasting 20 minutes, while viewing the database CPU, IO, network and other metrics from the console. Here we will continuously test 32, 64, 128, 256 multiple concurrent connection scenarios by modifying the num_threads parameter.

sysbench --db-driver=mysql --mysql-host=<mysql host>  --mysql-user=<username> --mysql-password=<password> --mysql-port=<port>  --mysql-db=<dbname>   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_only run >> <dbname>_read.log
sysbench --db-driver=mysql --mysql-host=<mysql host>  --mysql-user=<username> --mysql-password=<password> --mysql-port=<port>  --mysql-db=<dbname>   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=64 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_only run >> <dbname>_read.log
sysbench --db-driver=mysql --mysql-host=<mysql host>  --mysql-user=<username> --mysql-password=<password> --mysql-port=<port>  --mysql-db=<dbname>   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=128 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_only run >> <dbname>_read.log
sysbench --db-driver=mysql --mysql-host=<mysql host>  --mysql-user=<username> --mysql-password=<password> --mysql-port=<port>  --mysql-db=<dbname>   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=256 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_only run >> <dbname>_read.log

clear test data

The command to clear data after the test is as follows:

sysbench --db-driver=mysql --mysql-host=--mysql-port=--mysql-user=--mysql-password=--mysql-db=--table_size=25000 --tables=250 --events=0 --time=600  oltp_read_only cleanup

2. Write only load test

Data injection

sysbench --db-driver=mysql --mysql-host=--mysql-port=--mysql-user=--mysql-password=--mysql-db=--table_size=25000 --tables=250 --events=0 --time=600  oltp_write_only prepare

test

Similarly, execute commands in the sysbench client to simulate a write-only load, each lasting 20 minutes, while viewing the database CPU, IO, network and other metrics from the console. Here we will continuously test 8, 16, 32 and 64 multiple concurrent connection scenarios by modifying the num_threads parameter.

sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=8 --percentile=95  --report-interval=20 oltp_write_only run >>_write.log
sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=16 --percentile=95  --report-interval=20 oltp_write_only run >>_write.log
sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=32 --percentile=95  --report-interval=20 oltp_write_only run >>_write.log
sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=64 --percentile=95  --report-interval=20 oltp_write_only run >>_write.log

Clear test data

sysbench --db-driver=mysql --mysql-host=--mysql-port=--mysql-user=--mysql-password=--mysql-db=--table_size=25000 --tables=250 --events=0 --time=600  oltp_write_only cleanup

3. Read/write hybrid stress test

test data injection

sysbench --db-driver=mysql --mysql-host=--mysql-port=--mysql-user=--mysql-password=--mysql-db=--table_size=25000 --tables=250 --events=0 --time=600  oltp_read_write  prepare

test

Similarly, execute commands on the sysbench client to simulate a mixed load of read and write, each lasting 20 minutes, and at the same time view metrics such as database CPU, IO, and network from the console. Here we will continuously test 32, 64, 128, and 256 multiple concurrent connection scenarios by modifying the num_threads parameter.

sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=32 --percentile=95  --report-interval=20 oltp_read_write  run >>_read_write.log
sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=64 --percentile=95  --report-interval=20 oltp_read_write  run >>_read_write.log
sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=128--percentile=95  --report-interval=20 oltp_read_write  run >>_read_write.log
sysbench --db-driver=mysql --mysql-host=  --mysql-user=--mysql-password=--mysql-port=  --mysql-db=   --table_size=25000 --tables=250 --events=0 --max-time=1200   --threads=256 --percentile=95  --report-interval=20 oltp_read_write  run >>_read_write.log

Clear test data

sysbench --db-driver=mysql --mysql-host=--mysql-port=--mysql-user=--mysql-password=--mysql-db=--table_size=25000 --tables=250 --events=0 --time=600  oltp_read_write cleanup

result analysis

1. Interpretation of results

The indicators in the output of the database performance test include:

TPS (Transactions Per Second) The number of transactions per second executed by the database, subject to the number of successful COMMITs.

In the SysBench standard OLTP read-write mixed scenario, a transaction contains 18 read-write SQL.

In the SysBench standard OLTP read-only scenario, a transaction contains 14 read SQL (10 primary key queries, 4 range queries).

In the SysBench standard OLTP write-only scenario, a transaction contains 4 write SQL (2 UPDATE, 1 DETELE, and 1 INSERT).

QPS (Queries Per Second) The number of SQL executed by the database per second.

2. Result statistics

The Aurora MySQL5.7 read-only load test data of R6g and R5 instances that use the same resource configuration under different numbers of concurrent connections are as follows:

Under different numbers of concurrent connections, the Aurora MySQL 5.7 write-only load test data of R6g and R5 instances that use the same resource configuration are as follows:

Under different numbers of concurrent connections, the Aurora MySQL 5.7 read/write mixed load test data of R6g and R5 instances that use the same resource configuration are as follows:

3. Conclusion

Through the above test data, we can find:

In the read-only load test, the QPS of the R6g instance is 21%-37% higher than that of the R5 instance with the same resource configuration.

In the write-only load test, the QPS of the R6g instance was improved by 79%-121% compared to the R5 instance with the same resource configuration.

In the read-write mixed load test, the QPS of the R6g instance was increased by 48%-60% compared to the R5 instance with the same resource configuration.

In the test, regardless of the workload and concurrency conditions, the performance of the R6g instance is significantly improved compared to the R5 instance with the same resource configuration.

In terms of price, we can refer to the Aurora price page. Take the r5.2xlarge and db.r6g.2xlarge we tested as examples. The hourly unit price of db.r6g.2xlarge is 89.4% of the hourly unit price of db.r5.2xlarge. The performance test results show that the R6g instance is undoubtedly more cost-effective than the R5 instance.

Note: The quotations for different instance types in different regions are different. The picture below is a screenshot from April 21, 2021. Please see the price page above for the latest prices.

Migrate MySQL to R6g instance Aurora

1. Reasons for migrating to R6g instance Aurora

Compared with the self-built MySQL on EC2, the use of Aurora MySQL can bring many benefits. Due to space limitations, the following four aspects are briefly described as follows:

High Availability

Aurora synchronously replicates data across 3 Availability Zones to the 6 storage nodes associated with the cluster volume, and provides an AZ (Availability Zone) +1 fault tolerance mechanism, that is, one AZ can still be written when it crashes, and one AZ+one data copy can’t be accessed. Readable.

Faster failover time, with RDS Proxy, usually Aurora's average failover time is about 3 seconds.

The cluster terminal node provides failover support for the read/write connection of the database cluster. If the current primary database instance of the database cluster fails, Aurora will automatically fail over to the new primary database instance. During the failover period, the database cluster will continue to provide services for requests from the new primary database instance to the cluster terminal node, with minimal service interruption.

The Amazon Aurora global database allows a single Amazon Aurora database to span multiple Amazon regions (regions). It replicates your data without affecting database performance, and provides disaster recovery capabilities in the event of a regional (region)-level interruption.

Scalability

Aurora can create up to 15 read-only Aurora replicas to horizontally scale the read capacity. The reader terminal node of the Aurora database cluster provides load balancing support for the read-only connection of the database cluster.

The read-only Aurora replica shares the same underlying storage as the master instance. Unlike MySQL's binlog-based replication, the replica on the Aurora cluster usually lags behind the master instance by 10 milliseconds.

The Amazon Aurora global database is designed for globally distributed applications, achieving low-latency and fast local reads in each region (region), using storage-based replication, with a typical latency of less than 1 second.

performance

Amazon Aurora tightly integrates the database engine with the SSD-based virtualized storage layer built for database workloads to reduce the write operations of the storage system, minimize lock contention and eliminate the delay caused by the database process thread, so that the performance greatly exceeds MySQL.

Parallel Query is very suitable for analytical workloads that require new data and good query performance. It can increase the running speed of analytical queries by up to 2 orders of magnitude. And there is no need to change the query syntax. The query optimizer can automatically determine whether to use PQ to run a particular query.

Manageability

As a managed database service, Aurora can handle time-consuming tasks, such as provisioning, patching, backup, recovery, fault detection and repair, greatly reducing your operation and maintenance pressure.

The Aurora Serverless database cluster will automatically start up, shut down, and expand or reduce capacity based on your application requirements. Aurora Serverless is a simple and more cost-effective choice for infrequent, intermittent or unpredictable workloads.

Aurora clones use the copy-on-write protocol. Using this mechanism, the clone requires very little additional space when it is first created. Initially, Aurora maintains a single copy of the data (used by the original database cluster and the new database cluster). Aurora allocates new storage space only when the data on the source cluster or clone cluster changes. With the help of cloning, we can quickly and economically create a new cluster for testing, auditing and other scenarios.

With the backtracking function, you can backtrack a database cluster to a specific time without restoring data from a backup. Especially when you need to constantly reset the test environment to eliminate changed data, the backtracking function is much more efficient than traditional point-in-time recovery.

The machine learning (ML) function can be used in conjunction with Amazon Aurora to simplify the process of developing database applications that use Amazon SageMaker and Amazon Comprehend services to perform predictions. This function is suitable for a variety of quick forecasts. This includes low-latency real-time use cases, such as fraud detection, ad targeting, and product recommendations. The query passes customer information, shopping history, and product catalog data to the SageMaker model. Then, your application will get product recommendations returned as query results.

2. Method of migrating from EC2 self-built MySQL to migrating to R6g instance Aurora

If you build MySQL 5.6 or 5.7 on EC2, you can use the following four methods to migrate to Aurora MySQL of the R6g instance:

via mysqldump

Because Amazon Aurora MySQL is a MySQL-compatible database, you can use the mysqldump utility to copy data from a MySQL or MariaDB database to an existing Aurora MySQL database cluster.

For a discussion on how to do this for a large MySQL database, see Importing data into a MySQL or MariaDB database instance while reducing downtime:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

For MySQL databases with a small amount of data, see Importing data from a MySQL or MariaDB database to a MySQL or MariaDB database instance:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.SmallExisting.html

via

You can copy full backup files and incremental backup files from the source MySQL version 5.5, 6, or 5.7 database to an Amazon S3 bucket, and then restore the Amazon Aurora MySQL DB cluster from these files. For specific steps, see: Migrating data from an external MySQL database to an Amazon Aurora MySQL database cluster:
https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.html

This option may be much faster than using mysqldump to migrate data, because using mysqldump requires replaying all commands to recreate the schema and data from the source database in the new Aurora MySQL database cluster. By copying the source MySQL data files, Aurora MySQL can immediately use these files as the data of the Aurora MySQL database cluster.

migrate from the master mode by native

Create a dedicated user for replication and grant permissions

Historical data can be imported through xtrbackup or mysqldump. When exporting data, add parameters to record the location information of the log point in the backup file

Set up the network and security group to ensure that the source MySQL and the target Aurora can be interconnected

Configure binary log replication by running the rds_set_external_master stored procedure.

Start binary log replication by running the rds_start_replication stored procedure.

For specific steps, see Migrating data from an external MySQL database to an Amazon Aurora MySQL database cluster:
https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.html

through DMS task

The Amazon Database Migration Service (DMS) service can help customers perform overall database migration by capturing change data on the source database and applying the changed data on the target database with minimal downtime. DMS can not only migrate the same database engine, but also supports migration between different database engines. For more information about DMS, please refer to: https://aws.amazon.com/dms/. The general steps are as follows:

Create a replication instance to ensure that you can connect to the source and target databases, create a dedicated user for replication data on the source and target databases, and assign corresponding permissions

Create endpoints of the source MySQL and target Aurora and test the connection through the replication instance

Create a Full load and CDC task, specify the object to be copied, which can be a combination of schema or tables.

Start the task, and the Full load task is responsible for migrating historical data.

After the full load is completed, the CDC task will capture the changes from the binlog and synchronize to Aurora

During the low business period, stop writing to the source database, wait for the CDC task replication delay to be 0, confirm that the data is consistent, switch the application to write to Aurora and complete the migration.

3. How to migrate from RDS to R6g instance Aurora

If you have used RDS of MySQL 5.6 or 5.7, you can migrate to Aurora MYSQL of R6g instance in the following three ways:

migrate through the read-only copy

You can create a special type of database cluster for the source MySQL database instance, called Aurora Read Replica. This method can migrate RDS data to Amazon Aurora and requires minimal downtime. Usually we recommend this migration method. The specific steps can be seen: Migrate via Aurora read replica:
https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.RDSMySQL.Replica.html

When creating an Aurora Read Replica of a MySQL DB instance, Amazon RDS creates a DB snapshot for the source MySQL DB instance (Amazon RDS is private and does not incur charges).

Then, Amazon RDS migrates the data from the database snapshot to the Aurora read replica.

After migrating the data from the database snapshot to the new Aurora MySQL database cluster, Amazon RDS began to replicate between the MySQL database instance and the Aurora MySQL database cluster.

If the tables contained in the MySQL database instance use storage engines other than InnoDB, or use compressed row format, you can modify these tables to use the InnoDB storage engine and dynamic row format before creating the Aurora read replica to speed up the Aurora read replica The creation process.

by RDS snapshot of migration

You can migrate the database snapshot of the RDS for MySQL database instance to create an Aurora MySQL database cluster. The new Aurora MySQL database cluster will be populated with data from the original RDS for MySQL database instance. This method cannot continuously replicate the changed data. If you want the data filled in Aurora to be consistent with the RDS, you need to keep the RDS read-only, which is usually used for migrations with sufficient downtime windows. The specific steps can be seen: Migrate RDS for MySQL snapshots to Aurora:
https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.RDSMySQL.Import.html

If the MySQL database instance and the Aurora database cluster are running the same version of MySQL, you can restore the MySQL snapshot directly to the Aurora database cluster.

If you want to migrate a snapshot of MySQL version 5.6 to Aurora MySQL version 5.7, you can use one of the following methods to perform the migration:

Migrate the MySQL version 5.6 snapshot to Aurora MySQL version 5.6, take a snapshot of the Aurora MySQL version 5.6 database cluster, and then restore the Aurora MySQL version 5.6 snapshot to Aurora MySQL version 5.7.

Upgrade the MySQL version 6 snapshot to MySQL version 5.7, take a snapshot of the MySQL version 5.7 database instance, and then restore the MySQL version 5.7 snapshot to Aurora MySQL version 5.7.

by DMS task migration

This method has been described in Section 2, so I will not repeat it.

summary

This article will show you the performance enhancement of Graviton2 R6g database instance to R5 database instance and the process of migrating from self-built or managed database to Graviton2 R6g database instance. Through testing, we can clearly see that regardless of the workload and concurrency conditions, the performance of R6g instances is significantly better than that of R5 instances with the same resource configuration, but the unit price per hour has dropped. Therefore, R6g instances are more important to customers. That said, there is a better price/performance ratio.

When you test according to the steps in this article, depending on the resource configuration, data set, and test load, you need to fine-tune the command parameters, and the test results will change accordingly, but the test idea and the objective law of the test results change are Common. In addition to sysbench, we also have other performance testing tools, such as mysqlslap or tpcc-mysql. This article is limited in space and does not introduce them. We will introduce them in future blogs.

reference

Amazon Aurora User Guide:
https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html

sysbench in github： 
https://github.com/akopytov/sysbench

Amazon Graviton2：
https://aws.amazon.com/cn/ec2/graviton/

Amazon RDS for MySQL：
https://aws.amazon.com/cn/rds/mysql/

Amazon RDS for PostgreSQL：
https://aws.amazon.com/cn/rds/postgresql/

Amazon RDS for MariaDB：
https://aws.amazon.com/cn/rds/mariadb/

Author of this article

Lu
Amazon Cloud Technology Database Expert Architect

Mainly responsible for the technical support of Amazon Cloud Technology (China) partners, as well as the consulting and architecture design of cloud computing solutions based on Amazon Cloud Technology. At the same time, he is committed to the research and promotion of databases and big data. Prior to joining Amazon Cloud Technology, he served as a senior lecturer at Oracle and a senior DBA at Amazon. He has extensive experience in database design, operation and maintenance, DR solutions, big data, and enterprise applications.