Feeds

Facebook open sources live MySQL makeover

Schema reschemed on fly

Internet Security Threat Report 2014

Facebook has open sourced a new MySQL utility that lets the social networking colossus update its database indexes and juice query times without staging the changes on test servers. With the tool – known as Online Schema Change for MySQL, or OSC – it can update indexes on live servers.

In the past, according Facebook MySQL guru Mark Callaghan, the company needed a good six months to roll index updates across its sea of MySQL servers. Now, it needs no more than a few days. "This lets us make schema changes much, much faster," Callaghan tells The Register. "And the benefit from the changes is that database queries will be faster."

At Facebook, MySQL is the primary repository for user data, with InnoDB the accompanying storage engine. "When it comes to user data, the workload is transaction processing – many simple database queries with a lot of concurrency," Callaghan explains. "And that workload is something that MySQL, along with InnoDB, excels at supporting. Performance is very good on that workload."

Nonetheless, the company is always working to improve performance, creating new indexes or updating existing indexes as a means of juicing query response time. For Facebook, such changes take to long when they're done through ALTER TABLE statements or the "fast index create" tool offered by the InnoDB plugin in MySQL 5.1. One other option is to first stage changes on test servers: you make the change on an extra server, and then you move the live workload onto that extra server when the change is complete.

But such juggling doesn't suit the sort of massive infrastructure that backs a social networking site serving over 500 million users. "We run a, well, large number of servers," Callaghan says, "and we prefer to not have a large number of extra servers."

All Callaghan will say is that the company runs "X thousands" of MySQL servers. "X" is such a large number, the company needed a way of making index changes on live machines. There's an existing tool that allows for live updates – "oak-online-alter-table," from independent developer Shlomi Noach – but Facebook has merely used this as a starting point.

"We wanted to do some things slightly different," Callaghan says. "And we do a lot in PHP, which [Noach's] tool doesn't use. It's a great tool, but it just wasn't right for us."

Written by Facebook engineer Vamsi Ponnekanti, Open Schema Change lets the company update indexes without user downtime, according to Callaghan. "Previously, we could make an update quickly on a small number of machines, but to do it on all of the machines, it took six months," Callaghan says, explaining that the company could only make updates during off-peak hours. "Now we can do it on all of the machines at pretty the same time and we can do it in about half a day.

"The difference is that there is no downtime, so we have more flexibility in scheduling when we do it."

When updating indexes, Facebook makes copies of its MySQL tables, and this alone can take a few hours. In essence, the OSC utility gives the company a way of accommodating changes that were made to the table while it was being copied.

"We need some way of applying the changes that we missed after the copy was started," he said. OSC does so using database triggers. "When the copy finishing, we replay all of the changes that were logged by the triggers, and then we briefly – for a fraction of a second – block access to the table and then we switch the original table with the copy."

This allows the company to make a change whenever it likes – even in the midst of peak workloads.

The code for OSC is derived from Shlomi Noach's tool, and like Noach's tool, it has been open sourced under a BSD license. You can find the code here.

At Facebook, the tool has been in place for about four weeks. And it's been used to make index changes the company has been planning for well over a year.

Facebook has developed other systems for other data storage and retrieval tasks, including the open source Cassandra for inbox search, the Hadoop-friendly SQL-like language known as Hive for analytics, and the proprietary Haystack for photos. But at least for the time being, MySQL will remain the tool of choice for user data.

"I personally don't have the time [to build a replacement]," Callaghan says. "All of my time is dedicated to improving quality of service and reliability of MySQL. So if it's going to be done, it's not going to be done by me." ®

Update: This story has been updated to mention ALTER TABLE statements and the "fast index create" InnoDB plugin tool as possible options for making index changes.

Choosing a cloud hosting partner with confidence

More from The Register

next story
Netscape Navigator - the browser that started it all - turns 20
It was 20 years ago today, Marc Andreeesen taught the band to play
Sway: Microsoft's new Office app doesn't have an Undo function
Content aggregation, meet the workplace ... oh
Sign off my IT project or I’ll PHONE your MUM
Honestly, it’s a piece of piss
Return of the Jedi – Apache reclaims web server crown
.london, .hamburg and .公司 - that's .com in Chinese - storm the web server charts
NetWare sales revive in China thanks to that man Snowden
If it ain't Microsoft, it's in fashion behind the Great Firewall
Chrome 38's new HTML tag support makes fatties FIT and SKINNIER
First browser to protect networks' bandwith using official spec
Admins! Never mind POODLE, there're NEW OpenSSL bugs to splat
Four new patches for open-source crypto libraries
prev story

Whitepapers

Forging a new future with identity relationship management
Learn about ForgeRock's next generation IRM platform and how it is designed to empower CEOS's and enterprises to engage with consumers.
Why cloud backup?
Combining the latest advancements in disk-based backup with secure, integrated, cloud technologies offer organizations fast and assured recovery of their critical enterprise data.
Win a year’s supply of chocolate
There is no techie angle to this competition so we're not going to pretend there is, but everyone loves chocolate so who cares.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Intelligent flash storage arrays
Tegile Intelligent Storage Arrays with IntelliFlash helps IT boost storage utilization and effciency while delivering unmatched storage savings and performance.