Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.3.28
-
None
Description
db01 Not collected since 2022, analyze ran on 7th Feb 2025
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
| TABLE_ID | NAME | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
|
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
| 471 | db_name/table_01 | Initialized | 58482748 | 2225989 | 2744374 | 38857 | 0 | 2 |
|
| 464 | db_name/table_02 | Uninitialized | 0 | 0 | 0 | 0 | 283 | 0 |
|
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
|
db02 Recently collected This is now master
 |
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
| TABLE_ID | NAME | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
|
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
| 471 | db_name/table_01 | Initialized | 58088451 | 2200325 | 2717616 | 1837429 | 0 | 8 |
|
| 464 | db_name/table_02 | Initialized | 275 | 19 | 2 | 7 | 283 | 8 |
|
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
db03 Not collected since 2022 analyze ran on 7th Feb 2025
|
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
| TABLE_ID | NAME | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
|
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
| 471 | db_name/table_01 | Initialized | 57317093 | 2226053 | 2748023 | 27326 | 0 | 1 |
|
| 464 | db_name/table_02 | Uninitialized | 0 | 0 | 0 | 0 | 283 | 0 |
|
+----------+--------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|
|
While source code says :
|
if (dict_stats_is_persistent_enabled(table)) {
|
if (counter > n_rows / 10 /* 10% */
|
&& dict_stats_auto_recalc_is_enabled(table))
|
|
So for table table_01 on DB01
The total number of rows in the table: 58,482,748
10% of this: 5,84,827
counter value: 38,857
This condition will only be true if counter > 584,827 and dict_stats_auto_recalc_is_enabled(table) is true. But since 38,857 < 584,827, the condition will not be met. So thats the possible reason why it show Uninitialized.
For table table_02
Why value shows 0 for each of the row, does that mean this table is having zero records ?
And where its DB02 shows 275 NUM_ROWS is db02 where MODIFIED_COUNTER is 7 less than 10% so InnoDB did not trigger an automatic statistics recalculation. The counter value for the related table table_02 was less than 10% of NUM_ROWS, which is likely why InnoDB did not trigger an automatic statistics recalculation.
MODIFIED_COUNTER =0 : A counter tracking the number of rows modified since the last statistics update.
So feels like this table wasn't meet the 10% of threshold value ever that's the reason why statistics re-calculation never happen.
The concern now is that automatic statistics calculations are based on a percentage, whereas the innodb_stats_modified_counter accepts only numeric values. Perhaps we can lower this value to trigger more frequent statistics recalculations, even when modifications are below 10%.
Attachments
Issue Links
- includes
-
-
- Open
-
-
MDEV-36063 Allow a configurable percentage threshold for InnoDB persistent statistics auto recalc (currently hard coded at 10%)
-
- Open
-
-
-
- Closed
-