3

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

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.