MySQL Bugs: #78289: bad execution plan with innodb_stats

Bug #78289	bad execution plan with innodb_stats_persistent enabled
Submitted:	31 Aug 2015 13:20	Modified:	31 Aug 2015 15:09
Reporter:	Miguel Angel Nieto	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If innodb_stats_persistent is enabled, a simple query that uses the PK in where condition switches to full table scan.

Workarounds:

- Disable innodb_stats_persistent globally or...
- Run ANALYZE table to fix the problem or...
- Wait some time and the execution plan will get back to normal.

How to repeat:
CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`site_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`custom1` varchar(255) DEFAULT NULL,
`custom2` varchar(255) DEFAULT NULL,
`custom3` varchar(255) DEFAULT NULL,
`custom4` varchar(255) DEFAULT NULL,
`custom5` varchar(255) DEFAULT NULL,
`custom6` varchar(255) DEFAULT NULL,
`custom7` varchar(255) DEFAULT NULL,
`custom8` varchar(255) DEFAULT NULL,
`custom9` varchar(255) DEFAULT NULL,
`custom10` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`custom11` varchar(255) DEFAULT NULL,
`custom12` varchar(255) DEFAULT NULL,
`custom13` varchar(255) DEFAULT NULL,
`custom14` varchar(255) DEFAULT NULL,
`custom15` varchar(255) DEFAULT NULL,
`custom16` varchar(255) DEFAULT NULL,
`custom17` varchar(255) DEFAULT NULL,
`custom18` varchar(255) DEFAULT NULL,
`custom19` varchar(255) DEFAULT NULL,
`custom20` varchar(255) DEFAULT NULL,
`custom21` varchar(255) DEFAULT NULL,
`custom22` varchar(255) DEFAULT NULL,
`custom23` varchar(255) DEFAULT NULL,
`custom24` varchar(255) DEFAULT NULL,
`custom25` varchar(255) DEFAULT NULL,
`ticket_count` mediumint(8) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO companies (id) VALUES (NULL);
INSERT INTO companies (id) VALUES (NULL);
INSERT INTO companies (id) VALUES (NULL);
... up to 30 times for example.

Good execution plan:

# Time: 150831 14:55:30
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 0.001113  Lock_time: 0.000420 Rows_sent: 5  Rows_examined: 5
SET timestamp=1441025730;
SELECT `companies`.* FROM `companies` WHERE `companies`.`id` IN (1,
2, 3, 4, 5);

Add a column using a tool like pt-online-schema-change. It will create new temporary table, triggers, copy the data, then rename tables.

pt-online-schema-change \
--nocheck-replication-filters \
--execute \
--chunk-size-limit=0 \
--max-load Threads_running=50 \
--critical-load Threads_running=500 \
--alter "
ADD COLUMN label_array text
" u=root,D=test,t=companies

Now, the execution plan changes to full table scan (primary key is there, but it is not used anymore):

# Time: 150831 14:55:49
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 0.000590  Lock_time: 0.000411 Rows_sent: 5  Rows_examined: 30
SET timestamp=1441025749;
SELECT `companies`.* FROM `companies` WHERE `companies`.`id` IN (1, 2, 3, 4, 5);

Now, to fix the problem you need to wait (until MySQL chooses the good execution plan again) or run ANALYZE TABLE.

looks like a duplicate of not-a-bug https://bugs.mysql.com/bug.php?id=70617

This is well known behavior in many cases where innodb_stats_persistent is enabled.

For a complete explanation of why these phenomena occurs and possible solutions, please read the entire text in the bug # 70617. A developer that works on this feature has provided all the explanations required.

Not a bug.

So, I have a database with large tables. I run thousands of pretty simple queries that use PK as the where clause. This "not bug" will cause downtime, queuing thousand of queries that will be scanning million of rows. A bit scary for a non bug :)

Correct me if I am wrong, but shouldn't be as easy as triggering the calculation when a rename table is done?

There will be some changes due to RENAME, but not the ones that you might expect.

Simply,  the statistics table will include the table name (not ID), hence it will have a new string there for the name.

It will have no effect on the statistics themselves, nor on the refreshing of the persistent stats. Simply, RENAME does not touch any page, node or stats value.