Scaling Our Database for Peak Season: A 60% Latency Reduction Bonus

Authors: Rahul Bansal, Srikanth Raju, Greg Sterin, Ankit Singh

Introduction

Given Affirm’s partnerships with over 300K merchants, Black Friday Cyber Monday (BFCM) is our most important period of the year. The traffic during BFCM can be 5 to 10 times a normal day’s traffic and we need to handle it without any downtime or performance degradations.

The preparations for BFCM don’t start weeks, or even months, in advance — our planning for BFCM 2024 actually began in late 2023. The preparation is a cross-company effort that includes traffic forecasting, load testing, scaling our systems, ensuring our operations team is ready to handle support load, and so on.

A key part of the preparation includes applying last year’s BFCM learnings to prioritize projects that we expect to be most important for this year’s peak shopping period. In this blog post, we will deep dive into how we built confidence that our Aurora database will scale to the projected traffic for BFCM 2024.

Background

During checkout, Affirm makes a real-time credit decision for every transaction, determining which financial products to offer and enabling approved consumers to choose how to pay for their purchase over time. Checkout runs on Amazon EKS and consists of many microservices. The microservices in turn use multiple AWS Services such as Application Load Balancer (ALB), Amazon Aurora MySQL, and STS.

Our multi-year journey to break up the monolith has seen the successful separation of many microservices and data models. However, a significant part of our core data model still resides in our monolithic database — “affirm1”, a testament to its status as Affirm’s first database cluster. While the work continues to split up affirm1, it is the single most-critical database at Affirm.

Problem Statement

During BFCM 2023, the Aurora MySQL instances hosting affirm1 saw significantly higher load than what we had seen previously in production. We were running Aurora MySQL 2.11.x compatible with community database version MySQL 5.7 on db.r5.24xlarge instances

Due to the higher load, Performance Insights showed that Average Active Sessions (AAS) was close to utilizing all the vCPUs on our instances. A large proportion of the waits from InnoDB were in the wait/io/aurora_redo_log_flush event, which represents InnoDB waiting on the redo log to flush into the Aurora storage.

We also noticed that our commit latency was pushing up to 50ms, much higher than the usual 15ms we see at our regular daily loads. This demonstrated that our increased query processing load increased the commit latency.

We were able to serve all the traffic during BFCM 2023 without any issues. However, we were alarmed with the pattern of increase in commit latency with higher concurrency, especially as we prepare to handle significantly larger traffic in BFCM 2024. This led to concerns about the scalability of our most-critical affirm1 database.

Deep dive to understand the problem

Our initial reaction was that we needed to optimize the database to reduce AAS by optimizing the data model (e.g. by optimizing the indices) and queries (e.g. by optimizing expensive queries).

However, as we looked deeper, we realized that we didn’t really understand the problem. Among several puzzling things, two stood out

1. None of the core system metrics — vCPUs, memory, network bandwidth were close to saturation. So what was the bottleneck?
2. Regular DMLs such as insert, update and delete saw slight degradations but they were still 20x faster than commit. Why was commit latency so much higher than other queries?

We then started working closely with the Amazon Aurora team. We had already read the two papers published related to Aurora.

We then held a full-day event with the Aurora team to dive much deeper into the Aurora internals. That helped us build a much deeper mental model of the system, especially around the storage gateway that takes the requests from the database engine and fans them out to the scale-out storage nodes. There were two key takeaways:

1. Given our vCPU utilization is low, AAS being significantly higher than vCPU is not a problem. Specifically, we see AAS attributed to redo_log_flush continue to increase with concurrency indicating async waiting. Aurora batches database writes, so a higher number of writes in-flight with increased load is working as intended.
2. Insert requests get acknowledged when the storage layer receives those requests while commits are acknowledged only when all the corresponding writes have been made durable. So, the commit latency can be quite higher than the insert latency.

This deep dive led us to conclude that we need to focus on commit latency as the metric to optimize. However, we still could not explain

1. Why is commit latency 20x higher than insert latency?
2. How will this latency change with more load?
3. How to bring the latency down?

The Aurora team could not answer these questions solely based on looking at historical data of our database during BFCM. After discussions with the Aurora team, we decided that the best way to solve this would be to reproduce our expected BFCM load in a load test. That way the Aurora team could debug the system live while the load test was ongoing.

Load Test Methodology

At Affirm, we have several ways to run load tests. However, we did not find any of them suitable for debugging and fixing this problem. As an example, one way is an end to end checkout load test. However, those tests are expensive to run both in terms of resource cost and iteration time since they require scale-ups of all microservices involved in checkout and reservation of our dev environments. So, for this purpose, we developed database-only load tests to be able to iterate quickly.

In order to have a realistic load test, we needed a way to simulate the transactions that run on our production databases, and be able to adjust the rate of these transactions. We need to know not only the select/insert/update statements, but also the commits and rollbacks, as frequency of commits (i.e. larger vs smaller transactions) impacts database performance.

The simplest way to get valid queries would have been to turn on and collect them from the MySQL general query log. Then we would make a clone of the production database, and replay the general query log against the clone with an adjustable rate. This however had some complications:

1. Once the queries are replayed, the clone needs to be restored to the original state, as otherwise the queries would fail with duplicate key errors. Rewinding a clone or creating a new database from a snapshot takes some time, and thus would impact our iteration speed when executing many load test runs for different experiments.
2. Production queries contain user sensitive information, which requires extra caution during store and replay.
3. This approach captures all queries run on the database, which includes non-checkout flows with varying throughput. Checkout throughput spikes during BFCM, while repayment flow’s throughput does not, as the majority of payments are due later. Therefore, replaying all the transactions on the database at faster rates isn’t the most accurate reflection of peak season load, and determining from the SQL query which flow they belong to is a tedious manual process.

