My Oracle Support Banner

Optimizer Enhancements in MySQL 5.6: Multi-Range Read (MRR), Block Nested-Loop (BNL), Batched Key Access (BKA) (Doc ID 1464378.1)

Last updated on FEBRUARY 27, 2020

Applies to:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.

Details

What exactly is optimized

When new approach is faster and troubleshooting

To disable new feature use following commands:

set optimizer_switch="mrr=off";
set optimizer_switch="block_nested_lookup=off";
set optimizer_switch="batched_key_access=off";

Currently MRR cost is set too high, to be able utilize new algorithm in all suitable cases, it is required to tell optimizer to ignore MRR cost penalty:

set optimizer_switch="mrr_cost_based=off";

MRR and BKA bring most performance boost for systems with expensive random accesses comparing to sequential reads.

So e.g. MyISAM (except SSD storage) and NDB should benefit the most from utilizing MRR. In contrast, InnoDB doesn't benefit much from accessing rows sequentially when buffer_pool is hot, so keeping mrr_cost_based=on may be good solution.

Performance boost should be always expected if MySQL is able to utilize BNL.

Increasing following parameters may speed up performance of new algorithms:

Typically increasing related parameter should bring performance benefit until buffers are big enough (so MySQL is able to produce resultset just with few iterations).

WARNING: Setting per session buffers to too high values will exhaust available RAM in highly concurent environment, leading to instability and even unavailability of MySQL Server.

Side Note: Order of rows

MySQL doesn't guarantee order of rows if no sorting is used in query, thus knowing details of algorithm implementation we can somehow guess order in output (this is still not reliable approach and used only for better understanding how algorithms work)

BNL in MySQL statistics

Example below shows advantages of using BNL in mysql statistics.

Actions

 

Contacts

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Details
 What exactly is optimized
 When new approach is faster and troubleshooting
 Side Note: Order of rows
 BNL in MySQL statistics
Actions
Contacts
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.