Global fashion e-commerce retailer shortens SQL query time by 30% using Amazon EBS io2 Block Express

A top global e-commerce retailer employs the concept of on-demand manufacturing to deliver a wide variety of affordable fashion apparel products to customers around the world. It serves end users globally with a mobile app and website for ordering, payment, and delivery. To improve the performance, resilience, flexibility, and scalability of its systems, the retailer deployed them entirely on AWS.

Smooth and safe transaction handling is important for online shoppers to be satisfied with their online shopping experience. The core component of the retailer’s architecture is the transaction processing database built on MySQL. The company uses a distributed architecture across multiple Availability Zones (AZs) with Amazon Elastic Compute Cloud (Amazon EC2) R6i and M6i instances and Amazon Elastic Block Store (Amazon EBS) io2 Block Express volumes. This architecture provides high availability (HA) and disaster recovery (DR) assurance, and it helps the company easily and quickly scale to support elevated workloads during peak shopping hours. For the retailer’s online transaction processing (OLTP) database, the types of data stored are shopping transactions and payments. Because these are primary records with financial information, data persistence is a key requirement. In addition, the OLTP database needs the underlying storage service to handle large I/O requests quickly and safely with consistent performance. By using Amazon EBS, the retailer is able to fulfill its high performance, flexibility, and data persistence requirements as an EBS volume can scale up or down easily before and after peak events while persisting independently from the life of an EC2 instance.

In this post, we discuss the retailer’s journey in building a high-performance and resilient system that improved the shopping experience for millions of shoppers worldwide through the adoption of io2 Block Express. You should gain a deeper understanding of the storage offering and the crucial role it plays in enabling the retailer to better serve its shoppers. This helps you evaluate io2 Block Express as a potential storage solution for your mission-critical workloads with high performance and durability requirements.

The pursuit of high performance to better serve shoppers

The hallmark of cloud computing is that it offers e-commerce retailers greater flexibility to scale up and down in a cost-efficient way, supporting multiple peak events such as holidays and major sales events throughout the year. In 2018, the retailer began the migration of its on-premises online shopping systems to AWS. The online shopping system consists of many subsystems, such as coupon issuing, commodity listing, shopping cart management, stock management, ordering, and payment systems. The transaction data of these sub-systems are stored in MySQL databases. In an end-user shopping flow, these sub-systems work together through application programming interface (API) calls. For example, a shopper may receive a coupon from emails or through other means, then the shopper may begin to browse the latest merchandise on the app or website, add items to the shopping cart, and finally place an order and complete payment. If even one of the subsystems experiences higher than expected latency or a timeout, it is amplified in downstream sub-systems, directly impacting the user shopping experience and company revenue.

The retailer used Amazon EC2 R6i instances and Amazon EBS gp3 volumes to host its MySQL databases at first. gp3 is a general purpose volume that provides superior price performance by enabling users to provision performance independent of storage capacity, and it can achieve up to 16K IOPS per volume with single-digit millisecond I/O latency. Due to the rapid growth in scale of its global online shopping mall, the company soon ran into more stringent performance requirements for higher IOPS and lower latencies at consistent sub-millisecond rates. Meeting the performance requirements would allow the retailer to provide a smooth, uninterrupted shopping experience for its shoppers at any time of the day.

A game changer: Amazon EBS io2 Block Express

With recommendations from AWS storage specialists, followed by internal testing, the retailer decided to use Amazon EBS io2 Block Express as the primary storage engine for its critical MySQL databases. The next generation EBS volumes are designed to deliver up to 4,000 MB/s throughput per volume, 256,000 IOPS/volume, 64 TiB storage capacity, and scale at 1,000 IOPS per GB. All this while delivering sub-millisecond latency and provisioned performance 99.9% of the time. They are purpose-built for meeting the performance requirements of the most demanding I/O-intensive applications that run on instances built on the Amazon EC2 Nitro System leveraging Scalable Reliable Datagram (SRD). SRD is a proprietary networking protocol implemented in the Nitro Card dedicated for Amazon EBS I/O processing on the host hardware of the instance, enabling the minimization of latency outliers and the fastest response to network congestion fluctuations. With 99.999% durability and the best outlier latency control in the cloud, io2 Block Express is ideal for running performance-intensive, mission-critical workloads, such as Oracle, SAP HANA, Microsoft SQL Server, MySQL, IBM DB2, and SAS Analytics.

Figure 1: Peter DeSantis, SVP at AWS, discussed SRD in his Keynote at re:Invent 2022

Performance improvement and more with io2 Block Express

For new MySQL database deployments, the retailer started with io2 Block Express. For existing MySQL databases, the retailer used the Amazon EBS Elastic Volumes feature to migrate its gp3 volumes to io2 Block Express without any downtime or impact on performance.

Previously, the retailer’s ordering system experienced a 30-second timeout due to slow MySQL commits, causing a poor shopping experience that led to user churn and loss of revenue. After migrating to io2 Block Express to reduce latency and improve transaction processing, the retailer saw a 30% reduction in SQL query time and a 20% increase in transactions per second (TPS). Following the success of this workload migration, the retailer moved other MySQL databases to io2 Block Express in eight weeks, totaling 4 PB of data. With io2 Block Express’s low latency, the entire shopping experience, such as commodity browsing, shopping cart item addition, ordering, and payment, became fast and seamless for shoppers. Application timeouts are no longer an issue, allowing the IT staff to focus on downstream system innovation.

In addition to performance improvement, io2 Block Express provides high durability of 99.999%. To further strengthen HA and DR for the entire system, the retailer set up a distributed MySQL architecture across multi-AZs with binary log replication technology. It used EBS Snapshots to set up MySQL replica nodes or establish MySQL environment for development. As shown in the following architecture diagram, the retailer adopted one source node with two replicas nodes in three AZs and set up semi-synchronous replication for data protection. In this architecture, applications connect to the MySQL cluster through the customized proxy on Amazon EC2, while the cluster manager monitors the health of MySQL cluster nodes every five seconds. If the source node fails, then the cluster manager promotes one of the replicas to the “source” role and updates the connection information in a customized proxy. Typically, the failover takes about 30 to 60 seconds to complete.

Figure 2: MySQL HA & DR Architecture

Conclusion

In this post, we discussed io2 Block Express performance, durability, consistent sub-millisecond latency, and scalability and resilience benefits to the retail customer’s database workload. With the adoption of io2 Block Express, the retailer improved its online shopping systems by eliminating timeouts and delivered a fast and smooth shopping experience for its shoppers through the 2023 Black Friday peak season. To further improve performance, the retailer plans to use Amazon EBS Torn Write Prevention for MySQL, which can increase the TPS up to 30% and reduce the write latency up to 50%.

Thank you for reading this post. To get started for free, you can try out AWS Free Tier which includes 30 GB of storage, 2 million I/Os, and 1 GB of snapshot storage for 12 months with Amazon EBS. If you have questions or suggestions, leave them in the comments section.