Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb 建表的时候会分配多少个region？
|
username: Raymond
May I ask the teachers, when creating a table without inserting data, will only one region be allocated at this time? Is the region pre-allocated or is it allocated only when needed?
Single table, partitioned table, pre_split_regions. After creating the table, use “show table regions” to check and you’ll know.
The scenarios you described can all be satisfied, and this is closely related to the model chosen for table creation.
High-performance models will automatically allocate and generate regions, but some scenarios are not suitable. Clustered index mode.
Another one is the non-clustered index mode, which requires pre-allocation or manual scattering.
Please refer to the above. Relevant documentation can be found through search.
I tested it, but it seems that the PRE_SPLIT_REGIONS parameter didn’t take effect.
|
username: 
|
username: 
|
username: MySQL [test]> create table test (id int primary key );
Query OK, 0 rows affected (0.10 sec)
MySQL [test]> show table test regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2858_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 50 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> insert into test VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MySQL [test]> show table test regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2858_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 50 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> create table test2 (id int primary key ) PRE_SPLIT_REGIONS = 2;
Query OK, 0 rows affected (0.09 sec)
MySQL [test]> show table test2 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2860_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 0 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> insert into test2 VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
MySQL [test]> show table test2 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2860_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 0 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> create table test3 (id int primary key ) PRE_SPLIT_REGIONS = 4;
Query OK, 0 rows affected (0.08 sec)
MySQL [test]> show table test3 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2862_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 547 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.1.0 |
+--------------------+
1 row in set (0.00 sec)
MySQL [test]> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:15:11
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [test]>
When creating a table using SHARD_ROW_ID_BITS, if you want to evenly split the Regions at the time of table creation, you can consider using PRE_SPLIT_REGIONS together. This will start pre-splitting 2^(PRE_SPLIT_REGIONS) Regions right after the table is successfully created.
Reference:
SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4
Use the two parameters together.
|
username:
MySQL [test]> CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4;
Query OK, 0 rows affected (0.09 sec)
MySQL [test]> show table t regions;
+-----------+------------------------------+------------------------------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+------------------------------+------------------------------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111570150 | t_2873_ | t_2873_r_576460752303423488 | 111570151 | 110997197 | 111570151, 111570152, 111570153 | 0 | 0 | 0 | 1 | 0 |
| 111570154 | t_2873_r_576460752303423488 | t_2873_r_1152921504606846976 | 111570155 | 110997197 | 111570155, 111570156, 111570157 | 0 | 0 | 0 | 1 | 0 |
| 111570158 | t_2873_r_1152921504606846976 | t_2873_r_1729382256910270464 | 111570159 | 110997197 | 111570159, 111570160, 111570161 | 0 | 0 | 0 | 1 | 0 |
| 111570162 | t_2873_r_1729382256910270464 | t_2873_r_2305843009213693952 | 111570163 | 110997197 | 111570163, 111570164, 111570165 | 0 | 39 | 0 | 1 | 0 |
| 111570166 | t_2873_r_2305843009213693952 | t_2873_r_2882303761517117440 | 111570167 | 110997197 | 111570167, 111570168, 111570169 | 0 | 39 | 0 | 1 | 0 |
| 111570170 | t_2873_r_2882303761517117440 | t_2873_r_3458764513820540928 | 111570171 | 110997197 | 111570171, 111570172, 111570173 | 0 | 39 | 0 | 1 | 0 |
| 111570174 | t_2873_r_3458764513820540928 | t_2873_r_4035225266123964416 | 111570175 | 110997197 | 111570175, 111570176, 111570177 | 0 | 39 | 0 | 1 | 0 |
| 111570178 | t_2873_r_4035225266123964416 | t_2873_r_4611686018427387904 | 111570179 | 110997197 | 111570179, 111570180, 111570181 | 0 | 39 | 0 | 1 | 0 |
| 111570182 | t_2873_r_4611686018427387904 | t_2873_r_5188146770730811392 | 111570183 | 110997197 | 111570183, 111570184, 111570185 | 0 | 39 | 0 | 1 | 0 |
| 111570186 | t_2873_r_5188146770730811392 | t_2873_r_5764607523034234880 | 111570187 | 110997197 | 111570187, 111570188, 111570189 | 0 | 39 | 0 | 1 | 0 |
| 111570190 | t_2873_r_5764607523034234880 | t_2873_r_6341068275337658368 | 111570191 | 110997197 | 111570191, 111570192, 111570193 | 0 | 27 | 0 | 1 | 0 |
| 111570194 | t_2873_r_6341068275337658368 | t_2873_r_6917529027641081856 | 111570195 | 110997197 | 111570195, 111570196, 111570197 | 0 | 27 | 0 | 1 | 0 |
| 111570198 | t_2873_r_6917529027641081856 | t_2873_r_7493989779944505344 | 111570199 | 110997197 | 111570199, 111570200, 111570201 | 0 | 39 | 0 | 1 | 0 |
| 111570202 | t_2873_r_7493989779944505344 | t_2873_r_8070450532247928832 | 111570203 | 110997197 | 111570203, 111570204, 111570205 | 0 | 39 | 0 | 1 | 0 |
| 111570206 | t_2873_r_8070450532247928832 | t_2873_r_8646911284551352320 | 111570207 | 110997197 | 111570207, 111570208, 111570209 | 0 | 39 | 0 | 1 | 0 |
| 111528001 | t_2873_r_8646911284551352320 | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 0 | 0 | 1 | 0 |
+-----------+------------------------------+------------------------------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
16 rows in set (0.02 sec)
The parameters shard_row_id_bits = 4 and pre_split_regions = 4 should be used together.
You can check the result by using show table <tablename> regions.
PRE_SPLIT_REGIONS and shard_row_id_bits are used together, but if the pre-allocated regions are not used and you have enabled the region merge feature, the empty regions will be automatically merged. Refer to Investigating and Analyzing the Causes of Large Number of Empty Regions.
The image is not visible. Please provide the text you need translated.
PCTP Official Courseware:
|
username: 
|
username: -- Create a non-clustered table and add SHARD_ROW_ID_BITS to scatter the data, and pre-split the table into 4 Regions after creation
CREATE TABLE t (c int PRIMARY KEY NONCLUSTERED) SHARD_ROW_ID_BITS = 4 pre_split_regions = 2;
-- Modify the table's shard random bits to 5
ALTER TABLE t SHARD_ROW_ID_BITS = 5;
-- View the table's region distribution
show table t regions;
This topic was automatically closed 1 minute after the last reply. No new replies are allowed.
|
username: