Queries performing worse in 5.6 with optimizations

We have a set of Percona Server slaves performing read-only queries, one of which we’ve upgraded to Percona Server 5.6 to test production usage against our existing 5.5 slaves. All the slaves receive production traffic equally via a load balancer, so the 5.6 slave is receiving the same type of queries as the other slaves.

I’ve noticed that with 5.6, certain queries perform much more poorly due to the use of index condition pushdown and take a couple of orders of magnitude longer to complete. Here are some example EXPLAIN statements:

Percona Server 5.5


1 SIMPLE inbox ref PRIMARY,idx_participants,reverse_read_status idx_participants 4 const 387 Using index; Using temporary
1 SIMPLE inbox_message eq_ref PRIMARY,imt PRIMARY 4 inbox.im_id 1 Using where

Percona Server 5.6


1 SIMPLE inbox_message range PRIMARY,imt imt 6 NULL 40 Using index condition; Using where; Using temporary
1 SIMPLE inbox ref PRIMARY,idx_participants,reverse_read_status PRIMARY 8 const,inbox_message.im_id 1 Using index; Distinct

On 5.5, the query takes ~10ms to execute. On 5.6, it takes in the order of 30-60 seconds to complete using the altered query plan. The only way so far that I’ve found to solve this is to add FORCE INDEX (PRIMARY) to the query which makes the query plan on 5.6 match the one on 5.5 (and 5.5 stay the same), and then the query will complete in a similar amount of time. I’ve also tried disabling optimizations completely in my.cnf and restarting the server, but I still get “Using index condition” in the EXPLAIN output in this instance.

I’ve also noticed that there are some queries that aren’t using the optimizer but that still seem to suffer from a bad query plan. I’m not sure if this is to do with the changes in the way that statistics are calculated for a table:

Percona Server 5.5 (query time ~300ms)


1 SIMPLE im range PRIMARY,idx_sentdate,imt,im_recipient idx_sentdate 4 NULL 102856 Using where; Using filesort
1 SIMPLE mr eq_ref PRIMARY PRIMARY 4 im.im_recipient 1 Using where
1 SIMPLE inbox ref PRIMARY,idx_participants,reverse_read_status PRIMARY 8 mr.me_id,im.im_id 1 Using where

Percona Server 5.6 (query time ~60-120s)


1 SIMPLE im range PRIMARY,idx_sentdate,imt,im_recipient PRIMARY 4 NULL 174862225 Using where
1 SIMPLE mr eq_ref PRIMARY PRIMARY 4 im.im_recipient 1 Using where
1 SIMPLE inbox ref PRIMARY,idx_participants,reverse_read_status PRIMARY 8 im.im_recipient,im.im_id 1 Using where

Again, the only way to solve this has been to use FORCE INDEX (idx_sentdate) on all queries, which isn’t a particularly long-term solution in case the tables and data change in such a way that forcing those particular indexes no longer constitutes a good query plan.

Are there any tips for MySQL 5.6 that allow you to work around situations like that, such as changing server variables that calculate statistics better?

On further investigation, it appears that the query planner is just picking a different index in 5.6 that’s resulting in a much worse performing query. I’ve managed to disable the optimizations completely, and now get the following EXPLAIN for the first query in 5.6:


1 SIMPLE inbox_message range PRIMARY,imt imt 6 NULL 40 Using where; Using temporary
1 SIMPLE inbox ref PRIMARY,idx_participants,reverse_read_status PRIMARY 8 const,wld.inbox_message.im_id 1 Using index; Distinct

It seems to simple be down to the selection of imt in place of PRIMARY as the key for inbox_message that’s causing the problem. I’ve tried using ANALYZE TABLE to recreate the index statistics for all the tables concerned, to no avail. Seems like 5.6 just doesn’t like generating query plans for the tables we have, which means we’re probably going to have to stick with 5.5 for the time being.

…i’m seeing similar issues except I hit table scans. When I look at the cardinailty of the index in information_schema.statistics, it’s just warped.

Anyway - did you ever solve the issue or did u stay on 5.5?
thx

Never managed to solve it; I could never find a combination of configuration values that would cause the statistics for the indexes not to be crazy and pick a bad query plan. Ended up using index hinting (FORCE INDEX) for every query that was affected.