Hello, Khabrovites. Traditionally, we continue to share interesting material on the eve of the start of new courses. Today, especially for you, we have translated an article about Google Cloud Spanner, timed to coincide with the launch of the course .
Originally published in .
As a company that offers a variety of cloud-based POS solutions for retailers, restaurateurs, and online merchants around the world, Lightspeed uses several different types of database platforms for a variety of transactional, analytics, and search use cases. Each of these database platforms has its own strengths and weaknesses. Therefore, when Google introduced Cloud Spanner to the market - promising features not seen in the world of relational databases, such as virtually unlimited horizontal scalability and a 99,999% service level agreement (SLA), We couldn't pass up the opportunity to have her 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:
- Our evaluation criteria
- Cloud Spanner in a nutshell
- Our rating
- Our findings
1. Our evaluation criteria
Before diving into the specifics of Cloud Spanner, its similarities and differences with other solutions on the market, let's first talk about the main use cases we had in mind when considering where to deploy Cloud Spanner in our infrastructure:
- As a replacement for the (prevailing) traditional SQL database solution
- As an OLAP-enabled OLTP solution
Note: For ease of comparison, this article compares Cloud Spanner with the MySQL variants of the GCP Cloud SQL and Amazon AWS RDS solution families.
Using Cloud Spanner as a replacement for a traditional SQL database solution
In the environment traditional databases, when the response time for a database query approaches or even exceeds predefined application thresholds (primarily due to an increase in the number of users and/or requests), there are several ways to reduce the response time to acceptable levels. However, most of these solutions involve manual intervention.
For example, the first step to take is to look at the various performance-related database settings and tune them to best match application usage scenario 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, typically by adding more processors/cores, more RAM, faster storage, etc. Adding more hardware resources results in increased database performance, measured primarily in transactions per second, and transaction latency for OLTP systems. Relational database systems (that use a multi-threaded approach) such as MySQL scale well vertically.
There are several drawbacks to this approach, but the most obvious is the maximum server size on the market. Once the largest Server Instance limit is reached, there is only one path left: scale out.
Scale-out is an approach that adds more servers to a cluster to ideally increase performance linearly as more servers are added. Majority traditional database systems do not scale well or do not scale at all. For example, MySQL can scale out for read operations by adding slave readers, but cannot scale out for write operations.
On the other hand, due to its nature, Cloud Spanner can easily scale horizontally with minimal intervention.
Full featured DBMS as a service must be evaluated from different perspectives. As a basis, we took the most popular DBMS in the cloud - for Google, GCP Cloud SQL and for Amazon, AWS RDS. In our evaluation, 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: Instance management such as scaling up/down and upgrading instances; SLA, backup and recovery; security/access control.
Using Cloud Spanner as an OLAP-Enabled OLTP Solution
While Google does not explicitly state that Cloud Spanner is for analytics, it does share some attributes with other engines such as Apache Impala & Kudu and YugaByte that are designed for OLAP workloads.
Even if there was only a small chance that Cloud Spanner included a consistent scale-out HTAP (Hybrid Transactional/Analytic Processing) engine with a (more or less) usable OLAP feature set, we think it would merit our attention.
With that in mind, we looked at the following categories:
- Data loading, indexes and partitioning support
- Query performance and DML
2. Cloud Spanner in a Nutshell
Google Spanner is a clustered 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 Cloud Spanner attributes:
- 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 a single table (unlike Apache HBase or Apache Kudu).
- Primary Key Based Tables: All tables must have a declared Primary Key (PC), which can consist of multiple table columns. Tabular data is stored in PC order, which makes it very efficient and fast for PC searches. As with other PC-based systems, the implementation must be modeled against preconceived use cases in order to achieve .
- Striped tables: Tables can have physical dependencies on each other. The rows of the child table can be matched with the rows of the parent table. This approach speeds up the search for relationships that can be determined at the data modeling stage, for example, when placing customers and their invoices together.
- Indexes: Cloud Spanner supports secondary indexes. An index consists of indexed columns and all PC columns. Optionally, the index can also contain other non-indexed columns. The index can be interleaved with the parent table to speed up queries. Several restrictions apply to indexes, such as the maximum number of additional columns that can be stored in an index. Also, queries through indexes may not be as straightforward as in other RDBMS.
βCloud Spanner selects an index automatically only in rare cases. In particular, Cloud Spanner does not automatically select a secondary index if the query requests any columns that are not stored in Β».
- Service Level Agreement (SLA): Single region deployment with 99,99% SLA; multi-region deployments with 99,999% SLA. While the SLA itself is just an agreement and not a guarantee of any kind, I believe that Google people do have some hard data to make such a strong claim. (For reference, 99,999% means 26,3 seconds of service downtime per month.)
- More:
Note: The Apache Tephra project adds advanced transaction support to Apache HBase (also now implemented in Apache Phoenix as a beta).
3. Our evaluation
So, we've all read Google's statements about the benefits of Cloud Spanner - virtually unlimited horizontal scaling while maintaining high consistency and a very high SLA. Although these claims are, in any case, 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, two of the most popular OLTP databases in the cloud market, have a very large feature set. However, in order to scale these databases beyond the size of a single node, you need to perform application splitting. This approach creates additional complexity for both applications and administration. We looked at how Spanner fits into the scenario of combining multiple shards into one instance and what features (if any) might have to be sacrificed.
Support for SQL, DML and DDL, as well as the connector and libraries?
First, when starting with any database, you need to create a data model. If you think you can connect JDBC Spanner to your favorite SQL tool, you'll find that you can query your data with it, but you can't use it to create a table or update (DDL) or any insert/update/delete operations ( DML). Google's official JDBC does not support either.
"Drivers do not currently support DML or DDL statements."
Spanner Documentation
The situation is no better with the GCP console - you can only send SELECT queries. Luckily, there is a JDBC driver with DML and DDL support from the community, including transactions . While this driver is extremely useful, the absence of Google's own JDBC driver is surprising. Luckily, Google offers fairly broad client library support (based on gRPC): C#, Go, Java, node.js, PHP, Python, and Ruby.
The near-mandatory use of Cloud Spanner's custom APIs (due to the lack of DDL and DML in JDBC) results in some limitations for related areas of code such as connection pooling or database binding frameworks (like Spring MVC). Generally, when using JDBC, you are free to choose your favorite connection pool (eg HikariCP, DBCP, C3PO, etc.) that is tested and works well. In the case of custom Spanner APIs, we have to rely on frameworks/binding/session pools that we have created ourselves.
The primary key (PC) oriented design allows Cloud Spanner to be very fast when accessing data via the PC, but also introduces some query issues.
- You cannot update the value of a primary key; You must first delete the original PC entry and re-insert it with the new value. (This is similar to other PC oriented database/storage engines.)
- Any UPDATE and DELETE statements must specify the PC in the WHERE, therefore, there cannot be empty DELETE all statements - there must always be a subquery, for example: UPDATE xxx WHERE id IN (SELECT id FROM table1)
- Lack of an auto-increment option or something similar that sets the sequence for the PC field. For this to work, the corresponding value must be created on the application side.
Secondary indices?
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 via Apache Phoenix.
Indexes in Kudu and HBase can be modeled as a separate table with different composition of primary keys, but the atomicity of the operations performed on the parent table and related index tables must be performed at the application level and is not trivial to implement correctly.
As mentioned in the Cloud Spanner review, its indexes may differ from MySQL indexes. Thus, special care must be taken in query building and profiling to ensure that the correct index is used where it is needed.
Representation?
A very popular and useful object in a database is views. They can be useful for a large number of use cases; my two favorites are the logical abstraction layer and the security layer. Unfortunately Cloud Spanner does NOT support views. However, this only partially limits us, as there is no column-level granularity for access permissions where views can be an acceptable solution.
See the Cloud Spanner documentation for a section detailing quotas and limits (), there is one in particular that can be problematic for some applications: Cloud Spanner out of the box has a maximum of 100 databases per instance. Obviously, this can be a major hurdle for a database that is designed to scale to over 100 databases. Luckily, after speaking 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 programming language support for working with its API. The officially supported libraries are in the area of ββC#, Go, Java, node.js, PHP, Python, and Ruby. The documentation is fairly detailed, but as with other cutting-edge technologies, the community is quite small compared to most popular database technologies, which can result in more time spent on less common use cases or problems.
So what about local development support?
We haven't found a way to create a Cloud Spanner instance on-premises. The closest we got is a Docker image which is similar in principle, but very different in practice. For example CockroachDB can use PostgreSQL JDBC. Since the development environment should be as close as possible to the production environment, Cloud Spanner is not ideal because you need to rely on a full Spanner instance. To save costs, you can select a single region instance.
Administration support?
Creating a Cloud Spanner instance is very simple. You just need to choose between creating a multi-region or a single-region instance, specify the region(s) and the number of nodes. In less than a minute, the instance will be up and running.
Several elementary metrics are directly available on the Spanner page in the Google Console. More detailed views are available via Stackdriver, where you can also set metric thresholds and alert policies.
Access to resources?
MySQL offers extensive and very granular user permission/role settings. You can easily customize access to a specific table, or even just a subset of its columns. Cloud Spanner uses the Google Identity & Access Management (IAM) tool, which only allows you to set policies and permissions at a very high level. The most granular option is database-level permission, which doesn't fit in 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?
To put it simply, there are no backups in Cloud Spanner. While Google's high SLA requirements can ensure you don't lose any data due to hardware or database failures, human error, application defects, etc. We all know the rule: high availability is no substitute for a smart backup strategy. Currently, 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 load data and test queries. Cloud Serving Benchmark. The table below shows the B YCSB workload with a 95% read to 5% write ratio.
* The load test was run on n1-standard-32 Compute Engine (CE) (32 vCPUs, 120 GB memory) and the test instance was never the bottleneck in the tests.
** The maximum number of threads in one YCSB instance is 400. In total, six parallel instances of YCSB tests had to be run to get a total of 2400 threads.
Looking at the benchmark results, in particular the combination of CPU load and TPS, we can clearly see that Cloud Spanner scales quite well. The large load created by a large number of threads is offset by a large number of nodes in the Cloud Spanner cluster. Although the latency looks quite high, especially when running at 2400 threads, it may be necessary to retest with 6 smaller instances of the compute engine to get more accurate numbers. Each instance will run one YCSB test instead of one large CE instance with 6 parallel tests. This will make it easier to distinguish between Cloud Spanner request delays and delays added by the network connection between Cloud Spanner and the CE instance running the test.
How does Cloud Spanner perform as an OLAP?
Partitioning?
Dividing data into physically and/or logically independent segments, called partitions, is a very popular concept found in most OLAP engines. Partitions can greatly improve query performance and database maintainability. Further delving into partitioning would be a separate article(s), so let's just mention the importance of having a partitioning scheme and sub-partitioning. The ability to split data into partitions and even further into sub-partitions is key to the performance of analytical queries.
Cloud Spanner does not support partitions per se. It separates data internally into so-called split-s based on primary key ranges. The partitioning is done automatically to balance the load on the Cloud Spanner cluster. A very handy feature of Cloud Spanner is splitting the base load of a parent table (a table that is not interleaved with another). Spanner automatically detects if it contains split data that is read more frequently than data in other split-ah, and may decide on a further separation. Thus, more nodes can be involved in a request, which also effectively increases throughput.
Loading data?
The Cloud Spanner method for bulk data is the same as for a regular upload. For maximum performance, you need to follow some guidelines, including:
- Sort your data by primary key.
- Divide them by 10*number of nodes individual sections.
- Create a set of worker tasks that load data in parallel.
This data load uses all Cloud Spanner nodes.
We used the A YCSB workload to generate a 10M row dataset.
* The load test was run on the n1-standard-32 compute engine (32 vCPUs, 120 GB memory) and the test instance was never the bottleneck in the tests.
** A 1 node setup is not recommended for any production workload.
As mentioned above, Cloud Spanner automatically processes splits depending on their load, so the results improve after several consecutive iterations 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) as the number of nodes in the cluster increases. The numbers that stand out are extremely low average latency, which contrasts with results from mixed workloads (95% read and 5% write) 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 load data quickly, you may want to 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 normal load after all the data in the database , keeping in mind the 2 TB/node limit.
We were reminded of this limit even with a much smaller database. After several load test runs, our database was about 155 GB in size, and when scaled down to a 1 node instance, we got the following error:
We were able to scale down from 25 to 2 instances, but we are stuck on two nodes.
Raising and decrementing the number of nodes in a Cloud Spanner cluster can be automated using the REST API. This can be especially useful for reducing the increased load on the system during busy hours.
OLAP query performance?
We originally planned to devote considerable time to our evaluation of Spanner on this part. After a few SELECT COUNTs, we immediately realized that the test would be short and that Spanner would NOT be a suitable engine for OLAP. Regardless of the number of nodes in the cluster, simply choosing the number of rows in a 10M row table took 55 to 60 seconds. Also, any query 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 queries can be found in Todd Lipcon's article , slides 42 and 43. These numbers are consistent with our own results (unfortunately).
4. Our findings
Given the current state of Cloud Spanner's features, it's hard to see it as a simple replacement for an existing OLTP solution, especially when your needs outgrow it. It would take a significant amount of time to build a solution around the shortcomings of Cloud Spanner.
When we started evaluating Cloud Spanner, we expected its management features to be on par with, or at least not far from, other Google SQL solutions. But we were surprised by the complete lack of backups and very limited access control to resources. Not to mention no views, no local development environment, unsupported sequences, JDBC without DML and DDL support, and so on.
So, where to go for someone who needs to scale a transactional database? There doesn't seem to be a single solution on the market yet that fits all use cases. There are many closed and open source solutions (some of which are mentioned in this article), each with their own strengths and weaknesses, but none of them offer SaaS with a 99,999% SLA and a high degree of consistency. If a high SLA is your primary goal and you're not inclined to build your own solution for multiple clouds, Cloud Spanner might be the solution you're looking for. But you should be aware of all its limitations.
To be fair, Cloud Spanner was only released to the public in the spring of 2017, so it's reasonable to expect some of its current flaws may eventually go away (hopefully), and when it does, it could be a game-changer. After all, Cloud Spanner is not just a side 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 become highly consistent for object lists on a global scale (which is still not the case for ).
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
We invite everyone to visit our in which we will tell you in detail about the course from OTUS.
Source: habr.com