Complex hot/cold storage re-architecture strategy
A client came with a performance problem: the e-com site struggled with performance problems due to popularity… good news for business, but the problem for the tech side
Discovery call
The discovery call has revealed the following:
- E-com targeting CMS-based solution
- SQL DB of approx. 40 GB size
- millions of records within primary tables
- complex JOIN queries originated by e-com front-end
- in-house team’s strategy: split the DB into hot and cold storages to reduce the number of records
Effectively, the trigger to search for external help was a lack of understanding and experience in how to split the DB and build complex hot/cold storage infrastructure.
Our solution
What we have proposed instead:
- let’s park the DB split
- let’s try to optimize the DB performance… the size of 40 GB does not sound scary, complex JOINs within millions of records – that is the natural use of relational DBs…
Our actions
What we have done:
- the overall site performance analysis together with APM (Application Performance Monitoring) tuning
- slow query execution plan analysis
EXPLAIN command can make a miracle!
Alternative technics that can improve the situation
Key analysis results and correction actions:
- a lot of static content/pages are constantly re-generated by the server causing the extra load to Web-server – caching
- execution plan analysis has revealed a lack of indices to support the JOIN operations – re-indexing the DB
You would be surprised, but the combination of above mentioned really standard and relatively simple technics has radically improved the situation!
Sometimes you need external help to see the problem
Most interesting, when we were digging into the problem, the in-house team was with us and absolutely knowledgeable about those approaches!
That could sound strange: the team knows about those approaches… why don’t they use them themselves? The reason is BAU(Business As Usual). When you are under the pressure, the constant fight to meet business expectations… you could be just tired, when you are dog tired the “tunnel vision“ comes…you focus on something and loses the broader picture… That’s when external help is required!
Outcome
The outcome of cooperation: instead of complex system re-engineering with the objective to build hot/cold storage we managed to help the team to solve the problem with well-known, simple configuration-based tweaks!
Frankly, I’m more than confident, that team was absolutely able to solve the problems by themselves, they simply lacked the capacity and space for thoughts beyond the BAU!
That’s OK to ask for help! In this case, the business made the right decision to support their in-house team with a side view and temporarily reinforce with external capacity!
Contact us in ApricusIT, together we are stronger!
Good luck with your projects!