There are several benefits to using Vitess, but one of the biggest reasons organizations choose it is its native support of vertical and horizontal sharding on MySQL.
Vertical sharding is the process of dividing up the tables from a single MySQL server and spreading them out into multiple servers.
Horizontal sharding is the process of spreading data out onto multiple servers using a different technique. Data is distributed on a per-row basis, and which server each row goes to is determined by the sharding key. For example, you might have a table with 10 billion rows in it, with an auto incrementing ID. You could distribute this across 10 servers by placing everything with IDs 0-1 billion on one server, 1-2 billion on another, and so on up the chain.
In this lesson, I’ll show you how you can do vertical sharding. Horizontal will come next.
Before we begin vertical sharding on this database, let's look at a few helper scripts. These can be used to populate the customer, corder, and product tables that were created in the same cluster from the last lesson. Here are the scripts:
#!/bin/bash
BEGIN=${1}
END=${2}
x=${BEGIN}
while [ $x -le ${END} ]
do
echo "insert into customer (email) values('person${x}@domain.com');"
echo "insert into corder (customer_id, sku, price) values(${x}, 'SKU-100${x}', 100);"
echo "insert into product (sku, description, price) values('SKU-100${x}', 'something', 100);"
x=$(( $x + 1 ))
done
#!/bin/bash
BEGIN=${1}
END=${2}
x=${BEGIN}
while [ $x -le ${END} ]
do
echo "select * from customer limit 10;"
echo "select * from corder limit 10;"
echo "select * from product limit 10;"
x=$(( $x + 1 ))
done
You can execute the former to get some sample data loaded into the database, and the latter to run some test load on the database.
bash write-traffic.sh 1 20001 | mysql -P 15306 -u root --protocol tcp > write.log
bash read-traffic.sh 1 20001 | mysql -P 15306 -u root --protocol tcp > read.log
In the examples/local directory, there are a bunch of other scripts that start with three digits. The ones starting with 2 have a series of examples related to doing vertical sharding. Let's run these in sequence.
First, we need to run ./201_customer_tablets.sh. This script sets up three additional instances of MySQL and VTTablet. These will be used as our second keyspace which will be called customer:
./201_customer_tablets.sh
Next, we need to run a workflow to move the corder and customer tables from the first keyspace (commerce) into this new keyspace. This is done by the next script:
After this completes, the tables should be copied over to the new keyspace. However, there are still two other steps that need to happen to cause the traffic to be routed to these tables. Run the next two scripts for this:
./203_switch_reads.sh
./204_switch_writes.sh
To finish up:
We've now vertically sharded!
If you run the read-traffic.sh script again, you should be able to see in the VTAdmin UI that some of the queries are being handled by the primary node in the commerce keyspace, while others are being handled by the primary in customer.