Querying metadata from information_schema.columns is very slow

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

Original topic: 查询元数据information_schema.columns很慢

| username: wakaka

【TiDB Usage Environment】Production Environment
【TiDB Version】5.2.2
【Encountered Problem】
Querying the metadata columns table is very slow
【Reproduction Path】
【Problem Phenomenon and Impact】
No bottleneck in machine resource performance, querying the columns table is very slow


Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

| username: OnTheRoad | Original post link

information_schema.columns is a virtual table. When queried, TiDB dynamically constructs this table based on the data. If the data volume is large, it will indeed be slow.

| username: wakaka | Original post link

Is it normal for the data volume shown in the picture to be this slow? Are there any ways to optimize it?

| username: wuxiangdong | Original post link

Why does executing this statement result in warnings?

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

It’s normal to be slow.

| username: wakaka | Original post link

1111.log (4.1 MB)
Uploaded trace format=‘row’ select count(*) from information_schema.columns where table_schema=‘wms_core’; The result is in the attachment.

| username: wakaka | Original post link

I found a region ID with 4680 occurrences. This region contains dozens of tables and indexes, all with very small amounts of data, so it feels like a merged region. I wonder if the slow metadata query is related to this?

| username: h5n1 | Original post link

It feels like a bug. In version 5.2.3, there are over 40,000 rows in the columns trace result, but there isn’t a single region request. This bug is fixed in version 5.2.4.

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

It’s a bug.