ROBERT KUBIS: In this episode of "Things
I Learned About Cloud Spanner," we
look at choosing the right primary keys
to help detect and avoid traffic jams during data load.
Will we be able to clear the data highway congestion?
Stay tuned to find out.
So a while ago, my friend Anna approached
me asking if I could give her advice on a database migration.
Her online shop, Pile of Gadgets,
selling toys like the Cloud Spanner fidget spinner,
has grown tremendously, and she was
worried to have the business running
on the back of a traditional relational database.
After watching the first episode of "Things
I Learned About Cloud Spanner," her team
decided to explore and evaluate this new SQL database.
They started from a recent backup of the database
where all the data was exported through CSV files
and uploaded to Google Cloud Storage.
They then wrote a little loader program
that was reading the files, processing them, and writing
the data to Cloud Spanner.
So far so good.
Having the files in Google Cloud Storage
and processing them from a GCE instance,
Cloud Storage or Cloud Spanner instance, is actually great.
This way you don't need to worry about network capacity or added
latencies being an issue during your data load.
However, while running their first data loads
on a regional instance, there was some disappointment.
They only saw a 35% utilization of their three node instance.
Trying to increase the utilization by scaling
out the loaders didn't help either.
Now, that was interesting.
It looked like their load is hot spotting on only one node.
Three nodes, 35%-- you do the math.
To verify the assumption, they scaled the instance
to five nodes and saw that the utilization dropped
to around 20%, while the number of transactions and throughputs
stayed the same.
And that was a clear indicator that only one of the nodes
was doing work here while the others--
[SPINNING]
At this point, we needed to dig a little deeper
and understand how Cloud Spanner organizes
data to scale your workloads.
Reading the documentation, you find
that Cloud Spanner organizes rows
in a table lexicographically, or for the mere mortals like me,
alphabetically by the primary key.
Then driven by the size of your tables and load patterns,
your data gets divided into chunks, so-called splits.
These splits are distinct ranges of rows
based on your primary key.
To efficiently scale out all your reads and writes,
each individual split can move freely
between the available compute nodes while making sure there
is always--
and I mean always--
only one node that controls the writes to a split.
Now, that's where it gets tricky.
If you write to a table with monotonically increasing
or decreasing primary key values, like 123 or ABC,
all your requests get sent to one split, the first
or the last in your keyspace.
And if you load many rows at once,
you eventually exhaust the resources
of your responsible node for that one split.
Packed with this newly gained knowledge,
we went back and took a look at the schema and data
from Pile of Gadget.
It turned out that the application uses
an outer incremented integer for some of their primary keys,
and the data was exported in that order, which
is creating a hotspot during load
by adding new rows always to the last split.
To resolve this, they needed to modify
the schema, such as introducing a distribution key that you
prefix in your primary key, or using a well distributed hash
function to hash your existing key,
or introduce an entirely new primary key
that is well disputed.
With future scalability requirements in mind,
they opted to generate a new UUID
version for base primary key and kept the old ID around
for potential legacy purposes.
After these modifications, they ran the data loads again
and saw over time a full utilization
of their Cloud Spanner instance where
the throughput scaled with the number of instance nodes.
A little side note here, as you start
to load an empty database, it will take some time
for the first splits to occur and the load-based rebalancing
to kick in.
So it will take some time before you see the full utilization
of your instance.
So now my friend's company, Pile of Gadget,
was happy to continue their evaluation
and see if they could improve the load performance even
further.
Unfortunately, we are out of time.
To find out what we did next to maximize throughput
during load, check out the next episode
of "Things I Learned About Cloud Spanner."
And remember, choosing well distributed primary keys
sets you up for scalability.
Last but not least, for the ones that
love to dive into source code and run
the experiments, to learn more, check out
the links in the descriptions.
[MUSIC PLAYING]