Today, Wayfair uses SQL Server to store our customer data. We have a sharded architecture in place which is made up of many shards spread across multiple datacenters located throughout the US and Europe. Over the past few years, we have been pushing the limits of this infrastructure, causing problems like:
- Difficulty scaling due to reaching the maximum number of nodes in our sharding strategy
- Added complexity when trying to increase availability
- An ever-increasing price point due to licensing costs
We have been evaluating Google Cloud Spanner as a potential replacement for our current architecture.
Spanner is a Distributed Relational Database Management System (RDMS) that boasts easy horizontal scaling, high availability
, external consistency
, and decreased cost. As part of our effort to evaluate its viability, we completed a performance test of its available instance configurations
. Each configuration provides a different geographic layout of its replicas, and we worked to quantify the increase in query times due to geographic distance between a sampling of available GCP locations and Spanner’s replicas.
All of Spanner’s multi-region instance configurations are made up of replicas spread across multiple GCP regions in a variety of geographic locations. When performing certain operations, Spanner is required to communicate with replicas that may be in a region across the world from the client. We wanted to determine the exact latency penalty of performing such operations.
We performed four core performance tests: Insert, Update, Strong Read, and Stale Read . All tests were conducted using a stand-alone Java application run on a CentOS VM in five sample GCP regions: us-east4 (Virginia), us-central1 (Iowa), us-west1 (Oregon), europe-west4 (Netherlands), and europe-west3 (Frankfurt). The application used the Spanner Database Client supplied by the Spring Data Cloud Spanner client library to connect to the Spanner instances.
Tests were run on nine different Spanner instance configurations
each with a single node. During each test suite run, all tests were performed simultaneously on a single client using a single instance configuration within a 30 minute time span. We excluded a 15 minute connection warming period from our measurements, resulting in us sampling the final 15 minutes of each 30 minute run. The Spanner instances had no additional load outside of the tests being run. The reported results are all using the mean of their respective datasets.
The following schema was used for the database, a very simplified version of our customer data table schema in SQL Server today:
Spanner has two different read types, Stale and Strong . The former is a read of data from a timestamp in the past and the latter is a read of the most up-to-date data. Strong reads require the serving replica to communicate with the leader replica to ensure it’s up to date. This requires a round-trip to a potentially geographically distant region. Stale reads need only hit the closest readable replica without communication with the leader, thus reducing latency. The amount of staleness is configurable, but Google suggests using a staleness of 15 seconds for optimal benefit. This is the value we used in all Stale read tests.
To complete a write, the Spanner replica handling the request must interface with the leader replica, which in turn initiates a vote on whether the write can occur. The vote requires communication with additional geographically distant replicas. All of this back-and-forth results in significant latency costs for write requests in most instance configurations.
Below is the timing data for our core read and write operation tests from each of our 5 sample client regions to all nine instance configurations. As mentioned, the differences in timings is primarily due to geographic latency from the client to a Spanner replica. Depending on the instance configuration, some clients are geographically distant from the necessary replica for the operation while others share the same GCP region.
The nam-eur-asia1 configuration’s leader region is in us-central1 (Iowa). Inserts, updates, and most Strong reads are required to interface with the Spanner leader replica, so for these operations we see very high latency from the European clients in europe-west3 and europe-west4, medium latency from the east and west coast replicas, us-east4 and us-west1, and low latency from us-central1, the same GCP region as the lead Spanner replica.
Similarly, Stale reads must talk with the nearest readable replica, and, in the nam-eur-asia1 configuration, one exists very close to both European clients and to us-central1. This results in minimal latency for all three in our Stale read tests. On the other hand, us-east4 and us-west1 have their closest readable replica in Iowa, thus causing the excess latency seen above.
nam3, nam6, and eur3
One can see similar geographic latency patterns for eur3, nam3, and nam6 instance configurations. In both North American configurations, cross-continent latency is easiest to notice. For example, the leader replica of the nam3 configuration is us-east4, and the us-west1 client shows the largest latency when writing or strong reading since the round-trip to the leader is from the west coast to east coast.
The European configuration shows much less latency impact as the leader is located in europe-west1 (Belgium) which is nearby both European clients in europe-west3 (Frankfurt) and europe-west4 (Netherlands); however, neither client is co-located with the Spanner leader replica so the minimum latency for writes and strong reads is still higher than other configurations.
For our regional configuration tests, we actually used five different instance configurations. Each configuration has all of its replicas co-located in the same GCP region as the client under test. Looking at these configurations allows us to essentially remove geographic latency from the equation.
The most significant difference to note in these results is that write operations are much faster than that of multi-region instance configurations whose leaders are co-located with a client. Initially one might expect the latency to be the same since the leader and client are in the same GCP region for both configurations, but considering the inner workings of Spanner, this difference makes sense. When a client makes a write request to a Spanner replica it is first passed along to the leader replica. Then the leader will then initiate a vote between the other read/write and witness replicas who, in a multi-region configuration, are likely geographically distant from the leader. A regional configuration doesn’t have to take this round-trip, resulting in less latency for the operation.
From our testing, it’s clear that the geographic latency impact of switching to Google Cloud Spanner would be significant, especially when compared to similar timings from on-prem SQL Server infrastructure. In Spanner’s best case (nam6 with client in us-central1), read and write timings are double that of SQL Server and in its worst case (nam-eur-asia1 with client in europe-west3), latency is up to 15 times greater. It’s important to note, though, that some amount of performance hit is expected for any distributed RDMS due to the geographic latency between servers. After all, light can only travel so fast. There are also avenues available to mitigate latency like using stale reads as the default query option, pushing for architectures that allow for concurrent query execution and increased query caching, and working with Google to optimize Spanner’s instance configuration offerings to one’s client GCP regions.
Of course, latency is only one piece of the puzzle when comparing database management systems. Overall, we expect Spanner to reduce costs and maintenance time due to its fully managed nature, ease of scaling, and high availability. We also expect benefits from strong global and intracontinental consistency for our different use cases — something that would be very difficult to achieve with SQL Server. By carefully selecting instance configurations based on use case and taking into consideration the potential for improvements through latency mitigation techniques, it is our opinion that the many benefits of this relational database management system outweigh the increased latency cost.