The Coprocessor

Transcript

Welcome back. Now that you've had a chance to experiment with EXPLAIN, let's talk about the coprocessor in more detail. For performance reasons, TiDB pushes parts of the query execution down to TiKV for coprocessing. This saves on copying data around which tends to be inefficient. So to revisit our earlier query, we can say that the coprocessor supports the count(*) function:

EXPLAIN SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';

I am also going to drop the index start_date which was added earlier.

The coprocessor supports most of the functions that TiDB does, but for arguments’ sake let's use a function which is not supported, which is the RAND() function. So I have two queries:

EXPLAIN SELECT count(trip_id + RAND()) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';
EXPLAIN SELECT count(trip_id) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';

So in EXPLAIN we can see:

Without the coprocessor:

MySQL [bikeshare]> EXPLAIN SELECT count(trip_id + RAND()) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';
+------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------+
| id                     | count      | task | operator info                                                                                                          |
+------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_9            | 1.00       | root | funcs:count(plus(cast(bikeshare.trips.trip_id), rand()))                                                               |
| └─TableReader_19       | 6975.66    | root | data:Selection_18                                                                                                      |
|   └─Selection_18       | 6975.66    | cop  | ge(bikeshare.trips.start_date, 2017-07-01 00:00:00.000000), le(bikeshare.trips.start_date, 2017-07-01 23:59:59.000000) |
|     └─TableScan_17     | 3757777.00 | cop  | table:trips, range:[-inf,+inf], keep order:false                                                                       |
+------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

With the coprocessor:

MySQL [bikeshare]> EXPLAIN SELECT count(trip_id) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';
+--------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------+
| id                       | count      | task | operator info                                                                                                          |
+--------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_20             | 1.00       | root | funcs:count(col_0)                                                                                                     |
| └─TableReader_21         | 1.00       | root | data:StreamAgg_9                                                                                                       |
|   └─StreamAgg_9          | 1.00       | cop  | funcs:count(bikeshare.trips.trip_id)                                                                                   |
|     └─Selection_19       | 6975.66    | cop  | ge(bikeshare.trips.start_date, 2017-07-01 00:00:00.000000), le(bikeshare.trips.start_date, 2017-07-01 23:59:59.000000) |
|       └─TableScan_18     | 3757777.00 | cop  | table:trips, range:[-inf,+inf], keep order:false                                                                       |
+--------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

Between TiKV and TiDB the data is efficiently encoded in protocol buffers, and the communication layer is based on gRPC, which is also efficient… but in the no-coprocessor case, 8708 rows need to be copied back to TiDB for processing versus one with the coprocessor. On a reference system, this changes query execution time from 3.33 seconds to 3.28 seconds. It's not significant yet, largely because without the index most of the time is spent scanning the table in TiKV.

Let's now try a second example, which is the performance with the start_date column indexed. I would call this example more typical in that in a production environment you try to avoid tablescans on large amounts of data, and add suitable indexes:

mysql> ALTER TABLE trips ADD INDEX (start_date);

Without the coprocessor:

mysql> EXPLAIN SELECT count(trip_id + RAND()) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';
+----------------------+---------+------+--------------------------------------------------------------------------------------------------+
| id                   | count   | task | operator info                                                                                    |
+----------------------+---------+------+--------------------------------------------------------------------------------------------------+
| StreamAgg_9          | 1.00    | root | funcs:count(plus(cast(bikeshare.trips.trip_id), rand()))                                         |
| └─IndexReader_24     | 8708.34 | root | index:IndexScan_23                                                                               |
|   └─IndexScan_23     | 8708.34 | cop  | table:trips, index:start_date, range:[2017-07-01 00:00:00,2017-07-01 23:59:59], keep order:false |
+----------------------+---------+------+--------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

With the coprocessor:

mysql> EXPLAIN SELECT count(trip_id) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';
+------------------------+---------+------+--------------------------------------------------------------------------------------------------+
| id                     | count   | task | operator info                                                                                    |
+------------------------+---------+------+--------------------------------------------------------------------------------------------------+
| StreamAgg_25           | 1.00    | root | funcs:count(col_0)                                                                               |
| └─IndexReader_26       | 1.00    | root | index:StreamAgg_9                                                                                |
|   └─StreamAgg_9        | 1.00    | cop  | funcs:count(bikeshare.trips.trip_id)                                                             |
|     └─IndexScan_24     | 8708.34 | cop  | table:trips, index:start_date, range:[2017-07-01 00:00:00,2017-07-01 23:59:59], keep order:false |
+------------------------+---------+------+--------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Without: 0.04 sec With: 0.02 sec

Here we can see an even larger difference in time execution (by percentage). The same 8708 rows need to be copied to TiDB, but the table scan time has efficiently been removed from TiKV's processing.

Another way to look at this, is the efficiency of a distributed system such as the TiDB Platform benefits from the same principles as edge computing, which is to push key parts of the processing closer to the data. If you see a low count of rows for root tasks, as we can see in our coprocessor examples here, then congratulations! This query is working well.