Will “Database as a Service” work for retail inventory management? A case study

A global retail company faced the challenge of bringing their inventory management system to the cloud. The company required their new cloud-based solution to include online and store inventory in all geographical regions, and deliver a fast and consistent database performance within each region. The company invited Grid Dynamics to make an evaluation of cloud databases-as-a-service solutions (DBaaS) for the critical inventory storage system due to their expertise in building inventory management systems.

We looked at three fully-managed database services to determine their suitability as retail inventory management systems for global enterprise companies: Amazon DynamoDB, Google Cloud Spanner and Microsoft Azure Cosmos DB. Each of these products offer high availability (at least 99.99% uptime), horizontal scalability, multi-region deployments, multiple consistency models and a rich feature set. Our goal was to evaluate data modeling, integration and performance capabilities in both single- and multi-region deployments. This article summarizes our observations and the lessons we learned from these implementation and performance comparisons.

Our inventory management evaluation test uses a simplified model in a controlled environment. We limited the test to three queries which are frequently used in a typical production environment:

• Inventory quantity: this is the most frequently queried scenario. The available item quantity is checked using the identifier (SKU) and location (store, warehouse, online etc.). The database performs only “read” operations to satisfy this request.
• Replenish items: this scenario adds items to the SKU quantity field in the given location. The database performs a “write” operation, which increases the item quantity field by the given amount.
• Reserve items: this scenario adds items to a shopping cart for later checkout. The reservation is made by subtracting the amount of items from the inventory field by SKU and location. Reserve Items must first check if enough items are currently in inventory by first performing Inventory quantity. If there is a sufficient quantity, Reserve Item proceeds. Reserve Items consists of a “read” operation, followed by a conditional “write” operation.

In order to implement the scenarios listed above, we used a simple data model of one database table. This also allowed us to test concurrent read/write access to the same items.

Here is a representation of the table used in the evaluation:

CREATE TABLE Inventory (
            storeId INT NOT NULL,
            skuId INT NOT NULL,
            inventoryCount INT NOT NULL,
            reservedCount INT NOT NULL
          ) PRIMARY KEY(storeId, skuId)

The evaluation included:

• Development of a tiny Spring Boot application that exposed a REST endpoint for each use case
• Development of load tests using JMeter and Terraform scripts for deployment of the database
• Application in the given cloud, populating synthetic data into the database, running the load test scenarios and capturing metrics

There were two load test scenarios:

• A sequential test, which executes test cases one-by-one in the given number of loops in the following sequence:

1. Reserve items
2. Replenish items
3. Inventory quantity

• A parallel test, which executes test cases in parallel in the given number of loops.

Each test was executed where the application interacts with the primary database instance in the same region, and the fallback case where the application interacts with the database replica in a different region (we chose regions geographically close to each other).

Cloud Spanner is a fully managed, mission-critical, relational database service that offers global transactional consistency, schemas, SQL (ANSI 2011 with extensions), and automatic, synchronous replication for high availability. The service allows running a database in a single region (US, Europe or Pacific Asia) with inter-region replicas for availability, or in multiple regions (2 configurations in US, 1 in Europe and 1 covers three continents). Multi-region setup increases availability from 99.99% to 99.999% and offers read-write replicas in several geographic regions, for an additional cost.

The resource provisioning model in Cloud Spanner is defined in terms of nodes. When a database instance is created, the user allocates a certain number of nodes which share partitioned data and handle the load. Google recommends running at least 3 nodes for production deployments. There is no auto-scaling feature, but the number of nodes per database instance can be changed after launch.

Although Cloud Spanner is a relational SQL-compliant database, its distributed nature impacts schema design. Google outlined some best practices in designing primary keys and indexes to prevent hotspots, which is connected with how data is organized and distributed between database servers.

The integration at the application level was implemented via Cloud Spanner native Java driver (com.google.cloud:google-cloud-spanner:1.5.0) with read-only and read-write transactions. Neither application nor JMeter nodes were the bottleneck during these tests.

In our single-region test we used the environment configuration:

The observed performance testing metrics looked like this:

Cloud Spanner peak load tests with 50 concurrent threads showed high CPU loads (about 60%):

Sequential test

Parallel test

The multi-region test used this environment configuration:

The multi-region performance test results:

