Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7360

alter table doesn't regenerate persistent engine-independent-index stats

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: 10.0.15
        • Fix Version/s: N/A
        • Component/s: Optimizer
        • 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 |
+-----------+------------+------------------+--------------+---------------+

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                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 |
+---------+------------+-------------+
                Show
                elenst Elena Stepanova added a comment - 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 | +---------+------------+-------------+
                Hide
                Permalink
                danblack Daniel Black added a comment -

                quite right, was confusing the two.

                A non-bug it is.

                Show
                danblack Daniel Black added a comment - quite right, was confusing the two. A non-bug it is.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    danblack Daniel Black
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Due:
                      Created:
                      Updated:
                      Resolved: