Querying INFORMATION_SCHEMA.COLUMNS for single table metadata is slow

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

Original topic: 查询INFORMATION_SCHEMA.COLUMNS 单表元数据慢

| username: CHENGX

【TiDB Usage Environment】Production Environment
【TiDB Version】v7.5.1
【Reproduction Path】What operations were performed to cause the issue
【Encountered Issue: Problem Phenomenon and Impact】
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

This SQL takes 3-5 seconds to execute, and the more it is executed, the slower it gets.

| username: FutureDB | Original post link

EXPLAIN ANALYZE to see the actual execution plan.

| username: CHENGX | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: CHENGX | Original post link

Too slow

| username: CHENGX | Original post link

DatabaseMetaData metaData = conn.getMetaData();
tableFieldResultSet = metaData.getColumns(catalog, schema, tableName, “%”);

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

Do you have a lot of tables in your cluster? Check the count(*) of the columns table.

| username: 友利奈绪 | Original post link

When querying the single table metadata in INFORMATION_SCHEMA.COLUMNS in TiDB becomes slow, there are several possible reasons to consider:

  1. Inaccurate Statistics: If the statistics are inaccurate or outdated, TiDB may generate suboptimal execution plans. You can try using the ANALYZE TABLE statement to update the table’s statistics to help optimize the execution plan.
  2. Query Optimization: Check whether the query statement can be optimized. Sometimes, complex queries or queries without appropriate indexes may cause the query to slow down.
  3. Resource Constraints: Check if system resources such as CPU, memory, and disk I/O are constrained. If resources are limited, TiDB may not be able to execute queries efficiently.
  4. System Load: If the system load is high, query performance may be affected. Ensure that the system load is within an acceptable range. If possible, consider increasing hardware resources or optimizing query distribution.
  5. TiDB Version: Ensure that you are using the latest version of TiDB and that any relevant performance optimization patches or updates have been applied.
| username: vincentLi | Original post link

TiDB should indeed learn more from Oracle in terms of table metadata management. I might be wrong because I haven’t deeply researched why querying metadata is so inefficient. However, from the phenomenon, it is indeed quite confusing. In our production database, the top SQL queries, surprisingly, 50% are slow queries to information_schema, which is quite interesting.

| username: 鱼跃龙门 | Original post link

Sometimes querying metadata in TiDB is very slow. I wonder if it’s due to TiDB’s own limitations.

| username: CHENGX | Original post link

The quantity is not much, around a few hundred thousand.

| username: CHENGX | Original post link

This table seems to be a system table and cannot be analyzed.

| username: CHENGX | Original post link

The issue only became apparent after upgrading from 6.5.3 to 7.5.1. This problem was not observed in version 6, but after upgrading to 7.5.1, it has affected functionality.

| username: 濱崎悟空 | Original post link

It could be a version issue.

| username: FutureDB | Original post link

Check the execution_info to see which step takes the most time.

| username: 小于同学 | Original post link

Execution plan?

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

You can set up another test environment, just migrate the table structure and see if the system tables are still slow…

| username: CHENGX | Original post link

This is a picture from the slow query.

| username: CHENGX | Original post link

In our test environment, the columns table has around 80,000 rows, and queries take 1-2 seconds.

| username: CHENGX | Original post link

When querying a large amount of table structure information, this SQL can slow down to 9 seconds.

| username: TiDBer_H5NdJb5Q | Original post link

The schema shouldn’t change much, why is it so slow?