MySQL 6.0 news: Batched Key Access is in

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?

Posted in Uncategorized on December 30th, 2008 by spetrunia | | 7 Comments