Description:
When restoring all database from Fullbackup(mysqldump --all-databases), all data of innodb_index_stats, innodb_table_stats are reset. So need to analyze all InnoDB Tables for restore innodb_%_stats.
When making 1000-row table 'test.t1',
[Before restoring]
mysql [localhost] {msandbox} (mysql) > select * from innodb_table_stats where database_name = 'test';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test | t1 | 2020-01-10 13:45:45 | 1000 | 4 | 0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (mysql) > select * from innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | n_diff_pfx01 | 1000 | 3 | DB_ROW_ID |
| test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | n_leaf_pages | 3 | NULL | Number of leaf pages in the index |
| test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | size | 4 | NULL | Number of pages in the index |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
[After restoring]
mysql [localhost] {msandbox} ((none)) > select * from mysql.innodb_table_stats where database_name = 'test';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test | t1 | 2020-01-10 13:56:53 | 0 | 1 | 0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
mysql [localhost] {msandbox} ((none)) > select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
How to repeat:
(1) Making 8.0.x environment with mysql sandbox, like this.
make_sandbox 8.0.17
(2) Making backup command like this. Replace /bin/mysql -> /bin/mysqldump (Line.24)
cp use backup
# and replace Line.24
(3) Activate mysql commanline tool, and making 'test.t1' table with 1000-row.
./use test
create table t1 SELECT @seq_no := 1 AS seq_no UNION SELECT @seq_no := @seq_no + 1 AS seq_no FROM information_schema.COLUMNS LIMIT 1000;
(4) Check statistics.
select * from mysql.innodb_table_stats where database_name = 'test';
select * from mysql.innodb_index_stats where database_name = 'test';
(5) Making backup and restore.
./backup --all-databases > fullbackup.sql
./use < fullbackup.sql
(6) Re-Check statistics.
select * from mysql.innodb_table_stats where database_name = 'test';
select * from mysql.innodb_index_stats where database_name = 'test';
Suggested fix:
[Cause]
When making backup, in client/mysqldump.cc, these codes add for not modifying innodb_%_stats;
693 if (stats_tables_included) {
694 fprintf(sql_file,
695 "/*!50606 SET "
696 "@OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;\n");
697 fprintf(sql_file,
698 "/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;\n");
699 }
So dump file became as result.
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
(snip)
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;
When restoring mysql database, it works,
But subsequent user tables, drop & create tables for --opt option(default), so all user innodb table's stats reset :(
[Workaround] Do one of these workaround.
(1) Change this line of dump/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
into /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=ON */;
(2) After restoring, issue 'ANALYZE TABLE' for all InnoDB type tables.
(3) Truncate these tables, and re-INSERT from dump. (Need to edit a dump by hand.
| innodb_index_stats |
| innodb_table_stats |
[Suggested fix]
If backup file include user innodb tables and (stats_tables_included), add INNODB_STATS_AUTO_RECALC=ON instead of OFF.
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=ON */;
(snip)
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;
Description: When restoring all database from Fullbackup(mysqldump --all-databases), all data of innodb_index_stats, innodb_table_stats are reset. So need to analyze all InnoDB Tables for restore innodb_%_stats. When making 1000-row table 'test.t1', [Before restoring] mysql [localhost] {msandbox} (mysql) > select * from innodb_table_stats where database_name = 'test'; +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | test | t1 | 2020-01-10 13:45:45 | 1000 | 4 | 0 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (mysql) > select * from innodb_index_stats where database_name = 'test'; +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | n_diff_pfx01 | 1000 | 3 | DB_ROW_ID | | test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | n_leaf_pages | 3 | NULL | Number of leaf pages in the index | | test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | size | 4 | NULL | Number of pages in the index | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec) [After restoring] mysql [localhost] {msandbox} ((none)) > select * from mysql.innodb_table_stats where database_name = 'test'; +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | test | t1 | 2020-01-10 13:56:53 | 0 | 1 | 0 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > mysql [localhost] {msandbox} ((none)) > select * from mysql.innodb_index_stats where database_name = 'test'; +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID | | test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | size | 1 | NULL | Number of pages in the index | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec) How to repeat: (1) Making 8.0.x environment with mysql sandbox, like this. make_sandbox 8.0.17 (2) Making backup command like this. Replace /bin/mysql -> /bin/mysqldump (Line.24) cp use backup # and replace Line.24 (3) Activate mysql commanline tool, and making 'test.t1' table with 1000-row. ./use test create table t1 SELECT @seq_no := 1 AS seq_no UNION SELECT @seq_no := @seq_no + 1 AS seq_no FROM information_schema.COLUMNS LIMIT 1000; (4) Check statistics. select * from mysql.innodb_table_stats where database_name = 'test'; select * from mysql.innodb_index_stats where database_name = 'test'; (5) Making backup and restore. ./backup --all-databases > fullbackup.sql ./use < fullbackup.sql (6) Re-Check statistics. select * from mysql.innodb_table_stats where database_name = 'test'; select * from mysql.innodb_index_stats where database_name = 'test'; Suggested fix: [Cause] When making backup, in client/mysqldump.cc, these codes add for not modifying innodb_%_stats; 693 if (stats_tables_included) { 694 fprintf(sql_file, 695 "/*!50606 SET " 696 "@OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;\n"); 697 fprintf(sql_file, 698 "/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;\n"); 699 } So dump file became as result. /*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */; /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */; (snip) /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */; When restoring mysql database, it works, But subsequent user tables, drop & create tables for --opt option(default), so all user innodb table's stats reset :( [Workaround] Do one of these workaround. (1) Change this line of dump/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */; into /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=ON */; (2) After restoring, issue 'ANALYZE TABLE' for all InnoDB type tables. (3) Truncate these tables, and re-INSERT from dump. (Need to edit a dump by hand. | innodb_index_stats | | innodb_table_stats | [Suggested fix] If backup file include user innodb tables and (stats_tables_included), add INNODB_STATS_AUTO_RECALC=ON instead of OFF. /*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */; /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=ON */; (snip) /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;