Get best of the week

Google Cloud Spanner: good, bad, evil

Hi, Habrovsk. Traditionally, we continue to share interesting material in anticipation of the launch of new courses. Today, specially for you, we have published an article about Google Cloud Spanner, timed to coincide with the launch of the AWS for Developers course .



Originally posted on the Lightspeed HQ blog .

As a company that offers many cloud-based POS solutions for retailers, restaurateurs, and online retailers around the world, Lightspeed uses several different types of database platforms for a variety of transactional, analytic, and search cases. Each of these database platforms has its own strengths and weaknesses. Consequently, when Google introduced Cloud Spanner on the market, promising features unprecedented in the world of relational databases, such as virtually unlimited horizontal scalability and a 99.999% service level agreement (SLA), - we could not miss the opportunity to get it in our hands!

To give a comprehensive overview of our experience with Cloud Spanner, as well as the evaluation criteria we used, we will cover the following topics:

  1. Our evaluation criteria
  2. Cloud Spanner in a Nutshell
  3. Our rating
  4. Our findings



1. Our evaluation criteria


Before delving into the features of Cloud Spanner, its similarities and differences with other solutions on the market, let's first talk about the main user cases that we had in mind when considering where to deploy Cloud Spanner in our infrastructure:

  • As a replacement for the (prevailing) traditional SQL database solution
  • How an OLTP Solution with OLAP Support

Note: For simplicity and ease of comparison, this article compares Cloud Spanner with MySQL GCP Cloud SQL and Amazon AWS RDS family of solutions.

Using Cloud Spanner as a replacement for a traditional SQL database solution


In a traditional database environment , when the response time to a database query approaches or even exceeds predefined application thresholds (mainly due to an increase in the number of users and / or queries), there are several ways to reduce the response time to acceptable levels. However, most of these solutions include manual intervention.

For example, the first step you need to take is to look at the various database parameters related to performance and configure them to best match application usage patterns. If this is not enough, you can choose to scale the database vertically or horizontally.

Scaling up an application entails updating the server instance, usually by adding more processors / cores, more RAM, faster storage, etc. Adding more hardware resources leads to an increase in database performance, measured mainly in transactions in second, and transaction delay for OLTP systems. Relational database systems (which use a multi-threaded approach), such as MySQL, scale well vertically.

This approach has several drawbacks, but the most obvious is the maximum server size on the market. Once the limit of the largest server instance is reached, there is only one way: horizontal scaling.

Horizontal scaling is an approach in which more servers are added to the cluster to ideally linearly increase performance with the addition of the number of servers. Most traditional database systems do not scale well horizontally or do not scale at all. For example, MySQL can scale horizontally for read operations by adding slave readers, but cannot scale horizontally for write operations.

On the other hand, due to its nature, Cloud Spanner can easily scale horizontally with minimal interference.

Full-featured DBMS as a servicemust be evaluated from different angles. As a basis, we took the most popular DBMS in the cloud - for Google, GCP Cloud SQL and for Amazon, AWS RDS. In our assessment, we focused on the following categories:

  • Feature mapping: SQL extent, DDL, DML; connection libraries / connectors, transaction support, and so on.
  • Development support: ease of development and testing.
  • Administration support: management of instances - for example, scaling up / down and upgrade of instances; SLA, backup and restore; security / access control.

Using Cloud Spanner as OLTP with OLAP Support


Although Google does not explicitly claim that Cloud Spanner is for analytic processing, it shares some attributes with other mechanisms, such as Apache Impala & Kudu and YugaByte, which are designed for OLAP workloads.

Even if there was only a small chance that Cloud Spanner included a consistent horizontally scalable HTAP (hybrid transactional / analytical processing) engine with a (more or less) usable set of OLAP features, we think that it would deserve our attention.

With this in mind, we examined the following categories:

  • Data loading, indexes and partitioning support
  • Query Performance and DML

2. Cloud Spanner in a Nutshell


Google Spanner is a cluster relational database management system (RDBMS) that Google uses for several of its own services. Google made it publicly available to Google Cloud Platform users in early 2017.

