Amazon Aurora is a fully managed relational database compatible with MySQL and PostgreSQL, and is one of AWS's fastest growing services. Given Aurora's outstanding performance in design philosophy, performance, storage, scalability, and reliability, more and more customers choose Aurora as their core relational database to handle core data operations. This article analyzes using Aurora database in gaming scenarios, how adjusting a parameter value affects database system load and cost impact, and provides optimization recommendations.

Aurora Cost Analysis

Instance Cost

In terms of instance pricing, Aurora is approximately 1.2 times that of RDS MySQL. Considering Aurora's performance advantage over MySQL, in high-concurrency business scenarios, even if Aurora only improves performance by 2x compared to MySQL, costs will be significantly reduced. For example, MySQL requires 4 r4.xlarge nodes, while Aurora, due to performance improvements, only needs 2 r4.xlarge to meet business requirements.

In total cost, Aurora is cheaper

Aurora 0.58*2=1.16

MySQL 0.48*4=1.92

Storage Cost

In terms of storage pricing, Aurora charges based on actual capacity. Compared to RDS which requires specifying storage capacity when creating an instance, it's cheaper.

IO Cost

MySQL Provision IOPS EBS (IO1) charges based on preset capacity, this fee is fixed. Aurora calculates based on total IO, with different IO requests at different times, resulting in different fees. In actual high-concurrency business scenarios, this cost is quite high.

Optimization Parameter Introduction

Reference MySQL official documentation

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit

Simply put:

0: MySQL's main_thread writes redo logs from the storage engine log buffer to log file every second, and calls the file system's sync operation to flush logs to disk.

1: At each transaction commit, redo logs from the storage engine log buffer are written to log file, and the file system's sync operation is called to flush logs to disk.

2: At each transaction commit, redo logs from the storage engine log buffer are written to log file, and the storage engine's main_thread flushes logs to disk every second.

A value of 1 is the safest. If the value is 0, when the database program crashes, approximately 1 second of data may be lost. If the value is 2, approximately 1 second of data may be lost when the operating system crashes or there's a hardware failure.

The innodb_flush_log_at_trx_commit parameter in AWS Aurora belongs to the cluster parameter group and can be dynamically modified. If you change Aurora's cluster parameter group from the default parameter group to a custom parameter group, you may need to manually restart the Aurora cluster database.

Parameter Optimization Testing

The server uses EC2 C5.large, the database uses Aurora R5.large compatible with MySQL 5.6, and VolumnWriteIOPS is collected using CloudWatch API. It's important to note that even without any database operations, there will be VolumnWriteIOPS, approximately 15000-18000 times/hour. Also, VolumnWriteIOPS is not completely synchronized with operations. These factors cause some deviation in test data.

Gaming Business Operation Scenario

Game servers generally contain two thread groups: Request Thread Group and background thread Schedule Group. Each Request processing and Schedule event processing is protected by database Transaction. This test mainly evaluates empty Transaction (only begin and commit, no actual SQL) to understand the time consumption of Transaction mechanism and the impact of flush parameter.

Whether the flush parameter is 1 or 2, VolumnWriteIOPS can be ignored. From the above test, the flush parameter value has basically no impact on related performance.

Game Log Recording Scenario

Game logs are currently implemented using single-threaded asynchronous insert SQL to insert data into data tables, without using transactions. Each game server process has 4-5 log threads responsible for different log recordings, and about 10 game servers share one physical database.

From this data, after changing the flush parameter from 1 to 2, server processing throughput can be significantly increased while reducing WriteIOPS count.

Parameter Optimization in Practice

After completing parameter switching, focus on observing database CPU, Load during switching, and comparing daily WriteIOPS before and after switching. From the results, this parameter modification has obvious impact on both databases, with more significant impact on the log database.

Database Load Reduction

Before update, aurora_redo_log_flush data ratio was close to 20%

After update, aurora_redo_log_flush database ratio is 0, improving overall load capacity by 10-20%.

CPU Usage Reduction

CPU usage directly reduced by half.

Significant WriteIOPS Reduction

Parameter modification was done on 2020.07.16. Comparing data from 2020.07.17 with data from 2020.07.10 to 2020.07.15, for the log database, WriteIOPS dropped to 1/5 of the original, and the game database dropped by about half.

Optimization Recommendations

Considering industry characteristics and Aurora's own failure rate, if the risk of 1 second data loss is acceptable, consider optimizing the innodb_flush_log_at_trx_commit parameter value to 2 to reduce database IO requests. You can also consider downgrading instance types to further achieve cost optimization goals.

Part of this article references the Amazon AWS Official Blog:

https://aws.amazon.com/cn/blogs/china/aurora-mysql-redshift-application-scene-cost-analysis/

For further assistance or services, please leave a message. Taiyue Cloud Business provides automation tools and professional services.