CLOVER🍀

That was when it all began.

TiDB(TiKV)のデータのリージョンごとの配置状況を確認してみる

これは、なにをしたくて書いたもの?

TiDB(TiKV)のデータのリージョンごとの配置状況を確認する方法はあるのかな?ということで調べてみました。

調べる方法

いくつかありそうです。

1番簡単にわかりそうなのは、こちらかなと思います。指定したテーブルのリージョンに関する情報を表示してくれます。

SHOW TABLE REGIONS | PingCAP Docs

information_schemaのテーブルからも、近い情報が取得できそうです。

クラスター内のTiKVノードの情報。

TIKV_STORE_STATUS | PingCAP Docs

リージョンの役割、ピアに関する情報。

TIKV_REGION_PEERS | PingCAP Docs

リージョン内の開始キー、終了キーや統計情報。

TIKV_REGION_STATUS | PingCAP Docs

このあたりを組み合わせると、show table [テーブル名] regions相当の情報を得られそうです。

今回はこのあたりを使っていってみたいと思います。

また、今回は扱わないのですが、このあたりからもリージョンに関する情報は得られそうです。

TiDB Monitoring API / Use the status interface / PD server

Placement Driver API API documentation / GET /region/id/{id}

もっとも、モニタリングAPIについてはshow table [テーブル名] regionsやinformation_schemaの方が得られる情報が多そうですが。
PD APIから得られる情報だと、ほぼ同等ですね。

確認にはデータの登録が必要になるわけですが、それはこちらのドキュメントの内容で試してみようと思います。

Import Example Database | PingCAP Docs

また、自分でも少し追加を行ってみます。

環境

今回の環境はこちら。

$ tiup --version
1.15.2 v1.15.0-nightly-20
Go Version: go1.21.11
Git Ref: master
GitHash: 423dd0d00e17aaea6b907cdcedaf63dd2411a2a8

使用するMySQLクライアント。

$ mysql --version
mysql  Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)

TiDBのバージョン。

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v8.1.0 |
+--------------------+
1 row in set (0.01 sec)

Pythonも使います。

$ python3 --version
Python 3.10.12

TiDBは以下のコマンドで起動しているものとします。

$ tiup playground v8.1.0 --host 0.0.0.0

また、TiDBへの接続は以下のコマンドで行うこととします。

$ mysql -uroot -h127.0.0.1 -P4000 --local-infile=1

データを登録する

まずはこちらのページに沿って、サンプルデータを登録します。

Import Example Database | PingCAP Docs

データのダウンロードと展開。

$ mkdir -p bikeshare-data && cd bikeshare-data
$ curl -L --remote-name-all https://s3.amazonaws.com/capitalbikeshare-data/{2010..2017}-capitalbikeshare-tripdata.zip
$ unzip \*-tripdata.zip
$ cd ..

展開後はこんな感じです。

$ ll -h bikeshare-data
合計 2.9G
drwxr-xr-x 2 xxxxx xxxxx 4.0K  6月 19 13:27 ./
drwxr-x--- 1 xxxxx xxxxx 4.0K  6月 19 13:17 ../
-rw-r--r-- 1 xxxxx xxxxx  16M  3月 15  2018 2010-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 2.3M  6月 19 13:18 2010-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx 164M  3月 15  2018 2011-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  25M  6月 19 13:18 2011-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx  42M  6月 19 13:18 2012-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx  49M  3月 15  2018 2012Q1-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  77M  3月 15  2018 2012Q2-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  85M  3月 15  2018 2012Q3-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  63M  3月 15  2018 2012Q4-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  54M  6月 19 13:20 2013-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx  53M  3月 15  2018 2013Q1-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  99M  3月 15  2018 2013Q2-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 112M  3月 15  2018 2013Q3-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  78M  3月 15  2018 2013Q4-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  64M  6月 19 13:21 2014-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx  54M  3月 15  2018 2014Q1-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 120M  3月 15  2018 2014Q2-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 130M  3月 15  2018 2014Q3-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  86M  3月 15  2018 2014Q4-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  71M  6月 19 13:22 2015-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx  56M  3月 15  2018 2015Q1-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 133M  3月 15  2018 2015Q2-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 140M  3月 15  2018 2015Q3-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  94M  3月 15  2018 2015Q4-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  75M  6月 19 13:23 2016-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx  73M  3月 15  2018 2016Q1-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 125M  3月 15  2018 2016Q2-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 142M  3月 15  2018 2016Q3-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 102M  3月 15  2018 2016Q4-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx  86M  6月 19 13:25 2017-capitalbikeshare-tripdata.zip
-rw-r--r-- 1 xxxxx xxxxx  86M  3月 15  2018 2017Q1-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 147M  3月 15  2018 2017Q2-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 158M  3月 15  2018 2017Q3-capitalbikeshare-tripdata.csv
-rw-r--r-- 1 xxxxx xxxxx 108M  3月 15  2018 2017Q4-capitalbikeshare-tripdata.csv

TiDBに接続。

$ mysql -uroot -h127.0.0.1 -P4000 --local-infile=1

取り込み先のデータベースを作成します。

mysql> create database bikeshare;
Query OK, 0 rows affected (0.45 sec)

mysql> use bikeshare;
Database changed

テーブルも作成。

create table trips (
 trip_id bigint not null primary key auto_increment,
 duration integer not null,
 start_date datetime,
 end_date datetime,
 start_station_number integer,
 start_station varchar(255),
 end_station_number integer,
 end_station varchar(255),
 bike_number varchar(255),
 member_type varchar(255)
);

bikeshareデータベースのtripsテーブルです。

この時点でshow table [テーブル名] regionsを実行してみます。

mysql> show table trips regions\G
*************************** 1. row ***************************
             REGION_ID: 116
             START_KEY: t_106_
               END_KEY: t_281474976710649_
             LEADER_ID: 117
       LEADER_STORE_ID: 1
                 PEERS: 117
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 1
      APPROXIMATE_KEYS: 0
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
1 row in set (0.00 sec)

データに関する情報はありませんね。

tikv_store_statusテーブル。TiKVとTiFlashが1ノードずつあります。

mysql> select * from information_schema.tikv_store_status order by store_id asc;
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+----------------+
| STORE_ID | ADDRESS          | STORE_STATE | STORE_STATE_NAME | LABEL                                   | VERSION | CAPACITY | AVAILABLE | LEADER_COUNT | LEADER_WEIGHT | LEADER_SCORE | LEADER_SIZE | REGION_COUNT | REGION_WEIGHT | REGION_SCORE      | REGION_SIZE | START_TS            | LAST_HEARTBEAT_TS   | UPTIME         |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+----------------+
|        1 | 172.17.0.2:20160 |           0 | Up               | null                                    | 8.1.0   | 268.6GiB | 22.39GiB  |           61 |             1 |           61 |          61 |           61 |             1 | 5808675814.458395 |          61 | 2024-06-19 14:18:20 | 2024-06-19 14:20:20 | 2m0.585604849s |
|      224 | 172.17.0.2:3930  |           0 | Up               | [{"key": "engine", "value": "tiflash"}] | v8.1.0  | 268.6GiB | 22.39GiB  |            0 |             1 |            0 |           0 |            0 |             1 | 5831418973.566378 |           0 | 2024-06-19 14:19:19 | 2024-06-19 14:20:20 | 1m1.579874352s |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+----------------+
2 rows in set (0.00 sec)

tikv_region_peersテーブル。リージョンの一覧です。

