1

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?

Kushan Sen
  • 41
  • 5

1 Answers1

1

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

In my old post from Dec 16, 2017 After mysql_upgrade to 5.7, optimizer not using index on large IN clause, I recommended getting the old values 5.6 would have and using them in 5.7. In the case of the OP from that question, it worked. 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 How to update 10 million+ rows in MySQL single table as Fast as possible? 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 !!!

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536