| Bug #69179 | accessing information_schema.partitions causes plans to change | ||
|---|---|---|---|
| Submitted: | 9 May 2013 4:22 | Modified: | 23 Sep 2013 15:08 |
| Reporter: | Justin Swanhart | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.6.11, 5.5.31, 5.6.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb, performance, statistics | ||
[9 May 2013 4:52]
Justin Swanhart
if you want to duplicate data: get the ssb dbgen: https://github.com/electrum/ssb-dbgen You will need to fix config.h add: #include <sys/types.h> Generate the tables at scale factor 20 (12GB data): ./dbgen -s 20 -T l ./dbgen -s 20 -T c ./dbgen -s 20 -T s ./dbgen -s 20 -T p ./dbgen -s 20 -T d Get the DDL here: https://shard-query.googlecode.com/svn/trunk/tools/ssb/ssb_schema.sql You will have to partition the table. I used this PHP script to generate the partitioning clauses: <?php for($y=1992;$y<=1998;++$y) { for($m = 1; $m <= 12; ++$m) { if($m < 10) $m = "0$m"; echo "partition p{$y}_{$m} values less than ({$y}{$m}31),\n"; } } ?> to load the tables: load data infile '/path/to/ssb/lineorder.tbl' into table lineorder fields terminated by '|'; To set up shard-query download it from SVN. Use install_db.php to create the config database. Also grant all on *.* to shard_query@'localhost' identified by 'shard_query'; use the following bootstrap.ini: ;BEGIN [default] host=127.0.0.1 user=shard_query password=shard_query db=ssb [config] db=shard_query mapper="none" column="none" inlist="none" between="none" column_datatype=integer schema_name=default star_schema=false [single_node] db=ssb ;END php setup.php --ini=bootstrap.ini Download and compile gearmand from gearman.org run gearmand (ignore warnings about logging): gearmand -d -p 7001 -L 127.0.0.1 change to the ./bin directory inside of the shard-query source tree start a shard-query worker to verify that gearman is working properly. if it just sits there and does nothing (no errors) then everything is good: ./worker After 30 seconds or so with no errors, hit ctrl-c and start 8 workers (assuming you have 8 cores): ./start_workers 8 You now have 8 workers running in the background (ps -ef|grep worker) and if a worker dies, the run_worker script will restart it (run_worker functions like mysqld_safe). Verify shard-query is working: echo "select count(*) from lineorder;"| php run_query You should get a response back after awhile. Open another console and use SHOW PROCESSLIST to verify the queries are running. You should get one query per worker, as each worker accesses one partition.
[22 May 2013 8:56]
Justin Swanhart
Shard-Query uses: I_S.PARTITIONS I_S.TABLES I_S.COLUMNS I suspect that statistics are being recalculated after accessing these views.
[26 May 2013 4:31]
Justin Swanhart
run this script and the plan will change
Attachment: test.sql (text/x-sql), 633.39 KiB.
[26 May 2013 4:41]
Justin Swanhart
I've attached a test script that should cause the query plan for Q2.2 of the star schema benchmark to change. Generate SSB scale factor 10 and load it into the tables. Collect statistics for all tables and restart the database. Examine the query plan for Q2.2: explain partitions select sum(lo_revenue), d_year, p_brand from lineorder join dim_date on lo_orderdatekey = d_datekey join part on lo_partkey = p_partkey join supplier on lo_suppkey = s_suppkey where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' group by d_year, p_brand order by d_year, p_brand; Execute the provided test script. Examine the query plan again. Before running the test script the query plan should start that the `part` table. After running the test script it will start at `dim_date`.
[26 May 2013 5:27]
Justin Swanhart
Sorry, this is the exact query that has a plan change. It seems problem occurs when partition elimination is used:
explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey ) JOIN part AS `part` ON( lo_partkey = p_partkey ) JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey ) WHERE p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND LO_OrderDateKey < (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
Here is the exact query that causes the plan change for the above query:
select
partition_expression,
CONCAT(partition_name,IF(SUBPARTITION_NAME IS NULL,'', concat('_', subpartition_name))) partition_name,
concat(
-- get the boundary expression for the previous partition (if any)
IFNULL(
(select
concat(p3.partition_expression, ' >= (',
if(p3.partition_description = 'MAXVALUE', (select max(p2.partition_description)
from information_schema.partitions p2
where p2.table_name = p3.table_name
and p2.table_schema = p3.table_schema
and p2.partition_description != 'MAXVALUE'
and table_name = 'lineorder' and table_schema = 'ssb_tokudb'
and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
), p3.partition_description), ') AND ')
from information_schema.partitions p3
where p.partition_description != 'MAXVALUE'
and p3.table_name = p.table_name and p.table_schema = p3.table_schema
and table_name = 'lineorder' and table_schema = 'ssb_tokudb'
and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
and p3.PARTITION_ORDINAL_POSITION=(p.PARTITION_ORDINAL_POSITION-1)),''), ' ',
-- get the boundary expression for the current partition
p.partition_expression, ' ', IF(p.partition_description != 'MAXVALUE', '< ', '>= '), ' (',
if(p.partition_description = 'MAXVALUE', (select max(p2.partition_description)
from information_schema.partitions p2
where p2.table_name = p.table_name
and p2.table_schema = p.table_schema
and p2.partition_description != 'MAXVALUE'
and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
and table_name = 'lineorder' and table_schema = 'ssb_tokudb'
), p.partition_description), ')'
) as where_expression
from information_schema.partitions p
where partition_method like 'RANGE%'
and partition_expression not like '%`,`%'
and table_name = 'lineorder'
and table_schema = 'ssb_tokudb'
and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
UNION ALL
select partition_expression,
CONCAT(partition_name,IF(SUBPARTITION_NAME IS NULL,'', concat('_', subpartition_name))) partition_name,
concat(p.partition_expression, ' IN (',
p.partition_description, ')'
) as expression
from information_schema.partitions p
where partition_method like 'LIST%'
and partition_expression not like '%`,`%'
and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
and table_schema = 'ssb_tokudb'
and table_name = 'lineorder';
Note that innodb_stats_on_metadata=off (I also tested with on, it had no effect).
I also duplicated this bug in TokuDB-5.5.30-mariadb-7.0.1. Not sure if it affects Oracle MySQL 5.5 since MariaDB inherits portions of the 5.6 codebase.
[26 May 2013 6:06]
Justin Swanhart
Tested on Oracle MySQL 5.5.31. mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey ) JOIN part AS `part` ON( lo_partkey = p_partkey ) JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey ) WHERE p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND LO_OrderDateKey < (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL; +----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+ | 1 | SIMPLE | part | NULL | range | PRIMARY,P_Brand | P_Brand | 18 | NULL | 1583 | Using where; Using index; Using temporary | | 1 | SIMPLE | lineorder | p1995_12 | ref | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_PartKey | 4 | ssb.part.P_PartKey | 1 | Using where | | 1 | SIMPLE | supplier | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ssb.lineorder.LO_SuppKey | 1 | Using where | | 1 | SIMPLE | dim_date | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ssb.lineorder.LO_OrderDateKey | 1 | | +----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> select count(*) From information_Schema.partitions;+----------+ | count(*) | +----------+ | 273 | +----------+ 1 row in set (0.03 sec) mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey ) JOIN part AS `part` ON( lo_partkey = p_partkey ) JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey ) WHERE p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND LO_OrderDateKey < (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL; +----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+ | 1 | SIMPLE | dim_date | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 29 | Using where; Using temporary | | 1 | SIMPLE | lineorder | p1995_12 | ref | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4 | ssb.dim_date.D_DateKey | 1 | | | 1 | SIMPLE | supplier | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ssb.lineorder.LO_SuppKey | 1 | Using where | | 1 | SIMPLE | part | NULL | eq_ref | PRIMARY,P_Brand | PRIMARY | 4 | ssb.lineorder.LO_PartKey | 1 | Using where | +----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+ 4 rows in set (0.00 sec) innodb_stats_on_metadata does not affect behavior but the I_S query is two orders of magnitude slower with it on, and it still generates bad plan.
[26 May 2013 7:52]
Justin Swanhart
Update version info. Fat fingers.
[27 May 2013 9:50]
Justin Swanhart
Here is a fix (at least I think it is).
[justin@workstation sql]$ diff -u ha_partition.cc /tmp/ha_partition.cc
--- ha_partition.cc 2013-04-05 05:27:18.000000000 -0700
+++ /tmp/ha_partition.cc 2013-05-27 02:45:01.680676228 -0700
@@ -6455,9 +6455,11 @@
void ha_partition::get_dynamic_partition_info(PARTITION_STATS *stat_info,
uint part_id)
{
+
handler *file= m_file[part_id];
DBUG_ASSERT(bitmap_is_set(&(m_part_info->read_partitions), part_id));
- file->info(HA_STATUS_CONST | HA_STATUS_TIME | HA_STATUS_VARIABLE |
+
+ info(HA_STATUS_CONST | HA_STATUS_TIME | HA_STATUS_VARIABLE |
HA_STATUS_VARIABLE_EXTRA | HA_STATUS_NO_LOCK);
stat_info->records= file->stats.records;
You should have an SCA for me on file. I'm pretty certain I signed one for my blackhole engine fix. If not, let me know and I'll file an OCA.
[27 May 2013 17:55]
MySQL Verification Team
Hi! This bug looks quite serious, so we are eager to reproduce it. First of all, please confirm that you have verified it on our standard GPL 5.6.11 binary. Second, would you be so kind to provide us with a test case not involving third party programs. If there is a need to run queries in parallel in order to reproduce the bug, just let us know which statements (or batches thereof) should be run in parallel and we shall use our standard tools for the verification. Many, many thanks in advance !!!
[27 May 2013 19:19]
Justin Swanhart
SSB schema with partitions
Attachment: ssb_schema_partitioned.sql (text/x-sql), 6.19 KiB.
[27 May 2013 19:28]
Justin Swanhart
-- There is no third party program needed except the data generator, which -- produces cardinalities necessary to see the problem. -- You will need to download and compile the data generator: -- get the ssb dbgen: https://github.com/electrum/ssb-dbgen -- You will need to fix config.h add: #include <sys/types.h> -- Generate the tables at scale factor 10 (6GB data) (you can try -s 1 too): ./dbgen -s 10 -T l ./dbgen -s 10 -T c ./dbgen -s 10 -T s ./dbgen -s 10 -T p ./dbgen -s 10 -T d -- Create the schema using the attached ssb_schema_partitioned.sql file. \. /data/ssb/SF10/ssb_schema_partitioned.sql -- Load the data into the tables: LOAD DATA INFILE '/data/ssb/SF10/lineorder.tbl' INTO TABLE lineorder fields terminated by '|'; LOAD DATA INFILE '/data/ssb/SF10/customer.tbl' INTO TABLE customer fields terminated by '|'; LOAD DATA INFILE '/data/ssb/SF10/part.tbl' INTO TABLE part fields terminated by '|'; LOAD DATA INFILE '/data/ssb/SF10/supplier.tbl' INTO TABLE supplier fields terminated by '|'; LOAD DATA INFILE '/data/ssb/SF10/dim_date.tbl' INTO TABLE dim_date fields terminated by '|'; -- Add indexes to lineorder: alter table lineorder add key(lo_orderdatekey), add key(lo_suppkey), add key(lo_partkey), add key(lo_custkey); -- switch stats to permanent for all tables: ALTER TABLE lineorder stats_persistent=1, stats_auto_recalc=0; ALTER TABLE customer stats_persistent=1, stats_auto_recalc=0; ALTER TABLE supplier stats_persistent=1, stats_auto_recalc=0; ALTER TABLE part stats_persistent=1, stats_auto_recalc=0; ALTER TABLE dim_date stats_persistent=1, stats_auto_recalc=0; -- analyze tables analyze table lineorder; analyze table customer; analyze table supplier; analyze table part; analyze table dim_date; -- Examine the plan for a problem query. It SHOULD START AT THE `part` TABLE mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey ) JOIN part AS `part` ON( lo_partkey = p_partkey ) JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey ) WHERE p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND LO_OrderDateKey < (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL; +----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+ | 1 | SIMPLE | part | NULL | range | PRIMARY,P_Brand | P_Brand | 18 | NULL | 1583 | Using where; Using index; Using temporary | | 1 | SIMPLE | lineorder | p1995_12 | ref | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_PartKey | 4 | ssb.part.P_PartKey | 1 | Using where | | 1 | SIMPLE | supplier | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ssb.lineorder.LO_SuppKey | 1 | Using where | | 1 | SIMPLE | dim_date | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ssb.lineorder.LO_OrderDateKey | 1 | | +----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> select count(*) From information_Schema.partitions; +----------+ | count(*) | +----------+ | 273 | +----------+ 1 row in set (0.03 sec) mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey ) JOIN part AS `part` ON( lo_partkey = p_partkey ) JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey ) WHERE p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND LO_OrderDateKey < (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL; +----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+ | 1 | SIMPLE | dim_date | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 29 | Using where; Using temporary | | 1 | SIMPLE | lineorder | p1995_12 | ref | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4 | ssb.dim_date.D_DateKey | 1 | | | 1 | SIMPLE | supplier | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ssb.lineorder.LO_SuppKey | 1 | Using where | | 1 | SIMPLE | part | NULL | eq_ref | PRIMARY,P_Brand | PRIMARY | 4 | ssb.lineorder.LO_PartKey | 1 | Using where | +----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+ 4 rows in set (0.00 sec) This duplicates with binaries and source compiled 5.6.11. It exists in all variants of MySQL including MariaDB. The problem appears to be in ::get_dynamic_partition_info which is in ha_partition.cc, which is storage engine agnostic. Thus this affects InnoDB, MyISAM, TokuDB, etc.
[27 May 2013 22:33]
Justin Swanhart
Uploaded FTP attachment: bug-data-69179.tgz It contains data for one partition (the one in the example query), and a README file for reproduction. This removes any requirement for a third party binary.
[28 May 2013 12:10]
Joe Grasse
Could this be related to http://bugs.mysql.com/bug.php?id=60071?
[28 May 2013 17:21]
Justin Swanhart
Yes, it appears to be a duplicate of 60071. A workaround may be running FLUSH TABLES after accessing I_S.PARTITIONS. This should discard the statistics (mysql stats, not innodb stats) being collected by I_S.PARTITIONS.
[29 May 2013 8:25]
MySQL Verification Team
Hello Justin, I can not repeat described behavior neither on reported version or later version with the provided test case. Could you please provide fully repeatable test case? Thanks, Umesh
[29 May 2013 8:26]
MySQL Verification Team
Test case with results...
Attachment: 69179.txt (text/plain), 20.56 KiB.
[29 May 2013 19:08]
Justin Swanhart
Look at the README. In the test data the files are TAB separated not '|'.. You have thousands of warnings per LOAD DATA.
[29 May 2013 19:31]
Justin Swanhart
I didn't do that to confuse you. As I mentioned in the bug, the original files come from dbgen but the files in the data I sent you came from SELECT ... INTO OUTFILE which produces tab delimited files by default. It was you who wanted to ensure you had the exact same data as me. If you had compiled dbgen your test would have worked :)
[30 May 2013 3:20]
Justin Swanhart
Also, apologies if I sounded rude. Thank you for looking into this.
[30 May 2013 5:30]
MySQL Verification Team
Thank you for the report. Verified as described.
[30 May 2013 5:37]
MySQL Verification Team
Test case with results...
Attachment: 69179.txt (text/plain), 11.20 KiB.
[28 Aug 2013 12:14]
Sergei Glushchenko
The bug is fixed in Percona Server http://bazaar.launchpad.net/~percona-core/percona-server/5.5/revision/543
[24 Sep 2013 8:10]
Olav Sandstå
Duplicate of Bug#60071.
Description: MySQL 5.6 supposed supports persistent statistics. I enabled persistent statistics in my "my.cnf" and I also enabled them with the ALTER TABLE statement. However, in my testing, a read-only workload quickly resets the statistics as evidenced by query plans changes which result in severe performance loss. In my testing, the first few batches of test queries will execute in 20 seconds or less with the data in the buffer pool, but then a batch of queries will start taking 80 seconds or more. An examination of the EXPLAIN plan on one of the queries in the batch shows a query plan change. How to repeat: I use Shard-Query with the Star Schema Benchmark to execute queries. I can give you details for how to set this up, but I think you can probably duplicate this without using Shard-Query. I have a partitioned table with STATS_PERSISTENT=1, STATS_AUTO_RECALC=0: CREATE TABLE `lineorder` ( `LO_OrderKey` bigint(20) NOT NULL, `LO_LineNumber` tinyint(4) NOT NULL, `LO_CustKey` int(11) NOT NULL, `LO_PartKey` int(11) NOT NULL, `LO_SuppKey` int(11) NOT NULL, `LO_OrderDateKey` int(11) NOT NULL, `LO_OrderPriority` varchar(15) DEFAULT NULL, `LO_ShipPriority` char(1) DEFAULT NULL, `LO_Quantity` tinyint(4) DEFAULT NULL, `LO_ExtendedPrice` decimal(10,0) DEFAULT NULL, `LO_OrdTotalPrice` decimal(10,0) DEFAULT NULL, `LO_Discount` decimal(10,0) DEFAULT NULL, `LO_Revenue` decimal(10,0) DEFAULT NULL, `LO_SupplyCost` decimal(10,0) DEFAULT NULL, `LO_Tax` tinyint(4) DEFAULT NULL, `LO_CommitDateKey` int(11) NOT NULL, `LO_ShipMode` varchar(10) DEFAULT NULL, KEY `LO_OrderDateKey` (`LO_OrderDateKey`), KEY `LO_SuppKey` (`LO_SuppKey`), KEY `LO_PartKey` (`LO_PartKey`), KEY `LO_CustKey` (`LO_CustKey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 STATS_AUTO_RECALC=0 /*!50100 PARTITION BY RANGE (LO_OrderDateKey) (PARTITION p1992_01 VALUES LESS THAN (19920131) ENGINE = InnoDB, ..., PARTITION p1998_12 VALUES LESS THAN (19981231) ENGINE = InnoDB) */ 1 row in set (0.00 sec) I used ANALYZE TABLE to refresh the stats: ANALYZE TABLE lineorder; Here is the join before I run SELECT queries: mysql> explain SELECT c_nation AS expr$0,s_nation AS expr$1,d_year AS expr$2,SUM(lo_revenue) AS expr_2336619454 FROM customer AS `customer` JOIN lineorder AS `lineorder` ON( lo_custkey = c_customerkey ) JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey ) JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey ) WHERE c_region = 'ASIA' and s_region = 'ASIA' and d_year >= 1992 and d_year <= 1997 AND LO_OrderDateKey >= (19920731) AND LO_OrderDateKey < (19920831) GROUP BY expr$0,expr$1,expr$2 ORDER BY NULL\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer type: ref possible_keys: PRIMARY,C_Region key: C_Region key_len: 15 ref: const rows: 218196 Extra: Using index condition; Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: lineorder type: ref possible_keys: LO_OrderDateKey,LO_SuppKey,LO_CustKey key: LO_CustKey key_len: 4 ref: ssb.customer.C_CustomerKey rows: 3 Extra: Using where *************************** 3. row *************************** id: 1 select_type: SIMPLE table: dim_date type: eq_ref possible_keys: PRIMARY,D_Year,D_Year_2 key: PRIMARY key_len: 4 ref: ssb.lineorder.LO_OrderDateKey rows: 1 Extra: Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: supplier type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: ssb.lineorder.LO_SuppKey rows: 1 Extra: Using where 4 rows in set (0.01 sec) In another session, I run a bunch of select queries of the same type, but the WHERE clause contains different constants such that one partition is evaluated per query. After some query executions the query statistics on lineorder change because the query plan changes (notice that it now starts from dim_date not customer!): *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dim_date type: range possible_keys: PRIMARY,D_Year,D_Year_2 key: PRIMARY key_len: 4 ref: NULL rows: 29 Extra: Using where; Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: lineorder type: ref possible_keys: LO_OrderDateKey,LO_SuppKey,LO_CustKey key: LO_OrderDateKey key_len: 4 ref: ssb.dim_date.D_DateKey rows: 1 Extra: NULL *************************** 3. row *************************** id: 1 select_type: SIMPLE table: supplier type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: ssb.lineorder.LO_SuppKey rows: 1 Extra: Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: customer type: eq_ref possible_keys: PRIMARY,C_Region key: PRIMARY key_len: 4 ref: ssb.lineorder.LO_CustKey rows: 1 Extra: Using where 4 rows in set (0.00 sec) ^-- notice that I never ran analyze between the plans changing. Where did the new stats come from? Note that not only do I have the flag for persistent stats turned on at the table level, I also have it turned on at the instance level: mysql> show global variables like '%persist%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | +--------------------------------------+-------+ 2 rows in set (0.00 sec) I also tried turning innodb_stats_auto_recalc=off. Suggested fix: Make persistent statistics persistent.