[MDEV-7360] alter table doesn't regenerate persistent engine-independent-index stats Created: 2014-12-22  Updated: 2014-12-22  Due: 2015-01-15  Resolved: 2014-12-22

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

With innodb_stats_auto_recalc=1 persistent statistics are meant to be updated when more that 10% of rows change (and other criteria). Using an ALTER TABLE statement changes 100% of rows and persistent stats aren't created in the process.

MariaDB [oq_drupal]> show global variables like '%stats%';
 
+--------------------------------------+---------------+
 
| Variable_name                        | Value         |
 
+--------------------------------------+---------------+
 
| aria_stats_method                    | nulls_unequal |
 
| innodb_stats_auto_recalc             | ON            |
 
| 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_sample_pages            | 8             |
 
| innodb_stats_traditional             | ON            |
 
| innodb_stats_transient_sample_pages  | 8             |
 
| myisam_stats_method                  | nulls_unequal |
 
+--------------------------------------+---------------+
 
11 rows in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> show global variables like '%use_stat%';
 
+-----------------+---------------+
 
| Variable_name   | Value         |
 
+-----------------+---------------+
 
| use_stat_tables | COMPLEMENTARY |
 
 
 
 
 
MariaDB [oq_drupal]> alter table zipcodes STATS_PERSISTENT=1;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [oq_drupal]> show create table  mysql.table_stats; 
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table       | Create Table                                                                                                                                                                                                                                                                                                        |
 
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| table_stats | CREATE TABLE `table_stats` (
 
  `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
 
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
 
  `cardinality` bigint(21) unsigned DEFAULT NULL,
 
  PRIMARY KEY (`db_name`,`table_name`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' |
 
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> select * from mysql.table_stats;     
+-----------+------------+-------------+
 
| db_name   | table_name | cardinality |
 
+-----------+------------+-------------+
 
| oq_drupal | watchdog   |         742 |
 
| oq_drupal | url_alias  |        8974 |
 
+-----------+------------+-------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> alter table zipcodes add column x varchar(100);
 
Query OK, 0 rows affected (0.06 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [oq_drupal]> select * from mysql.table_stats;               
+-----------+------------+-------------+
 
| db_name   | table_name | cardinality |
 
+-----------+------------+-------------+
 
| oq_drupal | watchdog   |         742 |
 
| oq_drupal | url_alias  |        8974 |
 
+-----------+------------+-------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> show create table zipcodes;  
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
 
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| zipcodes | CREATE TABLE `zipcodes` (
 
  `zip` varchar(16) NOT NULL DEFAULT '0',
 
  `city` varchar(30) NOT NULL DEFAULT '',
 
  `state` varchar(30) NOT NULL DEFAULT '',
 
  `latitude` decimal(10,6) NOT NULL DEFAULT '0.000000',
 
  `longitude` decimal(10,6) NOT NULL DEFAULT '0.000000',
 
  `timezone` tinyint(4) NOT NULL DEFAULT '0',
 
  `dst` tinyint(4) NOT NULL DEFAULT '0',
 
  `country` char(2) NOT NULL DEFAULT '',
 
  `x` varchar(100) DEFAULT NULL,
 
  KEY `pc` (`country`,`zip`),
 
  KEY `zip` (`zip`),
 
  KEY `latitude` (`latitude`),
 
  KEY `longitude` (`longitude`),
 
  KEY `country` (`country`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=1 |
 
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> insert into zipcodes values ('2323','insyd','syd', -35.22, 150, 'AEST', '0', 'aust',NULL);      
Query OK, 1 row affected, 2 warnings (0.00 sec)
 
 
 
MariaDB [oq_drupal]> select * from mysql.table_stats;
 
+-----------+------------+-------------+
 
| db_name   | table_name | cardinality |
 
+-----------+------------+-------------+
 
| oq_drupal | watchdog   |         742 |
 
| oq_drupal | url_alias  |        8974 |
 
+-----------+------------+-------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> alter table zipcodes drop column x;
 
Query OK, 0 rows affected (0.05 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [oq_drupal]> select * from mysql.table_stats;
 
+-----------+------------+-------------+
 
| db_name   | table_name | cardinality |
 
+-----------+------------+-------------+
 
| oq_drupal | watchdog   |         742 |
 
| oq_drupal | url_alias  |        8974 |
 
+-----------+------------+-------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> select * from mysql.index_stats;
 
+-----------+------------+------------------+--------------+---------------+
 
| db_name   | table_name | index_name       | prefix_arity | avg_frequency |
 
+-----------+------------+------------------+--------------+---------------+
 
| oq_drupal | watchdog   | PRIMARY          |            1 |        1.0000 |
 
| oq_drupal | watchdog   | type             |            1 |      106.0000 |
 
| oq_drupal | watchdog   | type             |            2 |        1.0000 |
 
| oq_drupal | url_alias  | PRIMARY          |            1 |        1.0000 |
 
| oq_drupal | url_alias  | dst_language_pid |            1 |        1.0001 |
 
| oq_drupal | url_alias  | dst_language_pid |            2 |        1.0001 |
 
| oq_drupal | url_alias  | dst_language_pid |            3 |        1.0000 |
 
| oq_drupal | url_alias  | src_language_pid |            1 |        1.0091 |
 
| oq_drupal | url_alias  | src_language_pid |            2 |        1.0088 |
 
| oq_drupal | url_alias  | src_language_pid |            3 |        1.0000 |
 
+-----------+------------+------------------+--------------+---------------+
 
10 rows in set (0.00 sec)

same with innodb

MariaDB [oq_drupal]> alter table zipcodes add column x varchar(100), ENGINE=Innodb;
 
Query OK, 0 rows affected (0.04 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [oq_drupal]> select * from mysql.index_stats;                              
+-----------+------------+------------------+--------------+---------------+
 
| db_name   | table_name | index_name       | prefix_arity | avg_frequency |
 
+-----------+------------+------------------+--------------+---------------+
 
| oq_drupal | watchdog   | PRIMARY          |            1 |        1.0000 |
 
| oq_drupal | watchdog   | type             |            1 |      106.0000 |
 
| oq_drupal | watchdog   | type             |            2 |        1.0000 |
 
| oq_drupal | url_alias  | PRIMARY          |            1 |        1.0000 |
 
| oq_drupal | url_alias  | dst_language_pid |            1 |        1.0001 |
 
| oq_drupal | url_alias  | dst_language_pid |            2 |        1.0001 |
 
| oq_drupal | url_alias  | dst_language_pid |            3 |        1.0000 |
 
| oq_drupal | url_alias  | src_language_pid |            1 |        1.0091 |
 
| oq_drupal | url_alias  | src_language_pid |            2 |        1.0088 |
 
| oq_drupal | url_alias  | src_language_pid |            3 |        1.0000 |
 
+-----------+------------+------------------+--------------+---------------+
 
10 rows in set (0.00 sec)
 
 
 
MariaDB [oq_drupal]> alter table zipcodes drop column x;
 
Query OK, 0 rows affected (0.06 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [oq_drupal]> select * from mysql.index_stats;
 
+-----------+------------+------------------+--------------+---------------+
 
| db_name   | table_name | index_name       | prefix_arity | avg_frequency |
 
+-----------+------------+------------------+--------------+---------------+
 
| oq_drupal | watchdog   | PRIMARY          |            1 |        1.0000 |
 
| oq_drupal | watchdog   | type             |            1 |      106.0000 |
 
| oq_drupal | watchdog   | type             |            2 |        1.0000 |
 
| oq_drupal | url_alias  | PRIMARY          |            1 |        1.0000 |
 
| oq_drupal | url_alias  | dst_language_pid |            1 |        1.0001 |
 
| oq_drupal | url_alias  | dst_language_pid |            2 |        1.0001 |
 
| oq_drupal | url_alias  | dst_language_pid |            3 |        1.0000 |
 
| oq_drupal | url_alias  | src_language_pid |            1 |        1.0091 |
 
| oq_drupal | url_alias  | src_language_pid |            2 |        1.0088 |
 
| oq_drupal | url_alias  | src_language_pid |            3 |        1.0000 |
 
+-----------+------------+------------------+--------------+---------------+



 Comments   
Comment by Elena Stepanova [ 2014-12-22 ]

Hi,

Which persistent statistics are you trying to use? It seems there is some mix up here.

innodb_stats_auto_recalc=1 and STATS_PERSISTENT=1 relate to the upstream InnoDB persistent statistics (http://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html). This data can be collected and updated automatically if it's configured so, and is stored in mysql.innodb_*_stats tables:

MariaDB [test]> create table t1 (i int) engine=InnoDB STATS_PERSISTENT=1;
 
Query OK, 0 rows affected (0.73 sec)
 
 
 
MariaDB [test]> create table t2 (i int) engine=InnoDB STATS_PERSISTENT=0;
 
Query OK, 0 rows affected (0.60 sec)
 
 
 
MariaDB [test]> select * from mysql.innodb_table_stats;     
+---------------+----------------+---------------------+--------+----------------------+--------------------------+
 
| database_name | table_name     | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
 
+---------------+----------------+---------------------+--------+----------------------+--------------------------+
 
| mysql         | gtid_slave_pos | 2014-12-22 16:24:23 |      0 |                    1 |                        0 |
 
| test          | t1             | 2014-12-22 16:35:34 |      0 |                    1 |                        0 |
 
+---------------+----------------+---------------------+--------+----------------------+--------------------------+
 
2 rows in set (0.00 sec)

Then, there is MariaDB engine-independent statistics (https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries/engine-independent-table-statistics). use_stat_tables relates to it; this data is only collected/updated upon ANALYZE, and is stored in mysql.table_stats, mysql.column_stats, and mysql.index_stats. 

MariaDB [test]> drop table t1, t2;
 
Query OK, 0 rows affected (0.39 sec)
 
 
 
MariaDB [test]> set use_stat_tables = complementary;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> create table t1 (i int) engine=InnoDB;
 
Query OK, 0 rows affected (1.06 sec)
 
 
 
MariaDB [test]> select * from mysql.table_stats;
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> analyze table t1;
 
+---------+---------+----------+-----------------------------------------+
 
| Table   | Op      | Msg_type | Msg_text                                |
 
+---------+---------+----------+-----------------------------------------+
 
| test.t1 | analyze | status   | Engine-independent statistics collected |
 
| test.t1 | analyze | status   | OK                                      |
 
+---------+---------+----------+-----------------------------------------+
 
2 rows in set (0.19 sec)
 
 
 
MariaDB [test]> select * from mysql.table_stats;
 
+---------+------------+-------------+
 
| db_name | table_name | cardinality |
 
+---------+------------+-------------+
 
| test    | t1         |           0 |
 
+---------+------------+-------------+

Comment by Daniel Black [ 2014-12-22 ]

quite right, was confusing the two.

A non-bug it is.

Generated at Thu Feb 08 07:18:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.