Slow System Table Query

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

Original topic: 系统表查询慢

| username: porpoiselxj

【TiDB Version】 v7.1.1
【Impact of the Bug】 Queries on many system tables are very slow. The slow query list is full of system table query statements, overshadowing the slow queries that actually need attention. The execution plans show memory scans, which is confusing.

【Possible Steps to Reproduce the Issue】

If you have similar concerns, please join the discussion.

Here is a text version of the execution plan:

explain SELECT  COLUMN_NAME,  DATA_TYPE,  CHARACTER_MAXIMUM_LENGTH,  NUMERIC_PRECISION,  NUMERIC_SCALE,  IS_NULLABLE FROM  INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = UPPER('xxxx')  AND TABLE_NAME = UPPER('TB_xxxx');
+----------------------+----------+------+---------------+--------------------------------------------------------------+
| id                   | estRows  | task | access object | operator info                                                |
+----------------------+----------+------+---------------+--------------------------------------------------------------+
| Projection_4         | 10000.00 | root |               | Column#4, Column#8, Column#9, Column#11, Column#12, Column#7 |
| └─MemTableScan_5     | 10000.00 | root | table:COLUMNS | table_schema:["xxxx"], table_name:["tb_xxxx]         |
+----------------------+----------+------+---------------+--------------------------------------------------------------+
2 rows in set (0.00 sec)
| username: Billmay表妹 | Original post link

Could you please post the execution plan?

| username: tidb菜鸟一只 | Original post link

This is indeed a common issue… Since system tables are not real physical tables but memory tables, they are scanned in memory. However, if the cluster is large and there are many tables, the query can indeed be slow.

| username: WinterLiu | Original post link

This shouldn’t affect the business, right?

| username: zhang_2023 | Original post link

Post the execution plan.

| username: WalterWj | Original post link

TiDB hasn’t done any specific optimizations for system queries.

Everyone can list the SQL queries that frequently check system tables. We can also involve the PM to take a look.
Optimizing based on specific scenarios is relatively easier to implement.

| username: xfworld | Original post link

It’s always very slow… I can complain.

| username: come_true | Original post link

Expand memory

| username: WalterWj | Original post link

Will it be faster if the SQL upper is removed?

| username: 双开门变频冰箱 | Original post link

Is it possible for system tables to have no indexes?

| username: ywqzzy | Original post link

Could you provide a more detailed execution plan screenshot?

| username: porpoiselxj | Original post link

As long as no conversion is done on the fields in the table, it will not be affected.

| username: ywqzzy | Original post link

The self-tested flame graph shows that the permission verification overhead is relatively high and needs to be optimized.

| username: ywqzzy | Original post link

In my test scenario, there is not much difference.

| username: porpoiselxj | Original post link

The official team has optimized the query performance of system tables in version 8.0. Looking forward to the new version.

| username: system | Original post link

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