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?

1
  • For any version, I would expect the Optimizer to be changed in ways that cannot be configured. If you have a specific query to discuss, there may be a workaround that makes if faster in both versions.
    – Rick James
    Mar 11, 2022 at 22:17

1 Answer 1

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 !!!

1
  • @@global.innodb_read_io_threads turns out to be 32 in my case. As per AWS documentation: aws.amazon.com/blogs/database/… "For a bad query plan or wrong estimation by the optimizer, you might need to adjust these flags. For example, you can mitigate performance regression in queries after upgrading from 5.6 to 5.7 by disabling the optimizer_switch flags condition_fanout_filter and derived_merge." This gives me results I need. Anyway, intensive app testing is due.
    – Kushan Sen
    Mar 14, 2022 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.