maria-discuss team mailing list archive

[ragul rangarajan <ragulrangarajan@xxxxxxxxx>]

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