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
   

   View Slide

2. ALISTAIR STEAD
   CTO
   @ALISTAIRSTEAD
   #magetitans
   

   View Slide

3. FOCUSED SEASONAL TRENDS SUCH AS
   CYBER MONDAY HAVE THE CAPACITY TO
   MELT TIN
   #magetitans
   

   View Slide

4. SERVER CAPACITY CAN OF
   COURSE BE EXPANDED
   HARDWARE IS INEXPENSIVE...
   #magetitans
   

   View Slide

5. BACK PRESSURE
   FROM LOWER SYSTEMS WILL BLOCK PHP
   #magetitans
   

   View Slide

6. WHEN THE DB IS THE
   BOTTLENECK
   ADDING MORE SERVERS WILL ONLY MAKE IT
   WORSE
   #magetitans
   

   View Slide

7. I'm not a DBA
   #magetitans
   

   View Slide

8. WHEN YOUR STORE IS POPULAR YOU HAVE
   TO COPE LEARN
   #magetitans
   

   View Slide

9. DON'T JUST COPY WHAT IS ON
   STACKOVERFLOW
   IDENTIFY YOUR EXACT PROBLEM.
   #magetitans
   

   View Slide

10. YOU HAVE TO LEARN TO FIND THE RIGHT
    QUESTIONS FIRST!
    #magetitans
    

    View Slide

11. YOU HAVE TO FIND PEOPLE THAT CAN HELP!
    #magetitans
    

    View Slide

12. ENGINEERING IS NOT A SOLITARY PERSUIT
    #magetitans
    

    View Slide

13. SO, YOU HAVE YOUR STORE UP AND
    RUNNING...
    #magetitans
    

    View Slide

14. ALL WEB SERVERS ARE CONFIGURED AND
    RUNNING NICELY...
    #magetitans
    

    View Slide

15. YOU HAVE MAGENTO CONFIGURED FOR
    OPTIMAL RUNNING...
    #magetitans
    

    View Slide

16. NOW YOU HAVE MORE TRAFFIC AND THINGS
    ARE SLOWING DOWN...
    #magetitans
    

    View Slide

17. What do you do?
    #magetitans
    

    View Slide

18. Well... ..?
    #magetitans
    

    View Slide

19. YOU CAN'T DO ANYTHING WITHOUT
    INSTRUMENTATION
    #magetitans
    

    View Slide

20. DEVELOPMENT
    INSTRUMENTATION
    IDENTIFY PROBLEMS EARLY
    #magetitans
    

    View Slide

21. PRODUCTION
    INSTRUMENTATION
    WILL SHOW THE REAL PROBLEMS
    #magetitans
    

    View Slide

22. WE HAVE METRICS THAT SAY THE DB IS A
    SLOWING US DOWN
    #magetitans
    

    View Slide

23. We need to take some actions...
    #magetitans
    

    View Slide

24. BUT FIRST...
    a brief interlude
    #magetitans
    

    View Slide

25. PERFORMANCE IS A MOVING TARGET
    BUT IF YOU AIM FOR IT, YOU STAND A GREATER CHANCE OF HITTING IT
    #magetitans
    

    View Slide

26. PERFORMANCE, SCALING, HIGH
    AVAILABILITY AND REDUNDANCY
    ALL RELATED BUT SEPARATE THINGS
    #magetitans
    

    View Slide

27. PERFORMANCE
    THE ABILITY TO FUNCTION QUICKLY
    #magetitans
    

    View Slide

28. SCALING
    THE ABILITY TO FUNCTION WITHIN ACCEPTABLE LIMITS AS THE NUMBER OF USERS INCREASES
    #magetitans
    

    View Slide

29. HIGH AVAILABILITY
    THE ABILITY TO FACILITATE CONTINUOUS FUNCTION FOLLOWING AND DURING FAILURE
    #magetitans
    

    View Slide

30. REDUNDANCY
    DUPLICATION OF CRITICAL SYSTEMS SO AS TO HAVE NO SINGLE POINT OF FAILURE
    #magetitans
    

    View Slide

31. So where should we start?
    #magetitans
    

    View Slide

32. THE APACHE / NGINX PROCESS IS BLOCKING
    WAITING ON PHP...
    PHP IS WAITING ON THE DATABASE...
    #magetitans
    

    View Slide

33. 8 OF THE MANY STEPS TO
    PERFORMANCE
    #magetitans
    

    View Slide

34. STEP 1
    MAKE YOUR QUERIES FASTER
    #magetitans
    

    View Slide

35. MYSQL INDEXES
    IDENTIFY MISSING INDEXES FOR A QUERY AND SPEED UP THE RESULT
    #magetitans
    

    View Slide

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
    

    View Slide

37. STEP 2
    CACHE AS MUCH AS YOU CAN
    #magetitans
    

    View Slide

