Neither option seems is pleasant or desirable. Workload would also have to be justifiable reason for scaling up. EXAMPLE : db.r5.24xl will have 16 innodb_read_io_threads. For example, you can enable an optimization for one table in a statement and disable the optimization for a different table. Higher instance classes have more innodb_read_io_threads. #MYSQL OPTIMIZER TURN OFF UPGRADE#OPTION #2 : Upgrade to larger Instance class. You can always change innodb_read_io_threads in the DB Parameter Group for RDS. OPTION #1 : Switch to RDS and increase your IOPs. Unfortunately, I could only recommend the following two options You can contact AWS Support if you wish to verify this.Īlso note that you cannot innodb_read_io_threads in the DB Parameter Group for Aurora Here is something that caught my attention: You said AURORA !!!ĭid 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 it says 1, you found the problem.ĪFAIK, Amazon has not documented this but I have seen others bitten by this. Some queries may work better for MySQL 5.7 out-of-the-box, other queries may not. While it is possible and a viable option to change all switch back to 5.6 settings, it's not one-size-fits-all. #MYSQL OPTIMIZER TURN OFF HOW TO#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. The best thing to do is to tweak the individual optimizer switch options. This is not to say that it will work in every case but the option is always there. In the case of the OP from that question, it worked. In my old post from 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 past, I have recommended doing that from 5.6 to 5.7. 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.įor 5.7 -optimizer-switch=duplicateweedout=off,condition_fanout_filter=off,derived_merge=offįor 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ĭoes aligning the optimiser flags in this way make mysql 5.7/8 behave same as mysql 5.6? Also, is this recommended? Even if a query plan same as 5.6 was chosen by the optimiser, the queries were a bit slow. choosing table scans over index scans or not choosing plans same as 5.6). For some of the data, the query optimiser was not choosing the best query plan (ie. We noticed that MySQL 5.7 and 8 were not performing as well as 5.6. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |