Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.15
-
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 |
|
+-----------+------------+------------------+--------------+---------------+
|