Here are some of the attributes of Cloud Spanner:

  • Highly consistent scalable RDBMS cluster: Uses hardware time synchronization to ensure data consistency.
  • Cross-table transaction support: transactions can span multiple tables - not necessarily limited to one table (unlike Apache HBase or Apache Kudu).
  • : (), . , . , .
  • : . . , , , -.
  • : Cloud Spanner . . . . , , , . , .

«Cloud Spanner . , Cloud Spanner , - , ».


: Apache Tephra Apache HBase ( Apache Phoenix -).

3.


So, we all read Google’s statements about the benefits of Cloud Spanner - virtually unlimited horizontal scaling while maintaining high consistency and very high SLA. Although these requirements are, at any rate, extremely difficult to achieve, our goal was not to refute them. Instead, let's focus on other things that most database users care about: parity and usability.

We rated Cloud Spanner as a replacement for Sharded MySQL


Google Cloud SQL and Amazon AWS RDS, the two most popular OLTP DBMSs in the cloud market, have a very large feature set. However, to scale these databases beyond the size of a single node, you need to perform application partitioning. This approach creates additional complexity for both applications and administration. We looked at how Spanner fits into the scenario of combining several segments into one instance and which features (if any) may have to be sacrificed.

Support for SQL, DML and DDL, as well as connector and libraries?


First, when starting with any database, you must create a data model. If you think that you can connect the JDBC Spanner to your favorite SQL tool, you will find that you can query your data with it, but you cannot use it to create a table or change (DDL) or any insert / update / delete operations ( DML). Google's official JDBC does not support either.
"The drivers do not currently support DML or DDL."
Spanner Documentation

With the GCP console, the situation is no better - you can send only SELECT queries. Fortunately, there is a JDBC driver with DML and DDL support from the community, including transactions github.com/olavloite/spanner-jdbc . Although this driver is extremely useful, the lack of Google's own JDBC driver is surprising. Fortunately, Google offers fairly broad support for client libraries (based on gRPC): C #, Go, Java, node.js, PHP, Python, and Ruby.

The almost mandatory use of Cloud Spanner user APIs (due to the lack of DDL and DML in JDBC) leads to some restrictions for related areas of code, such as connection pools or database binding frameworks (e.g. Spring MVC). As a rule, when using JDBC, you can freely select your favorite connection pool (for example, HikariCP, DBCP, C3PO, etc.), which is tested and works well. In the case of custom Spanner APIs, we must rely on the frameworks / binding pools / sessions we created ourselves.

The primary key (PC) design allows Cloud Spanner to be very fast when accessing data via a PC, but also leads to some query problems.

  • ; . ( / .)
  • UPDATE DELETE WHERE, , DELETE all — , : UPDATE xxx WHERE id IN (SELECT id FROM table1)
  • - , . , .

?


Google Cloud Spanner has built-in support for secondary indexes. This is a very nice feature that is not always present in other technologies. Apache Kudu does not currently support secondary indexes at all, and Apache HBase does not support indexes directly, but can add them through Apache Phoenix.

Indexes in Kudu and HBase can be modeled as a separate table with different composition of primary keys, but the atomicity of operations performed with the parent table and related index tables must be performed at the application level and is not trivial in the correct implementation.

As mentioned in the Cloud Spanner review, its indexes may differ from the MySQL indexes. Therefore, particular care should be taken when building queries and profiling to ensure that the proper index is used where it is needed.

Representation?


A very popular and useful object in the database is views. They can be useful for a large number of user cases; my two favorites are the level of logical abstraction and the level of security. Unfortunately, Cloud Spanner does NOT support submissions. However, this only partially limits us, because for access permissions there is no granularity at the column level where representations may be an acceptable solution.

The Cloud Spanner documentation has a section that details quotas and restrictions ( spanner / quotas), there is, in particular, one that may be problematic for some applications: Cloud Spanner out of the box has a limit of a maximum of 100 databases per instance. Obviously, this can be a serious obstacle for a database that is designed to scale to more than 100 databases. Fortunately, after talking with our Google technical representative, we found out that this limit can be increased to almost any value through Google support.

Development support?


Cloud Spanner offers pretty decent support for programming languages ​​to work with its API. Officially supported libraries are in the areas of C #, Go, Java, node.js, PHP, Python, and Ruby. The documentation is quite detailed, but, as with other advanced technologies, the community is quite small compared to the most popular database technologies, which can lead to an increase in the time spent on solving less common use cases or problems.

So what about local development support?


We did not find a way to create a Cloud Spanner instance in the local environment. The closest we got is the CockroachDB Docker image , which in principle is similar, but in practice it is very different. For example, CockroachDB may use PostgreSQL JDBC. Since the development environment should be as close as possible to the working environment, Cloud Spanner is not ideal, since you need to rely on a full Spanner instance. To save costs, you can choose an instance for one region.

Administration Support?


Creating a Cloud Spanner instance is easy. You just need to choose between creating a multi-regional or instance for one region, specify the region (s) and the number of nodes. In less than a minute, the instance will be up and running.

Several basic metrics are directly available on the Spanner page in the Google console. More detailed views are available through Stackdriver, where you can also set threshold metrics and alert policies.

Access to resources?


MySQL offers extensive and very detailed user permission / role settings. You can easily configure access to a specific table, or even just a subset of its columns. Cloud Spanner uses the Google Identity & Access Management (IAM) tool, which allows you to set policies and permissions only at a very high level. The most detailed option is a database-level resolution that does not fit into most production cases. This restriction forces you to add additional security measures to your code, infrastructure, or both to prevent unauthorized use of Spanner resources.

Backups?


In simple terms, there are no backups in Cloud Spanner. Although the high requirements of the Google SLA can guarantee that you will not lose any data due to hardware or database failures, human errors, application defects, etc. We all know the rule: high availability does not replace a reasonable backup strategy. At the moment, the only way to back up data is to programmatically stream it from the database to a separate storage environment.

Query performance?


We used Yahoo! to download data and test queries. Cloud Serving Benchmark. The table below shows the YCSB B workload with a reading ratio of 95% and a write ratio of 5%.



* The load test was performed on the n1-standard-32 computational engine (CE) (32 vCPU, 120 GB of memory), and the test instance was never a bottleneck in the tests.
** The maximum number of threads in one instance of YCSB is 400. In total, six parallel instances of YCSB tests had to be run to get a total of 2400 threads.

Looking at the test results, in particular the combination of processor load and TPS, we clearly see that Cloud Spanner scales quite well. The large load created by a large number of threads is compensated by the large number of nodes in the Cloud Spanner cluster. Although the delay looks rather high, especially when working with 2400 threads, retesting with 6 smaller instances of the computational engine may be necessary to get more accurate numbers. Each instance will run one YCSB test instead of one large CE instance with 6 parallel tests. Thus, it will be easier to distinguish between Cloud Spanner request delays and delays added by the network connection between Cloud Spanner and the CE instance on which the test is running.

How does Cloud Spanner handle OLAP?


Partitioning?


Dividing data into physically and / or logically independent segments, called partitions, is a very popular concept inherent in most OLAP mechanisms. Partitions can significantly improve query performance and database support. Further deepening in the partition would come up in a separate article (s), so let's just mention the importance of having a partitioning scheme and sub-partitioning. The ability to partition data into partitions and even further into subpartitions is key to the performance of analytic queries.

Cloud Spanner does not support partitions per se. It splits data internally into so-called splits based on primary key ranges. Separation is performed automatically to balance the load in the Cloud Spanner cluster. A very convenient feature of Cloud Spanner is to split the base load of the parent table (a table that does not alternate with another). Spanner automatically determines if split contains data that is read more often than data in other split s, and can decide on further separation. Thus, more nodes can be involved in the request, which also effectively increases throughput.

Loading data?


The Cloud Spanner method for bulk data is the same as for regular downloads. To achieve maximum performance, you need to follow some recommendations, including:

  • Sort your data by primary key.
  • Divide them by 10 * the number of nodes of individual sections.
  • Create a set of work tasks that load data in parallel.

