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

MySQL & MariaDB Other MySQL® Questions
1

[TABLE]
[TR]
[TD=“class: wysiwyg_table_votecell”]0 down vote favorite[/TD]
[TD=“class: wysiwyg_table_postcell”]After upgrading to Percona 5.6.20 from 5.5.29 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-5 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).
Has anyone ran into this before? Are there any new 5.6 cnf settings that might be related to this?

		I've attached the output of pt-config-diff between the old 5.5 server (aka GoodServer) vs the 5.6 instance giving me troubles..
		
		The schema for the table in question with column names anonymized
		
		 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;
		 [/TD]
	[/TR]
 [/TABLE]

pt-config-diff.txt (2.53 KB)

2

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.