4 ms·

The team here at Quizlet did a lot of performance testing on Spanner with one of our MySQL workloads to see if it's an option for us. Here are the test results:

by tedd4u 10y ago

The team here at Quizlet did a lot of performance testing on Spanner with one of our MySQL workloads to see if it's an option for us. Here are the test results: https://quizlet.com/blog/quizlet-cloud-spanner https://quizlet.com/blog/quizlet-cloud-spanner

koolba 10y ago
What's the SQL and wire compatibility level? MySQL? EDIT: Found quite a bit of my answers in your linked article: > Cloud Spanner uses a SQL dialect which matches the ANSI SQL:2011 standard with some extensions for Spanner-specific features. This is a SQL standard simpler than that used in non-distributed databases such as vanilla MySQL, but still supports the relational model (e.g. JOINs). It includes data-definition language statements like CREATE TABLE. Spanner supports 7 data types: bool, int64, float64, string, bytes, date, timestamp[20]. > Cloud Spanner doesn't, however, support data manipulation language (DML) statements. DML includes SQL queries like INSERT and UPDATE. Instead, Spanner's interface definition includes RPCs for mutating rows given their primary key[21]. This is a bit annoying. You would expect a fully-featured SQL database to include DML statements. Even if you don't use DML in your application you'll almost certainly want them for one-off queries you run in a query console. > Though Cloud Spanner supports a smaller set of SQL than many other relational databases, its dialect is well-documented and fits our use case well. Our requirements for a MySQL replacement are that it supports secondary indices and common SQL aggregations, such as the GROUP BY clause. We've eliminated most of the joins we do, so we haven't tested Cloud Spanner's join performance. This seems like it'd prevent any kind of easy switch over to Spanner.
- havermeyer 10y ago
  It has the same basis as standard SQL in BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/ https://cloud.google.com/bigquery/docs/reference/standard-sq...
- saycheese 10y ago
  Seems more like they want all the data that ever existed for the database.
- deesix 10y ago
  Just to be clear, the JOINS were removed for the vertical sharding prior to looking at Cloud Spanner. Cloud Spanner fully supports complex JOINS of many times (e.g. INNER, OUTER) Details - https://cloud.google.com/spanner/docs/query-syntax#join-types https://cloud.google.com/spanner/docs/query-syntax#join-type... Disclaimer: I work on Cloud Spanner
  - koolba 10y ago
    Sorry for the confusion but I meant the DML portion.
    - MichaelGG 10y ago
      It sounded like you can only modify by primary key? Can you make a transaction that contains a query and a bunch of updates by PK ? And yeah it makes it sound like writing an OEM adapter will be much more difficult.
      - obstinate 10y ago
        From reading the docs, the answer seems to clearly be yes, but I'm open to being corrected.
  - toadthewet 10y ago
    How did you go from 10s of ms for an update for 2012 Spanner, to sub-ms update (best case) for Cloud Spanner according to Quizlet? Did TrueTime get an order of magnitude better? Or is Quizlet measuring the wrong thing?
eric_b 10y ago
> We've eliminated most of the joins we do, so we haven't tested Cloud Spanner's join performance. The join performance is by far the most interesting part of this to me. A more traditional NoSQL solution sounds like it would have worked just as well for you, sans all the atomic clock fanciness. Joining across geographically disparate data is a real trick, and it seems like there would be some physical performance limits?
karmakaze 10y ago
> Not every application can handle Spanner's ~5ms minimum query time, but if you can, then you can have that latency for a very high-throughput workload
- hoschicz 10y ago
  This is the tradeoff we've all been looking for. Cool product, anyway!
aamederen 10y ago
I'm reading the test results and had a question. >> Cloud Spanner doesn't, however, support data manipulation language (DML) statements. DML includes SQL queries like INSERT and UPDATE. Instead, Spanner's interface definition includes RPCs for mutating rows given their primary key[21]. Does this mean I need to rewrite my application? My application uses an ORM and it typically converts my logic to SQL statements and fires them off to Postgres. Would I need to change it such that it doesn't issue INSERT / UPDATE statements?
- knz42 10y ago
  yes.
- parasubvert 10y ago
  No, though it does imply you would need to write adapter code to have your ORM call the equivalent stored procedures for inserts or deletes. Most ORMs can do this to varying degrees.
