Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 关于tidb的分区表日志太多问题
[Test Environment] TiDB
[TiDB Version] 7.1.2
[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
[Attachment: Screenshot/Log/Monitoring]
We frequently add and delete table partitions, and there are quite a few partitions. When deleting partitions, a lot of error logs are always written, affecting the normal viewing of logs. Is there any setting to prevent this type of log from being recorded?
This is the error log. Should this also be filtered out?
This error is meaningless. Every time a partition is deleted, an error occurs, prompting to analyze the table, and it’s especially long, listing all the partitions in the log.
This is probably because they are worried that after you delete the partition, the statistics are not updated, which leads to abnormal execution plan generation…
If there are many partitioned tables, this log will be very long.
Try manually executing analyze after deleting the partition.
Isn’t there any parameter to separate the logs? Just write to two log files! I remember there was something about separating application logs or something 
The error appears when deleting, and there is no chance to analyze.
It is indeed better to separate them. For example, if a table has thousands of partitions and you loop to delete them, it will currently write a massive amount of logs.
I personally think the original poster should not focus on the length of this log segment but should address this Error message.
It is clearly stated above that you need to execute analyze to fix it.
At present, there seems to be no problem. The database will analyze itself.
The conditions for the system to analyze by itself are relatively stringent.
When insert, delete, and update statements occur, TiDB will automatically update the total number of rows and the number of modified rows every 60 seconds. This information will be periodically persisted.
By default, when the ratio of modified rows to the total number of rows in a table with more than 1,000 rows exceeds 50%, ANALYZE will be executed in the background to automatically update the statistics of this table. For tables with fewer than 1,000 rows, the system will not analyze them.
Therefore, it is usually recommended to handle it manually or through periodic scripts if possible.
Automatic analysis has two conditions:
- The number of rows in the table is greater than 1000.
- modify/total > 50%
For tables with multiple partitions, the total number of rows is likely to be large, which makes it difficult to trigger automatic collection.
If it is an empty table you created for testing, it is also difficult to trigger automatic collection due to condition 1.
Probably, you meet one of these two conditions to some extent, and then this error keeps occurring.
It is also possible, but for partitioned tables, statistics are collected by partition. Theoretically, the drop partition operation should not affect the accuracy of the statistics.
It seems that the previous statistics were missing the statistical information of a certain field (draw_id) on a certain partition (p564) for unknown reasons. As a result, it has not been possible to establish global statistics.
The root cause needs to be traced further back.
Directly drop the table, then add partitions, insert data, delete partitions, now the error message has changed.
Use drop stats to manually delete the statistics of the corresponding table and see.
Both 55 and 58 are deleting partitions, but 58 is missing 2 IDs. If the DDL executed at these two time points is the same, it gives me a feeling that the stats_meta table is not being written properly.
At least this table structure does not have any measures to avoid write hotspots. The version field seems to store a TSO, so the version field is also inserted sequentially. Check the traffic visualization to see if there are any regions where this table is located that are very active in a short period of time.
What about the CREATE TABLE statement? For tables with frequent insert, update, and delete operations, especially partitioned tables, is there no local index?
It’s just a regular list partition table, and the operation is to drop a partition.
|
username: zhanggame1
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 