MySQL Bugs: #103158: mysql didn't auto collect the statistics and MODIFIED

Bug #103158	mysql didn't auto collect the statistics and MODIFIED_COUNTER have been reset
Submitted:	31 Mar 2021 3:11	Modified:	31 Mar 2021 13:24
Reporter:	swift swift	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.27	OS:	Linux
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	statistics not updated, the MODIFIED_COUNTER in INNODB_SYS_TABLESTATS changed in a wrong way

Description:
hi,i have a master database and two slave databases whose version is 5.6.27,recently we found out there's one table have not updated its statistics since last time collected statistics at 2020-10-01 21:15:00 in the master database,but both two slave database have updated the statistics in Feburary 2021.

How to repeat:
we have done these things below to check for the reason why the master database haven't update the statistics.

1.Check for the version and if the database set global variable innodb_stats_persistent=on?
mysql Server version: 5.6.27-log Source distribution
innodb_stats_persistent=on

2.Check parameter for INNODB_STATS_AUTO_RECALC and data variation.
INNODB_STATS_AUTO_RECALC=ON
last time statistics  n_rows:90388974 (this value is given from table  mysql.innodb_table_stats,which updated in 2020-10-01 21:15:00)
the actual n_rows: 256753265 (select count(*) from database.table; counted at 2021/03/31)

3.check what DML or DDL we have done recently:
Basically this table just insert data and no update or delete data,no DDL.
data variation is about 300000rows each day.

4.Check the MODIFIED_COUNTER from information_schema.INNODB_SYS_TABLESTATS if the value is greater than 10% of NUM_ROWS.
2021/03/30 recorded that MODIFIED_COUNTER=279536;
2021/03/31 recorded that MODIFIED_COUNTER=80109;
and confirmed that statistics haven't been updated.
the MODIFIED_COUNTER at least greater than 9040000 then statistics will be auto collected.

we did the same data collect from the two slave databases, and the value of MODIFIED_COUNTER at 2021/03/31 is greater than 2021/03/30.

Suggested fix:
we speculate that MODIFIED_COUNTER can't get a accurate value if there're parallel insert into this table,and we wonder why that MODIFIED_COUNTER have been reduced and the statistics haven't updated? 

We want to comfirm if the parallel DML will impact the MODIFIED_COUNTER,so the database can't auto collect statistics for the table,and why MODIFIED_COUNTER is reduced even the statistics haven't been update?

Hi,

You are using too old version of the database. You should for start update to latest 5.6 ( 5.6.51) as there is a lot of replication and storage engine related bugs fixed since 5.6.27 and if you can reproduce the problem on 5.6.51 we can see if there's anything we can do about it. 

Notice that 5.6 is, as described here:
https://www.mysql.com/support/eol-notice.html 
now only covered under sustaining support meaning that even if you can reproduce this with 5.6.51 I doubt much can be done about it.

My suggestion is that you explore upgrading to modern 8.x

kind regards
Bogdan