mysql> select * from information_schema.tikv_region_peers order by region_id asc, store_id asc;
+-----------+---------+----------+------------+-----------+--------+--------------+
| REGION_ID | PEER_ID | STORE_ID | IS_LEARNER | IS_LEADER | STATUS | DOWN_SECONDS |
+-----------+---------+----------+------------+-----------+--------+--------------+
|         2 |       3 |        1 |          0 |         1 | NORMAL |         NULL |
|         4 |       5 |        1 |          0 |         1 | NORMAL |         NULL |
|        46 |      47 |        1 |          0 |         1 | NORMAL |         NULL |
|       102 |     103 |        1 |          0 |         1 | NORMAL |         NULL |
|       104 |     105 |        1 |          0 |         1 | NORMAL |         NULL |
|       106 |     107 |        1 |          0 |         1 | NORMAL |         NULL |
|       116 |     117 |        1 |          0 |         1 | NORMAL |         NULL |
|       118 |     121 |        1 |          0 |         1 | NORMAL |         NULL |
|       119 |     120 |        1 |          0 |         1 | NORMAL |         NULL |
|       122 |     123 |        1 |          0 |         1 | NORMAL |         NULL |
|       124 |     125 |        1 |          0 |         1 | NORMAL |         NULL |
|       126 |     127 |        1 |          0 |         1 | NORMAL |         NULL |
|       128 |     129 |        1 |          0 |         1 | NORMAL |         NULL |
|       130 |     131 |        1 |          0 |         1 | NORMAL |         NULL |
|       132 |     133 |        1 |          0 |         1 | NORMAL |         NULL |
|       134 |     135 |        1 |          0 |         1 | NORMAL |         NULL |
|       136 |     137 |        1 |          0 |         1 | NORMAL |         NULL |
|       138 |     139 |        1 |          0 |         1 | NORMAL |         NULL |
|       140 |     141 |        1 |          0 |         1 | NORMAL |         NULL |
|       142 |     143 |        1 |          0 |         1 | NORMAL |         NULL |
|       144 |     145 |        1 |          0 |         1 | NORMAL |         NULL |
|       146 |     147 |        1 |          0 |         1 | NORMAL |         NULL |
|       148 |     149 |        1 |          0 |         1 | NORMAL |         NULL |
|       150 |     151 |        1 |          0 |         1 | NORMAL |         NULL |
|       152 |     153 |        1 |          0 |         1 | NORMAL |         NULL |
|       154 |     155 |        1 |          0 |         1 | NORMAL |         NULL |
|       156 |     157 |        1 |          0 |         1 | NORMAL |         NULL |
|       158 |     159 |        1 |          0 |         1 | NORMAL |         NULL |
|       160 |     161 |        1 |          0 |         1 | NORMAL |         NULL |
|       162 |     163 |        1 |          0 |         1 | NORMAL |         NULL |
|       164 |     165 |        1 |          0 |         1 | NORMAL |         NULL |
|       166 |     167 |        1 |          0 |         1 | NORMAL |         NULL |
|       168 |     169 |        1 |          0 |         1 | NORMAL |         NULL |
|       170 |     171 |        1 |          0 |         1 | NORMAL |         NULL |
|       172 |     173 |        1 |          0 |         1 | NORMAL |         NULL |
|       174 |     175 |        1 |          0 |         1 | NORMAL |         NULL |
|       176 |     177 |        1 |          0 |         1 | NORMAL |         NULL |
|       178 |     179 |        1 |          0 |         1 | NORMAL |         NULL |
|       180 |     181 |        1 |          0 |         1 | NORMAL |         NULL |
|       182 |     183 |        1 |          0 |         1 | NORMAL |         NULL |
|       184 |     185 |        1 |          0 |         1 | NORMAL |         NULL |
|       186 |     187 |        1 |          0 |         1 | NORMAL |         NULL |
|       188 |     189 |        1 |          0 |         1 | NORMAL |         NULL |
|       190 |     191 |        1 |          0 |         1 | NORMAL |         NULL |
|       192 |     193 |        1 |          0 |         1 | NORMAL |         NULL |
|       194 |     195 |        1 |          0 |         1 | NORMAL |         NULL |
|       196 |     197 |        1 |          0 |         1 | NORMAL |         NULL |
|       198 |     199 |        1 |          0 |         1 | NORMAL |         NULL |
|       200 |     201 |        1 |          0 |         1 | NORMAL |         NULL |
|       202 |     203 |        1 |          0 |         1 | NORMAL |         NULL |
|       204 |     205 |        1 |          0 |         1 | NORMAL |         NULL |
|       206 |     207 |        1 |          0 |         1 | NORMAL |         NULL |
|       208 |     209 |        1 |          0 |         1 | NORMAL |         NULL |
|       210 |     211 |        1 |          0 |         1 | NORMAL |         NULL |
|       212 |     213 |        1 |          0 |         1 | NORMAL |         NULL |
|       214 |     215 |        1 |          0 |         1 | NORMAL |         NULL |
|       216 |     217 |        1 |          0 |         1 | NORMAL |         NULL |
|       218 |     219 |        1 |          0 |         1 | NORMAL |         NULL |
|       220 |     221 |        1 |          0 |         1 | NORMAL |         NULL |
|       222 |     223 |        1 |          0 |         1 | NORMAL |         NULL |
|       225 |     226 |        1 |          0 |         1 | NORMAL |         NULL |
+-----------+---------+----------+------------+-----------+--------+--------------+
61 rows in set (0.00 sec)

tikv_region_status。データベースとテーブルを指定して出力しています。得られる情報はshow table [テーブル名] regionsに
近いです。

mysql> select * from information_schema.tikv_region_status where db_name = 'bikeshare' and table_name = 'trips' order by region_id asc\G
*************************** 1. row ***************************
                REGION_ID: 116
                START_KEY: 7480000000000000FF6A00000000000000F8
                  END_KEY: 748000FFFFFFFFFFFFF900000000000000F8
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 59
            WRITTEN_BYTES: 0
               READ_BYTES: 0
         APPROXIMATE_SIZE: 1
         APPROXIMATE_KEYS: 0
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
1 row in set (0.02 sec)

では、データを登録します。

load data local infile 'bikeshare-data/2017Q1-capitalbikeshare-tripdata.csv' into table trips
  fields terminated by ',' enclosed by '"'
  lines terminated by '\r\n'
  ignore 1 lines
(duration, start_date, end_date, start_station_number, start_station,
end_station_number, end_station, bike_number, member_type);

ちなみに、この時にmysqlコマンドにつけていた--local-infile=1がポイントになっていて

$ mysql -uroot -h127.0.0.1 -P4000 --local-infile=1

もしも付けていない場合は以下のようなエラーを見ることになります。

