Hello all,

I am facing a weird issue with my DB table where its Index and table stats not getting updated with values automatically in MariaDB 10.6.7

The table has more than a billion records. On querying the stats of the table, I could not see any stats for the table that begin automatically calculated until we do a mysql restart or ANALYZE TABLE.

Once triggered we can able to see the index values are calculated and updated in this table

127.0.0.1:3307> SELECT * FROM mysql.innodb_table_stats ;
+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name                          | last_update         | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+
| DB            |   TableStats#P#p2023_04_11_13_00_00 | 2023-04-11 12:00:11 |       0 |                    1 |                        0 |
| DB            |   TableStats#P#p2023_04_11_14_00_00 | 2023-04-11 13:00:10 |       0 |                    1 |                        0 |
| DB            |   TableStats#P#p2023_04_11_15_00_00 | 2023-04-11 14:00:04 |       0 |                    1 |                        0 |
| DB            |   TableStats#P#p2023_04_11_16_00_00 | 2023-04-11 15:00:04 |       0 |                    1 |                        0 |
+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+

127.0.0.1:3307> SELECT * FROM mysql.innodb_index_stats;
+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+
| database_name | table_name                          | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                                           |
+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+
| DB            |   TableStats#P#p2023_04_11_16_00_00 | PRIMARY    | 2023-04-11 15:00:04 | n_diff_pfx01 |          0 |           1 | AgentId                                                  |
| DB            |   TableStats#P#p2023_04_11_16_00_00 | PRIMARY    | 2023-04-11 15:00:04 | n_diff_pfx02 |          0 |           1 | AgentId,rackNr                                           |
| DB            |   TableStats#P#p2023_04_11_16_00_00 | PRIMARY    | 2023-04-11 15:00:04 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index                       |
| DB            |   TableStats#P#p2023_04_11_16_00_00 | PRIMARY    | 2023-04-11 15:00:04 | size         |          1 |        NULL | Number of pages in the index                            |
+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+

As per the documentation https://mariadb.com/kb/en/innodb-persistent-statistics/, enabling innodb_stats_auto_recalc parameter should update the index value automatically which was not happening for me even though the changes are 10% of the row got affected within few days.

The same was working for other DB table which is of lower load.

Is there any way to debug why the calculation was not updated properly with values or any way to reduce the sample size?

Thanks and Regards,

Ragul R