Extremely Slow Query on Metadata Table

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

Original topic: 查元数据表巨慢

| username: 等一分钟

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.1
[Reproduction Path]

select * from (select TABLE_SCHEMA, TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'access' order by TABLE_NAME asc) vresult

The query is very slow, taking anywhere from 10 seconds to over 100 seconds.
Result set: 23 rows

Total rows: 63,663

| username: 等一分钟 | Original post link

Execution Plan

| username: 等一分钟 | Original post link

Where was the time spent?

| username: 托马斯滑板鞋 | Original post link

Check the load of each node on the dashboard?

| username: 等一分钟 | Original post link

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

| username: 等一分钟 | Original post link

The default value of tidb_gc_life_time is 10m, which means that data older than 10 minutes will be cleaned up. You can adjust this parameter according to your needs. For example, if you need to keep data for 1 hour, you can set it to 1h.

| username: 托马斯滑板鞋 | Original post link

:joy: Is your load a bit too high? Try using count(*) directly?

| username: 等一分钟 | Original post link

Count is also slow, as shown in the picture. Actually, the load is okay.

| username: 托马斯滑板鞋 | Original post link

Use EXPLAIN ANALYZE FORMAT='true_card_cost' to output an execution plan.

| username: 等一分钟 | Original post link

| username: 等一分钟 | Original post link

“explain analyze format=true_card_cost”

| username: 托马斯滑板鞋 | Original post link

:upside_down_face: Not sure if it’s due to the version, but in 7.5, the output includes specific CPU and memory overhead.

mysql> explain analyze format=true_card_cost select * from (select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'mysql' order by TABLE_NAME asc) vresult;
+---------------------------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+---------------+--------------------------------------------------+-----------------------+----------+---------+
| id                        | estRows  | estCost  | costFormula                                                                                                                                                                                                                                    | actRows | task | access object | execution info                                   | operator info         | memory   | disk    |
+---------------------------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+---------------+--------------------------------------------------+-----------------------+----------+---------+
| Projection_6              | 8000.00  | 77180.72 | (((cpu(852*filters(1)*tidb_cpu_factor(49.9))) + ()) + ((exprCPU(55*0*tidb_cpu_factor(49.9))) + (orderCPU(55*log(55)*tidb_cpu_factor(49.9)))) + (sortMem(55*1708*tidb_mem_factor(0.2)))) + ((cpu(55*filters(0.02)*tidb_cpu_factor(49.9)))/5.00) | 55      | root |               | time:9.24ms, loops:2, RU:0.000000, Concurrency:5 | Column#2, Column#3    | 77.9 KB  | N/A     |
| └─Sort_7                  | 8000.00  | 77169.74 | ((cpu(852*filters(1)*tidb_cpu_factor(49.9))) + ()) + ((exprCPU(55*0*tidb_cpu_factor(49.9))) + (orderCPU(55*log(55)*tidb_cpu_factor(49.9)))) + (sortMem(55*1708*tidb_mem_factor(0.2)))                                                          | 55      | root |               | time:9.14ms, loops:2                             | Column#3              | 250.4 KB | 0 Bytes |
|   └─Selection_9           | 8000.00  | 42514.80 | (cpu(852*filters(1)*tidb_cpu_factor(49.9))) + ()                                                                                                                                                                                               | 55      | root |               | time:9.05ms, loops:2                             | eq(Column#2, "mysql") | 297.5 KB | N/A     |
|     └─MemTableScan_10     | 10000.00 | 0.00     |                                                                                                                                                                                                                                                | 852     | root | table:TABLES  | time:8.95ms, loops:3                             |                       | N/A      | N/A     |
+---------------------------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+---------------+--------------------------------------------------+-----------------------+----------+---------+
4 rows in set, 4 warnings (0.02 sec)
| username: 托马斯滑板鞋 | Original post link

Try using trace select again?

trace select * from (select TABLE_SCHEMA, TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'access' order by TABLE_NAME asc) vresult;
mysql> trace select * from (select TABLE_SCHEMA, TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'mysql' order by TABLE_NAME asc) vresult;
+-------------------------------------------------------+-----------------+-------------+
| operation                                             | startTS         | duration    |
+-------------------------------------------------------+-----------------+-------------+
| trace                                                 | 15:33:27.484968 | 12.086123ms |
|   ├─session.ExecuteStmt                               | 15:33:27.485711 | 2.354008ms  |
|   │ ├─executor.Compile                                | 15:33:27.485728 | 270.31µs    |
|   │ └─session.runStmt                                 | 15:33:27.487302 | 344.126µs   |
|   ├─*executor.ProjectionExec.Next                     | 15:33:27.488073 | 8.934055ms  |
|   │ ├─*executor.SortExec.Next                         | 15:33:27.488149 | 8.830804ms  |
|   │ │ ├─*executor.SelectionExec.Next                  | 15:33:27.488170 | 8.724945ms  |
|   │ │ │ ├─*executor.MemTableReaderExec.Next           | 15:33:27.488173 | 8.583582ms  |
|   │ │ │ └─*executor.MemTableReaderExec.Next           | 15:33:27.496887 | 2.347µs     |
|   │ │ └─*executor.SelectionExec.Next                  | 15:33:27.496908 | 5.867µs     |
|   │ │   └─*executor.MemTableReaderExec.Next           | 15:33:27.496910 | 468ns       |
|   │ └─*executor.SortExec.Next                         | 15:33:27.496993 | 814ns       |
|   └─*executor.ProjectionExec.Next                     | 15:33:27.497014 | 1.05µs      |
+-------------------------------------------------------+-----------------+-------------+
13 rows in set (0.02 sec)
| username: 等一分钟 | Original post link

The image you provided is not visible. Please provide the text content that needs to be translated.

| username: 托马斯滑板鞋 | Original post link

Can you execute it on the terminal? Or post a complete one, so we can see exactly which step is slow.

| username: 托马斯滑板鞋 | Original post link

By the way, is TiDB constantly creating tables? :joy:

| username: 等一分钟 | Original post link

No, there isn’t.

| username: 等一分钟 | Original post link

Data is being synchronized.

| username: 托马斯滑板鞋 | Original post link

I don’t know if this is caused by frequently updating the metadata… I repeatedly tested it on an idle test database, and the execution plan is always the same (the schema keeps changing).

| username: wangccsy | Original post link

In my virtual machine environment (experiment), directly running the SQL statement in parentheses took 0.01 seconds, while running your complete SQL statement took 0.02 seconds. There are 637 records. The only difference is that I capitalized all the keywords (such as SELECT FROM).