Under what circumstances will modifycount be greater than count?

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

Original topic: 什么情况下modifycount会大于count?

| username: Kongdom

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.4
[Reproduction Path] None. The table is frequently updated with a small number of inserts.

As mentioned, why does this situation occur?

image

| username: 我是咖啡哥 | Original post link

Multiple updates on the same row?

| username: ajin0514 | Original post link

Let’s try an example.

| username: ShawnYan | Original post link

REPLACE INTO?

| username: xfworld | Original post link

Is it because the statistics haven’t been updated, resulting in a negative health score?

| username: Kongdom | Original post link

There is such a situation, but I feel that this situation should be normal.

| username: Kongdom | Original post link

There is no such way of writing.

| username: Kongdom | Original post link

There are suspicions in this regard. Currently, we are manually forcing analyze table, but we haven’t found out why there is no automatic analyze.

| username: xfworld | Original post link

Because it is a negative number, it does not meet the rules for statistical collection, so it was discarded… :slightly_smiling_face:

| username: Kongdom | Original post link

That’s true, there is no automatic collection of statistics now. So I want to see what causes the negative values.

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

If you update all the data in a table twice, should modify_count show the count or twice the count?

| username: ajin0514 | Original post link

Negative numbers do not conform to the collected statistics.

| username: Kongdom | Original post link

However, there is a very obvious flaw with this approach. After a full update, this table will never trigger the automatic analyze table again.

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

Isn’t “healthy” supposed to automatically collect statistics when it’s less than a certain value? Does it still need to be greater than 0?

| username: TiDBer_小阿飞 | Original post link

You need to set up automatic analyze.

| username: Kongdom | Original post link

:sweat_smile: Currently, none of these tables have been automatically analyzed. We have already planned to implement automatic checks and automatic analysis at the application layer.

| username: 有猫万事足 | Original post link

In the example of this issue, it is an insert, but if an update is executed, such an accumulation should cause ModifyCount to be greater than RowCount.

I guess this table used to have normal automatic analysis, and it might be the updates executed during the automatic analysis that eventually led to a day when ModifyCount became greater than RowCount, and then automatic analysis would no longer be executed.

| username: Kongdom | Original post link

Yes, that’s possible. Right now, it feels a bit unbelievable; this issue is quite serious.

| username: gcworkerishungry | Original post link

For example, ModifyCount/RowCount > 0.3

| username: Kongdom | Original post link

:sweat_smile: What is this talking about?