38. EXPAND QUERY CACHE AS
    MUCH AS YOU CAN
    CAN YOU FIT YOUR ENTIRE DB INTO MEMORY?
    #magetitans
    

    View Slide

39. USE FULL PAGE CACHE
    STATING THE OBVIOUS BUT IT PROTECTS THE DATABASE AT PEAK LOADS
    #magetitans
    

    View Slide

40. USE PROXY OR EDGE CACHES
    IF YOU DON'T NEED TO EXECUTE PHP DON'T
    #magetitans
    

    View Slide

41. AT SOME POINT YOUR CACHE
    MUST EXPIRE
    ON HIGHLY MERCHANDISED SITES THEN CACHE IS SIMPLY NOT AS EFFECTIVE
    #magetitans
    

    View Slide

42. BUT THIS IS ALL FOR READ
    OPERATIONS...
    What about writing data?
    #magetitans
    

    View Slide

43. LOCK WAIT TIMEOUT...
    HAVE YOU SEEN THIS IN YOUR EXCEPTION LOG?
    #magetitans
    

    View Slide

44. STEP 3
    ENSURE ALL TABLES ARE INNODB
    SOME LEGACY CODE WILL HAVE CREATED
    MYISAM
    #magetitans
    

    View Slide

45. INCREASE lock_wait_timeout
    DON'T THIS IS AN ANTI-PATTERN
    #magetitans
    

    View Slide

46. STEP 4
    TRANSACTION LEVEL
    #magetitans
    

    View Slide

47. USE READ COMMITTED
    INSTEAD OF THE MYSQL DEFAULT OF REPEATABLE READ
    #magetitans
    

    View Slide

48. STEP 5
    REDUCE TRANSACTION SIZE
    #magetitans
    

    View Slide

49. YOUR TRANSACTION IS NOT
    COMMITTED?
    YOU'RE WAITING FOR EXTERNAL SERVICE CALLS OR NONE CRITICAL WRITES...
    #magetitans
    

    View Slide

50. STEP 6
    REDUCING NON-CRITICAL WRITE
    OPERATIONS
    #magetitans
    

    View Slide

51. LOGGING CAN BE DONE SOMEWHERE ELSE
    
    
    
    
    disabled
    
    
    disabled
    
    ...
    
    
    #magetitans
    

    View Slide

52. HTTP 101
    ONLY MODIFY STATE ON HTTP POST
    #TIP 1. THIS SIMPLE RULE CAN HELP SO MANY ASPECTS OF SCALING
    #magetitans
    

    View Slide

53. OFF-LOAD FUNCTIONALITY TO
    THIRD PARTIES
    LOGGING AND TRACKING CAN BE HANDLED ELSEWHERE
    #magetitans
    

    View Slide

54. MOVE DATA AND LOGIC TO
    THE CLIENT
    IF STATE HAS NOT CHANGED THEN THE CLIENT SHOULD KNOW ALL IT NEEDS TO KNOW
    #magetitans
    

    View Slide

55. STEP 7
    ASYNCHRONOUS WRITE OPERATIONS
    #magetitans
    

    View Slide

56. USE JOB QUEUES
    NON-CRTICAL WRITE OPERATIONS CAN BE PUSHED TO THE QUEUE
    YOU THEN HAVE TO WORK WITH EVENTUAL CONSISTENCY
    #magetitans
    

    View Slide

57. STEP 8
    CLUSTERING & REPLICATION
    #magetitans
    

    View Slide

58. INTRODUCE A SLAVE
    DATABASE
    REPLICATE DATA TO THE SLAVE DATABASE
    #magetitans
    

    View Slide

59. USE STANDARD MYSQL
    REPLICATION
    ENABLE BINARY LOGGING
    #magetitans
    

    View Slide

60. ENSURE YOU HAVE
    COMPRESSION ENABLED!
    OR YOU WILL FLOOD YOUR INTERNAL NETWORK
    #magetitans
    

    View Slide

61. USE MIXED BINARY
    LOGGING FORMAT
    FOR QUICKER REPLICATION
    #magetitans
    

    View Slide

62. STATEMENT BINARY
    LOGGING
    CAN CAUSE PK CLASHES... IN OUR EXPERIENCE...
    #magetitans
    

    View Slide

63. SINGLE THREADED
    REPLICATION
    PRIOR TO MYSQL 5.6 YOU ONLY HAVE ONE THREAD
    #magetitans
    

    View Slide

64. SPLIT READ FROM WRITE
    OPERATIONS
    ACROSS THE CLUSTER
    #magetitans
    

    View Slide

65. WRITE, READ CONSISTENCY
    CAN BE RESOLVED WITH MODULE LEVEL CONNECTIONS
    #magetitans
    

    View Slide

