Scaling Data in Magento

In this technical session, we will look at how you can scale the database horizontally behind Magento. We will discuss the reasons for scaling through replication and how this may impact on your infrastructure, deployment and Magento implementation. Replication brings with it a great deal of benefits but also some pitfalls and potential problems with things such as high rates of data change. We will present simple solutions with MySQL replication and also using Tungsten by Continuent to bring high availability and high performance to MySQL. This will be backed by real data and metrics from one of the highest volume Magento stores in the UK showing how Magento can be deployed at scale with high availability to serve the UK, USA and Australia from a single implementation generating over $100 million in revenue.

Transcript

1. Scalable and Highly Available Data in MAGENTO #magetitans
2. ALISTAIR STEAD CTO @ALISTAIRSTEAD #magetitans
3. FOCUSED SEASONAL TRENDS SUCH AS CYBER MONDAY HAVE THE CAPACITY TO MELT TIN #magetitans
4. SERVER CAPACITY CAN OF COURSE BE EXPANDED HARDWARE IS INEXPENSIVE... #magetitans
5. BACK PRESSURE FROM LOWER SYSTEMS WILL BLOCK PHP #magetitans
6. WHEN THE DB IS THE BOTTLENECK ADDING MORE SERVERS WILL ONLY MAKE IT WORSE #magetitans
7. I'm not a DBA #magetitans
8. WHEN YOUR STORE IS POPULAR YOU HAVE TO COPE LEARN #magetitans
9. DON'T JUST COPY WHAT IS ON STACKOVERFLOW IDENTIFY YOUR EXACT PROBLEM. #magetitans
10. YOU HAVE TO LEARN TO FIND THE RIGHT QUESTIONS FIRST! #magetitans
11. YOU HAVE TO FIND PEOPLE THAT CAN HELP! #magetitans
12. ENGINEERING IS NOT A SOLITARY PERSUIT #magetitans
13. SO, YOU HAVE YOUR STORE UP AND RUNNING... #magetitans
14. ALL WEB SERVERS ARE CONFIGURED AND RUNNING NICELY... #magetitans
15. YOU HAVE MAGENTO CONFIGURED FOR OPTIMAL RUNNING... #magetitans
16. NOW YOU HAVE MORE TRAFFIC AND THINGS ARE SLOWING DOWN... #magetitans
17. What do you do? #magetitans
18. Well... ..? #magetitans
19. YOU CAN'T DO ANYTHING WITHOUT INSTRUMENTATION #magetitans
20. DEVELOPMENT INSTRUMENTATION IDENTIFY PROBLEMS EARLY #magetitans
21. PRODUCTION INSTRUMENTATION WILL SHOW THE REAL PROBLEMS #magetitans
22. WE HAVE METRICS THAT SAY THE DB IS A SLOWING US DOWN #magetitans
23. We need to take some actions... #magetitans
24. BUT FIRST... a brief interlude #magetitans
25. PERFORMANCE IS A MOVING TARGET BUT IF YOU AIM FOR IT, YOU STAND A GREATER CHANCE OF HITTING IT #magetitans
26. PERFORMANCE, SCALING, HIGH AVAILABILITY AND REDUNDANCY ALL RELATED BUT SEPARATE THINGS #magetitans
27. PERFORMANCE THE ABILITY TO FUNCTION QUICKLY #magetitans
28. SCALING THE ABILITY TO FUNCTION WITHIN ACCEPTABLE LIMITS AS THE NUMBER OF USERS INCREASES #magetitans
29. HIGH AVAILABILITY THE ABILITY TO FACILITATE CONTINUOUS FUNCTION FOLLOWING AND DURING FAILURE #magetitans
30. REDUNDANCY DUPLICATION OF CRITICAL SYSTEMS SO AS TO HAVE NO SINGLE POINT OF FAILURE #magetitans
31. So where should we start? #magetitans
32. THE APACHE / NGINX PROCESS IS BLOCKING WAITING ON PHP... PHP IS WAITING ON THE DATABASE... #magetitans
33. 8 OF THE MANY STEPS TO PERFORMANCE #magetitans
34. STEP 1 MAKE YOUR QUERIES FASTER #magetitans
35. MYSQL INDEXES IDENTIFY MISSING INDEXES FOR A QUERY AND SPEED UP THE RESULT #magetitans
36. RE-DESIGN QUERIES NOT RECOMMENDED FOR CORE QUERIES BUT SOMETIMES YOU HAVE TO... HOWEVER SEND A PATCH BACK TO MAGENTO FOR INCLUSION IN THE NEXT RELEASE #magetitans
37. STEP 2 CACHE AS MUCH AS YOU CAN #magetitans
38. EXPAND QUERY CACHE AS MUCH AS YOU CAN CAN YOU FIT YOUR ENTIRE DB INTO MEMORY? #magetitans
39. USE FULL PAGE CACHE STATING THE OBVIOUS BUT IT PROTECTS THE DATABASE AT PEAK LOADS #magetitans
40. USE PROXY OR EDGE CACHES IF YOU DON'T NEED TO EXECUTE PHP DON'T #magetitans
41. AT SOME POINT YOUR CACHE MUST EXPIRE ON HIGHLY MERCHANDISED SITES THEN CACHE IS SIMPLY NOT AS EFFECTIVE #magetitans
42. BUT THIS IS ALL FOR READ OPERATIONS... What about writing data? #magetitans
43. LOCK WAIT TIMEOUT... HAVE YOU SEEN THIS IN YOUR EXCEPTION LOG? #magetitans
44. STEP 3 ENSURE ALL TABLES ARE INNODB SOME LEGACY CODE WILL HAVE CREATED MYISAM #magetitans
45. INCREASE lock_wait_timeout DON'T THIS IS AN ANTI-PATTERN #magetitans
46. STEP 4 TRANSACTION LEVEL #magetitans
47. USE READ COMMITTED INSTEAD OF THE MYSQL DEFAULT OF REPEATABLE READ #magetitans
48. STEP 5 REDUCE TRANSACTION SIZE #magetitans
49. YOUR TRANSACTION IS NOT COMMITTED? YOU'RE WAITING FOR EXTERNAL SERVICE CALLS OR NONE CRITICAL WRITES... #magetitans
50. STEP 6 REDUCING NON-CRITICAL WRITE OPERATIONS #magetitans
51. LOGGING CAN BE DONE SOMEWHERE ELSE <?xml version="1.0" encoding="UTF-8"?> <frontend> <events> <controller_action_predispatch> <observers><log><type>disabled</type></log></observers> </controller_action_predispatch> <controller_action_postdispatch> <observers><log><type>disabled</type></log></observers> </controller_action_postdispatch> ... </events> </frontend> #magetitans
52. HTTP 101 ONLY MODIFY STATE ON HTTP POST #TIP 1. THIS SIMPLE RULE CAN HELP SO MANY ASPECTS OF SCALING #magetitans
53. OFF-LOAD FUNCTIONALITY TO THIRD PARTIES LOGGING AND TRACKING CAN BE HANDLED ELSEWHERE #magetitans
54. MOVE DATA AND LOGIC TO THE CLIENT IF STATE HAS NOT CHANGED THEN THE CLIENT SHOULD KNOW ALL IT NEEDS TO KNOW #magetitans
55. STEP 7 ASYNCHRONOUS WRITE OPERATIONS #magetitans
56. USE JOB QUEUES NON-CRTICAL WRITE OPERATIONS CAN BE PUSHED TO THE QUEUE YOU THEN HAVE TO WORK WITH EVENTUAL CONSISTENCY #magetitans
57. STEP 8 CLUSTERING & REPLICATION #magetitans
58. INTRODUCE A SLAVE DATABASE REPLICATE DATA TO THE SLAVE DATABASE #magetitans
59. USE STANDARD MYSQL REPLICATION ENABLE BINARY LOGGING #magetitans
60. ENSURE YOU HAVE COMPRESSION ENABLED! OR YOU WILL FLOOD YOUR INTERNAL NETWORK #magetitans
61. USE MIXED BINARY LOGGING FORMAT FOR QUICKER REPLICATION #magetitans
62. STATEMENT BINARY LOGGING CAN CAUSE PK CLASHES... IN OUR EXPERIENCE... #magetitans
63. SINGLE THREADED REPLICATION PRIOR TO MYSQL 5.6 YOU ONLY HAVE ONE THREAD #magetitans
64. SPLIT READ FROM WRITE OPERATIONS ACROSS THE CLUSTER #magetitans
65. WRITE, READ CONSISTENCY CAN BE RESOLVED WITH MODULE LEVEL CONNECTIONS #magetitans
66. EXAMPLE MODULE CONFIG.XML <?xml version="1.0" encoding="UTF-8"?> <config> <global> <resources> <module_read> <connection> <use>core_write</use> </connection> </module_read> </resources> </global> </config> #magetitans
67. 3 GOALS OF HIGH AVAILABILITY... #magetitans
68. GOAL 1 ZERO POINTS OF FAILURE? PLAN FOR FAILURE! #magetitans
69. GOAL 2 AUTOMATED RECOVERY OF SERVICES AND DATA #magetitans
70. GOAL 3 NETWORK SEGMENTATION WILL HAVE ZERO IMPACT ON PERFORMANCE #magetitans
71. CLUSTER & REPLICATION OPTIONS #magetitans
72. REDHAT CLUSTER INTERFACE #magetitans
73. MYSQL FABRIC #magetitans
74. TUNGSTEN #magetitans
75. REPLICATOR A MULTI-THREADED REPLICATION PROCESS OVER MYSQL AND OTHER DATABASES #magetitans
76. SMART CONNECTION MANAGER A SMART CONNECTION MANAGER THAT CAN ROUTE CONNECTIONS BASED ON QUERY CONTENT #magetitans
77. HIGH AVAILABILITY CONNECTION MANAGER PROVIDES ACTIVE SERVICE DISCOVERY MAGENTO CONNECTS TO A LOCAL PORT 3306 #magetitans
78. CONNECTION MANAGER RUNS ON EVERY SERVER AND ALLOWS THE MASTER TO FLOAT AROUND THE CLUSTER #magetitans
79. HOT PRODUCTION UPGRADES MYSQL CAN BE CONFIGURED OR UPGRADED WITH ZERO DOWNTIME #magetitans
80. THE MASTER DATABASE CAN BE MOVED TO ANY NODE WITHOUT CONFIG CHANGES OR DOWNTIME #magetitans
81. SERVICE DISCOVERY ALL SERVERS CONNECT TO THEIR OWN CONNECTION MANAGER #magetitans
82. Next steps... #magetitans
83. ONE SETTING DOES NOT RULE THEM ALL USE YOUR OWN METRICS TO DEFINE THE BEST SETTINGS FOR YOUR APPLICATION #magetitans
84. ONE CONNECTION DOES NOT FIT ALL TASKS USE MANY TUNED CONNECTIONS FOR SPECIFIC OPERATIONS TYPES #magetitans
85. ALTERNATE REPLICATION ARCHITECTURE FAN-IN FOR EXAMPLE ALLOWING MULTIPLE MASTERS #magetitans
86. SHARDING THE SMART CONNECTOR CAN RE-WRITE THE QUERY ON THE FLY YOU WILL NEED TO INTRODUCE UUIDS FOR KEY TABLES #magetitans
87. Real Numbers #magetitans
88. ~ €850,000 revenue per day #magetitans
89. ~ 23,000 orders per day #magetitans
90. ~ 2000 orders per hour at peak #magetitans
91. These are still small numbers! #magetitans
92. They will be much higher this holiday season #magetitans
93. Gotchas... #magetitans
94. TURN OFF AUTOMATIC SECURITY UPDATES YOUR CLUSTER WILL FAIL #magetitans
95. ALLOCATE ENOUGH RAM HEAVY MERCHANDISING CAN YIELD LARGE TRANSACTION SIZE #magetitans
96. DO YOU HAVE ENOUGH FILE DESCRIPTORS? THIS WILL BE LIMITED UNLESS YOU CONFIGURE THE SYSTEM #magetitans
97. BE CAREFUL WITH MAGENTO UPGRADES DISABLE AUTOMATIC APPLICATION OF UPGRADE AND DATA SCRIPTS #magetitans
98. THANK YOU! Questions? #magetitans
99. HTTP://BIT.LY/SDINMAGE #magetitans