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?

7 Comments

  1. 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

    Like

  2. 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

    Like

  3. 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.

    Like

  4. 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.

    Like

Leave a Reply to Mark Callaghan Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s