Not the answer you need?
Register and ask your own question!

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

atxdbaatxdba EntrantInactive User Role Beginner


0 down vote favorite
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;


Comments

  • atxdbaatxdba Entrant Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.