ex3ndr 10y ago
> So a query that accesses 10 rows in disparate parts of the primary key space will take longer than one where the keys reside on the same splits. This is expected with a distributed system. No, why? Query can be executed in parallel. BTW, isn't 20k/sec is a very very small performance for 30 node installation. Cassandra can handle 50k+ (both writes and read) on a single node. When in most queries you are trying to collect data from many nodes it will scale almost linearly.
- daenney 10y ago
  I don't think that comparison holds. It's easy to push 50k+ on a single node, you're basically only resource bound on that machine. Pushing 20k+ on something that's globally consistent spread out over so many instances is a different exercise entirely. It also depends on the level of consistency you're asking from Cassandra. You'd probably need to set this to EACH_QUORUM or ALL to mimic the behaviour Spanner gives you. And yes Cassandra will scale linearly-ish as long as you're in the same datacenter. Try running a geo-distributed 30-node Cassandra ring and it's a whole different story at that level of consistency and availability.
  - ex3ndr 10y ago
    Sure, but you can make geo-replication and even implement proxy for providing consistency across DC, sure it won't be that fast in terms of latency, but throughput will be the same.
    - daenney 10y ago
      How does this proxy that provides cross-DC consistency work? And how does it guarantee that you'll get globally consistent reads? Most cases I've seen latency can affect throughput plenty so I doubt your assertion that it won't affect throughput quite a bit. Even more so for anything that relies on TCP/IP. I'd highly recommend reading the Bigtable[0] and Spanner[1] papers first and maybe then we can have a sensible and fruitful argument. [0]: https://research.google.com/archive/bigtable.html https://research.google.com/archive/bigtable.html [1]: https://research.google.com/archive/spanner.html https://research.google.com/archive/spanner.html
adevine 10y ago
Just wanted to say thanks for this writeup. This is really excellent, to the point I was passing around your blog post in lieu of the GCP announcement.
inssein 10y ago
Quickly skimmed this.. how is comparing Cloud Spanner to a VM running MySQL comparable?
- packetslave 10y ago
  it isn't
mikeytown2 10y ago
I'm curious about how you have MySQL configured. If the query cache is enabled you will see MySQL plateau. There's also the all important innodb_buffer_pool_size and innodb_flush_log_at_trx_commit. See this for more info https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/ https://www.percona.com/blog/2013/09/20/innodb-performance-o...
- ryangordon 10y ago
  We typically do yearly performance audits with Percona to ensure our databases are optimally configured. We disable the query cache. We set innodb_buffer_pool_size based on a % of total memory (as MySQL will use more than that in total, much for allocating connection structs and things like that). We set innodb_flush_log_at_trx_commit to 0 which is not ideal for data integrity but gives us more performance. In practice, because of Google's Live Migration technology we have never experienced a crash due to hardware on our master nodes (LM will run an emergency migration for any server that has hardware that is detected to be going bad before it becomes a problem) and disks are abstracted away even further. Our main risk is the kernel crashing or MySQL crashing which we've been fortunate enough to not happen on our masters. Spanner provides ACID compliance by default with all the scaleability and performance we get out of it.

by tedd4u 10y ago

koolba 10y ago

What's the SQL and wire compatibility level? MySQL? EDIT: Found quite a bit of my answers in your linked article: > Cloud Spanner uses a SQL dialect which matches the ANSI SQL:2011 standard with some extensions for Spanner-specific features. This is a SQL standard simpler than that used in non-distributed databases such as vanilla MySQL, but still supports the relational model (e.g. JOINs). It includes data-definition language statements like CREATE TABLE. Spanner supports 7 data types: bool, int64, float64, string, bytes, date, timestamp[20]. > Cloud Spanner doesn't, however, support data manipulation language (DML) statements. DML includes SQL queries like INSERT and UPDATE. Instead, Spanner's interface definition includes RPCs for mutating rows given their primary key[21]. This is a bit annoying. You would expect a fully-featured SQL database to include DML statements. Even if you don't use DML in your application you'll almost certainly want them for one-off queries you run in a query console. > Though Cloud Spanner supports a smaller set of SQL than many other relational databases, its dialect is well-documented and fits our use case well. Our requirements for a MySQL replacement are that it supports secondary indices and common SQL aggregations, such as the GROUP BY clause. We've eliminated most of the joins we do, so we haven't tested Cloud Spanner's join performance. This seems like it'd prevent any kind of easy switch over to Spanner.

havermeyer 10y ago
It has the same basis as standard SQL in BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/ https://cloud.google.com/bigquery/docs/reference/standard-sq...
saycheese 10y ago
Seems more like they want all the data that ever existed for the database.
deesix 10y ago
Just to be clear, the JOINS were removed for the vertical sharding prior to looking at Cloud Spanner. Cloud Spanner fully supports complex JOINS of many times (e.g. INNER, OUTER) Details - https://cloud.google.com/spanner/docs/query-syntax#join-types https://cloud.google.com/spanner/docs/query-syntax#join-type... Disclaimer: I work on Cloud Spanner
- koolba 10y ago
  Sorry for the confusion but I meant the DML portion.
  - MichaelGG 10y ago
    It sounded like you can only modify by primary key? Can you make a transaction that contains a query and a bunch of updates by PK ? And yeah it makes it sound like writing an OEM adapter will be much more difficult.
    - obstinate 10y ago
      From reading the docs, the answer seems to clearly be yes, but I'm open to being corrected.
