Scaling Data in Magento

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.

7ad4c8a7218e44fdf1600b4ebc451738?s=128

Alistair Stead

October 14, 2014
Tweet

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