MySQL, an open source database developed by Oracle, powers some of Facebook’s most important workloads. We actively develop new features in MySQL to support our evolving requirements. These feature…
Overview
The article discusses Facebook's migration from MySQL 5.6 to MySQL 8.0, detailing the challenges faced, the migration process, and the benefits of new features. It highlights the complexities of porting custom features, ensuring compatibility, and the extensive testing required to validate the migration.
What You'll Learn
1
How to manage the migration of a large-scale MySQL database to a new version
2
Why row-based replication is essential for MySQL 8.0 migration
3
How to validate automation and application compatibility during a database upgrade
Prerequisites & Requirements
- Understanding of MySQL database architecture and replication
- Experience with large-scale database management
Key Questions Answered
What challenges did Facebook face during the MySQL 8.0 migration?
Facebook encountered several challenges during the migration, including porting over 1,700 custom code patches, ensuring replication compatibility, minimizing changes to existing application queries, and addressing performance regressions. The complexity of upgrading directly from 5.6 to 8.0 without passing through 5.7 added to these difficulties.
How did Facebook automate the migration process of MySQL replica sets?
Facebook automated the migration by grouping multiple mysqld instances into replica sets and transitioning them through a series of steps. This included creating 8.0 secondaries, enabling read traffic, promoting the 8.0 instance to primary, and eventually removing the 5.6 instances, allowing for independent transitions and rollbacks as needed.
What specific features did MySQL 8.0 offer that were beneficial for Facebook?
MySQL 8.0 introduced several compelling features such as writeset-based parallel replication, a transactional data dictionary for atomic DDL support, and enhancements like instant DDL that could speed up schema changes for MyRocks. These features were crucial for Facebook's evolving database requirements.
What performance issues were discovered during the MySQL 8.0 migration?
During the migration, Facebook identified new mutex contention hotspots around the ACL cache and binlog index access, which caused performance issues under high load. Additionally, some queries involving temporary tables returned unexpected errors or timed out, necessitating further investigation and optimization.
Key Statistics & Figures
Number of code patches to port
1,700
This was the initial count of custom patches that needed to be ported from MySQL 5.6 to 8.0.
Total patches evaluated
2,300
By the end of the migration process, Facebook had evaluated over 2,300 patches and successfully ported 1,500 of them to MySQL 8.0.
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Mysql
Used as the primary database system for Facebook's workloads.
Storage Engine
Myrocks
A custom storage engine developed by Facebook to optimize MySQL for their specific needs.
Key Actionable Insights
1Establish a comprehensive testing framework before migrating to a new database version.This ensures that all automation and application queries are compatible with the new version, reducing the risk of encountering issues post-migration.
2Utilize row-based replication to facilitate smoother transitions during database upgrades.Row-based replication simplifies the migration process and helps avoid issues related to statement-based replication, especially when dealing with complex queries.
3Organize code patches into categories to streamline the porting process.By categorizing patches as drop, build/client, non-MyRocks server, and MyRocks server, teams can prioritize and manage the migration more effectively, ensuring critical features are retained.
Common Pitfalls
1
Skipping a major version during migration can lead to unforeseen compatibility issues.
By skipping MySQL 5.7, Facebook faced challenges in detecting API changes and had to run extensive shadow tests to identify failures, which could have been mitigated with proper version upgrades.
2
Not validating automation against the new database version can result in significant operational issues.
As Facebook discovered, changes in error codes and server output formats in MySQL 8.0 broke existing automation tools, necessitating updates to ensure compatibility.
Related Concepts
Database Migration Strategies
Replication Techniques In Mysql
Performance Tuning For Mysql
Custom Storage Engines In Databases