[TiDB Usage Environment] Production Environment
[TiDB Version] V4.0.16
[Reproduction Path] SQL query did not return results
[Problem Encountered] Want to know the data distribution of a certain table and which KV nodes it is stored on
[Resource Configuration] Cluster with 14 TiKV nodes (16c 64G 2T *14)
[Attachment: Screenshot/Log/Monitoring]
SELECT DISTINCT a.db_name, a.`TABLE_NAME`, a.`REGION_ID`, c.`STORE_ID`, b.`ADDRESS`
FROM INFORMATION_SCHEMA.`TIKV_REGION_STATUS` a
JOIN INFORMATION_SCHEMA.`TIKV_STORE_STATUS` b ON c.`STORE_ID` = b.`STORE_ID`
JOIN INFORMATION_SCHEMA.`TIKV_REGION_PEERS` c ON a.`REGION_ID` = c.`REGION_ID`
WHERE c.`IS_LEADER` = '1'
AND a.`TABLE_NAME` = 'b';
This SQL can query the node information where Region IS_LEADER is located. You can remove c.IS_LEADER = '1':
SELECT DISTINCT a.db_name, a.TABLE_NAME, a.REGION_ID, c.STORE_ID, b.ADDRESS
FROM INFORMATION_SCHEMA.TIKV_REGION_STATUS a, INFORMATION_SCHEMA.TIKV_STORE_STATUS b, INFORMATION_SCHEMA.TIKV_REGION_PEERS c
WHERE
-- c.IS_LEADER='1'AND
c.STORE_ID = b.STORE_ID
AND a.REGION_ID = c.REGION_ID
AND table_name = 'b'
ORDER BY a.REGION_ID
SELECT
rs.DB_NAME,
rs.TABLE_NAME,
s.ADDRESS,
r.IS_LEADER,
r.IS_LEARNER,
COUNT(r.REGION_ID)
FROM
INFORMATION_SCHEMA.TIKV_REGION_PEERS r,
INFORMATION_SCHEMA.TIKV_STORE_STATUS s,
INFORMATION_SCHEMA.tikv_region_status rs
WHERE r.STORE_ID = s.STORE_ID
AND r.REGION_ID = rs.REGION_ID
AND rs.DB_NAME = 'xxx'
AND rs.TABLE_NAME = 'tablename'
AND r.IS_LEARNER = 0
GROUP BY rs.DB_NAME,
rs.TABLE_NAME,
s.ADDRESS,
r.IS_LEADER,
r.IS_LEARNER
ORDER BY r.IS_LEADER;
|
username: niegang
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: