Yahoo: Experiences with Percona Cluster
0 likes36 views
This document summarizes the experiences of Trey Raymond and Yashada Jadhav with implementing and operating Percona Cluster at Yahoo. It describes how they used PXC for the Yahoo Commerce Central application with three clusters across three data centers. It also discusses some of the operational challenges encountered like state transfers and sensitivity to network failures. Monitoring of the clusters and configurations are also outlined.
Yahoo: Experiences with Percona Cluster
- 1. E x p e r i e n c e s w i t h P e r c o n a C l u s t e r PRESENTED BY Yashada Jadhav and Trey Raymond April 14, 2015
- 2. About Trey 2 Yahoo Confidential & Proprietary Sr. MySQL Engineer with a background in shouting about keys per page 10 years experience with MySQL in a variety of environments Responsible for company schema design standards and SQL best practices Active helping folks and bantering in the freenode #mysql community In his free time, enjoys playing with his dog and receiving single malt scotch from conference attendees
- 3. About Yashada 3 Yahoo Confidential & Proprietary MySQL DevOps Engineer with a background in database design and performance tuning. 4+ years of experience on various flavors of relational databases. In her spare time, she enjoys listening to music and going to concerts. She appreciates sarcasm a lot too!
- 4. About Yahoo 4 Yahoo Confidential & Proprietary Yahoo is focused on making the worlds daily habits inspiring and entertaining. By creating highly personalized experiences for our users, we keep people connected to what matters most to them, across devices and around the world. In turn, we create value for advertisers by connecting them with the audiences that build their businesses More than 1B monthly active users across Yahoo and Tumblr More than 575M mobile monthly active users across Yahoo and Tumblr
- 5. Ad Products Team 5 Yahoo Confidential & Proprietary Thousands of Production Servers OLTP systems & Data marts Database Design and Architecture Capacity Planning and Performance Reviews 24x7 Monitoring and Operational Support Mission Statement: Delivering scalable and cost efficient data services through innovation and automation powering Yahoo Products
- 6. MySQL at Yahoo 6 Yahoo Confidential & Proprietary MySQL powers many mission-critical products within Advertising and User space across Desktop and Mobile Multiple production configurations based on product requirement DBaaS setup for multiple products Yahoo Sports: Mobile friendly Flickr: Sharded across thousands of servers Hot:Hot, Hot:Warm Configurations Versions range from Percona Server 5.1 to 5.6 including Percona XtraDB Cluster Operating systems running customized RHEL 5.6 to 6.5
- 7. PXC Implementations at Yahoo 7 Yahoo Confidential & Proprietary Yahoo Commerce Central - An open cloud platform that lets developers reach merchants on several E-Commerce platforms
- 8. ≒Hot:Hot Configuration ≒Scale up to future growth ≒Reduced cost of service ≒Easy integration with Yahoo products 8 Yahoo Confidential & Proprietary PXC at Yahoo
- 9. 財 API: Visitor statistics of merchant sites 財 UI: Inventory of all products ≒ 70% OLAP, 20% OLTP, 10% reporting ≒ 1000 7000 QPS 財 Metadata service: Billing and Subscription Management ≒ 90% OLTP load, 50% reads 50% writes ≒ 150-500 QPS 9 Yahoo Confidential & Proprietary YCC Product Architecture
- 10. 3 Clusters API, Proxy and MDB spread across 3 DCs. DNS Based Load Balancer based on proximity and round robin 10 Yahoo Confidential & Proprietary YCC Database Architecture
- 11. Configuration Specifics 11 Yahoo Confidential & Proprietary PXC version: 5.5.33.2 wsrep_provider_options: gmcast.listen_addr=tcp:// 0.0.0.0:13306;evs.keepalive_period=PT3S;evs.send_window=1024;evs.user_ send_window=512;evs.inactive_check_period=PT10S;evs.suspect_timeout=P T30S;evs.inactive_timeout=PT1M;evs.install_timeout=PT1M;gcache.size=8G;i st.recv_addr=tcp://_IPADDR_:2222 transaction_isolation: REPEATABLE-READ wsrep_retry_autocommit: 3 wsrep_slave_threads: 8 wsrep_sst_receive_address: _HOSTNAME_:9999
- 12. What do we monitor? 12 Yahoo Confidential & Proprietary wsrep_local_state wsrep_cluster_size wsrep_ready wsrep_cluster_status Long running queries Queries > 300 seconds for wsrep in pre-commit stage pt-query-digest
- 13. State Transfers 13 Yahoo Confidential & Proprietary OLAP cluster cache sizes were too less which triggered SST Database size > 1T SST is a pain! FTWRL at the end of SST causes front end downtimes We increased the cache sizes to avoid this issue.
- 14. OLTP vs OLAP Uptime 14 Yahoo Confidential & Proprietary OLAP Uptime OLTP Uptime
- 15. OLTP vs OLAP QPS 15 Yahoo Confidential & Proprietary OLAP QPS OLTP QPS
- 16. OLTP vs OLAP Deep Dive 16 Yahoo Confidential & Proprietary Database size Transactions per second
- 17. PXC Operations 17 Yahoo Confidential & Proprietary Sensitive to Network Failures Identical Hardware XtraBackup from a live node cause downtime Deadlocks and Cluster Stalls with Multi Node Writes Cluster is sensitive to all nodes Transaction sizes matter Race conditions
- 18. Dual Master Hot Warm Set up 18 Yahoo Confidential & Proprietary Read Write Traffic Splitting Better use of capacity across master and slaves Subject to replication lags and delays More suited for OLTP applications
- 19. PXC Bugs we hit 19 Yahoo Confidential & Proprietary Node consistency issues with foreign key grammar https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1260713 Non Deterministic Execution of Transactions from Same Threads https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1217653
- 20. Contact us 20 Yahoo Confidential & Proprietary Trey Raymond wraymond@yahoo-inc.com Yashada Jadhav yashada@yahoo-inc.com