| Bug #116711 | Incorrect last_update time of innodb_index_stats | ||
|---|---|---|---|
| Submitted: | 19 Nov 2024 12:47 | Modified: | 19 Nov 2024 15:26 |
| Reporter: | Steven Duan (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Optimizer, statistics state | ||
[19 Nov 2024 12:47]
Steven Duan
[19 Nov 2024 12:48]
Steven Duan
bugfix patch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-bugfix-Incorrect-last_update-time-of-innodb_index_st.patch (application/octet-stream, text), 3.17 KiB.
[19 Nov 2024 12:50]
Steven Duan
this bug may introduced by the commit 56e6cdfc45a01aec87111eb97430ca6aab85e302 Solution: ========= Fix the code bug to ensure that updating one index does not affect the statistics of other indexes.
[19 Nov 2024 14:14]
MySQL Verification Team
Hi Mr. Duan, Thank you for your bug report. However, we were unable to repeat it with out own binary for 8.0.40. Version 5.7 is no longer supported. mysql> create table t1 (c1 int primary key,c2 int ,c3 int, index idx1(c2)); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values (1,1,1),(2,2,2),(3,3,3),(4,4,4); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> analyze table t1; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> select last_update from mysql.innodb_index_stats where table_name = 't1' and index_name = 'PRIMARY' and stat_name = 'n_diff_pfx01'; +---------------------+ | last_update | +---------------------+ | 2024-11-19 16:11:17 | +---------------------+ 1 row in set (0.00 sec) mysql> select sleep(3); +----------+ | sleep(3) | +----------+ | 0 | +----------+ 1 row in set (3.03 sec) mysql> alter table t1 add key idx2(c3); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select last_update from mysql.innodb_index_stats where table_name = 't1' and index_name = 'PRIMARY' and stat_name = 'n_diff_pfx01'; +---------------------+ | last_update | +---------------------+ | 2024-11-19 16:11:20 | +---------------------+ 1 row in set (0.00 sec) As you can see last_update value is correct. Can't repeat.
[19 Nov 2024 14:22]
Steven Duan
It was also rerepeated under 8.0.40. It should be noted that after 'alter table', the index statistics of the primary key are not triggered to update, which means that the 'last_update' value should not change. But in reality, based on your repeat results, last_update time has changed. ``` mysql> select last_update from mysql.innodb_index_stats where table_name = 't1' and index_name = 'PRIMARY' and stat_name = 'n_diff_pfx01'; +---------------------+ | last_update | +---------------------+ | 2024-11-19 16:11:17 | +---------------------+ 1 row in set (0.00 sec) mysql> select last_update from mysql.innodb_index_stats where table_name = 't1' and index_name = 'PRIMARY' and stat_name = 'n_diff_pfx01'; +---------------------+ | last_update | +---------------------+ | 2024-11-19 16:11:20 | +---------------------+ 1 row in set (0.00 sec) ```
[19 Nov 2024 14:52]
MySQL Verification Team
Hi Mr. Duan, Actually, this seems to be expected behaviour. These are full outputs from the index statistics output. Here are both of them: First one: select * from mysql.innodb_index_stats where table_name='t1'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | t1 | PRIMARY | 2024-11-19 16:40:29 | n_diff_pfx01 | 4 | 1 | c1 | | test | t1 | PRIMARY | 2024-11-19 16:40:29 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | PRIMARY | 2024-11-19 16:40:29 | size | 1 | NULL | Number of pages in the index | | test | t1 | idx1 | 2024-11-19 16:40:29 | n_diff_pfx01 | 4 | 1 | c2 | | test | t1 | idx1 | 2024-11-19 16:40:29 | n_diff_pfx02 | 4 | 1 | c2,c1 | | test | t1 | idx1 | 2024-11-19 16:40:29 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | idx1 | 2024-11-19 16:40:29 | size | 1 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 7 rows in set (0.00 sec) and second one: select * from mysql.innodb_index_stats where table_name='t1'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | t1 | PRIMARY | 2024-11-19 16:40:32 | n_diff_pfx01 | 4 | 1 | c1 | | test | t1 | PRIMARY | 2024-11-19 16:40:32 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | PRIMARY | 2024-11-19 16:40:32 | size | 1 | NULL | Number of pages in the index | | test | t1 | idx1 | 2024-11-19 16:40:32 | n_diff_pfx01 | 4 | 1 | c2 | | test | t1 | idx1 | 2024-11-19 16:40:32 | n_diff_pfx02 | 4 | 1 | c2,c1 | | test | t1 | idx1 | 2024-11-19 16:40:32 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | idx1 | 2024-11-19 16:40:32 | size | 1 | NULL | Number of pages in the index | | test | t1 | idx2 | 2024-11-19 16:40:32 | n_diff_pfx01 | 4 | 1 | c3 | | test | t1 | idx2 | 2024-11-19 16:40:32 | n_diff_pfx02 | 4 | 1 | c3,c1 | | test | t1 | idx2 | 2024-11-19 16:40:32 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | idx2 | 2024-11-19 16:40:32 | size | 1 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 11 rows in set (0.00 sec) As you can see the update time seems to be applied to the entire table. This is also documented here: https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html You can also see that this is valid for entire table only, from this example: --------------------------------------------------------------------------------------- mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 't1'\G *************************** 1. row *************************** database_name: test table_name: t1 last_update: 2014-03-14 14:36:34 n_rows: 5 clustered_index_size: 1 sum_of_other_index_sizes: 2 ------------------------------------------------------------------------------------------ Hence, this is how this feature was designed. Updating this column to be on per index basis could be a feature request, at best .......
[19 Nov 2024 14:53]
MySQL Verification Team
Hi, If you wish, we can verify this report as a feature request.
[19 Nov 2024 15:20]
Steven Duan
From the document, the meaning of 'last_update' is "A timestamp indicating the last time the row was updated".
When executing the 'alter table add index', only the statistics of newly added indexes will be added, and statistical information of other indexes will not be collected. The other indexes only updated the 'last_update' time and did not actually update the statistical values. This does not match the actual update time stated in the document.
In other words, if the last_update column is updated, the statistical values should also be updated to the latest value of the current time, but the actual statistical values (stat_value) have not been updated.
That is to say, either the last_update time is not updated, or the last_update time is updated but the statistical values are also updated.
```
create table t1 (c1 int primary key,c2 int ,c3 int, index idx1(c2));
insert into t1 values (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),(11,11,11),(12,12,12),(13,13,13);
analyze table t1;
select * from mysql.innodb_index_stats where table_name = 't1' and index_name = 'PRIMARY' and stat_name = 'n_diff_pfx01';
sleep 2;
insert into t1 values (14,14,14);
alter table t1 add key idx2(c3);
# current last_update should equal the value saved before
# or current stat_value should updated to the latest value
select * from mysql.innodb_index_stats where table_name = 't1' and index_name = 'PRIMARY' and stat_name = 'n_diff_pfx01';
```
In addition, from the actual code perspective, the comments of the function also indicate that only the update of the newly added index will be triggered (see "@param[in] only_for_index if this is non-NULL, then stats for indexes that are not equal to it will not be saved"). However, the actual running effect does not match the annotations. So, this looks more like a bug.
```
/** Save the table's statistics into the persistent statistics storage.
@param[in] table_orig table whose stats to save
@param[in] only_for_index if this is non-NULL, then stats for indexes
that are not equal to it will not be saved, if NULL, then all indexes' stats
are saved
@return DB_SUCCESS or error code */
dberr_t dict_stats_save(dict_table_t *table_orig,
const index_id_t *only_for_index)
```
[19 Nov 2024 15:26]
MySQL Verification Team
Hi Mr. Duan, What you stated is correct, but only for the new indices. This report is verified as a feature request, but a team in charge will determine whether it will be feature request, severity S4 or severity of S3. We shall write an e-mail to the team in charge about the internal bug report, that we shall create from your report. Verified as a feature request, for the time being.
