[Maria-discuss] mysql.innodb-table-stats-and mysql.innodb-index-stats are not updating properly
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
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
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
participants (2)
-
Marko Mäkelä -
ragul rangarajan