I have been working on a large scale greenfield development for over 12 months now. When I recommended Azure, I had no doubt that it was the correct choice. And for sure it has not let me down.
There are a few things that I have learnt and especially now with the new pricing structure in Azure you have to be carefull how to calculate stuff.
Now we drive about 800 sites from a database, each site has a list of a few hundred thousand rows for prices, catalog images, brand filtering, which all link back an external CDN. The problem started when we got about 500 sites * 100 thousand rows, 50+ million rows of pricing junk.
Thats not bad! All your compute belong to instance... not actually. When the pricing was designed it was decided to compute, link, squash, decide what belongs where in a stored procedure. This was great because we could offload all that computation onto the SQL Database and not pay anything extra. At this stage we started running into problems with connection time outs and really really slow sites.
So now we are lucky the new structure came in and we could switch to P2 (£590 a month just for database) but the sites are running fine again. Here is a chart of average "CPU usage" as they call it for out SQL Instance.
So on the very left you almost hear our sweat dripping while the database is deadlocked and running like a dying dog :( We moved from business into Standard 2, restarted everything but soon a back log started to build up and about 10/07 we moved into Premium 1. At peak times we were still experiencing deadlocks and the sites were just not as nippy as we wanted the. You can clearly see midway 11/07 a shift in the trend. Hello Premium 2! £590 per month (half price because its in preview mode, fuck me!)
What have I learnt?
Stored procedures are shit and I always knew this. But sometimes you just got to do it because you were told so. What really should have happened is that all the number crunching should have happening on the instances/ worker roles. Get the data we need with LINQ2SQL or Entity. Crunch and cache it on the front end / middle layer or whatever, just not in the bloody database for fucks sake, and refresh it once and a while.
Why?
Because expanding and contracting instance sizes is quick and is super cheap compared to bloody Premium 2 SQL. So if we needed to re calculate over 75 millions rows as it stands now, we could use the auto health feature "in preview as of writing" to scale out when CPU usage goes over a limit. So we could expand to maximum, crunch the numbers in no time, cache the results, and only pay a fraction while continuously experience blazingly fast speeds without breaking a sweat.
Alternatives
I proposed to use RavenDB NoSQL for the stock and pricing. But if you read what was above, you would realise our team was reluctant to use Entity Framework from the start, because they didn't trust its reliability. So imagine how they took on to NoSQL. Its built to solve this problem, in your face demonstrations and used by Facebook and Stack Exchange, -"We'll think about it"
Conclusion
Part of the problems is caused by "me (yourself as proposer)" not being able to push new technology forward and end end up feeling like you pushing against a brick wall. All that leads to is burnout and unnecessary stress.
There is a reason why Best Practices for Object Oriented Programming was created, but who bothers to read that shit any way? YOU SHOULD!
There are a few things that I have learnt and especially now with the new pricing structure in Azure you have to be carefull how to calculate stuff.
Now we drive about 800 sites from a database, each site has a list of a few hundred thousand rows for prices, catalog images, brand filtering, which all link back an external CDN. The problem started when we got about 500 sites * 100 thousand rows, 50+ million rows of pricing junk.
Thats not bad! All your compute belong to instance... not actually. When the pricing was designed it was decided to compute, link, squash, decide what belongs where in a stored procedure. This was great because we could offload all that computation onto the SQL Database and not pay anything extra. At this stage we started running into problems with connection time outs and really really slow sites.
So now we are lucky the new structure came in and we could switch to P2 (£590 a month just for database) but the sites are running fine again. Here is a chart of average "CPU usage" as they call it for out SQL Instance.
So on the very left you almost hear our sweat dripping while the database is deadlocked and running like a dying dog :( We moved from business into Standard 2, restarted everything but soon a back log started to build up and about 10/07 we moved into Premium 1. At peak times we were still experiencing deadlocks and the sites were just not as nippy as we wanted the. You can clearly see midway 11/07 a shift in the trend. Hello Premium 2! £590 per month (half price because its in preview mode, fuck me!)
What have I learnt?
Stored procedures are shit and I always knew this. But sometimes you just got to do it because you were told so. What really should have happened is that all the number crunching should have happening on the instances/ worker roles. Get the data we need with LINQ2SQL or Entity. Crunch and cache it on the front end / middle layer or whatever, just not in the bloody database for fucks sake, and refresh it once and a while.
Why?
Because expanding and contracting instance sizes is quick and is super cheap compared to bloody Premium 2 SQL. So if we needed to re calculate over 75 millions rows as it stands now, we could use the auto health feature "in preview as of writing" to scale out when CPU usage goes over a limit. So we could expand to maximum, crunch the numbers in no time, cache the results, and only pay a fraction while continuously experience blazingly fast speeds without breaking a sweat.
Alternatives
I proposed to use RavenDB NoSQL for the stock and pricing. But if you read what was above, you would realise our team was reluctant to use Entity Framework from the start, because they didn't trust its reliability. So imagine how they took on to NoSQL. Its built to solve this problem, in your face demonstrations and used by Facebook and Stack Exchange, -"We'll think about it"
Conclusion
Part of the problems is caused by "me (yourself as proposer)" not being able to push new technology forward and end end up feeling like you pushing against a brick wall. All that leads to is burnout and unnecessary stress.
There is a reason why Best Practices for Object Oriented Programming was created, but who bothers to read that shit any way? YOU SHOULD!
Comments