- toadthewet 10y ago
  How did you go from 10s of ms for an update for 2012 Spanner, to sub-ms update (best case) for Cloud Spanner according to Quizlet? Did TrueTime get an order of magnitude better? Or is Quizlet measuring the wrong thing?

eric_b 10y ago

> We've eliminated most of the joins we do, so we haven't tested Cloud Spanner's join performance. The join performance is by far the most interesting part of this to me. A more traditional NoSQL solution sounds like it would have worked just as well for you, sans all the atomic clock fanciness. Joining across geographically disparate data is a real trick, and it seems like there would be some physical performance limits?

karmakaze 10y ago

> Not every application can handle Spanner's ~5ms minimum query time, but if you can, then you can have that latency for a very high-throughput workload

hoschicz 10y ago
This is the tradeoff we've all been looking for. Cool product, anyway!

aamederen 10y ago

I'm reading the test results and had a question. >> Cloud Spanner doesn't, however, support data manipulation language (DML) statements. DML includes SQL queries like INSERT and UPDATE. Instead, Spanner's interface definition includes RPCs for mutating rows given their primary key[21]. Does this mean I need to rewrite my application? My application uses an ORM and it typically converts my logic to SQL statements and fires them off to Postgres. Would I need to change it such that it doesn't issue INSERT / UPDATE statements?

knz42 10y ago
yes.
parasubvert 10y ago
No, though it does imply you would need to write adapter code to have your ORM call the equivalent stored procedures for inserts or deletes. Most ORMs can do this to varying degrees.

ex3ndr 10y ago

> So a query that accesses 10 rows in disparate parts of the primary key space will take longer than one where the keys reside on the same splits. This is expected with a distributed system. No, why? Query can be executed in parallel. BTW, isn't 20k/sec is a very very small performance for 30 node installation. Cassandra can handle 50k+ (both writes and read) on a single node. When in most queries you are trying to collect data from many nodes it will scale almost linearly.

daenney 10y ago
I don't think that comparison holds. It's easy to push 50k+ on a single node, you're basically only resource bound on that machine. Pushing 20k+ on something that's globally consistent spread out over so many instances is a different exercise entirely. It also depends on the level of consistency you're asking from Cassandra. You'd probably need to set this to EACH_QUORUM or ALL to mimic the behaviour Spanner gives you. And yes Cassandra will scale linearly-ish as long as you're in the same datacenter. Try running a geo-distributed 30-node Cassandra ring and it's a whole different story at that level of consistency and availability.
- ex3ndr 10y ago
  Sure, but you can make geo-replication and even implement proxy for providing consistency across DC, sure it won't be that fast in terms of latency, but throughput will be the same.
  - daenney 10y ago
    How does this proxy that provides cross-DC consistency work? And how does it guarantee that you'll get globally consistent reads? Most cases I've seen latency can affect throughput plenty so I doubt your assertion that it won't affect throughput quite a bit. Even more so for anything that relies on TCP/IP. I'd highly recommend reading the Bigtable[0] and Spanner[1] papers first and maybe then we can have a sensible and fruitful argument. [0]: https://research.google.com/archive/bigtable.html https://research.google.com/archive/bigtable.html [1]: https://research.google.com/archive/spanner.html https://research.google.com/archive/spanner.html

adevine 10y ago

Just wanted to say thanks for this writeup. This is really excellent, to the point I was passing around your blog post in lieu of the GCP announcement.

inssein 10y ago

Quickly skimmed this.. how is comparing Cloud Spanner to a VM running MySQL comparable?

packetslave 10y ago
it isn't

mikeytown2 10y ago

I'm curious about how you have MySQL configured. If the query cache is enabled you will see MySQL plateau. There's also the all important innodb_buffer_pool_size and innodb_flush_log_at_trx_commit. See this for more info https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/ https://www.percona.com/blog/2013/09/20/innodb-performance-o...

ryangordon 10y ago
We typically do yearly performance audits with Percona to ensure our databases are optimally configured. We disable the query cache. We set innodb_buffer_pool_size based on a % of total memory (as MySQL will use more than that in total, much for allocating connection structs and things like that). We set innodb_flush_log_at_trx_commit to 0 which is not ideal for data integrity but gives us more performance. In practice, because of Google's Live Migration technology we have never experienced a crash due to hardware on our master nodes (LM will run an emergency migration for any server that has hardware that is detected to be going bad before it becomes a problem) and disks are abstracted away even further. Our main risk is the kernel crashing or MySQL crashing which we've been fortunate enough to not happen on our masters. Spanner provides ACID compliance by default with all the scaleability and performance we get out of it.