Ok this isn’t very timely reporting, but about two weeks ago Batched Key Access feature has been pushed into MySQL 6.0. You can get it from the bazaar repo now (bzr branch lp:mysql-server/6.0), or wait several more weeks till MySQL 6.0.9 is released and get it from there.
Batched Key Access in a nutshell
BKA is about accessing tables in batches when running nested loop joins. The benefits of batching table accesses are that
- “Remote” engines save on number of roundtrips
- Disk-based engines do reads in disk order instead of randomly probing the table, which allows to be easier on disk cache and take advantage of prefetching
Batched Key Access only works if the used storage engine supports it. At the moment there is support for MyISAM, InnoDB, Maria, Falcon (these are disk-based) and NDB (this one is remote) engines.
Documentation
At the moment there’s no manual chapter yet. There is a short introduction at Batched_Key_Access page on the forge and there are MySQL Conference 2008 session slides. The slides cover some benchmarking and give an idea about what kind of queries and dataset you need to get speedups with MyISAM/InnoDB. We’ve seen great speedup with NDB also but didn’t publish anything so far.
Observation
With Batched Key Access and condition pushdown, it is now feasible to create a remote table engine with decent performance. We have a remote engine, ha_federated and it doesn’t support BKA or condition pushdown and is a death by latency if you have queries that do not match the
SELECT * FROM table WHERE primary_key=const
pattern. I have a strong temptation to code a performance version of ha_federated myself, but have to resist it as there is subquery optimization work to be finished and optimizer “bugs” to be addressed.
This is now a rather low-hanging fruit, any takers?
Sergei Petrunia's blog 
Sergey, nice to read progress is being made on the optimizer! Kudos to you and the rest of the optimizer team.
I will download MySQL 6.0 shortly to try it, but is there anything you can tell us in advance what to expect in terms of performance gains?
TIA,
Roland
LikeLike
Hi!
From reading Igor’s comments it makes it sound like this relies on MRR. MRR has been really buggy, how much testing does this have? Do you have any benchmarks showing what if any regression this has added?
Looking at this, plus MRR, plus ICP, and the condition pushdown code I don’t see what the overall evolution is. What about running the entire test suite with this enabled? Are there result miss matches?
It is great to see evolution here, but I don’t see an overall “this is what the end piece is going to look like”.
Cheers,
-Brian
LikeLike
Roland,
From optimizer POV, the big news are MRR, BKA, and subquery optimizations. I’ve recently pushed a big chunk of new subquery optimization code, will cover that in a separate post.
LikeLike
Brian,
> From reading Igor’s comments it makes it sound like this relies on MRR. MRR has been really buggy, how much testing does this have? Do you have any benchmarks showing what if any regression this has added?
MRR itself is interface so it can’t have bugs. MRR implementations can, though. At the moment we have:
NDB/MRR- no known bugs.
MyISAM/MRR – no known bugs. It is known to cause 18% slowdown for small queries and memory-based load.
Maria/MRR is a copy of MyISAM/MRR
InnoDB/MRR – there are known crashes for mixed update/select load (I suspect they all are one problem) + see the MyISAM problem.
Falcon/MRR – no known problems, but I doubt it had much testing.
> Looking at this, plus MRR, plus ICP, and the condition pushdown code I don’t see what the overall evolution is. What about running the entire test suite with this enabled? Are there result miss matches?
ICP and MRR are enabled in 6.0 by default. At the moment pushbuild doesn’t show any problems because of it.
BKA needs to be specifically enabled, there are mysql-test/t/*_jcl6.test tests that are a re-run of relevant tests with BKA. The rest of the testsuite won’t give much more coverage I’m afraid because BKA is about *big joins*, and we don’t have them in the testsuite. BKA has received some testing (and we found and fixed bugs) when Igor had run various DBT3-based tests with it. This has happened both before and after the mentioned UC session.
LikeLike
What is ICP?
LikeLike
Mark,
ICP stands for “Index Condition Pushdown”, checking a part of the WHERE that refers to index columns before we go to retrieve full record.
http://dev.mysql.com/doc/refman/6.0/en/index-condition-pushdown-optimization.html says “MyISAM”, but actually it’s there for MyISAM, Maria and InnoDB. InnoDB version is known to have several bugs.
LikeLike
http://bugs.mysql.com/45029 — ICP and MRR are disabled by default because of bugs they cause in InnoDB
LikeLike