Is it okay to turn off new flags in optimizer-switch setting for making a higher version of mysql behave like a lower version?



  • We have been using MySQL 5.6 on Amazon Aurora RDS but are now forced to upgrade to a higher version due to end of life.

    We noticed that MySQL 5.7 and 8 were not performing as well as 5.6. For some of the data, the query optimiser was not choosing the best query plan (ie. choosing table scans over index scans or not choosing plans same as 5.6). Even if a query plan same as 5.6 was chosen by the optimiser, the queries were a bit slow.

    On spending sometime searching for a solution, we were able to achieve performance similar to MySQL 5.6 by turning off new flags introduced in optimizer_switch in higher versions of MySQL thereby aligning the flags with that in 5.6.

    For 5.7

    --optimizer-switch=duplicateweedout=off,condition_fanout_filter=off,derived_merge=off
    

    For 8

    --optimizer-switch=duplicateweedout=off,condition_fanout_filter=off,derived_merge=off,use_invisible_indexes=off,skip_scan=off,hash_join=off,subquery_to_derived=off,prefer_ordering_index=off,hypergraph_optimizer=off,derived_condition_pushdown=off
    

    Does aligning the optimiser flags in this way make mysql 5.7/8 behave same as mysql 5.6? Also, is this recommended?



  • In the past, I have recommended doing that from 5.6 to 5.7.

    In my old post from Dec 16, 2017 https://dba.stackexchange.com/questions/193262/after-mysql-upgrade-to-5-7-optimizer-not-using-index-on-large-in-clause/193279#193279 , I recommended getting the old values 5.6 would have and using them in 5.7. https://dba.stackexchange.com/questions/193262/after-mysql-upgrade-to-5-7-optimizer-not-using-index-on-large-in-clause/193279#comment538813_193279 . This is not to say that it will work in every case but the option is always there.

    The best thing to do is to tweak the individual optimizer switch options. For example, back on Dec 28, 2015, I posted the answer to https://dba.stackexchange.com/questions/119621/how-to-update-10-million-rows-in-mysql-single-table-as-fast-as-possible/124733#124733 where I recommended something from MySQL Documentation to enabling Batch Key Access to improve join performance.

    While it is possible and a viable option to change all switch back to 5.6 settings, it's not one-size-fits-all. Some queries may work better for MySQL 5.7 out-of-the-box, other queries may not. Same goes for MySQL 8.0.

    Here is something that caught my attention: You said AURORA !!!

    Did you know that the latest version of Aurora 2.x and 3.x lowers the innodb_read_io_threads to 1 for some Instance Classes ??? Go to MySQL and run

    mysql> select @@global.innodb_read_io_threads;
    

    If it says 1, you found the problem.

    AFAIK, Amazon has not documented this but I have seen others bitten by this. You can contact AWS Support if you wish to verify this.

    Also note that you cannot innodb_read_io_threads in the DB Parameter Group for Aurora

    Unfortunately, I could only recommend the following two options

    OPTION #1 : Switch to RDS and increase your IOPs. You can always change innodb_read_io_threads in the DB Parameter Group for RDS. Requires work and $$$$.

    OPTION #2 : Upgrade to larger Instance class. Higher instance classes have more innodb_read_io_threads. EXAMPLE : db.r5.24xl will have 16 innodb_read_io_threads. Requires $$$$$$$$$$$. Workload would also have to be justifiable reason for scaling up.

    Neither option seems is pleasant or desirable. Sorry !!!




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2