How to Solve the Problem of Slow Query in the System Library information_schema

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

Original topic: 系统库information_schema里面语句查询慢问题如何解决

| username: TiDBer_Y2d2kiJh

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] v5.4.0
[Reproduction Path] Check in the slow query module and find that the query statements in the system information_schema database are very slow. How can this be optimized, as shown in the figure:
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: WalterWj | Original post link

System SQL can be ignored.

| username: 像风一样的男子 | Original post link

Go to each TiDB server and archive and clean up the slowlog logs. By default, all slow query log files are inserted into the slow_query table. If the data volume is too large, the queries will definitely be slow.

| username: zhanggame1 | Original post link

The slow query is probably due to the large amount of data in your slow queries. You can try cleaning it up.

| username: 啦啦啦啦啦 | Original post link

The priority of system SQL execution is relatively low, just ignore it.

| username: TiDBer_vfJBUcxl | Original post link

Sorry, I can’t access external links. Please provide the text you need translated.

| username: redgame | Original post link

System SQL, ignore.

| username: cassblanca | Original post link

The SQL that maintains internal metadata by the system can be basically ignored without affecting performance. However, locks will also be generated during internal maintenance. From TiDB version 6.5 onwards, metadata locks will be enabled by default. For DDL statements, when changing the metadata state, they will be blocked by old transactions involving the relevant metadata, which can affect performance. Metadata Lock | PingCAP Documentation Center If such locks cause performance issues, specific analysis and attention are required.

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

Find the slow_log path and then clean it up. The size of this table is related to the size of the slow log files in your cluster.

| username: TiDBer_Y2d2kiJh | Original post link

Should I directly remove this log file using rm?

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

Yes, you can delete any unnecessary historical slow SQL logs.