MariaDB 5.3 has now three statistics counters for Multi Range Read optimization:
MariaDB [test]> show status like 'Handler_mrr%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Handler_mrr_extra_key_sorts | 0 | | Handler_mrr_extra_rowid_sorts | 0 | | Handler_mrr_init | 0 | +-------------------------------+-------+ 3 rows in set (0.08 sec)
I’ve just added the first two. The reason for having them is as follows: the point of MRR is to provide speedup over regular execution by doing reads in disk order. In order to make reads in disk order, MRR needs buffer space where it accumulates and sorts read requests. If there are too many read requests to fit into the buffer, MRR will make multiple accumulate-sort-read passes.
Doing multiple passes allows MRR to operate when having limited buffer space, but the speedup will be not as great as with one big disk-ordered read sweep. The purpose of
Handler_mrr_extra_rowid_sorts is to count the additional accumulate-sort-read passes, so you’re able to tell if you will benefit from increasing your
There are two counters,
_extra_rowid_sorts, because MariaDB has two places where it will do sorting:
- sort rowids before reading table records
- sort key values before making a bunch of index lookups
MRR code will try to distribute buffer space between them in an optimal way. The decision is a guess based on the available statistics, and can be wrong. Having both counters will allow us to check how the guess will work in practice.
p.s. if you could not make any sense of anything above, try reading Multi Range Read page in our knowlegebase. We have just put there a hopefully-readable explanation of what MRR is.
The votes have been counted and now there is a schedule for MySQL and Friends devroom at FOSDEM 2012.
I’m giving two talks:
- MariaDB 5.3’s query optimizer: taking the dolphin to where he’s never been before
- Replication features of 2011: what they were, how to get and how to use them
In an ideal world, you should never need to use optimizer hints. In the real world, hints are necessary. We needed hints even before we’ve made a release. The first requests came from our Quality engineer who complained about it being difficult to hit particular join orders while using a particular subquery strategy.
So, why not add the needed hints? MySQL already has the
FORCE INDEX, you can add more as required. The problem is that once you have transformations that change the query to be far enough from the original SQL, you don’t have a “natural” place or syntax for hints anymore.
For example, if you have a join
SELECT ... FROM table1, table2, table3 WHERE ....
and want a join order of (table2, table3, table1), you can write it as
SELECT ... FROM table2 STRAIGHT_JOIN table3 STRAIGHT_JOIN table1
and that gives the optimizer the join order while still looking like SQL. Now, how do you force the same join order when the query looks like this:
SELECT ... FROM table1 WHERE table1.column IN (SELECT table2.col1 FROM table2 WHERE table2.col2 IN (SELECT table3.col3 FROM table3)
The obvious answers are
- you have to put
table2, table3, table1together somewhere
- your hint syntax won’t be SQL
And since you now need to invent the whole new non-SQL syntax to specify query plans, the task becomes just too hard. It’s difficult to design a future-proof compact syntax for specifying query plans. My not-really-solution to the problem was to
- Add a
@@debug_optimizer_prefer_join_prefixsystem variable. That way, I avoided messing with the parser
- Do it in a never-to-be-pushed branch of MariaDB, so this solution is not exposed to the same rigor as everything that goes into release
As a result, you can make the optimizer to “strongly prefer” some particular join prefixes. The preferences are applied to each select, though. If you have a UNION and want to have one preference for on branch of it, and another preference for the other, there is no way to achieve that.
Given this kind of limitations, I think the patch is useful for development or debugging, but not for production use. Everything (documentation, link to launchpad tree) are here, at the mariadb-53-optimizer-debugging page.
I think the part about subquery optimizations should be fairly easy to read now, and our tech writer Daniel Bartholomew also did a pass over it. If you have a picture-type mind like I do, there is plenty of pictures, including the Subquery optimizations map.
For now, my goal was to just have a descriptions of all optimizations in place. We have also done substantial amount of benchmarking, but that data still waits to be processed and published.