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.t... . 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ä <marko.makela@mariadb.com> wrote:
On Wed, Apr 19, 2023 at 8:04 AM ragul rangarajan <ragulrangarajan@gmail.com> 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-my... 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