Super slow (day long) count(*) after upgrade to 5.6

Question

After upgrading to Percona 5.6.20 from 5.5.34 I noticed counts on larger tables take a massively long time to execute.

The table is about 27G and 300M rows. It is not compressed and was innodb before the upgrade as well.

Before a count(*) on that table would take maybe 3 minutes. Now, it's consistently taking over a day to run. The last one I actually let spin until it finished took 36 hours.

The DB is on the same hardware as before the upgrade. Buffer pool size remained the same (20G).

One config change was increasing the number of buffer pools from 1 to 8.

Has anyone ran into this before? Are there any new 5.6 cnf settings that might be related to this?

Table schema in question

CREATE TABLE `largetable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`char1` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`int1` int(11) NOT NULL,
`int2` int(11) NOT NULL,
`char2` char(2) NOT NULL,
`mydate` date NOT NULL,
PRIMARY KEY (`id`),
KEY `char1` (`char1`(8))
) ENGINE=InnoDB AUTO_INCREMENT=296639951 DEFAULT CHARSET=latin1;

Here's a pt-config-diff of a "good" 5.5 server and the newer 5.6 server http://pastebin.com/EM22WhBR

Answer 1

It appears to be a change in the optimizer. I haven't tracked down the exact setting that caused this yet but the explain on the 5.5 version was showing it using the Primary Key. On 5.6 It was using the secondary char1(8) index.

Adding force index (primary) got it back to it's 3-5 minute count time.