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?