Therefore, we were left with the challenge of finding a way to generate synthetic transactions that had the same pattern and shape as the transactions executed when our users go through a full checkout. To do this, we executed a single checkout in a pre-production environment and captured the raw SQL queries. Then, we wrote a script to automatically order the transactions by timestamp, and process these queries to parameterize the values in the queries such that these values could be dynamically generated. This involved parsing the sql, examining the query keys and values, and mapping values that were used in multiple queries into unique variables. For example:

INSERT INTO User (id, name) VALUES (“myID”, “Elaine”);

INSERT INTO Loan(id, user_id) VALUES (“myLoanID”, “myID”);

COMMIT;

SELECT from User where id = “myID”;

became

SET @user_id_0 = “myID”;

SET @loan_id_0 = “myLoanID”;

SET @user_name_0 = “Elaine”

INSERT INTO User (id, name) VALUES (@user_id_0, @user_name_0;

INSERT INTO Loan(id, user_id) VALUES(@loan_id_0, @user_id_0);

COMMIT;

SELECT from User where id = @user_id_0;

Running this script on all the queries yielded ~250 variables. Upon inspection, we found many of them were only used in a single statement (ex: @user_name_0 in the example above), or weren’t something that needed to change between checkouts (ex: the UPDATE that updates the state of the loan to “authorized” will always set it to “authorized” on every checkout, what is different between checkout runs is the loan ID in the WHERE condition that it is setting to that state). By iterating on the output of the script, we were able to narrow down the variables that needed to be generated for each checkout to ~50, most of which were IDs, hashes, names, DOB, and other such unique identifiers that had DB indexes on them.

Finally, we wrote a load test runner — a CLI tool that we can run and provide the database connection info to the DB we want to load test, the parameterized SQL file, and the rate of checkouts we want to run. The tool:

1. Reads the parameterized SQL file
2. Spawns the appropriate number of subprocesses to achieve the desired rate
3. In the subprocess, run the parameterized sql in a loop. Each execution of the loop:
4. Opens a new DB connection
5. Generates random similar values to each variable that needs to be generated.
6. Runs the parameterized SQL
7. Closes the DB connection

There were some other options we added to the tool, such as the ability to inject sleeps between statements and transactions to more closely simulate the pacing of queries executed by a checkout (time spent in our apps, not on the DB). These options enabled us to compare variations and select the appropriate method of load generation — where the metrics we see on the test DB are similar to what we see in production.

With our load test tooling ready, it was time to begin experimenting.

Experiments and Results

We used checkout load generator to run several A/B tests where we varied parameters in our database configuration to see the impact on the performance

In the control, we ran without any database changes. We were able to push the database instance to much higher rate of checkouts, and demonstrate that:

• Our understanding of AAS after diving into Aurora internals was correct where due to batching a higher AAS with increased load is working as expected. We were able to push the AAS way higher than the vCPUs limit with the majority contribution coming from redo_log_flush.
• Commit latency indeed continued increasing with higher load

We ran a few variants we initially wanted to test:

• Upgrading minor versions of Aurora: We learnt that upgrading our minor version to later patches which had some performance fixes didn’t actually change performance significantly.
• Changing database instance sizes: We ran our load test on a smaller instance size and we were able to get very similar performance numbers. This confirmed our bottleneck was not due to the instance size itself.

The Aurora team then debugged this live as the load tests were running. With this, they were able to identify the problem.

As described earlier, the storage gateway batches the requests from the database engine before fanning them out to the storage nodes. In Aurora MySQL 2, the batching logic tries to carefully balance the latency, the CPU, and the number of I/Os sent to storage nodes (which has cost implications for customers). Sending smaller batches reduces the latency, but increases the CPU usage and the number of I/Os sent to storage. Aurora MySQL 3 I/O-Optimized has significant improvements to the batching algorithm and cost savings for I/O-intensive applications.

The Aurora team found that the batching algorithm in Aurora MySQL 2 was performing sub-optimally for us. The storage gateway was optimizing CPU over latency that added a delay in flushing the batches out. This, in turn, was the root cause of higher latencies. To address this challenge, the Aurora team simulated the optimization of Aurora MySQL 3’s I/O-Optimized in our Aurora MySQL 2 environment, by limiting the number of outstanding batches in the storage gateway, thereby making the storage gateway flush those batches sooner. The Aurora team was able to temporarily patch our cluster so we could test our hypothesis.

Lo and behold, this change provided a huge improvement in our throughput making our commit latency to level out around 20ms even with increased load, and with the DB instance spending much less time in redo_log_flush.

After more discussions with the Aurora team, we have accelerated our already in-progress upgrade to Aurora MySQL 3. Given that the upgrade to MySQL 8 (and hence to Aurora MySQL 3) is risky due to many breaking changes, we will use this patch in Aurora MySQL 2 for BFCM 2024. We already have months of experience using this patch live in production and are very confident in its ability to meet our needs for BFCM 2024.

Conclusion

Affirm takes BFCM very seriously every year in order to ensure that we handle all the traffic with minimal downtime or performance degradation. In this blog post, we dove deep into a database scalability issue to understand the root cause and ensure we can scale. In parallel, we are re-architecting our systems to completely eliminate bottlenecks like our monolithic database. This is an example of the type of hard technical problems we solve in order to successfully handle BFCM scale.

Acknowledgements

We are thankful to the Aurora team for working closely with us over several months to deeply understand the problem and help put in a fix for us. They truly embodied Amazon’s customer obsession. While the Aurora team recommends moving to Aurora MySQL 3 as soon as possible, we are happy that we were able to find a middle ground compatible with Affirm’s BFCM 2024 timeline and will follow with the upgrade of affirm1 post-BFCM.