WordPress.com - 500 Million MySQL Tables

Presentation given at dotScale 2013 in Paris, France

Transcript

1. Welcome! Thursday, June 20, 13
2. Barry Abrahamson http://barry.wordpress.com/ Thursday, June 20, 13
3. Thursday, June 20, 13
4. Thursday, June 20, 13
5. Thursday, June 20, 13
6. 500 Million Database Tables and Beyond The WordPress.com Story Thursday, June 20, 13
7. 2003 Thursday, June 20, 13
8. Thursday, June 20, 13
9. •posts •postmeta •comments •commentmeta •links •options •terms •term_taxonomy •term_relationships Tables Thursday, June 20, 13
10. 2005 Thursday, June 20, 13
11. Thursday, June 20, 13
12. •posts •postmeta •comments •commentmeta •links •options •terms •term_taxonomy •term_relationships Tables Thursday, June 20, 13
13. •terms •terms •terms •term_taxonomy •term_taxonomy •term_taxonomy •term_relationships •term_relationships •term_relationships Table Multiplication •posts •posts •posts •postmeta •postmeta •postmeta •comments •comments •comments •commentmeta •commentmeta •commentmeta •links •links •links •options •options •options Thursday, June 20, 13
14. Thursday, June 20, 13
15. [ERROR] /usr/sbin/mysqld: Can't open file: './wpcom/wp_1234_posts.frm (errno: 24) [ERROR] /usr/sbin/mysqld: Can't open file: './wpcom/wp_999_postmeta.frm (errno: 24) [ERROR] /usr/sbin/mysqld: Can't open file: './wpcom/wp_42341_links.frm (errno: 24) $ perror 24 OS error code 24: Too many open files Thursday, June 20, 13
16. 2006 Thursday, June 20, 13
17. MyISAM → InnoDB No Problem! Thursday, June 20, 13
18. Error establishing a database connection Thursday, June 20, 13
19. InnoDB Data Dictionary The data dictionary is InnoDB ‘s internal catalog of tables. InnoDB stores the data dictionary on disk, and loads entries into memory while the server is running. Once a table is opened, it is never removed from the data dictionary unless you drop the table or you restart the server. http://www.percona.com/doc/percona-server/5.5/management/innodb_dict_size_limit.html Thursday, June 20, 13
20. Daily “Meetings” No Problem! Thursday, June 20, 13
21. Monolithic Tables Thursday, June 20, 13
22. Monolithic Tables Thursday, June 20, 13
23. Sharding No Problem! (InnoDB → MyISAM) Thursday, June 20, 13
24. HyperDB • http://wordpress.org/plugins/hyperdb/ • Splitting of read/write queries • Data center & location awareness • Replication lag detection • Graceful failover Thursday, June 20, 13
25. Sharding • Smallest unit is one site. All tables for a single site must be in a single dataset. • Cluster = 5 servers. 1 master, 3 read-slaves (one in each data center) and 1 for backups. • Multiple MySQL instances per server didn’t scale linearly. Decided to use cheaper servers and stick with one instance per server. Thursday, June 20, 13
26. Static Hashing • Hash on md5 of $blog_id • Initially split into 4 data sets • Then to 8...then to 16 Thursday, June 20, 13
27. Static Hashing • Simple • Evenly balanced • Inflexible • Scaling was not much fun • Always happened during SXSW Thursday, June 20, 13
28. Dynamic Assignment • Store shard id as metadata associated with each site. • ALTER TABLE `wp_blogs` ADD COLUMN `shard_id` int(11) NOT NULL DEFAULT '0' Thursday, June 20, 13
29. Dynamic Assignment • Very flexible • Can add as many new clusters at a time as you wish • More complicated • Where do new sites go? • Need to move sites between clusters Thursday, June 20, 13
30. Activity-based • Active shards = 55,000 - 100,000 sites per shard • Inactive shards = 1,500,000 - 2,000,000 sites per shard Thursday, June 20, 13
31. Rebalancing • Completely automated • ~400k sites per day in June 2013 Thursday, June 20, 13
32. Performance • MySQL Query Cache • MySQL Key Buffer • Object caching • SSDs • Database is not our current bottleneck Thursday, June 20, 13
33. Backups • Dedicated MySQL instances • LVM snapshots for disaster recovery • mysqldump for site-level restore • Consistent snapshots across all datasets • ~10% of total infrastructure cost Thursday, June 20, 13
34. Backups • Internal 1 hour SLA to restore a complete dataset. • Includes provisioning new server, copying data, extracting data, syncing to point-in-time • Slowest part is generally copying across data centers • Need reasonably small datasets to achieve SLA. • ~500GB per shard Thursday, June 20, 13
35. Restores • 217 restores in 2012 • 29 restores so far in 2013 • 0 failures • Average restore time ~22 minutes Thursday, June 20, 13
36. Today Thursday, June 20, 13
37. Almost 50 million sites on WordPress.com Thursday, June 20, 13
38. Thursday, June 20, 13
39. Thursday, June 20, 13
40. Thursday, June 20, 13
41. Thursday, June 20, 13
42. • Almost 500 million database tables • 120 shards • ~2200 servers • ~500 database servers • Hundreds of thousands of queries per second Thursday, June 20, 13
43. Thursday, June 20, 13
44. Thursday, June 20, 13
45. • Scaling is hard • Start simple • Iteration is not failure • Backups are important, restoring is 100x more important Lessons Thursday, June 20, 13
46. Thanks! Thursday, June 20, 13