Vitess attempts to be as transparent for the user as possible. As you may imagine, for a platform that supports sharding, this is not the easiest thing to do. There are quite a few factors you should consider when planning the execution of the queries. Indexes, sharding design, how to aggregate cross-shard data and so on. This all is handled by Vitess query planner. Usually, it is useful for user to have an insight into how the process is done or, at least, what is the outcome of the work done by the query planner. To make sure it is possible, a tool has been developed for Vitess that can be used to explain the query execution plan: vtexplain. In this blog we would like to explain how it can be used in a Vitess environment created by the Vitess Operator.
Installing vtexplain
The first challenge is to find vtexplain. Unfortunately, no matter where we looked, we were unable to find existing vtexplain binary. We have checked docker containers for vtgate, vttablets – no luck. Finally, we have found it available in Vitess’ binary packages. You can find the latest builds on Vitess GitHub release page. At this moment, the latest available package is for version 12.0.1. We can download it:
root@k8smaster:~/deb# wget https://github.com/vitessio/vitess/releases/download/v12.0.1/vitess_12.0.1-558a5eb_amd64.deb --2021-12-15 23:16:51-- https://github.com/vitessio/vitess/releases/download/v12.0.1/vitess_12.0.1-558a5eb_amd64.deb Resolving github.com (github.com)... 140.82.121.4 Connecting to github.com (github.com)|140.82.121.4|:443... connected. . . . Length: 419931622 (400M) [application/octet-stream] Saving to: ‘vitess_12.0.1-558a5eb_amd64.deb’ vitess_12.0.1-558a5eb_amd64.deb 100%[===================================================================================================================>] 400.48M 22.7MB/s in 17s 2021-12-15 23:17:08 (23.6 MB/s) - ‘vitess_12.0.1-558a5eb_amd64.deb’ saved [419931622/419931622]
Then we can extract its contents using ar and tar.
root@k8smaster:~/deb# ar x vitess_12.0.1-558a5eb_amd64.deb root@k8smaster:~/deb# ls control.tar.gz data.tar.gz debian-binary vitess_12.0.1-558a5eb_amd64.deb root@k8smaster:~/deb# tar zxf data.tar.gz root@k8smaster:~/deb# ls -alh usr/bin/vtexplain -rwxr-xr-x 1 root root 38M Dec 14 12:55 usr/bin/vtexplain
Now, we can copy the vtexplain binary into one of the directories listed in PATH on our system and we are good to go.
Preparing the files
Vtexplain requires a couple of things to be ready before it can execute. One, quite obvious, is a file that contains the query (or the query itself passed as an argument). Second, vschema of the keyspace, which allows vtexplain to understand what tables are there, how the sharding looks like and so on. Third, it needs schemas for all tables involved in the query.
First, we have to generate a json file with the vschema. We can use vtctlclient GetVSchema for that:
root@k8smaster:~/vitesstests# vtctlclient GetVSchema newsbtest > vschema.json
The main difference between what we got and what we need is that vtexplain expects to see a map of vschemas for each keyspace therefore while we got:
{
"sharded": true,
"vindexes": {
"w_id": {
"type": "reverse_bits"
}
},
"tables": {
"customer1": {
"columnVindexes": [
{
"column": "c_w_id",
"name": "w_id"
}
]
},
"customer10": {
"columnVindexes": [
{
"column": "c_w_id",
"name": "w_id"
}
]
.
.
.
}
we really need:
{
"newsbtest": {
"sharded": true,
"vindexes": {
"w_id": {
"type": "reverse_bits"
}
},
"tables": {
"customer1": {
"columnVindexes": [
{
"column": "c_w_id",
"name": "w_id"
}
]
},
.
.
.
}
Luckily, it’s not that time-consuming to make necessary changes and have it in a proper format, we just need to add two levels of intendention and then add:
{
"schemaname":
}
Next, we have to create the file with SQL for the query. There is nothing tricky nor fancy about this:
root@k8smaster:~/vitesstests/tpcc_vschema# cat query_aggr.sql
SELECT COUNT(DISTINCT (s.s_i_id))
FROM order_line5 AS ol JOIN stock5 AS s
ON (ol.ol_i_id=s.s_i_id AND ol.ol_w_id=s.s_w_id)
WHERE ol.ol_w_id = 3
AND ol.ol_d_id = 1
AND ol.ol_o_id < 3088
AND ol.ol_o_id >= 3068
AND s.s_w_id= 3
AND s.s_quantity < 17
Just create a file with your query in it.
Finally, we have to have schemas for tables involved in the query. Again, nothing fancy. Standard SHOW CREATE TABLE is more than enough to get it done:
root@k8smaster:~/vitesstests/tpcc_vschema# cat both.schema.file CREATE TABLE `order_line5` ( `ol_o_id` int NOT NULL, `ol_d_id` tinyint NOT NULL, `ol_w_id` smallint NOT NULL, `ol_number` tinyint NOT NULL, `ol_i_id` int DEFAULT NULL, `ol_supply_w_id` smallint DEFAULT NULL, `ol_delivery_d` datetime DEFAULT NULL, `ol_quantity` tinyint DEFAULT NULL, `ol_amount` decimal(6,2) DEFAULT NULL, `ol_dist_info` char(24) DEFAULT NULL, PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`), KEY `fkey_order_line_29` (`ol_supply_w_id`,`ol_i_id`), CONSTRAINT `fkey_order_line_1_9` FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`) REFERENCES `orders9` (`o_w_id`, `o_d_id`, `o_id`), CONSTRAINT `fkey_order_line_2_9` FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`) REFERENCES `stock9` (`s_w_id`, `s_i_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `stock5` ( `s_i_id` int NOT NULL, `s_w_id` smallint NOT NULL, `s_quantity` smallint DEFAULT NULL, `s_dist_01` char(24) DEFAULT NULL, `s_dist_02` char(24) DEFAULT NULL, `s_dist_03` char(24) DEFAULT NULL, `s_dist_04` char(24) DEFAULT NULL, `s_dist_05` char(24) DEFAULT NULL, `s_dist_06` char(24) DEFAULT NULL, `s_dist_07` char(24) DEFAULT NULL, `s_dist_08` char(24) DEFAULT NULL, `s_dist_09` char(24) DEFAULT NULL, `s_dist_10` char(24) DEFAULT NULL, `s_ytd` decimal(8,0) DEFAULT NULL, `s_order_cnt` smallint DEFAULT NULL, `s_remote_cnt` smallint DEFAULT NULL, `s_data` varchar(50) DEFAULT NULL, PRIMARY KEY (`s_w_id`,`s_i_id`), KEY `fkey_stock_29` (`s_i_id`), CONSTRAINT `fkey_stock_1_9` FOREIGN KEY (`s_w_id`) REFERENCES `warehouse9` (`w_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
This is it, we can now run vtexplain. The only thing that we have to decide on is to pass to vtexplain how the keyspace is sharded. We can either use an equal range distribution, but in that case the number of shards have to be a power of two. This is quite an annoying limitation as Vitess has no problems whatsoever to do equal sharding across 3 or 5 shards therefore this limit seems to be rather artificial.
root@k8smaster:~/vitesstests# ./vtexplain -shards 5 -sql-file tpcc_vschema/query_aggr.sql -vschema-file tpcc_vschema/vtexplain_schema.json -schema-file tpcc_vschema/both.schema.file ERROR: initVtgateExecutor: the shard count must be a power of two: 5
Another approach would be to define a shard map with custom ranges. It can be constructed as:
{ "keyspace1": $(vtctlclient FindAllShardsInKeyspace keyspace1), "keyspace2": $(vtctlclient FindAllShardsInKeyspace keyspace2) }
An example of a keyspace sharded 5 ways with equal range distribution would be:
{
"newsbtest": {
"-33": {
"primary_alias": {
"cell": "zone1",
"uid": 1680719348
},
"primary_term_start_time": {
"seconds": 1639524747,
"nanoseconds": 927122326
},
"key_range": {
"end": "Mw=="
},
"is_primary_serving": true
},
"33-66": {
"primary_alias": {
"cell": "zone1",
"uid": 600961333
},
"primary_term_start_time": {
"seconds": 1639524745,
"nanoseconds": 918892310
},
"key_range": {
"start": "Mw==",
"end": "Zg=="
},
"is_primary_serving": true
},
"66-99": {
"primary_alias": {
"cell": "zone1",
"uid": 271120913
},
"primary_term_start_time": {
"seconds": 1639524750,
"nanoseconds": 756532148
},
"key_range": {
"start": "Zg==",
"end": "mQ=="
},
"is_primary_serving": true
},
"99-cc": {
"primary_alias": {
"cell": "zone1",
"uid": 1542446643
},
"primary_term_start_time": {
"seconds": 1639524725,
"nanoseconds": 287813086
},
"key_range": {
"start": "mQ==",
"end": "zA=="
},
"is_primary_serving": true
},
"cc-": {
"primary_alias": {
"cell": "zone1",
"uid": 3658524143
},
"primary_term_start_time": {
"seconds": 1639524718,
"nanoseconds": 304865545
},
"key_range": {
"start": "zA=="
},
"is_primary_serving": true
}
}
}
How to use vtexplain to show the execution plan?
Now we can run our vtexplain:
root@k8smaster:~/vitesstests# ./vtexplain -ks-shard-map-file tpcc_vschema/shard_map.json -sql-file tpcc_vschema/query_aggr.sql -vschema-file tpcc_vschema/vtexplain_schema.json -schema-file tpcc_vschema/both.schema.file
----------------------------------------------------------------------
SELECT COUNT(DISTINCT (s.s_i_id))
FROM order_line5 AS ol JOIN stock5 AS s
ON (ol.ol_i_id=s.s_i_id AND ol.ol_w_id=s.s_w_id)
WHERE ol.ol_w_id = 3
AND ol.ol_d_id = 1
AND ol.ol_o_id < 3088
AND ol.ol_o_id >= 3068
AND s.s_w_id= 3
AND s.s_quantity < 17
1 newsbtest/99-cc: select COUNT(distinct s.s_i_id) from order_line5 as ol join stock5 as s on ol.ol_i_id = s.s_i_id and ol.ol_w_id = s.s_w_id where ol.ol_w_id = 3 and ol.ol_d_id = 1 and ol.ol_o_id < 3088 and ol.ol_o_id >= 3068 and s.s_w_id = 3 and s.s_quantity < 17 limit 10001
As you can see, Vitess optimizer understood that it is enough to reach out to just one shard and execute a COUNT() query. Please notice LIMIT 10001 added by Vitess.
Another example. Let’s say that we want to see how Vitess executes following three queries:
SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80; SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80 AND w.w_id IN (1,2,3,4,5,6,7,8,9,10); SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80 AND w.w_id = 5;
After preparing required files (vschema, schema, query and shard map) we can run the vtexplain for all of the queries (you just have to put more than one query into the file):
root@k8smaster:~/vitesstests# ./vtexplain -ks-shard-map-file tpcc_vschema/shard_map.json -sql-file tpcc_vschema/query1.sql -vschema-file tpcc_vschema/vtexplain_schema.json -schema-file tpcc_vschema/query1_schemas.file ---------------------------------------------------------------------- SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80 1 newsbtest/-33: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 limit 10001 1 newsbtest/33-66: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 limit 10001 1 newsbtest/66-99: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 limit 10001 1 newsbtest/99-cc: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 limit 10001 1 newsbtest/cc-: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 limit 10001 2 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 3 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 4 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 5 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 6 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001
First, the worst case scenario – sharding key (warehouse ID) is not used in the query thus Vitess will have to access every single shard, run the query and aggregate the result. What we can notice is that queries executed against the sharded keyspace will be executed in parallel. The first column shows the order in which queries are executed and you can notice “1” in all queries directed to all shards.
---------------------------------------------------------------------- SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80 AND w.w_id IN (1,2,3,4,5,6,7,8,9,10) 1 newsbtest/-33: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id in (4, 8) limit 10001 1 newsbtest/33-66: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id in (2, 6, 10) limit 10001 1 newsbtest/66-99: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id in (1, 9) limit 10001 1 newsbtest/99-cc: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id in (3, 5) limit 10001 1 newsbtest/cc-: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id in (7) limit 10001 2 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 3 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 4 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 5 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001 6 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001
Second attempt, we passed warehouse ID in IN() list. This allowed Vitess to start optimization. Instead of running the same query across all shards we can see that it modified IN() list so that only values valid for a given shard are shown there. If the contents of the global IN() list does not contain all warehouse ID’s, this approach will speed up the data collection process. As you can see, queries to sharded keyspace are still parallelized.
---------------------------------------------------------------------- SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80 AND w.w_id = 5 1 newsbtest/99-cc: select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id = 5 limit 10001 2 items/-: select i.i_name, i.i_price from item1 as i where i.i_id = 6 limit 10001
Finally, the best case scenario. We have passed exact warehouse ID to the query and Vitess can make a good use of it by avoiding executing the query over all shards, it’s instead directed to a proper shard (newsbtest/99-cc), where the w_id of 5 is located, making the query fast and efficient.
What is interesting, Vitess comes also with another way of checking the execution plan. We can run EXPLAIN FORMAT=vitess:
mysql> EXPLAIN FORMAT=vitess SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80; +--------------+-----------------+-----------+-------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | operator | variant | keyspace | destination | tabletType | query | +--------------+-----------------+-----------+-------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Join | Join | | | UNKNOWN | | | ├─ Route | SelectScatter | newsbtest | | UNKNOWN | select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 | | └─ Route | SelectUnsharded | items | | UNKNOWN | select i.i_name, i.i_price from item1 as i where i.i_id = :s_s_i_id | +--------------+-----------------+-----------+-------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> EXPLAIN FORMAT=vitess SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80 AND w.w_id IN (1,2,3,4,5,6,7,8,9,10); +--------------+-----------------+-----------+-------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | operator | variant | keyspace | destination | tabletType | query | +--------------+-----------------+-----------+-------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Join | Join | | | UNKNOWN | | | ├─ Route | SelectIN | newsbtest | | UNKNOWN | select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id in ::__vals | | └─ Route | SelectUnsharded | items | | UNKNOWN | select i.i_name, i.i_price from item1 as i where i.i_id = :s_s_i_id | +--------------+-----------------+-----------+-------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> EXPLAIN FORMAT=vitess SELECT s.s_quantity, w.w_name, i.i_name, i.i_price, w.w_city, w.w_street_1, w.w_street_2 FROM warehouse1 w JOIN stock1 s ON w.w_id = s.s_w_id JOIN items.item1 i ON s.s_i_id = i.i_id WHERE s.s_quantity > 80 AND w.w_id=5; +--------------+-------------------+-----------+-------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | operator | variant | keyspace | destination | tabletType | query | +--------------+-------------------+-----------+-------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Join | Join | | | UNKNOWN | | | ├─ Route | SelectEqualUnique | newsbtest | | UNKNOWN | select s.s_quantity, w.w_name, w.w_city, w.w_street_1, w.w_street_2, s.s_i_id from warehouse1 as w join stock1 as s on w.w_id = s.s_w_id where s.s_quantity > 80 and w.w_id = 5 | | └─ Route | SelectUnsharded | items | | UNKNOWN | select i.i_name, i.i_price from item1 as i where i.i_id = :s_s_i_id | +--------------+-------------------+-----------+-------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
The output gives us some idea about the execution plan (especially ‘variant’ column), but data is presented overall in slightly different, less detailed format. We will take a look at this method in the future blog post, it is quite helpful in a way you can execute it straight away while connected to VTGate, there’s no need to prepare data, schemas and so on.
If you have any questions or if you would like to share your experience with vtexplain, we would love to hear from you in the comments below.