66. EXAMPLE MODULE CONFIG.XML
    
    
    
    
    
    
    core_write
    
    
    
    
    
    #magetitans
    

    View Slide

67. 3 GOALS OF HIGH
    AVAILABILITY...
    #magetitans
    

    View Slide

68. GOAL 1
    ZERO POINTS OF FAILURE?
    PLAN FOR FAILURE!
    #magetitans
    

    View Slide

69. GOAL 2
    AUTOMATED RECOVERY OF SERVICES AND
    DATA
    #magetitans
    

    View Slide

70. GOAL 3
    NETWORK SEGMENTATION WILL HAVE ZERO
    IMPACT ON PERFORMANCE
    #magetitans
    

    View Slide

71. CLUSTER & REPLICATION
    OPTIONS
    #magetitans
    

    View Slide

72. REDHAT CLUSTER INTERFACE
    #magetitans
    

    View Slide

73. MYSQL FABRIC
    #magetitans
    

    View Slide

74. TUNGSTEN
    #magetitans
    

    View Slide

75. REPLICATOR
    A MULTI-THREADED REPLICATION PROCESS OVER MYSQL AND OTHER DATABASES
    #magetitans
    

    View Slide

76. SMART CONNECTION
    MANAGER
    A SMART CONNECTION MANAGER THAT CAN ROUTE CONNECTIONS BASED ON QUERY CONTENT
    #magetitans
    

    View Slide

77. HIGH AVAILABILITY
    CONNECTION MANAGER PROVIDES ACTIVE SERVICE DISCOVERY
    MAGENTO CONNECTS TO A LOCAL PORT 3306
    #magetitans
    

    View Slide

78. CONNECTION MANAGER
    RUNS ON EVERY SERVER AND ALLOWS THE MASTER TO FLOAT AROUND THE CLUSTER
    #magetitans
    

    View Slide

79. HOT PRODUCTION UPGRADES
    MYSQL CAN BE CONFIGURED OR UPGRADED WITH ZERO DOWNTIME
    #magetitans
    

    View Slide

80. THE MASTER DATABASE
    CAN BE MOVED TO ANY NODE WITHOUT CONFIG CHANGES OR DOWNTIME
    #magetitans
    

    View Slide

81. SERVICE DISCOVERY
    ALL SERVERS CONNECT TO THEIR OWN CONNECTION MANAGER
    #magetitans
    

    View Slide

82. Next steps...
    #magetitans
    

    View Slide

83. ONE SETTING DOES NOT RULE
    THEM ALL
    USE YOUR OWN METRICS TO DEFINE THE BEST SETTINGS FOR YOUR APPLICATION
    #magetitans
    

    View Slide

84. ONE CONNECTION DOES NOT
    FIT ALL TASKS
    USE MANY TUNED CONNECTIONS FOR SPECIFIC OPERATIONS TYPES
    #magetitans
    

    View Slide

85. ALTERNATE REPLICATION
    ARCHITECTURE
    FAN-IN FOR EXAMPLE ALLOWING MULTIPLE MASTERS
    #magetitans
    

    View Slide

86. SHARDING
    THE SMART CONNECTOR CAN RE-WRITE THE QUERY ON THE FLY
    YOU WILL NEED TO INTRODUCE UUIDS FOR KEY TABLES
    #magetitans
    

    View Slide

87. Real Numbers
    #magetitans
    

    View Slide

88. ~ €850,000 revenue per day
    #magetitans
    

    View Slide

89. ~ 23,000 orders per day
    #magetitans
    

    View Slide

90. ~ 2000 orders per hour at peak
    #magetitans
    

    View Slide

91. These are still small numbers!
    #magetitans
    

    View Slide

92. They will be much higher
    this holiday season
    #magetitans
    

    View Slide

93. Gotchas...
    #magetitans
    

    View Slide

94. TURN OFF AUTOMATIC
    SECURITY UPDATES
    YOUR CLUSTER WILL FAIL
    #magetitans
    

    View Slide

95. ALLOCATE ENOUGH RAM
    HEAVY MERCHANDISING CAN YIELD LARGE TRANSACTION SIZE
    #magetitans
    

    View Slide

96. DO YOU HAVE ENOUGH FILE
    DESCRIPTORS?
    THIS WILL BE LIMITED UNLESS YOU CONFIGURE THE SYSTEM
    #magetitans
    

    View Slide

97. BE CAREFUL WITH MAGENTO
    UPGRADES
    DISABLE AUTOMATIC APPLICATION OF UPGRADE AND DATA SCRIPTS
    #magetitans
    

    View Slide

98. THANK YOU!
    Questions?
    #magetitans
    

    View Slide

99. HTTP://BIT.LY/SDINMAGE
    #magetitans
    

    View Slide