OurSQL Episode 125: New and Improved...And Faster!

This week we discuss part 1 of the new optimizer enhancements in MySQL 5.6. Ear Candy is a gotcha about persistent replication configuration, and At the Movies is MVCC in PostgreSQL.

Events

Galera, SkySQL and MariaDB are doing a Road Show in Hamburg on February 1st and Stockholm on February 7th.

FOSDEM 2013 is taking place in Brussels, Belgium Sat Feb 2 - Sun Feb 3rd.

OSCON 2013 will take place in Portland, OR from Monday July 22nd through Friday July 26th. The call for speakers closes at midnight Pacific Time on Monday, February 4th.

Oracle's doing more MySQL tech tours. These seminars will be in the mornings, and are free. They will be on:
Tuesday, February 5th in Istanbul
Friday, February 15th near Milan
Tuesday, February 19th in Petach Tikva, Israel
Thursday, February 21st in Oslo and Brussels
Wednesday, March 20th in Stockholm.

LinuxFest Northwest will take place on Sat Apr 27th and Sun Apr 28th. The call for papers is open and you can submit proposals through Thursday Feb 28th.

Open Database Camp will be part of the Northeast LinuxFest at Harvard University, Cambridge MA Saturday March 16th and Sunday March 17th, 2013. You can register and submit presentations on the opensqlcamp.org website.

Percona Live: MySQL Conference and Expo 2013 is being held at the Santa Clara Convention Center Monday April 22nd - Thursday April 25th. Submit a lightning talk!

Training
MySQL Marinate, a FREE basic MySQL training course led by Sheeri.

SkySQL Trainings

Oracle's MySQL Trainings

Percona Trainings

MySQL 5.6 optimizer improvements, part 1
Episode 42, where we gave our first look at some new MySQL 5.6 features.

ZDnet reports that Product Manager Rob Young says MySQL 5.6 will be ready to ship in "early 2013"

Rob Young wrote an article about What's New in the MySQL 5.6 Release Candidate 4 months ago.

EXPLAIN on writes, and a blog post about EXPLAIN's new JSON format, which actually shows more information than EXPLAIN, including filtered, attached_condition, index_condition and used_key_parts, and MySQL Workbench Visual EXPLAIN.

Index Condition Pushdown
Setting the index_condition_pushdown flag of the optimizer_switch system variable.

optimizer_search_depth manual page, for optimizing a query that joins many tables.

MySQL manual page about optimizer_search_depth

Both SkySQL and Percona did benchmarks comparing different optimizer_search_depth values:
SkySQL's blog post about setting optimizer_search_depth
Percona's blog post aobut setting optimizer_search_depth

LIMIT optimization - specifically the optimizer won't use filesort if the sort_buffer_size is big enough and it would be more optimal to use the sort buffer as a queue than merge files to sort.

Batched key access

Gerry's blog post about gigantic IN clauses, which MySQL 5.6 can optimize by looking at its internal statistics. The internal statistics can be made more accurate by changing the eq_range_index_dive_limit variable. The documentation has an entire section with more details about equality range optimization.

InnoDB now has persistent InnoDB statistics, controlled by the innodb_stats_persistent variable.

You can control how much sampling is done by the innodb_stats_persistent_sample_pages and innodb_stats_transient_sample_pages variables.

You can override these variables by using the STATS_PERSISTENT and STATS_SAMPLE_PAGES clauses with CREATE and ALTER TABLE statements.

We also talked about innodb_table_stats, innodb_index_stats and using FLUSH TABLE tblname to clear statistics.

Ear Candy
In this week's ear candy, we present a gotcha on the new persistent replication configuration.

In order to turn on the persistent replication configuration, you need to set:
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay_log_recovery=ON

Without the relay_log_recovery set to ON, the 1st 2 options won't work as expected as per bug 67246.

At the Movies
This week we recommend checking a presentation by Bruce Momjian - MVCC in PostgreSQL from OSCON 2011. Postgresql uses a very different methodology from the ones we discussed in episode 107 for MySQL. The way Postgresql works is very different and it has some very interesting features. The sound quality is not perfect but it doesn't get in the way of the content.

Where you can see us
MySQL Marinate started January 1st, 2013. It is a virtual self-study course learning MySQL which both Gerry and Sheeri are leading. Even though it started last week, new members are accepted until Feb 1st.

Gerry will be at the February Seattle MySQL Meetup group on Monday February 11 2013.

Sheeri will be speaking at Rocky Mountain Oracle User Group Training Days in denver, colorodo from Monday Feb 11 - Thursday February 14th on "Backing up MySQL" and "Are You Getting the Most Out of Your Indexes?"

Sheeri will be speaking at SCALE 13 in Los Angeles from Friday, February 22nd through Sunday, February 24th on Are You Getting the Best Out of Your MySQL Indexes?

Sheeri will be speaking at Confoo in Montreal, Canada on Monday February 25 - Friday March 1, 2013, giving talks in English about "Are You Getting the Most Out of Your Indexes?" and "Different (MySQL) Forks for Different Folks".

Gerry will present a tutorial at Percona Live:MySQL Conference and Expo on Using TokuDB: A Guided Walk Through TokuDB Implementation. Many of the concepts apply to custom MySQL / MariaDB installations as well. Percona Live: MySQL Conference and Expo 2013 will be held April 22-25, 2013 in Santa Clara, California.