The latency of writes with a multi-region database deployment, even within the same region, is over twice as high as for a single region cluster, while the cost of reads is expectedly almost the same. With the fallback case and cross-region calls, the latencies are doubled, which needs to be taken into account in the application SLA (service level agreement). While the overall read latency looks good, the write latency is rather high for our inventory management test case even in a single-region deployment.

Amazon DynamoDB is a fully managed key-value and document database that supports multi-region, multi-master deployments with built-in security, backup and restore, and in-memory caching. Amazon does not publish an uptime SLA for the service. However, data is declared to be replicated across three different availability zones over the region. For a multi-region setup, Amazon offers the DynamoDB Global table, which can be used for geographically distributed applications as well as for failover in case of a primary region outage. This solution is significantly more expensive, as the price includes write capacity and data transfer for replicated data.

The DynamoDB resource provisioning model offers two options: provisioned and on-demand capacity modes. The provisioned model allocates read and write table capacity explicitly (it can be changed after creation), and configures auto-scaling policies to adjust the settings. The on-demand model simply pays for the actual amount of units consumed. The main trade-off of the on-demand model is a higher price per unit, which increases with the growing number of requests. However we have also noted delayed resource provisioning, so that response latency temporarily spikes when the load is increased.

Amazon DynamoDB is based on the wide-column data model, with flexible schema with various supported types for table attributes, including scalar types, documents (lists or maps) or scalar sets. DynamoDB provides JSON API for queries or DML operations, but does not support SQL, joins, aggregation or grouping.

The integration at the application level was implemented via a DynamoDB native Java driver (com.amazonaws:aws-java-sdk-dynamodb:1.11.462). For a single region configuration, we used transactions to make conditional and simple writes. Global table transactions (multi-region deployment) are not supported, so the implementation was changed to use optimistic locks based on document version.

Here’s the single-region test environment configuration:

Here are the single-region services’ benchmarking results:

At the peak load with 50 concurrent threads, consumption of DynamoDB resources ran over the allocated capacity of 5000 units, which led to spikes in the number of throttled read/write requests. Consumption of “read capacity” was constantly equal or higher than “write capacity” due to version-based optimistic locking, which required reading the record to be modified.

The sequential read capacity test:

The parallel read capacity test:

The sequential write capacity test:

The parallel write capacity test:

For the multi-region test, we deployed a DynamoDB Global table in two regions: us-east-1 and us-east-2. The environment configuration was:

The benchmarking results are as follows:

Since no server side transactions were used, we did not observe a difference in latencies between reads/writes in a single region table and the global table accessed from the primary region (i.e. us-east-1). However, in the case of cross-region calls, which mimic a primary region outage with a failover scenario, the latency of write cases doubled, while read case latency tripled.

Overall, DynamoDB demonstrated read performance good enough for the inventory service, very close to the performance of Google Cloud Spanner. The write latencies were significantly lower than the Google Cloud Spanner implementation, but this is a consequence of using optimistic version-based locking versus server-side transactions.

Azure Cosmos DB is a globally distributed, multi-model database service, which allows elastically and independently scaling throughput and storage across multiple geographic regions. Cosmos DB provides comprehensive service level agreements for throughput, latency, availability, and consistency guarantees, not just uptime. The database can be deployed in a single region (with service availability of 99.99%) or in a multi-region setup with multiple or single write regions (wth service availability increased to 99.999% for reads/writes or reads only).

The cost of all database operations is normalized by Azure Cosmos DB, and is expressed by Request Units (RUs). It abstracts system resources such as CPU, IOPS and memory that are required to perform the database operations. Cosmos DB does not support the auto-scaling of resource allocation, so one must explicitly specify the amount of provisioned request units. If the consumed Request Units run out of the allocated capacity, the service will start throttling requests. Throughput provisioning can be made at the container or database (which is a set of containers) level. The main difference is that database throughput provisioning is generally cheaper, but does not guarantee resource availability for a particular container.

Azure Cosmos DB, which was initially launched as a document database, has been extended to support SQL API, MongoDB and Cassandra APIs compatible with native drivers at the wire protocol level, Table API and Gremlin. The API is defined at the container level at creation time and cannot be changed on the fly, so the same data cannot be accessed through multiple APIs.

The integration at the application level was implemented through the Document API via the Spring Data repository (com.microsoft.azure:spring-data-cosmosdb:2.1.0). The data load functionality was implemented on Cosmos DB native bulk executor, which is more efficient than regular writes for batches. Document updates were implemented using an Optimistic Concurrency Control (OCC) pattern based on document “_etag”.

