You are viewing an old version of this article. View the current version here.

Multi Range Read is an optimization aimed at improving performance for IO-bound queries that need to scan lots of rows.

Multi Range Read is available in MariaDB since MariaDB 5.3, and MySQL has a subset of it in MySQL 5.6.

Multi Range Read can be used with

  • range access
  • ref and eq_ref access, when they are using Batched Key Access

as shown in this diagram:

possible-mrr-uses

The idea

Case 1: rowid sorting for range access

Consider a range query:

MariaDB [test]> explain select * from tbl where tbl.key1 between 1000 and 2000;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key1          | key1 | 5       | NULL |  960 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

When this query is executed, disk IO access pattern will follow the red line in this figure:

no-mrr-access-pattern

Execution will hit the table rows in random places, as marked with the blue line/numbers in the figure.

When the table is sufficiently big, each table record read will need to actually go to disk (and be served from buffer pool or OS cache), and query execution will be too slow to be practical. For example, a 10,000 RPM disk drive is able to make 167 seeks per second, so in the worst case, query execution will be capped at reading about 167 records per second.

SSD drives do not need to do disk seeks, so they will not be hurt as badly, however the performance will still be poor in man cases.

Multi-Range-Read optimization aims to make disk access faster by sorting record read requests and then doing one ordered disk sweep.

MariaDB [test]> set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.06 sec)

MariaDB [test]> explain select * from tbl where tbl.key1 between 1000 and 2000;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                     |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------+
|  1 | SIMPLE      | tbl   | range | key1          | key1 | 5       | NULL |  960 | Using index condition; Rowid-ordered scan |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------+
1 row in set (0.03 sec)

mrr-access-pattern

Reading disk data sequentially is generally faster, because

  • Rotating drives do not have to move the head back and forth
  • One can take advantage of IO-prefetching done at various levels
  • Each disk page will be read exactly once, which means we won't rely on disk cache (or buffer pool) to save us from reading the same page multiple times.

The above can make a huge difference on performance. There is also a catch, though:

  • If you're scanning small data ranges in a table that is sufficiently small so that it completely hits into the OS disk cache, then extra buffering/sorting that MRR does will only add CPU overhead.
  • LIMIT n and ORDER BY ... LIMIT n queries with small values of n may become slower. The reason is that MRR reads data in disk order, while ORDER BY ... LIMIT n wants first n records in index order.

Case 2: Rowid sorting for Batched Key Access

Factsheet

  • Batched Key Access optimization uses MultiRangeRead functionality, so Multi Range Read must be enabled for Batched Key Access to work.
  • Because Multi Range Read can cause slowdowns when ran for small ranges over small tables, it is disabled by default. To enable it, use the following settings:
SET optimizer_switch='mrr=on,mrr_sort_keys=on,mrr_cost_based=off'

Switching on mrr_cost_based is currently not recommended because cost model is not sufficiently well-tuned to be practically useful.

Differences from MySQL

  • Key-ordered scan is a MariaDB-only development
  • EXPLAIN in MySQL shows "Using MRR", while in MariaDB it is either "Rowid-ordered scan", "Key-ordered scan", or Key-ordered Rowid-ordered scan".
  • for range access, the size of the buffer is controlled by @mrr_buffer_size variable (Original MySQL 6.0 and current MySQL 5.6 use @@read_rnd_buffer_size, which is also used to control buffer size for other algorithms)
  • [is this a difference?] When used with BKA, buffer space management is done by BKA code. It uses up to @join_buffer_size bytes per table, distributing it across its own buffers and buffers it provides to MRR.

See also

Comments

Comments loading...