Why are there so many slow SQL UPDATEs on mysql.stats_meta in TiDB?

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

Original topic: tidb 非常多慢SQL UPDATE mysql.stats_meta 这些是什么原因?

| username: TiDBer_y9IRzLWc

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

From the monitoring, we found a lot of slow SQL queries, such as this one: update mysql.stats_meta set version = 443474762501455913, count = count + 6122, modify_count = modify_count + 18426 where table_id = 28672;
How are these generated? How can they be turned off?

| username: WalterWj | Original post link

System SQL doesn’t need to be considered. The slowness is due to the limited flow execution of system SQL.

| username: zhanggame1 | Original post link

Your database doesn’t have a load, right?

| username: TiDBer_y9IRzLWc | Original post link

Yes, it’s a cluster.

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

Try collecting the statistics for this table: analyze table mysql.stats_meta;.

| username: cassblanca | Original post link

This is an internal system operation. How can we ensure system consistency and availability when shutting down system metadata maintenance?

| username: TiDBer_vfJBUcxl | Original post link

By default, SQL queries that take more than 300ms to execute are considered slow queries and are recorded in the slow query log. You can query the recorded slow queries using this feature. The slow query threshold can be adjusted by modifying the tidb_slow_log_threshold SESSION variable or the TiDB slow-threshold parameter.


If the slow query log is disabled, this feature will not be available. The slow query log is enabled by default and can be enabled or disabled by modifying the TiDB configuration enable-slow-log.

| username: TiDBer_vfJBUcxl | Original post link

In the Slow Query section of the TiDB Dashboard, you can also view the execution time of each stage of the SQL, including parsing time, time spent generating the execution plan, Coprocessor execution time, and so on. By comparing with normal periods, you can pinpoint which stage is causing the increase in SQL execution time. In the submenu for Coprocessor reads, you can also view the number of keys scanned by the SQL.

| username: TiDBer_vfJBUcxl | Original post link

[TiDB Dashboard Slow Query Page] https://mbd.baidu.com/ma/s/gyfYgdtM

| username: redgame | Original post link

The ANALYZE TABLE command manually collects statistics.