With this data download, all Cloud Spanner nodes are used.

We used the A YCSB workload to generate a dataset of 10M rows.



* The load test was performed on the n1-standard-32 computational engine (32 vCPU, 120 GB of memory), and the test instance was never a bottleneck in the tests.
** A 1-node configuration is not recommended for any production load.

As mentioned above, Cloud Spanner automatically processes split-s depending on their load, so the results improve after several successive repetitions of the test. The results presented here are the best results we have received. Looking at the numbers above, we can see how Cloud Spanner scales (well) with an increase in the number of nodes in the cluster. The numbers that stand out are extremely low average delays that contrast with the results of mixed workloads (95% for reading and 5% for writing), as described in the section above.

Scaling?


Increasing and decreasing the number of Cloud Spanner nodes is a one-click task. If you want to quickly load data, you can consider boosting the instance to the maximum (in our case, it was 25 nodes in the US-EAST region), and then reduce the number of nodes suitable for your regular load after all the data in the database bearing in mind the limitation of 2 TB / node.

We were reminded of this limit even with a much smaller database. After several runs of load tests, our database was about 155 GB in size, and when reduced to an instance of 1 node, we received the following error:



We were able to reduce the scale from 25 to 2 instances, but we were stuck on two nodes.

Increasing and decreasing the number of nodes in a Cloud Spanner cluster can be automated using the REST API. This can be especially useful to reduce the increased load on the system during busy hours.

OLAP query performance?


Initially, we planned to devote considerable time to our evaluation of Spanner for this part. After several SELECT COUNTs, we immediately realized that testing would be short and that Spanner would NOT be an OLAP engine. Regardless of the number of nodes in the cluster, a simple selection of the number of rows in a table of 10M rows took 55 to 60 seconds. In addition, any request that required more memory to store intermediate results failed with an OOM error.

SELECT COUNT(DISTINCT(field0)) FROM usertable; — (10M distinct values)-> SpoolingHashAggregateIterator ran out of memory during new row.

Some numbers for TPC-H requests can be found in Todd Lipcon's article Nosql-kudu-spanner-slides.html , slides 42 and 43. These numbers are consistent with our own results (unfortunately).



4. Our findings


Given the current state of Cloud Spanner features, it’s hard to imagine it as a simple replacement for an existing OLTP solution, especially when your needs outgrow it. A significant amount of time would have to be spent building a solution that takes into account Cloud Spanner's flaws.

When we started evaluating Cloud Spanner, we expected its management features to be at or at least not so far from other Google SQL solutions. But we were surprised at the complete lack of backups and the very limited control of access to resources. Not to mention the lack of views, the lack of a local development environment, unsupported sequences, JDBC without DML and DDL support, and so on.

So, where does the one who needs to scale the transactional database go? It seems that there is no single solution on the market that is suitable for all use cases. There are many closed and open source solutions (some of which are mentioned in this article), each of which has its own strengths and weaknesses, but none of them offers SaaS with a 99.999% SLA and a high degree of consistency. If a high SLA is your primary goal and you are not inclined to create your own solution for multiple cloud environments, Cloud Spanner may be the solution you are looking for. But you must be aware of all its limitations.

In fairness, it should be noted that Cloud Spanner was not released to the public only in the spring of 2017, so it is reasonable to expect that some of its current shortcomings may eventually disappear (I hope), and when that happens, it can change the game. After all, Cloud Spanner is not just a third-party project for Google. Google uses it as the basis for other Google products. And when Google recently replaced Megastore in Google Cloud Storage with Cloud Spanner, it allowed Google Cloud Storage to be strictly consistent for object listings globally (which still doesn't apply to Amazon’s S3 ).

So, there is still hope ... we hope.

That's all. Like the author of the article, we also continue to hope, but what do you think about this? Write in the comments.

Everyone is invited to visit our free webinar, in the framework of which we will talk in detail about the OTUS AWS for Developers course.

More articles:


All Articles