As with the previously discussed database services, we ran Azure Cosmos DB benchmark tests for different deployments: single region, two regions with the primary one colocated with application, and the failover scenario when the application connects to the database in a different region. The environment was deployed in the West Europe region, with the database either in the same region with or without failover in the North Europe region. To mimic the failover scenario, the primary write instance of the database was in the North Europe region.

In our tests, we allocated 100,000 RUs/sec for data sharded into 1000 logical partitions (based on a synthetics partition key). Logical partitions are, in turn, distributed among physical partitions, which are automatically allocated by the database engine. The problem is that the number of physical partitions has a direct effect on database performance, because provisioned throughput is evenly distributed between them. So, if the load is unevenly distributed between logical partitions, the result would be spikes in load on certain physical partitions, which can be throttled even if the overall throughput capacity at the container level is sufficient. It is important to select the partitioning key not only with respect to proportional splitting of the dataset, but also with regard to the future load profile.

Here are the results of benchmark tests that simulated regular and peak load on the inventory services in the single region West Europe:

The tests did not face a bottleneck on the database side. However, under the stress parallel load of 50 threads per test, the application server reached the CPU capacity of 16 cores:

The sequential stress load test – Azure Cosmos DB:

The sequential stress load test – Application server:

The parallel stress load test – Azure Cosmos DB:

The parallel stress load test – Application server:

The multi-region database setup observed these benchmark results:

The performance of the multi-region database with the application running in the same region is almost the same as for a single region deployment. This is the result of using the “bounded staleness” consistency level, which provides “strong consistency” guarantees for reads within the region of writes, and enables cross region consistency outside of the staleness window. In the case of failover and cross-region calls from the application to the database, the service latency is almost 3 times higher, which is also observed for database services from other cloud providers.

An important question in designing an Azure Cosmos DB solution is estimating the provisioned throughput capacity. In our tests, we came up to 100,000 RUs/sec, but the greater the allocation, the higher the service cost. For example, 100,000 RUs/sec of provisioned capacity is estimated to cost $5,840 for a single region database, or $11,680 to add one more write region. Azure Cosmos DB has a database capacity planner to estimate the database throughput which needs to be provisioned. We compared the number obtained from the calculator with the actual observed throughput consumption. For the exercise, we took the throughput results from the parallel stress load test (50 thread per use case in a single region) and uploaded a sample document:

The calculator’s estimated throughput of 34,200 RU/s calculated value is very close to the observed value. Using the database throughput chart from this benchmark, the consumed throughput per partition is a little over 2,000 RUs/sec. Taking into account that the data was split into 17 physical partitions, 34,000 RUs/sec were actually consumed.

What if we use smaller provisioned throughput? We made similar tests with 20,000 RU/s and compared the results:

The throughput for each case gets significantly lower, almost doubling latency. Does this mean that fewer allocated RUs/sec lead to poorer database performance? No. The problem is in the request throttling, which is observed on the database monitoring charts:

Throttled requests result in an error response from the database service with a recommended delay in milliseconds before retrying the query. This routing is screened from the developer by the Cosmos DB client library. Insufficiently provisioned throughput with the inventory service level can affect the service SLA, and should be properly handled.

To conclude the Azure cosmos DB evaluation, the inventory service performance was comparable to the Google Cloud Spanner in writes and worse for read cases. On the other hand, Cosmos DB provides capacity planning, monitoring and diagnostic tools, which help to troubleshoot issues.

To summarize the sequential load profile in a single region deployment:

The comparison table shows that the performance of the implemented cases under high load is good, and is sufficient for a typical retail inventory service. Lastly, we looked at the provisioning and pricing model cloud services. This gives an idea of the basic database costs provisioned for this research:

Enterprise companies currently using a custom database deployment on the cloud (Platform as a Service or PaaS) solution should consider a DBaaS. All three of the tested DBaaS products are capable of meeting the maximum load requirements of a typical retail inventory management system. A company implementing DBaaS should note that each product varies in operational details. These differences can be useful to fine-tune the SLA with the company’s cloud provider.

Full service providers of DBaaS have built up an impressive array of features offering scalable multi-region databases that are suitable for inventory management services from the performance and functional perspective. We hope this article gives you some insight into making a more informed decision.