Thanks for the clarification Marko, Like to clarify a few things
- Able to see the issue only with the Partitioned tables and not with non-partitioned tables in MariaDB 10.6.7 while the same works in MariaDB 10.6.10 Is this fixed as part of MDEV-27805 <https://jira.mariadb.org/browse/MDEV-27805> ? - Trying to reproduce the issue with the dump file https://jira.mariadb.org/secure/attachment/65399/wptest.sql in MariaDB 10.6.7 but I don't see any issue with the index calculation as mentioned in the observation https://jira.mariadb.org/secure/attachment/65401/MDEV-28327_testing-10.8.4.txt . Am I missing something to reproduce the issue? But able to reproduce the same by turning off flags innodb_stats_auto_recalc <https://mariadb.com/kb/en/xtradbinnodb-server-system-variables/#innodb_stats_auto_recalc> & innodb_stats_persistent. Is this the expected behavior to reproduce? mysql wptest < wptest.sql mysql> > Server version: 10.6.7-MariaDB MariaDB Server > > Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. > > Type 'help;' or '\h' for help. Type '\c' to clear the current input > statement. > 127.0.0.1:3307> use wptest > Database changed > > 127.0.0.1:3307> show indexes from wp_options; > > +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > Index_comment | Ignored | > > +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ > | wp_options | 0 | PRIMARY | 1 | option_id | A > | 120 | NULL | NULL | | BTREE | | > | NO | > | wp_options | 0 | option_name | 1 | option_name | A > | 120 | NULL | NULL | | BTREE | | > | NO | > | wp_options | 1 | autoload | 1 | autoload | A > | 4 | NULL | NULL | | BTREE | | > | NO | > > +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ > 3 rows in set (0.000 sec) > > 127.0.0.1:3307> select TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH from > information_schema.tables where TABLE_SCHEMA = "wptest" and TABLE_NAME = > "wp_options"; > +------------+-------------+--------------+ > | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | > +------------+-------------+--------------+ > | 120 | 1589248 | 32768 | > +------------+-------------+--------------+ > 1 row in set (0.001 sec) > > 127.0.0.1:3307> select * from mysql.innodb_index_stats where > database_name="wptest" and table_name="wp_options"; > > +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ > | database_name | table_name | index_name | last_update | > stat_name | stat_value | sample_size | stat_description > | > > +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ > | wptest | wp_options | PRIMARY | 2023-04-21 10:22:08 | > n_diff_pfx01 | 120 | 1 | option_id > | > | wptest | wp_options | PRIMARY | 2023-04-21 10:22:08 | > n_leaf_pages | 1 | NULL | Number of leaf pages in the index > | > | wptest | wp_options | PRIMARY | 2023-04-21 10:22:08 | size > | 97 | NULL | Number of pages in the index | > | wptest | wp_options | autoload | 2023-04-21 10:22:08 | > n_diff_pfx01 | 2 | 1 | autoload > | > | wptest | wp_options | autoload | 2023-04-21 10:22:08 | > n_diff_pfx02 | 120 | 1 | autoload,option_id > | > | wptest | wp_options | autoload | 2023-04-21 10:22:08 | > n_leaf_pages | 1 | NULL | Number of leaf pages in the index > | > | wptest | wp_options | autoload | 2023-04-21 10:22:08 | size > | 1 | NULL | Number of pages in the index | > | wptest | wp_options | option_name | 2023-04-21 10:22:08 | > n_diff_pfx01 | 120 | 1 | option_name > | > | wptest | wp_options | option_name | 2023-04-21 10:22:08 | > n_leaf_pages | 1 | NULL | Number of leaf pages in the index > | > | wptest | wp_options | option_name | 2023-04-21 10:22:08 | size > | 1 | NULL | Number of pages in the index | > > +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ > 10 rows in set (0.001 sec) > > 127.0.0.1:3307> select * from mysql.innodb_table_stats where > database_name="wptest" and table_name="wp_options"; > > +---------------+------------+---------------------+--------+----------------------+--------------------------+ > | database_name | table_name | last_update | n_rows | > clustered_index_size | sum_of_other_index_sizes | > > +---------------+------------+---------------------+--------+----------------------+--------------------------+ > | wptest | wp_options | 2023-04-21 10:22:08 | 120 | > 97 | 2 | > > +---------------+------------+---------------------+--------+----------------------+--------------------------+ > 1 row in set (0.001 sec) > After disabling the flag, able to see the problem. 127.0.0.1:3307> Show variables like '%innodb_stats%'; +--------------------------------------+-------------+ > | Variable_name | Value | > +--------------------------------------+-------------+ > | innodb_stats_auto_recalc | OFF | > | innodb_stats_include_delete_marked | OFF | > | innodb_stats_method | nulls_equal | > | innodb_stats_modified_counter | 0 | > | innodb_stats_on_metadata | OFF | > | innodb_stats_persistent | ON | > | innodb_stats_persistent_sample_pages | 20 | > | innodb_stats_traditional | ON | > | innodb_stats_transient_sample_pages | 8 | > +--------------------------------------+-------------+ select * from mysql.innodb_index_stats where database_name="wptest" and > table_name="wp_options"; +------------+-------------+--------------+ | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | +------------+-------------+--------------+ | 0 | 16384 | 32768 | +------------+-------------+--------------+ 1 row in set (0.001 sec) 127.0.0.1:3307> 127.0.0.1:3307> select * from mysql.innodb_index_stats where database_name="wptest" and table_name="wp_options"; +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | wptest | wp_options | PRIMARY | 2023-04-21 14:07:53 | n_diff_pfx01 | 0 | 1 | option_id | | wptest | wp_options | PRIMARY | 2023-04-21 14:07:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | PRIMARY | 2023-04-21 14:07:53 | size | 1 | NULL | Number of pages in the index | | wptest | wp_options | autoload | 2023-04-21 14:07:53 | n_diff_pfx01 | 0 | 1 | autoload | | wptest | wp_options | autoload | 2023-04-21 14:07:53 | n_diff_pfx02 | 0 | 1 | autoload,option_id | | wptest | wp_options | autoload | 2023-04-21 14:07:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | autoload | 2023-04-21 14:07:53 | size | 1 | NULL | Number of pages in the index | | wptest | wp_options | option_name | 2023-04-21 14:07:53 | n_diff_pfx01 | 0 | 1 | option_name | | wptest | wp_options | option_name | 2023-04-21 14:07:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | option_name | 2023-04-21 14:07:53 | size | 1 | NULL | Number of pages in the index | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ 10 rows in set (0.000 sec) *Thanks and Regards,* *Ragul R* On Wed, Apr 19, 2023 at 12:16 PM Marko Mäkelä <[email protected]> wrote: > On Wed, Apr 19, 2023 at 8:04 AM ragul rangarajan > <[email protected]> wrote: > > 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 > > Hi Ragul! > The same question had been posted to > > https://stackoverflow.com/questions/76047397/mysql-innodb-table-stats-and-mysql-innodb-index-stats-are-not-updating-properly > where I posted a reply. I think that this is most likely due to > https://jira.mariadb.org/browse/MDEV-27805 and less likely > https://jira.mariadb.org/browse/MDEV-28327. Both bugs have been fixed > after the release of MariaDB Server 10.6.7. > > I recommend an upgrade to MariaDB Server 10.6.12 or the upcoming > 10.6.13 that should be out within a couple of weeks. > > Best regards, > -- > Marko Mäkelä, Lead Developer InnoDB > MariaDB plc >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
