maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #06549
Re: mysql.innodb-table-stats-and mysql.innodb-index-stats are not updating properly
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ä <marko.makela@xxxxxxxxxxx>
wrote:
> On Wed, Apr 19, 2023 at 8:04 AM ragul rangarajan
> <ragulrangarajan@xxxxxxxxx> 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
>
References
