Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: dashboard 流量图中的表不存在
|
username: porpoiselxj
[TiDB Usage Environment] Production Environment
[TiDB Version] v7.1.3
[Encountered Issue: Problem Phenomenon and Impact]
The dashboard traffic graph shows a high read rate, but the monitoring indicates that table_8920 does not exist in the database. What could be the reason for this?
This is already an old table. The table has undergone truncate or drop, and now the corresponding table cannot be found in the metadata. You can check which table_id 8920 corresponded to at that time by using set tidb_snapshot='2024-07-08 19:58:12', and then query the information_schema.tables table at that time where table_id=8920.
It can be basically confirmed that this table has not been created.
Then please follow the method I provided to check and confirm the table name first, and then use the table name to search in the dashboard’s log search function.
The diagram shows the display by region, and table_8920 is also decoded from the start_key. Therefore, when the table is relatively small, one region may involve multiple tables, and the table with the actual high traffic may not be this 8920 table.
Currently, I have been checking, but there is no table with id 8920 in the information_schema.tables table.
No, because there are still ongoing queries, not that they have already been deleted, so setting a snapshot point doesn’t seem to have much significance.
The current time is more than 10 minutes past, so it is probably not retrievable. I also want to know, for a table deleted 10 minutes ago, if you know the table ID, how can you reverse lookup the table name?
Why is it meaningless? Aren’t you trying to find out which table has the ID 8920? Truncate will change the table’s ID (of course, other operations might also do this).
|
username: 
|
username: MySQL [test]> select * from information_schema.tables where table_name='tt2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def | test | tt2 | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2024-07-05 10:50:51 | NULL | NULL | utf8mb4_bin | NULL | | | 1312 | NOT_SHARDED | NONCLUSTERED | NULL |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (20.28 sec)
MySQL [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2024-07-09 13:35:56 |
+---------------------+
1 row in set (0.00 sec)
MySQL [test]> truncate table tt2;
Query OK, 0 rows affected (0.54 sec)
MySQL [test]> select * from information_schema.tables where table_name='tt2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def | test | tt2 | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2024-07-05 10:50:51 | NULL | NULL | utf8mb4_bin | NULL | | | 1314 | NOT_SHARDED | NONCLUSTERED | NULL |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (0.01 sec)
MySQL [test]> show variables like '%snap%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| tidb_enable_analyze_snapshot | OFF |
| tidb_snapshot | |
+------------------------------+-------+
2 rows in set (0.00 sec)
MySQL [test]> set tidb_snapshot='2024-07-09 13:35:56';
Query OK, 0 rows affected (0.01 sec)
MySQL [test]> select * from information_schema.tables where table_name='tt2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def | test | tt2 | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2024-07-05 10:50:51 | NULL | NULL | utf8mb4_bin | NULL | | | 1312 | NOT_SHARDED | NONCLUSTERED | NULL |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (0.00 sec)
MySQL [test]> set tidb_snapshot='';
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> admin show ddl jobs where table_name='tt2';
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| 1315 | test | tt2 | truncate table | public | 2 | 1312 | 0 | 2024-07-09 13:36:10 | 2024-07-09 13:36:10 | 2024-07-09 13:36:10 | synced |
| 1313 | test | tt2 | truncate table | public | 2 | 1310 | 0 | 2024-07-09 13:34:42 | 2024-07-09 13:34:43 | 2024-07-09 13:34:43 | synced |
| 1311 | test | tt2 | truncate table | public | 2 | 1308 | 0 | 2024-07-09 13:34:41 | 2024-07-09 13:34:41 | 2024-07-09 13:34:41 | synced |
| 1309 | test | tt2 | truncate table | public | 2 | 1306 | 0 | 2024-07-09 13:34:38 | 2024-07-09 13:34:39 | 2024-07-09 13:34:39 | synced |
| 1307 | test | tt2 | create table | public | 2 | 1306 | 0 | 2024-07-05 10:50:51 | 2024-07-05 10:50:51 | 2024-07-05 10:50:51 | synced |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
5 rows in set (0.01 sec)
The admin show ddl jobs command can display the table ID, and the TiDB logs also record the table ID. Both of these are stored for a relatively long time.
I understand your point, but the premise is that the table we want to check has undergone operations such as drop or truncate that cause the table_id to change. If within the GC effective time, I can flashback to retrieve the data information before deletion.
In my current scenario, this table has continuous traffic, and GC can also proceed normally. Suppose a table is truncated or dropped, after the GC time has passed, it is impossible for anyone to still be able to check it, right? (If someone is checking, GC will not proceed.)
Take the start_key from the dashboard and use the following SQL to check:
select * from INFORMATION_SCHEMA.tikv_region_status where lower(start_key)='6e00000000000000f8';
|
username: 
|
username: