Recently we upgraded our percona mysql server from 5.5 to 5.6 version (5.6.34).
The problem is that with the same configuration, the same queries started to execute much longer ( comparing to 5.5 ).
I have started to examine some particular queries, however eventually came into conclusion that the problem rather common ( and not related to particular queries ).
A lot of queries started to use incorrect indexes ( comparing to 5.5 ).
As I understand the root cause is in changed optimizer logic and optimizer “improvements” in 5.6.
The solution to rewrite a lot of queries to use index hints seems to me bad and non permanent. I am looking to some server-side optimization.
I have tried to disable all new optimizer options, but without an effect:
SET @@global.optimizer_switch='index_condition_pushdow n=off,mrr=off,mrr_cost_based=off,block_nested_loop =off,materialization=off,semijoin=off,loosescan=of f,firstmatch=off,subquery_materialization_cost_bas ed=off,use_index_extensions=off';
Questions:
Have someone faced such situation after upgrade to 5.6? How did you deal in such case? What can be tuned to correct improvements in 5.6 and change optimizer work?
Any help is very appreciated!