Skip to main content

Throwing more Hardware vs developer time in tuning on a single db

Last weekend I read two interesting articles on scaling up vs scaling out http://highscalability.com/blog/2012/11/22/gone-fishin-plentyoffish-architecture.html and then I read Jeff atwood's http://www.codinghorror.com/blog/2009/06/scaling-up-vs-scaling-out-hidden-costs.html .  By the way I am a big fan of Jeff Atwood and if you guys haven't read him you should start reading him :).

But at our company we planned for a scale out model because being a startup sometimes management wont order hardware worth 100-200K or more in one shot and also if you have a table with 1B rows and you are doing agile programming then you are ought to build something and throw it in prod and then refine it. This can sometime lead to alter tables and data migration. While scaling up is good, doing alter table to add a column with default value on even 100M row table will incur significant downtime and we cant afford that. So when I designed our metadata db we chose to scale out.  As of now we are storing billions of rows in mysql database and last weekend we finally started chucking cassandra also out and moving this also to mysql. This would for sure add billions of more rows to the database and we are 30% done migrating the data and this weekend we would probably reach 80% ( this would be an interesting weekend). 

As highlighted in the http://neopatel.blogspot.com/2012/04/mysql-sharding-at-my-company-part1.html  that the approach we take for a new feature is to put it in global non sharded db and if it doesn't scale we move it to sharded db.  So for our event store database that we are migrating from cassandra I was 100% sure that it would need sharding because we had billions of rows but for one other functionality the engineer was confident that he would have < 1M rows as he would delete the rows fast.

As it turns out there were other factors out of  control and even with 30% of the nodes the table reached 10M rows on Thanksgiving weekend and man it was chaos.

On Saturday morning   the load on db that always used to be 1-2 shot up to 10-15 and there was everyone from VP engg to CTO to VP of Ops and the developer.  There were some indexes missing so we added them and that brought the load down to 7-10 but still we can't put more nodes on this code.

Then we found that for debugging purpose we were keeping data for 1 week and almost half the table was completed task so we cronned a job that would move the completed data to some archive table and that brought the load down to 5.

But still we cant put more nodes so on Sunday we changed the real time polling of table to every 30 sec, which defeats the purpose of the final functionality, I mean indexing was still better than before but its not the performance we wanted.

Ultimately we decided that in 1-2 weeks we would change the code to move to the sharded db. But was it worth it, I mean initially during when proposed to the idea of writing the code to sharded db, one argument that was made that do we need this complexity and other was that he can scale it on a single db table.  I mean I also know that people store Billions of rows in a single mysql instance like what the above links of Plentyoffish and stackexchange team did, but these guys are may be exceptions and think about spending almost 1-2 days coordinating so many people on a single issue and a 1 week delay to adding more nodes to the system and that also on Thanks giving(every one in family was raising eyebrows). If you incur all the costs I am definitely sure we can buy 2-3 mysql servers in that cost :).


So in short if I have a slight hint that this feature wont scale on a single db then I would rather throw more hardware on it by adding it to sharded db from start.  Anyways in 1-2 weeks we would move to sharded db and all problems would be solved.

Soon I think this sharded db would require more mysql servers as we are going to outrun the current 12 Mysql master/slave pairs but with some automation and graphite we can predict when to add more servers. Right now as these servers have spare capacity so that buys me some time to work on some other critical infrastructure changes but soon I will come back to this to scale it to next level.

Comments

Popular posts from this blog

Killing a particular Tomcat thread

Update: This JSP does not work on a thread that is inside some native code.  On many occasions I had a thread stuck in JNI code and it wont work. Also in some cases thread.stop can cause jvm to hang. According to javadocs " This method is inherently unsafe. Stopping a thread with Thread.stop causes it to unlock all of the monitors that it has locked". I have used it only in some rare occasions where I wanted to avoid a system shutdown and in some cases we ended up doing system shutdown as jvm was hung so I had a 70-80% success with it.   -------------------------------------------------------------------------------------------------------------------------- We had an interesting requirement. A tomcat thread that was spawned from an ExecutorService ThreadPool had gone Rogue and was causing lots of disk churning issues. We cant bring down the production server as that would involve downtime. Killing this thread was harmless but how to kill it, t

Adding Jitter to cache layer

Thundering herd is an issue common to webapp that rely on heavy caching where if lots of items expire at the same time due to a server restart or temporal event, then suddenly lots of calls will go to database at same time. This can even bring down the database in extreme cases. I wont go into much detail but the app need to do two things solve this issue. 1) Add consistent hashing to cache layer : This way when a memcache server is added/removed from the pool, entire cache is not invalidated.  We use memcahe from both python and Java layer and I still have to find a consistent caching solution that is portable across both languages. hash_ring and spymemcached both use different points for server so need to read/test more. 2) Add a jitter to cache or randomise the expiry time: We expire long term cache  records every 8 hours after that key was added and short term cache expiry is 2 hours. As our customers usually comes to work in morning and access the cloud file server it can happe

Preparing for an interview after being employed 11 years at a startup

I would say I didn't prepared a hell lot but  I did 2 hours in night every day and every weekend around 8 hours for 2-3 months. I did 20-30 leetcode medium problems from this list https://leetcode.com/explore/interview/card/top-interview-questions-medium/.  I watched the first 12 videos of Lecture Videos | Introduction to Algorithms | Electrical Engineering and Computer Science | MIT OpenCourseWare I did this course https://www.educative.io/courses/grokking-the-system-design-interview I researched on topics from https://www.educative.io/courses/java-multithreading-for-senior-engineering-interviews and leetcode had around 10 multithreading questions so I did those I watched some 10-20 videos from this channel https://www.youtube.com/channel/UCn1XnDWhsLS5URXTi5wtFTA