Global transaction IDs, full-text searches highlight MySQL 5.6 release

Performance boost is an important part of the MySQL 5.6 release, although integration may be an issue early on.

After two years of development, Oracle Corp. has released its first all-in-house version of MySQL. The MySQL 5.6 release debuted in early February, and experts say it represents a significant (though not unmitigated) improvement over version 5.5.

"I think there's something for everybody in 5.6," said Sheeri Cabral, senior database administrator and architect for Mozilla Corp. "As soon as I finish upgrading all of my machines to 5.5, I'll upgrade to 5.6."

The upgraded explain function offers a big boost to experts dealing with legacy systems that tend to be buried in layers of workarounds.

The new version offers raw performance improvements in addition to a batch of new features that make Cabral and others happy.

MySQL 5.6 global transaction IDs

Global transaction IDs give each action within your MySQL database a unique identifying number recognized by all the machines within the system. This change, Cabral said, reduces a lot of work when it's time to take a master down for maintenance. Instead of the "very fiddly" process of syncing one slave to another, the new global transaction IDs allow you change host with a simple command. The MySQL 5.6 release framework will take over from there -- but only if you want it to. MySQL will simplify the transition by default, but administrators can still fiddle if they want to.

This change is so "revolutionary," Cabral said, that she can imagine people in just a few years talking about the "old days" of having to tinker with servers after a failure.

MySQL 5.6 performance, full-text searches

According to Oracle's notes, the MySQL 5.6 release represents an approximately 200% transaction-per-second increase over 5.5 under some conditions, and its improvements should shine brightest as administrators add more connections and CPU threads.

Giuseppe Maxia, who worked on the MySQL team in several capacities from 2006 to 2010 and now heads quality assurance for Continuent Inc., said most of his customers should see a speed increase simply by upgrading to 5.6 without any additional optimization. However, they shouldn't expect speeds as fast as those advertised.

"The improvement is huge," said Maxia. "It's not momentous. It's not revolutionary, but it's big."

While several MySQL watchers, including Peter Zaitsev at the MySQL Performance Blog, have shown that the MySQL 5.6 release is slower than 5.5 under some circumstances, Maxia said those claims are overblown. Observers draw those conclusions, he said, by analyzing very specific and unusual situations. Zaitsev himself wrote that his analysis came from a "worst-case scenario" of a 5.6 implementation.

Maxia and Cabral both said the new InnoDB full-text searches excited them. According to Oracle's DBA and Developer Guide to What's New in MySQL 5.6, the change allows administrators to "build full-text indexes on InnoDB tables" -- which, in turn, allows InnoDB to handle word- and phrase-based searches without having to move data outside of the table.

The immediate effect of this change, said Maxia and Cabral, is that administrators can jettison MyISAM tables. MyISAM -- which doesn't support transactions -- had been kept around primarily as a workaround for InnoDB's lack of full-text search and tended to make systems less stable. Now, Maxia said, he can tell any of his customers still using MyISAM to get rid of them and trade up for a safer, more robust environment that crashes less often.

"This will probably be, long term, the biggest improvement for searches," Maxia said.

Upgraded explain function, iffy integration

Version 5.6 also helps administrators better understand how transactions will snake through their database. The upgraded explain function can now be used in conjunction with "insert," "update," and "delete." The function kicks out a description of which tables the function will touch and how. As an additional bonus, the newly-improved explanations can be interpreted by outside JSON programs to create a visual representation of how and where a given query impacts a database.

While the feature might offer a little help to newly minted administrators, Maxia said that the feature offers a big boost to the experts dealing with legacy systems that tend to be buried in layers of workarounds.

Maxia said the new tools please him overall -- and he added that they should probably please everybody -- but he's concerned at how they might work together.

In addition to the new global transaction IDs, the MySQL 5.6 release allows for parallel updates on new multi-threaded slaves. Both are nice features, Maxia said, but he's found that administrators can only use one or the other. For administrators who use both, he said, "You can get the job done, but the job's not easier."

This was first published in March 2013

Dig deeper on Oracle MySQL database

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close