mysql> load data local infile 'bikeshare-data/2017Q1-capitalbikeshare-tripdata.csv' into table trips   fields terminated by ',' enclosed by '"'   lines terminated by '\r\n'   ignore 1 lines (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

話を戻しまして。

データのロードが終了すると、64万件以上のレコードが登録されたことになります。

Query OK, 646510 rows affected (38.84 sec)
Records: 646510  Deleted: 0  Skipped: 0  Warnings: 0

少しデータを確認してみます。

mysql> select count(*) from trips;
+----------+
| count(*) |
+----------+
|   646510 |
+----------+
1 row in set (1.15 sec)


mysql> select * from trips order by trip_id limit 10;
+---------+----------+---------------------+---------------------+----------------------+---------------------------------------------+--------------------+----------------------------+-------------+-------------+
| trip_id | duration | start_date          | end_date            | start_station_number | start_station                               | end_station_number | end_station                | bike_number | member_type |
+---------+----------+---------------------+---------------------+----------------------+---------------------------------------------+--------------------+----------------------------+-------------+-------------+
|       1 |      221 | 2017-01-01 00:00:41 | 2017-01-01 00:04:23 |                31634 | 3rd & Tingey St SE                          |              31208 | M St & New Jersey Ave SE   | W00869      | Member      |
|       2 |     1676 | 2017-01-01 00:06:53 | 2017-01-01 00:34:49 |                31258 | Lincoln Memorial                            |              31270 | 8th & D St NW              | W00894      | Casual      |
|       3 |     1356 | 2017-01-01 00:07:10 | 2017-01-01 00:29:47 |                31289 | Henry Bacon Dr & Lincoln Memorial Circle NW |              31222 | New York Ave & 15th St NW  | W21945      | Casual      |
|       4 |     1327 | 2017-01-01 00:07:22 | 2017-01-01 00:29:30 |                31289 | Henry Bacon Dr & Lincoln Memorial Circle NW |              31222 | New York Ave & 15th St NW  | W20012      | Casual      |
|       5 |     1636 | 2017-01-01 00:07:36 | 2017-01-01 00:34:52 |                31258 | Lincoln Memorial                            |              31270 | 8th & D St NW              | W22786      | Casual      |
|       6 |     1603 | 2017-01-01 00:08:11 | 2017-01-01 00:34:55 |                31258 | Lincoln Memorial                            |              31270 | 8th & D St NW              | W20890      | Casual      |
|       7 |      473 | 2017-01-01 00:08:36 | 2017-01-01 00:16:29 |                31611 | 13th & H St NE                              |              31616 | 3rd & H St NE              | W20340      | Member      |
|       8 |      200 | 2017-01-01 00:11:07 | 2017-01-01 00:14:27 |                31104 | Adams Mill & Columbia Rd NW                 |              31121 | Calvert St & Woodley Pl NW | W20398      | Member      |
|       9 |      748 | 2017-01-01 00:13:20 | 2017-01-01 00:25:49 |                31041 | Prince St & Union St                        |              31097 | Saint Asaph & Madison St   | W00365      | Member      |
|      10 |      912 | 2017-01-01 00:14:35 | 2017-01-01 00:29:48 |                31202 | 14th & R St NW                              |              31505 | Eckington Pl & Q St NE     | W20771      | Member      |
+---------+----------+---------------------+---------------------+----------------------+---------------------------------------------+--------------------+----------------------------+-------------+-------------+
10 rows in set (0.00 sec)

OKそうですね。

データのリージョンごとの配置状況を確認してみる

では、先ほど確認した内容をデータが入った状態で確認してみます。

show table [テーブル名] regions。

mysql> show table trips regions\G
*************************** 1. row ***************************
             REGION_ID: 227
             START_KEY: t_106_
               END_KEY: t_106_r_207740
             LEADER_ID: 228
       LEADER_STORE_ID: 1
                 PEERS: 228
            SCATTERING: 0
         WRITTEN_BYTES: 82720751
            READ_BYTES: 33272581
  APPROXIMATE_SIZE(MB): 120
      APPROXIMATE_KEYS: 472174
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 2. row ***************************
             REGION_ID: 229
             START_KEY: t_106_r_207740
               END_KEY: t_106_r_460085
             LEADER_ID: 230
       LEADER_STORE_ID: 1
                 PEERS: 230
            SCATTERING: 0
         WRITTEN_BYTES: 100898150
            READ_BYTES: 40621627
  APPROXIMATE_SIZE(MB): 109
      APPROXIMATE_KEYS: 299100
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 3. row ***************************
             REGION_ID: 231
             START_KEY: t_106_r_460085
               END_KEY: t_106_r_517037
             LEADER_ID: 232
       LEADER_STORE_ID: 1
                 PEERS: 232
            SCATTERING: 0
         WRITTEN_BYTES: 22720272
            READ_BYTES: 9142415
  APPROXIMATE_SIZE(MB): 17
      APPROXIMATE_KEYS: 51866
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 4. row ***************************
             REGION_ID: 116
             START_KEY: t_106_r_517037
               END_KEY: t_281474976710649_
             LEADER_ID: 117
       LEADER_STORE_ID: 1
                 PEERS: 117
            SCATTERING: 0
         WRITTEN_BYTES: 51940893
            READ_BYTES: 13234228
  APPROXIMATE_SIZE(MB): 29
      APPROXIMATE_KEYS: 0
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
4 rows in set (0.00 sec)

こちらを例に見てみましょう。

*************************** 2. row ***************************
             REGION_ID: 229
             START_KEY: t_106_r_207740
               END_KEY: t_106_r_460085
             LEADER_ID: 230
       LEADER_STORE_ID: 1
                 PEERS: 230
            SCATTERING: 0
         WRITTEN_BYTES: 100898150
            READ_BYTES: 40621627
  APPROXIMATE_SIZE(MB): 109
      APPROXIMATE_KEYS: 299100
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:

ドキュメントから、ポイントなりそうな情報を見てみます。

REGION_IDはリージョンのID、START_KEY、END_KEYはそれぞれリージョンの開始キー、終了キーです。
LEADER_IDはリーダーのIDで、LEADER_STORE_IDはリーダーが配置されているストアのIDです。
APPROXIMATE_SIZEはリージョン内の推定データ量(MB)、APPROXIMATE_KEYSはリージョン内の推定キー数です。

SHOW TABLE REGIONS | PingCAP Docs

START_KEY、END_KEYは正確に一致しないこともあるそうです。
このt_106_r_207740の命名規則はtがテーブル、106はテーブルの内部ID、207740は主キーのデータだそうです。

START_KEY、END_KEYのデータが存在するか確認してみましょう。

mysql> select * from trips where trip_id = 207740;
+---------+----------+---------------------+---------------------+----------------------+----------------+--------------------+----------------------------------------+-------------+-------------+
| trip_id | duration | start_date          | end_date            | start_station_number | start_station  | end_station_number | end_station                            | bike_number | member_type |
+---------+----------+---------------------+---------------------+----------------------+----------------+--------------------+----------------------------------------+-------------+-------------+
|  207740 |      810 | 2017-02-06 06:39:45 | 2017-02-06 06:53:16 |                31255 | 24th & N St NW |              31236 | 37th & O St NW / Georgetown University | W20788      | Member      |
+---------+----------+---------------------+---------------------+----------------------+----------------+--------------------+----------------------------------------+-------------+-------------+
1 row in set (0.01 sec)


mysql> select * from trips where trip_id = 460085;
+---------+----------+---------------------+---------------------+----------------------+----------------+--------------------+----------------------------------------------+-------------+-------------+
| trip_id | duration | start_date          | end_date            | start_station_number | start_station  | end_station_number | end_station                                  | bike_number | member_type |
+---------+----------+---------------------+---------------------+----------------------+----------------+--------------------+----------------------------------------------+-------------+-------------+
|  460085 |      919 | 2017-03-08 17:48:21 | 2017-03-08 18:03:40 |                31285 | 22nd & P ST NW |              31220 | US Dept of State / Virginia Ave & 21st St NW | W22587      | Member      |
+---------+----------+---------------------+---------------------+----------------------+----------------+--------------------+----------------------------------------------+-------------+-------------+
1 row in set (0.00 sec)

ありましたね。

ところで、推定情報とはいえ表示されているリージョンのAPPROXIMATE_KEYSを合計すると、登録したデータ数を超えるのですが…。

tikv_store_statusテーブル。ノードの数は変わりませんが、中身の数値は変化していますね。

mysql> select * from information_schema.tikv_store_status order by store_id asc;
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+-----------------+
| STORE_ID | ADDRESS          | STORE_STATE | STORE_STATE_NAME | LABEL                                   | VERSION | CAPACITY | AVAILABLE | LEADER_COUNT | LEADER_WEIGHT | LEADER_SCORE | LEADER_SIZE | REGION_COUNT | REGION_WEIGHT | REGION_SCORE      | REGION_SIZE | START_TS            | LAST_HEARTBEAT_TS   | UPTIME          |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+-----------------+
|        1 | 172.17.0.2:20160 |           0 | Up               | null                                    | 8.1.0   | 268.6GiB | 21.97GiB  |           64 |             1 |           64 |         357 |           64 |             1 | 5894405428.323038 |         357 | 2024-06-19 14:18:20 | 2024-06-19 14:23:50 | 5m30.66764285s  |
|      224 | 172.17.0.2:3930  |           0 | Up               | [{"key": "engine", "value": "tiflash"}] | v8.1.0  | 268.6GiB | 21.97GiB  |            0 |             1 |            0 |           0 |            0 |             1 | 5888187014.039975 |           0 | 2024-06-19 14:19:19 | 2024-06-19 14:23:50 | 4m31.666863337s |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+-----------------+
2 rows in set (0.01 sec)

TIKV_STORE_STATUS | PingCAP Docs

tikv_region_peersテーブル。リージョンが3つ増えています。

mysql> select * from information_schema.tikv_region_peers order by region_id asc, store_id asc;
+-----------+---------+----------+------------+-----------+--------+--------------+
| REGION_ID | PEER_ID | STORE_ID | IS_LEARNER | IS_LEADER | STATUS | DOWN_SECONDS |
+-----------+---------+----------+------------+-----------+--------+--------------+
|         2 |       3 |        1 |          0 |         1 | NORMAL |         NULL |
|         4 |       5 |        1 |          0 |         1 | NORMAL |         NULL |
|        46 |      47 |        1 |          0 |         1 | NORMAL |         NULL |
|       102 |     103 |        1 |          0 |         1 | NORMAL |         NULL |
|       104 |     105 |        1 |          0 |         1 | NORMAL |         NULL |
|       106 |     107 |        1 |          0 |         1 | NORMAL |         NULL |
|       116 |     117 |        1 |          0 |         1 | NORMAL |         NULL |
|       118 |     121 |        1 |          0 |         1 | NORMAL |         NULL |
|       119 |     120 |        1 |          0 |         1 | NORMAL |         NULL |
|       122 |     123 |        1 |          0 |         1 | NORMAL |         NULL |
|       124 |     125 |        1 |          0 |         1 | NORMAL |         NULL |
|       126 |     127 |        1 |          0 |         1 | NORMAL |         NULL |
|       128 |     129 |        1 |          0 |         1 | NORMAL |         NULL |
|       130 |     131 |        1 |          0 |         1 | NORMAL |         NULL |
|       132 |     133 |        1 |          0 |         1 | NORMAL |         NULL |
|       134 |     135 |        1 |          0 |         1 | NORMAL |         NULL |
|       136 |     137 |        1 |          0 |         1 | NORMAL |         NULL |
|       138 |     139 |        1 |          0 |         1 | NORMAL |         NULL |
|       140 |     141 |        1 |          0 |         1 | NORMAL |         NULL |
|       142 |     143 |        1 |          0 |         1 | NORMAL |         NULL |
|       144 |     145 |        1 |          0 |         1 | NORMAL |         NULL |
|       146 |     147 |        1 |          0 |         1 | NORMAL |         NULL |
|       148 |     149 |        1 |          0 |         1 | NORMAL |         NULL |
|       150 |     151 |        1 |          0 |         1 | NORMAL |         NULL |
|       152 |     153 |        1 |          0 |         1 | NORMAL |         NULL |
|       154 |     155 |        1 |          0 |         1 | NORMAL |         NULL |
|       156 |     157 |        1 |          0 |         1 | NORMAL |         NULL |
|       158 |     159 |        1 |          0 |         1 | NORMAL |         NULL |
|       160 |     161 |        1 |          0 |         1 | NORMAL |         NULL |
|       162 |     163 |        1 |          0 |         1 | NORMAL |         NULL |
|       164 |     165 |        1 |          0 |         1 | NORMAL |         NULL |
|       166 |     167 |        1 |          0 |         1 | NORMAL |         NULL |
|       168 |     169 |        1 |          0 |         1 | NORMAL |         NULL |
|       170 |     171 |        1 |          0 |         1 | NORMAL |         NULL |
|       172 |     173 |        1 |          0 |         1 | NORMAL |         NULL |
|       174 |     175 |        1 |          0 |         1 | NORMAL |         NULL |
|       176 |     177 |        1 |          0 |         1 | NORMAL |         NULL |
|       178 |     179 |        1 |          0 |         1 | NORMAL |         NULL |
|       180 |     181 |        1 |          0 |         1 | NORMAL |         NULL |
|       182 |     183 |        1 |          0 |         1 | NORMAL |         NULL |
|       184 |     185 |        1 |          0 |         1 | NORMAL |         NULL |
|       186 |     187 |        1 |          0 |         1 | NORMAL |         NULL |
|       188 |     189 |        1 |          0 |         1 | NORMAL |         NULL |
|       190 |     191 |        1 |          0 |         1 | NORMAL |         NULL |
|       192 |     193 |        1 |          0 |         1 | NORMAL |         NULL |
|       194 |     195 |        1 |          0 |         1 | NORMAL |         NULL |
|       196 |     197 |        1 |          0 |         1 | NORMAL |         NULL |
|       198 |     199 |        1 |          0 |         1 | NORMAL |         NULL |
|       200 |     201 |        1 |          0 |         1 | NORMAL |         NULL |
|       202 |     203 |        1 |          0 |         1 | NORMAL |         NULL |
|       204 |     205 |        1 |          0 |         1 | NORMAL |         NULL |
|       206 |     207 |        1 |          0 |         1 | NORMAL |         NULL |
|       208 |     209 |        1 |          0 |         1 | NORMAL |         NULL |
|       210 |     211 |        1 |          0 |         1 | NORMAL |         NULL |
|       212 |     213 |        1 |          0 |         1 | NORMAL |         NULL |
|       214 |     215 |        1 |          0 |         1 | NORMAL |         NULL |
|       216 |     217 |        1 |          0 |         1 | NORMAL |         NULL |
|       218 |     219 |        1 |          0 |         1 | NORMAL |         NULL |
|       220 |     221 |        1 |          0 |         1 | NORMAL |         NULL |
|       222 |     223 |        1 |          0 |         1 | NORMAL |         NULL |
|       225 |     226 |        1 |          0 |         1 | NORMAL |         NULL |
|       227 |     228 |        1 |          0 |         1 | NORMAL |         NULL |
|       229 |     230 |        1 |          0 |         1 | NORMAL |         NULL |
|       231 |     232 |        1 |          0 |         1 | NORMAL |         NULL |
+-----------+---------+----------+------------+-----------+--------+--------------+
64 rows in set (0.00 sec)

TIKV_REGION_PEERS | PingCAP Docs

tikv_region_statusテーブル。show table [テーブル名] regions相当の情報が得られます。

mysql> select * from information_schema.tikv_region_status where db_name = 'bikeshare' and table_name = 'trips' order by region_id asc\G
*************************** 1. row ***************************
                REGION_ID: 116
                START_KEY: 7480000000000000FF6A5F728000000000FF07E3AD0000000000FA
                  END_KEY: 748000FFFFFFFFFFFFF900000000000000F8
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 62
            WRITTEN_BYTES: 42
               READ_BYTES: 0
         APPROXIMATE_SIZE: 51
         APPROXIMATE_KEYS: 145610
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 2. row ***************************
                REGION_ID: 227
                START_KEY: 7480000000000000FF6A00000000000000F8
                  END_KEY: 7480000000000000FF6A5F728000000000FF032B7C0000000000FA
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 60
            WRITTEN_BYTES: 0
               READ_BYTES: 0
         APPROXIMATE_SIZE: 120
         APPROXIMATE_KEYS: 472174
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 3. row ***************************
                REGION_ID: 229
                START_KEY: 7480000000000000FF6A5F728000000000FF032B7C0000000000FA
                  END_KEY: 7480000000000000FF6A5F728000000000FF0705350000000000FA
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 61
            WRITTEN_BYTES: 0
               READ_BYTES: 0
         APPROXIMATE_SIZE: 109
         APPROXIMATE_KEYS: 299100
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 4. row ***************************
                REGION_ID: 231
                START_KEY: 7480000000000000FF6A5F728000000000FF0705350000000000FA
                  END_KEY: 7480000000000000FF6A5F728000000000FF07E3AD0000000000FA
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 62
            WRITTEN_BYTES: 22720272
               READ_BYTES: 9142415
         APPROXIMATE_SIZE: 17
         APPROXIMATE_KEYS: 51866
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
4 rows in set (0.01 sec)

TIKV_REGION_STATUS | PingCAP Docs

ただ、START_KEYとEND_KEYの値はちょっと読めなくなっています。

この3つのテーブルをjoinすると、もう少しまとまった情報が得られそうですね。

select
  tikv.store_id,
  tikv.address,
  tikv.store_state_name,
  region.db_name,
  region.table_name,
  region.region_id,
  peer.peer_id,
  peer.is_leader,
  region.approximate_size,
  region.approximate_keys,
  peer.status
from
  information_schema.tikv_store_status tikv
inner join
  information_schema.tikv_region_peers peer
on
  tikv.store_id = peer.store_id
inner join
  information_schema.tikv_region_status region
on
  peer.region_id = region.region_id
where
  region.db_name = 'bikeshare' and region.table_name = 'trips'
order by
  tikv.store_id asc,
  region.region_id asc;

このクエリーではこんな感じになりました。

+----------+------------------+------------------+-----------+------------+-----------+---------+-----------+------------------+------------------+--------+
| store_id | address          | store_state_name | db_name   | table_name | region_id | peer_id | is_leader | approximate_size | approximate_keys | status |
+----------+------------------+------------------+-----------+------------+-----------+---------+-----------+------------------+------------------+--------+
|        1 | 172.17.0.2:20160 | Up               | bikeshare | trips      |        26 |      27 |         1 |               51 |           152254 | NORMAL |
|        1 | 172.17.0.2:20160 | Up               | bikeshare | trips      |      2003 |    2004 |         1 |              107 |           405776 | NORMAL |
|        1 | 172.17.0.2:20160 | Up               | bikeshare | trips      |      2005 |    2006 |         1 |               40 |           259002 | NORMAL |
+----------+------------------+------------------+-----------+------------+-----------+---------+-----------+------------------+------------------+--------+
3 rows in set (0.01 sec)

START_KEYとEND_KEYにリージョン内に含まれるキーの範囲の値が見えているので、複合主キーの場合はどうなるのか
試してみたいと思います。

testデータベースを使用。

use test;

2つの主キーのみのテーブルを作成。

create table ids(
  id1 varchar(36),
  id2 varchar(36),
  primary key(id1, id2)
);

show table [テーブル名] regionsのみ確認。

mysql> show table ids regions\G
*************************** 1. row ***************************
             REGION_ID: 116
             START_KEY: t_108_
               END_KEY: t_281474976710649_
             LEADER_ID: 117
       LEADER_STORE_ID: 1
                 PEERS: 117
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 51
      APPROXIMATE_KEYS: 145610
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
1 row in set (0.01 sec)

このテーブルにデータを登録します。

データ作成は、Pythonで行うことにします。今回はキーをそれぞれUUIDにして、50万件作成。

gen.py

import uuid

with open("uuid.csv", "w") as f:
    for i in range(0, 500000):
      print(f"{str(uuid.uuid4()) + ',' + str(uuid.uuid4())}", file=f)

作成。

$ python3 gen.py

50万件できました。

$ wc -l uuid.csv
500000 uuid.csv


$ head uuid.csv
9da9b232-474b-4ecb-bf5c-31364e2636d1,a94a4187-7788-4e5e-84a4-4c8e2d5e26aa
342a5356-cc60-4e41-b8a5-eaaaa69e7f58,81470edb-5f8b-43a9-95ff-cd6b35f0d3db
90c0fce0-5fca-44be-9c87-6ddeace28456,c4d6e527-9a06-44b6-910f-29253a9bc201
9a3c4c66-f2fb-4c99-9b37-abf188627d29,318899ae-a5d1-459d-8d40-5a056a492ab7
176a070b-f416-4fc2-8adc-c59aa1e0c8b3,759049f4-8427-4435-9421-d35a3bd378ee
c6e5cfba-828f-4f6b-b232-488ca96eff9a,ca3c6efd-0164-4323-99c3-c99e4ceaf9cd
53462544-9c41-46bb-ba5d-62a68eee4751,a64720c8-4022-4997-a9b3-1208d3d67751
61292e8e-5f3a-4a55-b9ce-cd50e9173793,6e33376d-f412-4790-a9d8-3ee14e5d1086
bbd4a844-78df-4580-85d2-774c15f91928,f8480de8-7e25-422b-a1ce-2cbe3ea48e72
2a95e188-2825-408e-aa0e-01e50a6b36c7,7a642383-9c18-4a84-85b5-0fde11407a26

このデータを登録します。

load data local infile 'uuid.csv' into table ids
  fields terminated by ','
  lines terminated by '\n'
(id1, id2);

50万件入りました。

Query OK, 500000 rows affected (23.98 sec)
Records: 500000  Deleted: 0  Skipped: 0  Warnings: 0

show table [テーブル名] regionsで見てみます。

mysql> show table ids regions\G
*************************** 1. row ***************************
             REGION_ID: 237
             START_KEY: t_108_
               END_KEY: t_108_r_013562636162616465ff2d353561662d3437ff65642d626136372dff3366633465393632ff6364343000000000fb013436656564663562ff2d363334652d3431ff30392d613965352dff3035373530303938ff6231653900000000fb
             LEADER_ID: 238
       LEADER_STORE_ID: 1
                 PEERS: 238
            SCATTERING: 0
         WRITTEN_BYTES: 124695959
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 132
      APPROXIMATE_KEYS: 288612
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 2. row ***************************
             REGION_ID: 235
             START_KEY: t_108_r_013562636162616465ff2d353561662d3437ff65642d626136372dff3366633465393632ff6364343000000000fb013436656564663562ff2d363334652d3431ff30392d613965352dff3035373530303938ff6231653900000000fb
               END_KEY: t_108_r_013830353566633762ff2d663432352d3432ff36382d386535662dff3166366430313762ff3230356600000000fb013431326533383662ff2d633336322d3435ff63322d396431612dff6133643730316266ff3731363800000000fb
             LEADER_ID: 236
       LEADER_STORE_ID: 1
                 PEERS: 236
            SCATTERING: 0
         WRITTEN_BYTES: 49478753
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 45
      APPROXIMATE_KEYS: 100518
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 3. row ***************************
             REGION_ID: 239
             START_KEY: t_108_r_013830353566633762ff2d663432352d3432ff36382d386535662dff3166366430313762ff3230356600000000fb013431326533383662ff2d633336322d3435ff63322d396431612dff6133643730316266ff3731363800000000fb
               END_KEY: t_108_r_016463346163626434ff2d646261642d3434ff36652d393736302dff6637373164323735ff6236643500000000fb013162643333646139ff2d623430382d3436ff33662d396339662dff3363323164386638ff6336653100000000fb
             LEADER_ID: 240
       LEADER_STORE_ID: 1
                 PEERS: 240
            SCATTERING: 0
         WRITTEN_BYTES: 124695304
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 109
      APPROXIMATE_KEYS: 166866
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 4. row ***************************
             REGION_ID: 116
             START_KEY: t_108_r_016463346163626434ff2d646261642d3434ff36652d393736302dff6637373164323735ff6236643500000000fb013162643333646139ff2d623430382d3436ff33662d396339662dff3363323164386638ff6336653100000000fb
               END_KEY: t_281474976710649_
             LEADER_ID: 117
       LEADER_STORE_ID: 1
                 PEERS: 117
            SCATTERING: 0
         WRITTEN_BYTES: 48598232
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 30
      APPROXIMATE_KEYS: 28496
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
4 rows in set (0.01 sec)

START_KEYとEND_KEYがどうなるか気になるところだったのですが、見えるには見えますがすごい表示になりましたね…。

*************************** 2. row ***************************
             REGION_ID: 235
             START_KEY: t_108_r_013562636162616465ff2d353561662d3437ff65642d626136372dff3366633465393632ff6364343000000000fb013436656564663562ff2d363334652d3431ff30392d613965352dff3035373530303938ff6231653900000000fb
               END_KEY: t_108_r_013830353566633762ff2d663432352d3432ff36382d386535662dff3166366430313762ff3230356600000000fb013431326533383662ff2d633336322d3435ff63322d396431612dff6133643730316266ff3731363800000000fb
             LEADER_ID: 236
       LEADER_STORE_ID: 1
                 PEERS: 236
            SCATTERING: 0
         WRITTEN_BYTES: 39
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 1
      APPROXIMATE_KEYS: 0
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:

なお、こちらもAPPROXIMATE_KEYSを合計すると50万件を超えました…。

infomation_schemaのテーブルについては、結果だけ載せておきます。

mysql> select * from information_schema.tikv_store_status order by store_id asc;
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+------------------+
| STORE_ID | ADDRESS          | STORE_STATE | STORE_STATE_NAME | LABEL                                   | VERSION | CAPACITY | AVAILABLE | LEADER_COUNT | LEADER_WEIGHT | LEADER_SCORE | LEADER_SIZE | REGION_COUNT | REGION_WEIGHT | REGION_SCORE      | REGION_SIZE | START_TS            | LAST_HEARTBEAT_TS   | UPTIME           |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+------------------+
|        1 | 172.17.0.2:20160 |           0 | Up               | null                                    | 8.1.0   | 268.6GiB | 21.38GiB  |           68 |             1 |           68 |         676 |           68 |             1 |  6048705379.84923 |         676 | 2024-06-19 14:18:20 | 2024-06-19 14:32:50 | 14m30.728544034s |
|      224 | 172.17.0.2:3930  |           0 | Up               | [{"key": "engine", "value": "tiflash"}] | v8.1.0  | 268.6GiB | 21.38GiB  |            0 |             1 |            0 |           0 |            0 |             1 | 6048701074.205022 |           0 | 2024-06-19 14:19:19 | 2024-06-19 14:32:50 | 13m31.727838626s |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+------------------+
2 rows in set (0.00 sec)



mysql> select * from information_schema.tikv_region_peers order by region_id asc, store_id asc;
+-----------+---------+----------+------------+-----------+--------+--------------+
| REGION_ID | PEER_ID | STORE_ID | IS_LEARNER | IS_LEADER | STATUS | DOWN_SECONDS |
+-----------+---------+----------+------------+-----------+--------+--------------+
|         2 |       3 |        1 |          0 |         1 | NORMAL |         NULL |
|         4 |       5 |        1 |          0 |         1 | NORMAL |         NULL |
|        46 |      47 |        1 |          0 |         1 | NORMAL |         NULL |
|       102 |     103 |        1 |          0 |         1 | NORMAL |         NULL |
|       104 |     105 |        1 |          0 |         1 | NORMAL |         NULL |
|       106 |     107 |        1 |          0 |         1 | NORMAL |         NULL |
|       116 |     117 |        1 |          0 |         1 | NORMAL |         NULL |
|       118 |     121 |        1 |          0 |         1 | NORMAL |         NULL |
|       119 |     120 |        1 |          0 |         1 | NORMAL |         NULL |
|       122 |     123 |        1 |          0 |         1 | NORMAL |         NULL |
|       124 |     125 |        1 |          0 |         1 | NORMAL |         NULL |
|       126 |     127 |        1 |          0 |         1 | NORMAL |         NULL |
|       128 |     129 |        1 |          0 |         1 | NORMAL |         NULL |
|       130 |     131 |        1 |          0 |         1 | NORMAL |         NULL |
|       132 |     133 |        1 |          0 |         1 | NORMAL |         NULL |
|       134 |     135 |        1 |          0 |         1 | NORMAL |         NULL |
|       136 |     137 |        1 |          0 |         1 | NORMAL |         NULL |
|       138 |     139 |        1 |          0 |         1 | NORMAL |         NULL |
|       140 |     141 |        1 |          0 |         1 | NORMAL |         NULL |
|       142 |     143 |        1 |          0 |         1 | NORMAL |         NULL |
|       144 |     145 |        1 |          0 |         1 | NORMAL |         NULL |
|       146 |     147 |        1 |          0 |         1 | NORMAL |         NULL |
|       148 |     149 |        1 |          0 |         1 | NORMAL |         NULL |
|       150 |     151 |        1 |          0 |         1 | NORMAL |         NULL |
|       152 |     153 |        1 |          0 |         1 | NORMAL |         NULL |
|       154 |     155 |        1 |          0 |         1 | NORMAL |         NULL |
|       156 |     157 |        1 |          0 |         1 | NORMAL |         NULL |
|       158 |     159 |        1 |          0 |         1 | NORMAL |         NULL |
|       160 |     161 |        1 |          0 |         1 | NORMAL |         NULL |
|       162 |     163 |        1 |          0 |         1 | NORMAL |         NULL |
|       164 |     165 |        1 |          0 |         1 | NORMAL |         NULL |
|       166 |     167 |        1 |          0 |         1 | NORMAL |         NULL |
|       168 |     169 |        1 |          0 |         1 | NORMAL |         NULL |
|       170 |     171 |        1 |          0 |         1 | NORMAL |         NULL |
|       172 |     173 |        1 |          0 |         1 | NORMAL |         NULL |
|       174 |     175 |        1 |          0 |         1 | NORMAL |         NULL |
|       176 |     177 |        1 |          0 |         1 | NORMAL |         NULL |
|       178 |     179 |        1 |          0 |         1 | NORMAL |         NULL |
|       180 |     181 |        1 |          0 |         1 | NORMAL |         NULL |
|       182 |     183 |        1 |          0 |         1 | NORMAL |         NULL |
|       184 |     185 |        1 |          0 |         1 | NORMAL |         NULL |
|       186 |     187 |        1 |          0 |         1 | NORMAL |         NULL |
|       188 |     189 |        1 |          0 |         1 | NORMAL |         NULL |
|       190 |     191 |        1 |          0 |         1 | NORMAL |         NULL |
|       192 |     193 |        1 |          0 |         1 | NORMAL |         NULL |
|       194 |     195 |        1 |          0 |         1 | NORMAL |         NULL |
|       196 |     197 |        1 |          0 |         1 | NORMAL |         NULL |
|       198 |     199 |        1 |          0 |         1 | NORMAL |         NULL |
|       200 |     201 |        1 |          0 |         1 | NORMAL |         NULL |
|       202 |     203 |        1 |          0 |         1 | NORMAL |         NULL |
|       204 |     205 |        1 |          0 |         1 | NORMAL |         NULL |
|       206 |     207 |        1 |          0 |         1 | NORMAL |         NULL |
|       208 |     209 |        1 |          0 |         1 | NORMAL |         NULL |
|       210 |     211 |        1 |          0 |         1 | NORMAL |         NULL |
|       212 |     213 |        1 |          0 |         1 | NORMAL |         NULL |
|       214 |     215 |        1 |          0 |         1 | NORMAL |         NULL |
|       216 |     217 |        1 |          0 |         1 | NORMAL |         NULL |
|       218 |     219 |        1 |          0 |         1 | NORMAL |         NULL |
|       220 |     221 |        1 |          0 |         1 | NORMAL |         NULL |
|       222 |     223 |        1 |          0 |         1 | NORMAL |         NULL |
|       225 |     226 |        1 |          0 |         1 | NORMAL |         NULL |
|       227 |     228 |        1 |          0 |         1 | NORMAL |         NULL |
|       229 |     230 |        1 |          0 |         1 | NORMAL |         NULL |
|       231 |     232 |        1 |          0 |         1 | NORMAL |         NULL |
|       233 |     234 |        1 |          0 |         1 | NORMAL |         NULL |
|       235 |     236 |        1 |          0 |         1 | NORMAL |         NULL |
|       237 |     238 |        1 |          0 |         1 | NORMAL |         NULL |
|       239 |     240 |        1 |          0 |         1 | NORMAL |         NULL |
+-----------+---------+----------+------------+-----------+--------+--------------+
68 rows in set (0.00 sec)



mysql> select * from information_schema.tikv_region_status where db_name = 'test' and table_name = 'ids' order by region_id asc\G
*************************** 1. row ***************************
                REGION_ID: 116
                START_KEY: 7480000000000000FF6C5F720164633461FF63626434FF2D6462FF61642D3434FF3665FF2D393736302DFF66FF37373164323735FFFF6236643500000000FFFB01316264333364FF6139FF2D62343038FF2D3436FF33662D39FF6339662DFF336332FF3164386638FF6336FF653100000000FB00FE
                  END_KEY: 748000FFFFFFFFFFFFF900000000000000F8
                 TABLE_ID: 108
                  DB_NAME: test
               TABLE_NAME: ids
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 65
            WRITTEN_BYTES: 48598232
               READ_BYTES: 0
         APPROXIMATE_SIZE: 30
         APPROXIMATE_KEYS: 28496
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 2. row ***************************
                REGION_ID: 235
                START_KEY: 7480000000000000FF6C5F720135626361FF62616465FF2D3535FF61662D3437FF6564FF2D626136372DFF33FF66633465393632FFFF6364343000000000FFFB01343665656466FF3562FF2D36333465FF2D3431FF30392D61FF3965352DFF303537FF3530303938FF6231FF653900000000FB00FE
                  END_KEY: 7480000000000000FF6C5F720138303535FF66633762FF2D6634FF32352D3432FF3638FF2D386535662DFF31FF66366430313762FFFF3230356600000000FFFB01343132653338FF3662FF2D63333632FF2D3435FF63322D39FF6431612DFF613364FF3730316266FF3731FF363800000000FB00FE
                 TABLE_ID: 108
                  DB_NAME: test
               TABLE_NAME: ids
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 65
            WRITTEN_BYTES: 49478753
               READ_BYTES: 0
         APPROXIMATE_SIZE: 45
         APPROXIMATE_KEYS: 100518
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 3. row ***************************
                REGION_ID: 237
                START_KEY: 7480000000000000FF6C00000000000000F8
                  END_KEY: 7480000000000000FF6C5F720135626361FF62616465FF2D3535FF61662D3437FF6564FF2D626136372DFF33FF66633465393632FFFF6364343000000000FFFB01343665656466FF3562FF2D36333465FF2D3431FF30392D61FF3965352DFF303537FF3530303938FF6231FF653900000000FB00FE
                 TABLE_ID: 108
                  DB_NAME: test
               TABLE_NAME: ids
                 IS_INDEX: 1
                 INDEX_ID: 1
               INDEX_NAME: PRIMARY
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 65
            WRITTEN_BYTES: 124695959
               READ_BYTES: 0
         APPROXIMATE_SIZE: 132
         APPROXIMATE_KEYS: 288612
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 4. row ***************************
                REGION_ID: 237
                START_KEY: 7480000000000000FF6C00000000000000F8
                  END_KEY: 7480000000000000FF6C5F720135626361FF62616465FF2D3535FF61662D3437FF6564FF2D626136372DFF33FF66633465393632FFFF6364343000000000FFFB01343665656466FF3562FF2D36333465FF2D3431FF30392D61FF3965352DFF303537FF3530303938FF6231FF653900000000FB00FE
                 TABLE_ID: 108
                  DB_NAME: test
               TABLE_NAME: ids
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 65
            WRITTEN_BYTES: 124695959
               READ_BYTES: 0
         APPROXIMATE_SIZE: 132
         APPROXIMATE_KEYS: 288612
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 5. row ***************************
                REGION_ID: 239
                START_KEY: 7480000000000000FF6C5F720138303535FF66633762FF2D6634FF32352D3432FF3638FF2D386535662DFF31FF66366430313762FFFF3230356600000000FFFB01343132653338FF3662FF2D63333632FF2D3435FF63322D39FF6431612DFF613364FF3730316266FF3731FF363800000000FB00FE
                  END_KEY: 7480000000000000FF6C5F720164633461FF63626434FF2D6462FF61642D3434FF3665FF2D393736302DFF66FF37373164323735FFFF6236643500000000FFFB01316264333364FF6139FF2D62343038FF2D3436FF33662D39FF6339662DFF336332FF3164386638FF6336FF653100000000FB00FE
                 TABLE_ID: 108
                  DB_NAME: test
               TABLE_NAME: ids
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 1
            EPOCH_VERSION: 65
            WRITTEN_BYTES: 124695304
               READ_BYTES: 0
         APPROXIMATE_SIZE: 109
         APPROXIMATE_KEYS: 166866
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
5 rows in set (0.01 sec)

TiKVノードを追加する

最後はTiKVノードを追加してみましょう。1つノードを追加します。

$ tiup playground scale-out --kv 1

増えました。

$ tiup playground display
Pid   Role     Uptime
---   ----     ------
1513  pd       16m36.541441245s
1514  tikv     16m36.506197944s
2890  tikv     6.716025514s
1517  tidb     16m36.467776625s
1750  tiflash  15m46.128862251s

mysqlコマンドでも確認してみましょう。

tikv_store_statusテーブル。TiKVノードが増えています。

mysql> select * from information_schema.tikv_store_status order by store_id asc;
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+------------------+
| STORE_ID | ADDRESS          | STORE_STATE | STORE_STATE_NAME | LABEL                                   | VERSION | CAPACITY | AVAILABLE | LEADER_COUNT | LEADER_WEIGHT | LEADER_SCORE | LEADER_SIZE | REGION_COUNT | REGION_WEIGHT | REGION_SCORE      | REGION_SIZE | START_TS            | LAST_HEARTBEAT_TS   | UPTIME           |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+------------------+
|        1 | 172.17.0.2:20160 |           0 | Up               | null                                    | 8.1.0   | 268.6GiB | 21.17GiB  |            0 |             1 |            0 |           0 |           68 |             1 | 6040785384.751917 |         676 | 2024-06-19 14:18:20 | 2024-06-19 14:35:30 | 17m10.763673382s |
|      224 | 172.17.0.2:3930  |           0 | Up               | [{"key": "engine", "value": "tiflash"}] | v8.1.0  | 268.6GiB | 21.17GiB  |            0 |             1 |            0 |           0 |            0 |             1 | 6040781058.993824 |           0 | 2024-06-19 14:19:19 | 2024-06-19 14:35:30 | 16m11.763722031s |
|      241 | 172.17.0.2:43269 |           0 | Up               | null                                    | 8.1.0   | 268.6GiB | 21.17GiB  |           68 |             1 |           68 |         676 |           68 |             1 | 6051466708.583883 |         676 | 2024-06-19 14:34:45 | 2024-06-19 14:35:26 | 41.142093154s    |
+----------+------------------+-------------+------------------+-----------------------------------------+---------+----------+-----------+--------------+---------------+--------------+-------------+--------------+---------------+-------------------+-------------+---------------------+---------------------+------------------+
3 rows in set (0.00 sec)

tikv_region_peersテーブルで、リージョンのピアの情報を見てみます。ノードが増えたからか、2倍くらいになっています。

mysql> select * from information_schema.tikv_region_peers order by region_id asc, store_id asc;
+-----------+---------+----------+------------+-----------+--------+--------------+
| REGION_ID | PEER_ID | STORE_ID | IS_LEARNER | IS_LEADER | STATUS | DOWN_SECONDS |
+-----------+---------+----------+------------+-----------+--------+--------------+
|         2 |       3 |        1 |          0 |         0 | NORMAL |         NULL |
|         2 |     295 |      241 |          0 |         1 | NORMAL |         NULL |
|         4 |       5 |        1 |          0 |         0 | NORMAL |         NULL |
|         4 |     298 |      241 |          0 |         1 | NORMAL |         NULL |
|        46 |      47 |        1 |          0 |         0 | NORMAL |         NULL |
|        46 |     299 |      241 |          0 |         1 | NORMAL |         NULL |
|       102 |     103 |        1 |          0 |         0 | NORMAL |         NULL |
|       102 |     300 |      241 |          0 |         1 | NORMAL |         NULL |
|       104 |     105 |        1 |          0 |         0 | NORMAL |         NULL |
|       104 |     301 |      241 |          0 |         1 | NORMAL |         NULL |
|       106 |     107 |        1 |          0 |         0 | NORMAL |         NULL |
|       106 |     302 |      241 |          0 |         1 | NORMAL |         NULL |
|       116 |     117 |        1 |          0 |         0 | NORMAL |         NULL |
|       116 |     303 |      241 |          0 |         1 | NORMAL |         NULL |
|       118 |     121 |        1 |          0 |         0 | NORMAL |         NULL |
|       118 |     242 |      241 |          0 |         1 | NORMAL |         NULL |
|       119 |     120 |        1 |          0 |         0 | NORMAL |         NULL |
|       119 |     297 |      241 |          0 |         1 | NORMAL |         NULL |
|       122 |     123 |        1 |          0 |         0 | NORMAL |         NULL |
|       122 |     296 |      241 |          0 |         1 | NORMAL |         NULL |
|       124 |     125 |        1 |          0 |         0 | NORMAL |         NULL |
|       124 |     243 |      241 |          0 |         1 | NORMAL |         NULL |
|       126 |     127 |        1 |          0 |         0 | NORMAL |         NULL |
|       126 |     244 |      241 |          0 |         1 | NORMAL |         NULL |
|       128 |     129 |        1 |          0 |         0 | NORMAL |         NULL |
|       128 |     245 |      241 |          0 |         1 | NORMAL |         NULL |
|       130 |     131 |        1 |          0 |         0 | NORMAL |         NULL |
|       130 |     246 |      241 |          0 |         1 | NORMAL |         NULL |
|       132 |     133 |        1 |          0 |         0 | NORMAL |         NULL |
|       132 |     247 |      241 |          0 |         1 | NORMAL |         NULL |
|       134 |     135 |        1 |          0 |         0 | NORMAL |         NULL |
|       134 |     248 |      241 |          0 |         1 | NORMAL |         NULL |
|       136 |     137 |        1 |          0 |         0 | NORMAL |         NULL |
|       136 |     249 |      241 |          0 |         1 | NORMAL |         NULL |
|       138 |     139 |        1 |          0 |         0 | NORMAL |         NULL |
|       138 |     250 |      241 |          0 |         1 | NORMAL |         NULL |
|       140 |     141 |        1 |          0 |         0 | NORMAL |         NULL |
|       140 |     251 |      241 |          0 |         1 | NORMAL |         NULL |
|       142 |     143 |        1 |          0 |         0 | NORMAL |         NULL |
|       142 |     252 |      241 |          0 |         1 | NORMAL |         NULL |
|       144 |     145 |        1 |          0 |         0 | NORMAL |         NULL |
|       144 |     253 |      241 |          0 |         1 | NORMAL |         NULL |
|       146 |     147 |        1 |          0 |         0 | NORMAL |         NULL |
|       146 |     254 |      241 |          0 |         1 | NORMAL |         NULL |
|       148 |     149 |        1 |          0 |         0 | NORMAL |         NULL |
|       148 |     255 |      241 |          0 |         1 | NORMAL |         NULL |
|       150 |     151 |        1 |          0 |         0 | NORMAL |         NULL |
|       150 |     256 |      241 |          0 |         1 | NORMAL |         NULL |
|       152 |     153 |        1 |          0 |         0 | NORMAL |         NULL |
|       152 |     257 |      241 |          0 |         1 | NORMAL |         NULL |
|       154 |     155 |        1 |          0 |         0 | NORMAL |         NULL |
|       154 |     258 |      241 |          0 |         1 | NORMAL |         NULL |
|       156 |     157 |        1 |          0 |         0 | NORMAL |         NULL |
|       156 |     259 |      241 |          0 |         1 | NORMAL |         NULL |
|       158 |     159 |        1 |          0 |         0 | NORMAL |         NULL |
|       158 |     260 |      241 |          0 |         1 | NORMAL |         NULL |
|       160 |     161 |        1 |          0 |         0 | NORMAL |         NULL |
|       160 |     261 |      241 |          0 |         1 | NORMAL |         NULL |
|       162 |     163 |        1 |          0 |         0 | NORMAL |         NULL |
|       162 |     262 |      241 |          0 |         1 | NORMAL |         NULL |
|       164 |     165 |        1 |          0 |         0 | NORMAL |         NULL |
|       164 |     263 |      241 |          0 |         1 | NORMAL |         NULL |
|       166 |     167 |        1 |          0 |         0 | NORMAL |         NULL |
|       166 |     264 |      241 |          0 |         1 | NORMAL |         NULL |
|       168 |     169 |        1 |          0 |         0 | NORMAL |         NULL |
|       168 |     265 |      241 |          0 |         1 | NORMAL |         NULL |
|       170 |     171 |        1 |          0 |         0 | NORMAL |         NULL |
|       170 |     266 |      241 |          0 |         1 | NORMAL |         NULL |
|       172 |     173 |        1 |          0 |         0 | NORMAL |         NULL |
|       172 |     267 |      241 |          0 |         1 | NORMAL |         NULL |
|       174 |     175 |        1 |          0 |         0 | NORMAL |         NULL |
|       174 |     268 |      241 |          0 |         1 | NORMAL |         NULL |
|       176 |     177 |        1 |          0 |         0 | NORMAL |         NULL |
|       176 |     269 |      241 |          0 |         1 | NORMAL |         NULL |
|       178 |     179 |        1 |          0 |         0 | NORMAL |         NULL |
|       178 |     270 |      241 |          0 |         1 | NORMAL |         NULL |
|       180 |     181 |        1 |          0 |         0 | NORMAL |         NULL |
|       180 |     271 |      241 |          0 |         1 | NORMAL |         NULL |
|       182 |     183 |        1 |          0 |         0 | NORMAL |         NULL |
|       182 |     272 |      241 |          0 |         1 | NORMAL |         NULL |
|       184 |     185 |        1 |          0 |         0 | NORMAL |         NULL |
|       184 |     273 |      241 |          0 |         1 | NORMAL |         NULL |
|       186 |     187 |        1 |          0 |         0 | NORMAL |         NULL |
|       186 |     274 |      241 |          0 |         1 | NORMAL |         NULL |
|       188 |     189 |        1 |          0 |         0 | NORMAL |         NULL |
|       188 |     275 |      241 |          0 |         1 | NORMAL |         NULL |
|       190 |     191 |        1 |          0 |         0 | NORMAL |         NULL |
|       190 |     276 |      241 |          0 |         1 | NORMAL |         NULL |
|       192 |     193 |        1 |          0 |         0 | NORMAL |         NULL |
|       192 |     277 |      241 |          0 |         1 | NORMAL |         NULL |
|       194 |     195 |        1 |          0 |         0 | NORMAL |         NULL |
|       194 |     278 |      241 |          0 |         1 | NORMAL |         NULL |
|       196 |     197 |        1 |          0 |         0 | NORMAL |         NULL |
|       196 |     279 |      241 |          0 |         1 | NORMAL |         NULL |
|       198 |     199 |        1 |          0 |         0 | NORMAL |         NULL |
|       198 |     280 |      241 |          0 |         1 | NORMAL |         NULL |
|       200 |     201 |        1 |          0 |         0 | NORMAL |         NULL |
|       200 |     281 |      241 |          0 |         1 | NORMAL |         NULL |
|       202 |     203 |        1 |          0 |         0 | NORMAL |         NULL |
|       202 |     282 |      241 |          0 |         1 | NORMAL |         NULL |
|       204 |     205 |        1 |          0 |         0 | NORMAL |         NULL |
|       204 |     283 |      241 |          0 |         1 | NORMAL |         NULL |
|       206 |     207 |        1 |          0 |         0 | NORMAL |         NULL |
|       206 |     284 |      241 |          0 |         1 | NORMAL |         NULL |
|       208 |     209 |        1 |          0 |         0 | NORMAL |         NULL |
|       208 |     285 |      241 |          0 |         1 | NORMAL |         NULL |
|       210 |     211 |        1 |          0 |         0 | NORMAL |         NULL |
|       210 |     286 |      241 |          0 |         1 | NORMAL |         NULL |
|       212 |     213 |        1 |          0 |         0 | NORMAL |         NULL |
|       212 |     287 |      241 |          0 |         1 | NORMAL |         NULL |
|       214 |     215 |        1 |          0 |         0 | NORMAL |         NULL |
|       214 |     288 |      241 |          0 |         1 | NORMAL |         NULL |
|       216 |     217 |        1 |          0 |         0 | NORMAL |         NULL |
|       216 |     289 |      241 |          0 |         1 | NORMAL |         NULL |
|       218 |     219 |        1 |          0 |         0 | NORMAL |         NULL |
|       218 |     290 |      241 |          0 |         1 | NORMAL |         NULL |
|       220 |     221 |        1 |          0 |         0 | NORMAL |         NULL |
|       220 |     291 |      241 |          0 |         1 | NORMAL |         NULL |
|       222 |     223 |        1 |          0 |         0 | NORMAL |         NULL |
|       222 |     292 |      241 |          0 |         1 | NORMAL |         NULL |
|       225 |     226 |        1 |          0 |         0 | NORMAL |         NULL |
|       225 |     293 |      241 |          0 |         1 | NORMAL |         NULL |
|       227 |     228 |        1 |          0 |         0 | NORMAL |         NULL |
|       227 |     294 |      241 |          0 |         1 | NORMAL |         NULL |
|       229 |     230 |        1 |          0 |         0 | NORMAL |         NULL |
|       229 |     309 |      241 |          0 |         1 | NORMAL |         NULL |
|       231 |     232 |        1 |          0 |         0 | NORMAL |         NULL |
|       231 |     308 |      241 |          0 |         1 | NORMAL |         NULL |
|       233 |     234 |        1 |          0 |         0 | NORMAL |         NULL |
|       233 |     307 |      241 |          0 |         1 | NORMAL |         NULL |
|       235 |     236 |        1 |          0 |         0 | NORMAL |         NULL |
|       235 |     305 |      241 |          0 |         1 | NORMAL |         NULL |
|       237 |     238 |        1 |          0 |         0 | NORMAL |         NULL |
|       237 |     306 |      241 |          0 |         1 | NORMAL |         NULL |
|       239 |     240 |        1 |          0 |         0 | NORMAL |         NULL |
|       239 |     304 |      241 |          0 |         1 | NORMAL |         NULL |
+-----------+---------+----------+------------+-----------+--------+--------------+
136 rows in set (0.01 sec)

show table [テーブル名] regionsを見てみましょう。

最初に登録したtripsテーブル。

mysql> use bikeshare;
Database changed
mysql> show table trips regions\G
*************************** 1. row ***************************
             REGION_ID: 227
             START_KEY: t_106_
               END_KEY: t_106_r_207740
             LEADER_ID: 228
       LEADER_STORE_ID: 1
                 PEERS: 228, 294
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 120
      APPROXIMATE_KEYS: 472174
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 2. row ***************************
             REGION_ID: 229
             START_KEY: t_106_r_207740
               END_KEY: t_106_r_460085
             LEADER_ID: 230
       LEADER_STORE_ID: 1
                 PEERS: 230, 309
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 109
      APPROXIMATE_KEYS: 299100
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 3. row ***************************
             REGION_ID: 231
             START_KEY: t_106_r_460085
               END_KEY: t_106_r_517037
             LEADER_ID: 308
       LEADER_STORE_ID: 241
                 PEERS: 232, 308
            SCATTERING: 0
         WRITTEN_BYTES: 42
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 8
      APPROXIMATE_KEYS: 56952
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 4. row ***************************
             REGION_ID: 233
             START_KEY: t_106_r_517037
               END_KEY: t_108_
             LEADER_ID: 307
       LEADER_STORE_ID: 241
                 PEERS: 234, 307
            SCATTERING: 0
         WRITTEN_BYTES: 39
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 20
      APPROXIMATE_KEYS: 129474
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
4 rows in set (0.01 sec)

よく見ると、PEERSという情報が増えています。

*************************** 2. row ***************************
             REGION_ID: 229
             START_KEY: t_106_r_207740
               END_KEY: t_106_r_460085
             LEADER_ID: 230
       LEADER_STORE_ID: 1
                 PEERS: 230, 309
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 109
      APPROXIMATE_KEYS: 299100
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:

これはピアとなるIDで、LEADER_IDが230なのでレプリカは309ということになります。

こういうことですね。

mysql> select * from information_schema.tikv_region_peers where peer_id in (230, 309) order by region_id asc, store_id asc;
+-----------+---------+----------+------------+-----------+--------+--------------+
| REGION_ID | PEER_ID | STORE_ID | IS_LEARNER | IS_LEADER | STATUS | DOWN_SECONDS |
+-----------+---------+----------+------------+-----------+--------+--------------+
|       229 |     230 |        1 |          0 |         1 | NORMAL |         NULL |
|       229 |     309 |      241 |          0 |         0 | NORMAL |         NULL |
+-----------+---------+----------+------------+-----------+--------+--------------+
2 rows in set (0.01 sec)

tikv_region_statusテーブル。

mysql> select * from information_schema.tikv_region_status where db_name = 'bikeshare' and table_name = 'trips' order by region_id asc\G
*************************** 1. row ***************************
                REGION_ID: 227
                START_KEY: 7480000000000000FF6A00000000000000F8
                  END_KEY: 7480000000000000FF6A5F728000000000FF032B7C0000000000FA
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 3
            EPOCH_VERSION: 60
            WRITTEN_BYTES: 0
               READ_BYTES: 0
         APPROXIMATE_SIZE: 120
         APPROXIMATE_KEYS: 472174
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 2. row ***************************
                REGION_ID: 229
                START_KEY: 7480000000000000FF6A5F728000000000FF032B7C0000000000FA
                  END_KEY: 7480000000000000FF6A5F728000000000FF0705350000000000FA
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 3
            EPOCH_VERSION: 61
            WRITTEN_BYTES: 0
               READ_BYTES: 0
         APPROXIMATE_SIZE: 109
         APPROXIMATE_KEYS: 299100
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 3. row ***************************
                REGION_ID: 231
                START_KEY: 7480000000000000FF6A5F728000000000FF0705350000000000FA
                  END_KEY: 7480000000000000FF6A5F728000000000FF07E3AD0000000000FA
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 3
            EPOCH_VERSION: 62
            WRITTEN_BYTES: 42
               READ_BYTES: 0
         APPROXIMATE_SIZE: 8
         APPROXIMATE_KEYS: 56952
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 4. row ***************************
                REGION_ID: 233
                START_KEY: 7480000000000000FF6A5F728000000000FF07E3AD0000000000FA
                  END_KEY: 7480000000000000FF6C00000000000000F8
                 TABLE_ID: 106
                  DB_NAME: bikeshare
               TABLE_NAME: trips
                 IS_INDEX: 0
                 INDEX_ID: NULL
               INDEX_NAME: NULL
             IS_PARTITION: 0
             PARTITION_ID: NULL
           PARTITION_NAME: NULL
           EPOCH_CONF_VER: 3
            EPOCH_VERSION: 63
            WRITTEN_BYTES: 39
               READ_BYTES: 0
         APPROXIMATE_SIZE: 20
         APPROXIMATE_KEYS: 129474
  REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
4 rows in set (0.01 sec)

joinした結果。

select
  tikv.store_id,
  tikv.address,
  tikv.store_state_name,
  region.db_name,
  region.table_name,
  region.region_id,
  peer.peer_id,
  peer.is_leader,
  region.approximate_size,
  region.approximate_keys,
  peer.status
from
  information_schema.tikv_store_status tikv
inner join
  information_schema.tikv_region_peers peer
on
  tikv.store_id = peer.store_id
inner join
  information_schema.tikv_region_status region
on
  peer.region_id = region.region_id
where
  region.db_name = 'bikeshare' and region.table_name = 'trips'
order by
  tikv.store_id asc,
  region.region_id asc;

結果。

+----------+------------------+------------------+-----------+------------+-----------+---------+-----------+------------------+------------------+--------+
| store_id | address          | store_state_name | db_name   | table_name | region_id | peer_id | is_leader | approximate_size | approximate_keys | status |
+----------+------------------+------------------+-----------+------------+-----------+---------+-----------+------------------+------------------+--------+
|        1 | 172.17.0.2:20160 | Up               | bikeshare | trips      |       227 |     228 |         1 |              120 |           472174 | NORMAL |
|        1 | 172.17.0.2:20160 | Up               | bikeshare | trips      |       229 |     230 |         1 |              109 |           299100 | NORMAL |
|        1 | 172.17.0.2:20160 | Up               | bikeshare | trips      |       231 |     232 |         0 |                8 |            56952 | NORMAL |
|        1 | 172.17.0.2:20160 | Up               | bikeshare | trips      |       233 |     234 |         0 |               20 |           129474 | NORMAL |
|      241 | 172.17.0.2:43269 | Up               | bikeshare | trips      |       227 |     294 |         0 |              120 |           472174 | NORMAL |
|      241 | 172.17.0.2:43269 | Up               | bikeshare | trips      |       229 |     309 |         0 |              109 |           299100 | NORMAL |
|      241 | 172.17.0.2:43269 | Up               | bikeshare | trips      |       231 |     308 |         1 |                8 |            56952 | NORMAL |
|      241 | 172.17.0.2:43269 | Up               | bikeshare | trips      |       233 |     307 |         1 |               20 |           129474 | NORMAL |
+----------+------------------+------------------+-----------+------------+-----------+---------+-----------+------------------+------------------+--------+
8 rows in set (0.06 sec)

追加したテーブルの方は、show table [テーブル名] regionsだけ見ておきます。

mysql> use test;
Database changed
mysql> show table ids regions\G
*************************** 1. row ***************************
             REGION_ID: 237
             START_KEY: t_108_
               END_KEY: t_108_r_013562636162616465ff2d353561662d3437ff65642d626136372dff3366633465393632ff6364343000000000fb013436656564663562ff2d363334652d3431ff30392d613965352dff3035373530303938ff6231653900000000fb
             LEADER_ID: 238
       LEADER_STORE_ID: 1
                 PEERS: 238, 306
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 132
      APPROXIMATE_KEYS: 288612
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 2. row ***************************
             REGION_ID: 235
             START_KEY: t_108_r_013562636162616465ff2d353561662d3437ff65642d626136372dff3366633465393632ff6364343000000000fb013436656564663562ff2d363334652d3431ff30392d613965352dff3035373530303938ff6231653900000000fb
               END_KEY: t_108_r_013830353566633762ff2d663432352d3432ff36382d386535662dff3166366430313762ff3230356600000000fb013431326533383662ff2d633336322d3435ff63322d396431612dff6133643730316266ff3731363800000000fb
             LEADER_ID: 236
       LEADER_STORE_ID: 1
                 PEERS: 236, 305
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 45
      APPROXIMATE_KEYS: 100518
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 3. row ***************************
             REGION_ID: 239
             START_KEY: t_108_r_013830353566633762ff2d663432352d3432ff36382d386535662dff3166366430313762ff3230356600000000fb013431326533383662ff2d633336322d3435ff63322d396431612dff6133643730316266ff3731363800000000fb
               END_KEY: t_108_r_016463346163626434ff2d646261642d3434ff36652d393736302dff6637373164323735ff6236643500000000fb013162643333646139ff2d623430382d3436ff33662d396339662dff3363323164386638ff6336653100000000fb
             LEADER_ID: 240
       LEADER_STORE_ID: 1
                 PEERS: 240, 304
            SCATTERING: 0
         WRITTEN_BYTES: 0
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 109
      APPROXIMATE_KEYS: 166866
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
*************************** 4. row ***************************
             REGION_ID: 116
             START_KEY: t_108_r_016463346163626434ff2d646261642d3434ff36652d393736302dff6637373164323735ff6236643500000000fb013162643333646139ff2d623430382d3436ff33662d396339662dff3363323164386638ff6336653100000000fb
               END_KEY: t_281474976710649_
             LEADER_ID: 303
       LEADER_STORE_ID: 241
                 PEERS: 117, 303
            SCATTERING: 0
         WRITTEN_BYTES: 42
            READ_BYTES: 0
  APPROXIMATE_SIZE(MB): 14
      APPROXIMATE_KEYS: 69931
SCHEDULING_CONSTRAINTS:
      SCHEDULING_STATE:
4 rows in set (0.01 sec)

これで、確認したいことは見れました。

おわりに

TiDBのTiKVノードに関する、データのリージョンごとの配置状況を確認してみました。

最初はどうやって確認したらいいのか調べるところからでしたが、ドキュメントを追ってみたり実際に動かしてみて内容を見てみると
理解が進んでよかったです。

そろそろなにかの言語から接続したりして試してみたいですね。