How to check the number of regions in a table and the relationship between each region and physical files

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何查看表分多少region,及各region与物理文件对应关系

| username: wenyi

May I ask the experts, how to check how many regions a table has and the relationship between each region and the physical files?

| username: tidb狂热爱好者 | Original post link

Region 6.5 is 96m, and PD can handle more than 20,000 regions. Starting from 6.6, the region is 15g, still 20,000 regions, but the capacity has reached the 1000p level.

| username: wenyi | Original post link

How to query how many regions correspond to a certain table and which files on the system correspond to these regions.

| username: Billmay表妹 | Original post link

You can use the SHOW TABLE REGIONS statement to see how many Regions a table has and the physical files corresponding to each Region. The specific steps are as follows:

  1. First, use the USE statement to select the database you want to query, for example:
USE your_database;
  1. Then, use the SHOW TABLE REGIONS statement to view the Region information of the table, for example:
SHOW TABLE REGIONS your_table;

This will return a table containing information about all Regions of the table, including Region ID, start key, end key, the TiKV node where the Leader replica is located, etc.

  1. If you want to view the physical files corresponding to each Region, you can use the pd-ctl tool to query. The specific steps are as follows:
  • First, use the pd-ctl tool to connect to the PD node, for example:
pd-ctl -u http://{pd_ip}:{pd_port}
  • Then, use the region <region_id> command to query the information of the specified Region, for example:
region <region_id>

This will return a JSON formatted string containing detailed information about the Region, including the TiKV node where the Region is located, the physical files corresponding to the Region, etc. Note that you need to replace <region_id> with the actual Region ID. You can get the ID of each Region from the results in step 2.

| username: hey-hoho | Original post link

Cousin, the answer you gave above is from AI, right? Some parts are not quite accurate. :rofl:

| username: hey-hoho | Original post link

To view the regions corresponding to a table:

show table xxx regions;

You can also check the system table information_schema.tikv_region_status.

To view the SST files corresponding to a region, refer to:

| username: residentevil | Original post link

Worth bookmarking, haha.

| username: redgame | Original post link

show table xxx regions; This is correct.

| username: FutureDB | Original post link

This is quite useful.

| username: FutureDB | Original post link

It’s obvious that this is AI.

| username: Aaronz | Original post link

You can use SHOW TABLE REGIONS your_table; to see the specific number of regions. To view detailed information, you should use the PD tool.

| username: TiDBer_rvITcue9 | Original post link

information_schema.tikv_region_status

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.