Twitter, Inc. MySQL @Twitter: No More Forkin’ - Migrating to MySQL Community Version
Twitter, Inc. MySQL @Twitter • Background • Migration Process • Migration Challenges • Post-Deployment Challenges • Future Plans • Q&A http://despicableme2mkt103.blogspot.com/2013/06/welcome-minions.html
Twitter, Inc. Most Twitter Followers Celebrity @katyperry (109m) US President @BarackObama (102m) Comedian @TheEllenShow (77.7m) Website @YouTube (71.9m) Sports Figure @Cristiano (72m) Tech Figure @BillGates (45.7m) https://www.pinterest.co.uk/pin/615796949022229370
Twitter, Inc. Answers to you questions •No - These clusters do not contain tweets •No - I don’t have access to tweets and user profiles •Yes - These clusters are used by internal applications and some external-facing applications / services.
Twitter, Inc. Background • History of MySQL @Twitter • Rationale for Twitter Fork • List of Custom Changes • Reasons for Abandoning Fork
Twitter, Inc. History of MySQL @Twitter October 2005 - MySQL 5.0 Community March 2006 - First Tweet from Jack Dorsey Version Released November 2008 - MySQL 5.1 Community Version Released December 2010 - MySQL 5.5 Released September 2011 - Start of Twitter Fork - MySQL 5.5.16 Twitter Version March 2012 - MySQL 5.5.21 Twitter Version Released February 2013 - MySQL 5.6.10 Community Version Released June 2013 - Start of MySQL 5.6 Twitter Version Build March 2014 - MySQL 5.6.16 Twitter Version Released October 2015 - MySQL 5.7 Community Q4 2016 - Twitter Evaluation of MySQL 5.7 Version Released Community Version January 2017 - Last Release of MySQL Twitter Version (5.6.35)
Twitter, Inc. Rationale for Twitter Fork General Operability by the DBA Team • Agility to Change • Observability • Memory Management • Predictability • Performance • Auditability • Source: https://confluence.twitter.biz/ display/MYSQL/Home
Twitter, Inc. List of Custom Changes User name length increased from 16 to 32 (Twitter MySQL • 5.5.39) innodb_deadlock_check (Twitter MySQL 5.5.35) • Improve memory allocation, especially under NUMA (Twitter • MySQL 5.5.19) Add partitioning functions for YMD and YMDH (Twitter MySQL • 5.5.23)
Twitter, Inc. Reasons for Abandoning Our Branch • Cost • Leverage Community Support and Agility • MySQL 5.7 New Features • Automatic Password Expiration • JSON Data Type Support • Multi-Source Replication • And others https://tenor.com/view/running-workout-minions-cardio-gif-4349585
Twitter, Inc. #MigrationProcess • MySQL Clusters @Twitter • Migration Methodology (High-Level) • Migration Methodology (Server Upgrade) • Deployment Schedule and Timeline
Twitter, Inc. MySQL Clusters @Twitter • Sandboxes / Staging Clusters - 240 • Multi-Tenant Clusters - 100 • Single-Tenant Clusters - 410 • Revenue Clusters - 220 • Gizzard Clusters - 3400 https://www.pinterest.com/pin/494481234062937503/
Twitter, Inc. Migration Methodology In-place Upgrade vs. Logical Upgrade (Uninstall / Reinstall / Restore)
Twitter, Inc. Migration Methodology (High-Level) • Create MySQL 5.7 bridge from a standby slave using mysql_twtr_to_community - dump source (standby), scan dump for incompatibilities, load to destination (bridge) • Perform checksum between the bridge and standby (pt-table- checksum) • Upgrade 1 slave within the cluster and restore from the MySQL 5.7 bridge • After having 1 slave in MySQL 5.7 for a while and no issues encountered, upgrade remaining slaves • Upgrade standby • Promote standby to master (point of no return) • Upgrade old master to MySQL 5.7
Twitter, Inc. Migration Methodology (Server Upgrade) Block host from receiving traffic and unmonitor • Monitor performance of remaining slaves, check for increase in • total queries and slow queries Uninstall old version of MySQL • Install MySQL 5.7 • Restore databases from another MySQL 5.7 slave •
Twitter, Inc. Deployment Timeline/Schedule •2016 Q4 - MySQL 5.7 Migration Gap Analysis / Strategy Development •May 2017 - Developed Migration Scripts •June 2017 - Build Staging/Sandbox Environments (24) •October 2017 - Upgrade Multi-Tenant Clusters (10) •November/December 2017 - Upgrade Single-Tenant Clusters (41) •February 2018 to March 2018 - Upgrade Revenue Clusters (After SB)
Twitter, Inc. #MigrationChallenges • Logistic Challenges • Technical Challenges https://giphy.com/gifs/minions-lightbulb-dJz8PK0eMNXdS
Twitter, Inc. Migration Logistic Challenges Multi-tenant cluster coordination • Unable to find cluster owners • Holidays / Moratoriums / Year-End Vacations (Black Friday, • Christmas, New Year, Year-End) https://tenor.com/view/shopping-minions- https://giphy.com/gifs/christmas-despicable-me- black-friday-sho-gif-4698509 minions-bCEkUOFPlCSha http://minionnation.tumblr.com/post/107236814057/ happy-new-year-from-the-minions
Twitter, Inc. Migration Technical Challenges • pt-show-grants - password not included anymore in IDENTIFIED BY • pt-table-checksum results issues • Missing user grants • Twitter-specific system variables • Slave capacity • Cluster too active (bridge unable to catch-up) • Partition by timestamp • GTID issues
Twitter, Inc. Migration Technical Challenges pt-show-grants •MySQL 5.6 •GRANT USAGE *.* TO <User> IDENTIFIED BY PASSWORD <Password> •MySQL 5.7 •GRANT USAGE *.* TO <User> Solution: Created our own script to extract user grants that returns the same format both for MySQL 5.6 and MySQL 5.7
Twitter, Inc. Migration Technical Challenges pt-table-checksum results issues Issue: Error checksumming table <Table Name>: Possible infinite loop detected! Solution: Compared row count between standby and bridge for all tables in all databases.
Twitter, Inc. Migration Technical Challenges Missing User Grants Solution: Extract user grants from master and apply to upgraded slaves.
Twitter, Inc. Migration Technical Challenges Twitter-Specific System Variables Issue: Unable to install MySQL 5.7 due to unknown configuration setting. Solution: Remove offending system variable from the puppet attribute of the host (example: innodb_deadlock_check)
Twitter, Inc. Migration Technical Challenges Slave Capacity Bridge Standby Master MySQL 5.7 MySQL 5.6 MySQL 5.6 Slave Slave Slave MySQL 5.6 MySQL 5.6 MySQL 5.6 Solution: Add more slaves (temporarily / permanently) to replace the slave to be upgraded.
Twitter, Inc. Migration Technical Challenges Cluster too active Issue: Bridge unable to catch-up after restoring from slave due to the master receiving too much activity. Solution: Turn durability off
Twitter, Inc. Migration Technical Challenges Partition by timestamp Issue: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed. Cause: In MySQL 5.7, only unix_timestamp function is allowed to partition a table by RANGE based on the value of a timestamp column. Solution: Change data type of partition column to datetime
Twitter, Inc. Migration Technical Challenges GTID Issues Error 'When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a di ff erent value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation.
Twitter, Inc. GTID Issue West Coast Data Center Read-Only = 1 Read-Only = 1 Read-Only = 0 Bridge Standby Master pt-heartbeat (super user) Slave Slave Slave
Twitter, Inc. GTID Issue • Connect bridge to master? West Coast Data Center Read-Only = 1 Read-Only = 1 Read-Only = 0 Bridge Standby Master MySQL 5.7 pt-heartbeat (super user) Slave Slave Slave
Twitter, Inc. GTID Issue • Make the standby read-write? West Coast Data Center Read-Only = 1 Read-Only = 0 Read-Only = 0 Bridge Standby Master pt-heartbeat (super user) Slave Slave Slave
Twitter, Inc. GTID Issue - Solution West Coast Data Center Read-Only = 0 Read-Only = 1 Read-Only = 0 Bridge Standby Master MySQL 5.6 pt-heartbeat pt-heartbeat (super user) (super user) Bridge Slave Slave Slave MySQL 5.7 Read-Only = 1
Twitter, Inc. Post-Deployment Challenges • Compatibility Issues • Application Performance • Post-Migration Realizations • Recommendations
Twitter, Inc. Compatibility Issues •New Key Words and Reserved Words •LOAD DATA LOCAL INFILE •Incorrect usage of UNION and LIMIT
Twitter, Inc. Compatibility Issues New Key Words and Reserved Words • Account, Channel, Encryption, Filter, Instance • SELECT * FROM virtual Solution: • Put table names within quotes: SELECT * FROM `virtual` • Rename table to avoid the use of a reserved word
Twitter, Inc. Compatibility Issues LOAD DATA LOCAL INFILE •LOAD DATA LOCAL INFILE <File Name> INTO TABLE <Table Name> FIELDS TERMINATED BY ’,’ ENCLOSED BY ‘“‘ (<Fields List>); •Error: Invalid utf8 character string Cause : Change of default value of innodb_strict_mode changed from OFF to ON. Solution #1: Add “CHARACTER SET latin1” LOAD DATA LOCAL INFILE <File Name> INTO TABLE <Table Name> CHARACTER SET latin1 FIELDS TERMINATED BY ’,’ ENCLOSED BY ‘“‘ (<Fields List>); Solution #2: Convert input file to utf8 using iconv $ iconv —from-code=ISO-8859-1 --to-code=UTF-8 [Input File] > [Output File]
Recommend
More recommend
