Monday, October 21, 2013

FAQ: InnoDB extended secondary keys

MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I'll try to get rid of the confusion once and for all. Famous last words... here goes:

Q1: Do I need to do anything to enable extended secondary keys?

No, nothing at all. It's on by default and I can't see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch='use_index_extensions={on|off}'.

Q2: Does extended secondary keys only work with InnoDB?

No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say "should" because it requires a minimum of work from the storage engine provider; it must announce to MySQL that it is supported.

Friday, October 18, 2013

The second MySQL seminar: a summary

Once again, Geir Høydalsvik and I had the pleasure of hosting a MySQL mini-seminar in Trondheim. 25+ attendants from at least 7 different companies and a few professors from the computer science dept. at NTNU showed up on yesterdays event. I recognized many of these from the first seminar but there were some new faces as well.

This time, Mark Leith came on a visit from the UK. He gave an introduction to Performance Schema and ps_helper. ps_helper is a really nice tool to make sense of the overwhelming amount of data collected by PS. He also gave a very convincing demo of MySQL Enterprise Monitor (MEM). More than a few attendants now plan to give MEM a try in their environment. You can too - there's a 30 day trial, which should be more than enough to decide if you need it :-)

Like last time, we had great discussions, pizza and a very good time. I'm looking forward to the next seminar already.

If you're interested, Mark's PS presentation can be found on slideshare. ps_helper is both on github and on his blog. ps_helper comes highly recommended!

Friday, September 13, 2013

Next MySQL mini-seminar in Trondheim October 17

We have the pleasure to announce that the next MySQL mini-seminar in Trondheim will be held on October 17 15:00-18:00. This time, MySQL Monitoring and performance schema evangelist Mark Leith will visit us. The agenda will be similar to last mini-seminar:

  • Presentation: MySQL monitoring and performance Schema, by Mark Leith
  • Q&A, discussions
  • Suggestions for presentations on future seminars
  • Food and mingling
For more info, see the registration page.

Don't miss this opportunity to meet experts, developers and other MySQL users.

Thursday, June 20, 2013

The first MySQL mini-seminar in Trondheim was a huge success!

Yesterday, Geir Høydalsvik and I had the pleasure of hosting a MySQL mini-seminar in Trondheim, Norway.

The topic of the day was a presentation by yours truly on how the MySQL optimizer works. Geir briefly explained how the MySQL teams are organized in Oracle, and that our focus is on delivering high quality on time.

We had lots of interesting questions and discussions (and pizza) afterwards. Of particular interest was:
  • How the MySQL code base can be modularized to make it maintainable and testable. The takeaway was that MySQL has invested a lot on refactoring the last couple of years to improve in this area and will continue to do so in both the near and far future.
  • How testing is done in MySQL. The answer was that the QA teams have been significantly ramped up since Sun acquired MySQL. In addition to much more resources to the QA teams, developers are now expected to write unit tests using the Google unit test framework for new functionality whenever it makes sense. Refactoring and modularization of the code makes this a lot easier to do now.

Close to 20 MySQL users attended in addition to a handful of MySQL hackers located in the Trondheim office. The users had very different backgrounds: from start-ups to solid established companies, and deployment on MySQL 5.1, 5.5 and 5.6. All agreed that the mini-seminar was a great initiative and that it should not be a one-time event. Yesterday's event will therefore not be the mini-seminar, but rather the first in a series.

We'll get back to you with agenda and date for the next mini-seminar once the details have been decided.

Wednesday, February 13, 2013

DBT-3 Q3: 6 x performance in MySQL 5.6.10

When MySQL gets a query, it is the job of the optimizer to find the cheapest way to execute that query. Decisions include access method (range access, table scan, index lookup etc), join order, sorting strategy etc. If we simplify a bit, the optimizer first identifies the different ways to access each table and calculate their cost. After that, the join order is decided.

However, some access methods can only be considered after the join order has been decided and therefore gets special treatment in the MySQL optimizer. For join conditions, e.g. "WHERE table1.col1 = table2.col2",  index lookup can only be used in table2 if table1 is earlier in the join sequence. Another class of access methods is only meaningful for tables that are first in the join order. An example is queries with ORDER BY ... LIMIT. Prior to MySQL 5.6.10 there was a bug in MySQL that made the optimizer choose inefficient execution plans for this query type. That's the topic I discuss below.