The effortless solution for revamping your database management process and scaling data in the cloud.
If you’re unsure what PlantScale is, I suggest you read the previous article, where I go over the basics of what PlantScale is intended for. This article intends to explain the technical side of PlanetScale and Vitess.
How do PlanetScale and Vitess differ?
Vitess is a MySQL solution designed for deploying, scaling, and managing big data.
PlanetScale offers Vitess in the cloud, providing a GUI, CLI, and, most recently (Jan ‘23), an API. Traditionally, configuring custom, scalable databases can be a difficult task, often requiring two teams: infrastructure and database administrators.
PlanetScale completely abstracts this by offering a fully managed Vitess cluster, along with comprehensive interfaces and additional features, reducing effort and concerns about infrastructure.
How does PlanetScale use Vitess?
When a database is spun up on PlanetScale, behind the scenes, an entirely new Vitess cluster is created. This includes everything from the databases to the Vitess components, such as VTTablets and VTGate, which I explain in more detail later in this post. The database can then be connected via exposed connection strings, as described in the previous PlanetScale article I wrote.
Below is an architecture diagram of a Vitess cluster for a PlanetScale database based on a PlanetScale blog post and the Vitess documentation.
This cluster contains a VTGate, a Topology Database, a vtctld instance, and two shards, each consisting of a VTTablet and two databases. The VTGate is a lightweight proxy that forwards MySQL queries to the relevant VTTablets, bridging the gap between PlanetScale and the shards.
A benefit of sharding is that it enables clusters to have more replicas (primary and secondary), which can enable better geographic spreading with the ability to configure instances based on workload and hardware capabilities. For example, writes could occur on primary instances while reads on other instances. PlanetScale offers this level of configurability at a high level, simply enabled at the click of a button.
In the infrastructure diagram above, you can also see a Vtcld instance and a topology server within the Vitess cluster diagram. These components are responsible for managing the data: recording metadata of keyspaces, shards, and cells.
Connection Management
A PlanetScale database can theoretically serve an infinite number of connections, which is achieved by the Vitess infrastructure through connection pooling, reducing the number of resource-intensive MySQL connections. In addition, the Vitess components are written in Go and communicate with each other using gRPC, leveraging concurrency to handle thousands of connections simultaneously.
In the diagram above, you can see that only the VTTablet has direct communication with the MySQL database. You can consider it to be a daemon — or, in the Kubernetes realm, as a sidecar. It’s the VTTablet that has direct access to the mysqld process, and therefore, typically, they run on the same host. Vitess also implements a VTGate, which is a proxy responsible for routing traffic to the correct VTTablet, combining and returning the result to the client. The client can connect to the VTGate as if it’s a single MySQL server, increasing the spread across multiple instances and databases.
Because PlanetScale implements Vitess behind the scenes, connection pool management is supported straight out of the box, enabling virtually unlimited simultaneous connections — ideal for serverless infrastructure.
Online Schema Migrations
Vitess enables online schema migrations through tools such as gh-ost. Schema Migrations, also known as Data Definition Language (DDL) (a subset of SQL responsible for table descriptions and relationships), can be a complex and inefficient task — especially when relational data is already present in the database.
Traditionally, schema migrations were done with MySQL at the database level. This introduced issues such as resource blocking, uninterruptible queries, and resource-intensive tasks. Online DDL moves the migration logic to the application layer, introducing abilities such as monitoring, cancelling, and retrying migrations. This is achieved through ghost tables, where data is copied from the existing table into a new ghost table with the applied migrations.
During the migration, queries are still applied to the original table. After the migration completes, the ghost table replaces it. An obvious downside to cloning data for the ghost table is multiplied disk space usage during the migration. However, after migration, Vitess will automate the clean-up process by “garbage collecting” old tables and artefacts.
With relational data, foreign keys are used to describe these relationships, which can be enforced by the storage engine through foreign key constraints. Deleting data that would result in broken relationships, such as orphaned data, would result in an error. This problem arises when a ghost table is used — what happens to the old data once the migration finishes? Data becomes orphaned, and InnoDB would throw an error.
To overcome this, relationships can be enforced at the application layer: through Online DDL. This can speed up transactions involving deleting relationships — it is no longer a requirement that all dependent rows are dealt with immediately during a DELETE. Online DDL means they can be dealt with at some point in the future, removing the need for long, uninterruptible queries. Instead, dependency deletion can be batched into smaller jobs (such as limiting to 100 rows at a time), and scheduled for periods of lower traffic. You can read more about how PlanetScale leverages Vitess for concepts such as relationship integrity and batch processing here.
Demo
Vitess provides a demo that can be downloaded here. There is also some in-depth documentation available about the demo here.
The demo also provides a web interface to help visualise where data is being stored. If you want to learn more about the demo’s functionality, follow the links to run the demo locally and read about it in the docs.
As explained in the previous article I wrote, sharding is where rows in a table are split across multiple resources to enable various concepts such as horizontal scaling. In the demo, the customer database has been sharded into customer:-80 and customer:80-. The VTGate proxy works to combine these shards to appear as a single database, ‘customer’. The VTGate can be connected to as if it were a normal MySQL database, consolidating the sharded data into a single database.
$ mysql -h 127.0.0.1 -P 12348
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-vitess-16.0.0-SNAPSHOT Version: 16.0.0-SNAPSHOT (Git revision 64de9d45663dccac95c0bbbc086d08fba65917e6 branch 'main') built on Mon Feb 13 15:25:58 GMT 2023 by vboxuser@vitess using go1.19.4 linux/amd64
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
In the demo, the customer table has two values: customer_id and uname. customer_id has been configured to be the Primary VIndex, which means it determines which shard the row should be placed into.
In the demo, the customer table has two values: customer_id and uname. customer_id has been configured to be the Primary VIndex, which means it determines which shard the row should be placed into. The demo uses a hash VIndex, which means each row is assigned a value (up to 64 bits) based on the value of the customer_id.
The data can then be split in half: storing rows where the VIndex is less than a specified value in shard -80, and otherwise in shard 80-. The VTGate can be used to calculate what the VIndex would be for IDs 1,2,3,4 and 5, and therefore which shard they would be placed in.
mysql> use customer;
Database changed
mysql> select * from hash where id in(1,2,3,4,5)\\G;
*************************** 1. row ***************************
id: 1
keyspace_id: k@?J?K?
range_start:
range_end: ?
hex_keyspace_id: 166b40b44aba4bd6
shard: -80
*************************** 2. row ***************************
id: 2
keyspace_id: ??"Βp?
range_start:
range_end: ?
hex_keyspace_id: 06e7ea22ce92708f
shard: -80
*************************** 3. row ***************************
id: 3
keyspace_id: N??ɢ??
range_start:
range_end: ?
hex_keyspace_id: 4eb190c9a2fa169c
shard: -80
*************************** 4. row ***************************
id: 4
-? keyspace_id: ???g?
range_start: ?
range_end:
hex_keyspace_id: d2fd8867d50d2dfe
shard: 80-
*************************** 5. row ***************************
id: 5
keyspace_id: p? ?z
range_start:
range_end: ?
hex_keyspace_id: 70bb023c810ca87a
shard: -80
5 rows in set (0.00 sec)
We can test this by running the SQL statement below to populate the customer table with 5 rows since we know the customer id will be automatically incremented:
mysql> insert into customer(uname) values ('alice'),('bob'),('charlie'),('dan'),('eve');
Query OK, 5 rows affected (0.01 sec)
We can then confirm which values were put into which shard via VTGate, which allows us to isolate a database’s shard:
mysql> use `customer:80-`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM customer;
+-------------+-------+
| customer_id | uname |
+-------------+-------+
| 4 | dan |
+-------------+-------+
2 rows in set (0.01 sec)
mysql> use `customer:-80`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM customer;
+-------------+---------+
| customer_id | uname |
+-------------+---------+
| 1 | alice |
| 2 | bob |
| 3 | charlie |
| 5 | eve |
+-------------+---------+
4 rows in set (0.00 sec)
VTGate will combine these shards into a single table when accessing just the customer database, consolidating the data to appear as if only one table was used:
mysql> use `customer`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM customer;
+-------------+---------+
| customer_id | uname |
+-------------+---------+
| 1 | alice |
| 2 | bob |
| 3 | charlie |
| 5 | eve |
| 4 | dan |
+-------------+---------+
5 rows in set (0.01 sec)
Within the same demo application, ‘corder’ is a table that has a dependency on customer: A customer can have zero or more orders. To reduce the number of queries, Vitess can be configured to place orders within the same shard as the associated customer. You can read more on how to configure Vitess to work like this here. Running the SQL statement below will assign:
mysql> insert into corder(customer_id, product_id, oname) values (1,1,'gift'),(1,2,'gift'),(2,1,'work'),(3,2,'personal'),(4,1,'personal');
Query OK, 5 rows affected (0.01 sec)
Similar to before, we can view the items in the corder table for each shard: the orders associated with a customer in shard -80 will also live in that shard, and vice versa.
mysql> use `customer:-80`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM corder;
+-----------+-------------+------------+----------+
| corder_id | customer_id | product_id | oname |
+-----------+-------------+------------+----------+
| 1 | 1 | 1 | gift |
| 2 | 1 | 2 | gift |
| 3 | 2 | 1 | work |
| 4 | 3 | 2 | personal |
+-----------+-------------+------------+----------+
4 rows in set (0.00 sec)
mysql> use `customer:80-`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM corder;
+-----------+-------------+------------+----------+
| corder_id | customer_id | product_id | oname |
+-----------+-------------+------------+----------+
| 5 | 4 | 1 | personal |
+-----------+-------------+------------+----------+
1 row in set (0.01 sec)
And, a consolidated corder table can also be viewed:
mysql> use customer;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM corder;
+-----------+-------------+------------+----------+
| corder_id | customer_id | product_id | oname |
+-----------+-------------+------------+----------+
| 5 | 4 | 1 | personal |
| 1 | 1 | 1 | gift |
| 2 | 1 | 2 | gift |
| 3 | 2 | 1 | work |
| 4 | 3 | 2 | personal |
+-----------+-------------+------------+----------+
5 rows in set (0.01 sec)
Do I really have to worry about any of this?
No! PlanetScale isolates and abstracts all of this functionality, so you can simply use it as a standard database. With many features provided straight out of the box, customers can perform data migration, scaling and gathering performance insights without complex configuration. In contrast, whilst alternatives such as AWS RDS support connection pooling require manual configuration, whereas PlanetScale harnesses Vitess’ ability to do this automatically.
How can YLD help?
If you’re looking to get into the latest technology or want to join a collaborative team of great engineers and designers that work on impressive client projects, feel free to contact us at YLD at talent@yld.io for open jobs or hello@yld.io to discuss the prospect of a potential project.
Planet Scale and Vitess: A Technical Deepdive was originally published in YLD Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.
.png){kind=small}
