Friday, November 21, 2014

Starting replication for a very large MySQL database on Amazon EC2

In this post, I will outline steps used to start replication of a very large MySQL database from scratch.

Here is the problem that we faced:
  1. I had set up Master-Slave replication for a MySQL database in the beginning (using the simple steps outlined here)
  2. On both the Master and the Slave server, I had /var/lib/mysql point to an, IOPS optimized, EBS disk
  3. Since this data was very critical, I had set up a cron job to backup the database on the slave server
  4. I was monitoring the Master MySQL server with NewRelic and set up alerts when CPU, Memory or Disk consumption reached certain thresholds
  5. There were three mistakes that I did (for those of you setting this up from scratch):
    • Not setting up NewRelic on the Slave Server
    • Performing the backup on the same EBS disk as the DB partition
    • Not "cleaning up" or archiving older backups
  6. As a result of these mistakes, the Slave EBS disk became full and replication stopped
  7. I was blissfully unaware of this until a few days passed
  8. I cleaned up the Slave EBS disk, but I did not know where or when the replication stopped. So, I could not just resume the replication
At this time, here were the constraints that we were dealing with:
  1. There were already over 10m records in some tables and 60m records in some other tables.
  2. This was a live, production, system - so shutting it down (with a READ LOCK) was not an option
  3. Performing the replication and doing the database back up remained to be critical
  4. This task had to be completed in a few hours - because without it we ran the risk of being exposed
These constraints ruled out:
  1. Following the replication steps in the beginning of this post - since we could not do a READ LOCK
  2. Backing up the Database (on the Master) and restoring on the Slave - this would take too much time
Given these requirements and constraints, all the documented methods for resuming the Master Slave replication were ruled out. Here are steps taken to accomplish this task:

On the Master server:
  1. Edit my.cnf

  2.  server-id=1  
     binlog-format= mixed  
     log-bin=/var/lib/mysql/mysql-bin.log  
     datadir=/var/lib/mysql  
     innodb_flush_log_at_trx_commit=1  
     sync_binlog=1  
     binlog-do-db={DB to replicate}  
     binlog-ignore-db=mysql  
     binlog-ignore-db=information_schema  
     binlog-ignore-db=performance_schema  
     sync_binlog=1  
     innodb_flush_log_at_trx_commit=1  
    

  3. Restart Master MySQL server

  4. In the mysql command line on the Master, run the following commands:

  5.  CREATE USER replicant@{SlaveDBServerIP};  
     GRANT REPLICATION SLAVE ON *.* to 'replicant'@'{SlaveDBServerIP}' IDENTIFIED BY 'r3pl_us3r';  
     FLUSH PRIVILEGES;  
     GRANT RELOAD ON *.* TO 'replicant'@{SlaveDBServerIP};  
     GRANT SUPER ON *.* TO 'replicant'@{SlaveDBServerIP};  
     FLUSH PRIVILEGES;  
    

  6. Take a back up of the database in the Master MySQL  server using:

  7.  mysqldump -u {user} -p{password} --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > dump.sql  
    

  8. Get the master_log_file and MASTER_LOG_POS from the MySQL dump:

  9.  head dump.sql -n80 | grep "MASTER_LOG_POS"  
    

  10. On your AWS console, take a Snapshot of the Volume housing your Master MySQL DB:
  11. Now create a Volume using the Snapshot-Id from above in the same AZ as the SlaveDB

  12. Attach this Volume to the SlaveDB instance
On the Slave Server:
  1. Point /var/lib/mysql to the mysql directory in the newly attached volume

  2. Start MySQL and verify that the MasterDB is now available on the slave server. Note that some records (towards the end) may be missing. That is ok. The steps that we will perform below will sync the DBs.

  3. Edit my.cnf

  4.  server-id=101  
     binlog-format = mixed  
     log-bin = /var/lib/mysql/mysql-bin.log  
     relay-log = /var/lib/mysql/mysql-relay-bin  
     log-slave-updates = 1  
     slave-skip-errors = all  
     read-only = 1  
     binlog-do-db={DBs to be replicated}  
    

  5. Restart the Slave

  6. In the mysql command line, run the following commands

  7.  change master to master_host={IP.Address.Of.Master},  
       master_user='replicant',  
       master_password='r3pl_us3r',  
       master_log_file='FROM#5ABOVE',  
       master_log_pos=FROM#5ABOVE;  
     STOP SLAVE;  
     RESET SLAVE;  
     SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  
     START SLAVE;  
     SHOW SLAVE STATUS;  
    

    The Output will look similar to this:

     Slave_IO_State: Waiting for master to send event  
     Master_Host: master_ip  
     Master_User: rep_user  
     Master_Port: 3306  
     Connect_Retry: 60  
     .............  
    

    It may take a while to reach this state - depending on how many records need to be synced

Monday, July 28, 2014

Remembering Amma ...


Last week (the week of July14th) there were a few events to commemorate the one year anniversary of the passing of Amma, Smt. Sharada KrishnaMoorthy.

On Wednesday, July 16th, was the "Oona masyam" ritual. On Thursday, July 17th, there was a "Thiruppgazh Anbargal" Sharada Krishnamoorthy Ninaivanjali Bhajan. Below are a few photos from this Bhajan (you may click on the photo to see an enlarged version of it):













A short, sample, video from the Bhajan is included here:


On Friday, July 18th was the "Sodha Kumbam".  Then on Saturday, July 19th was the "Masiyam", followed by the "Shrardham". This was attended by a few close relatives. Some pictures from this day are uploaded into this album:

The final "Subha Sweekaram" function was on Sunday, July 20th. This was very well attended by friends and relatives. Pictures from this day are uploaded into this album:




The following is the text of the "speech" that I gave at the conclusion of the event.

I started by rendering Unnikrishnan's "Uyirum Neeye" as a dedication to Amma:


I thank all of you for taking the time from your respective schedules to be present here to pay homage to Amma's soul.

Let me begin by narrating a short story. There was a king who received a gift of two magnificent falcons when he visited a neighboring kingdom. They were peregrine falcons, the most beautiful birds he had ever seen. He wanted to see these birds everyday - so he placed them on a tree outside his bedroom.

The king was, however, disappointed that though one of the falcons was flying majestically and soaring high in the sky, the other bird did not move from its branch since the day it arrived.

The king summoned his ministers, healers and sorcerers from all over his kingdom to tend to the falcon, but no one could make the bird fly.

He then announced a reward to anyone that could make the bird fly.

Soon, a woodcutter walked into the court and said, "I can make the bird fly".

The next morning, the king was thrilled to see the falcon soaring high above the palace gardens. He summoned the woodcutter to give him the reward. After giving him the reward, the king asked him, “How did you make the falcon fly?”

With his head bowed, the woodcutter said to the king, “It was very easy, your highness. I simply cut the branch where the bird was sitting.”

Many of us (and I include myself in that category) are like that second bird. We have our comfort zones and are hesitant to stray away from that zone. Amma was different. She was like the first bird - soaring all the time. She was always trying new things. I am sure that up there, Steve Jobs is continuing to innovate and when he needs somebody to test out his latest innovation, Amma is one of the first to try it!!

She was creative and passionate about art and music. If she had to sing at a wedding - whether it was Preethi and Mani's wedding in Maryland or Janani and Avinash's wedding in Chennai, she would compose a song for the occasion, set it to a unique tune and render it at the wedding. In contrast, when I decided to sing today, I merely repeated a popular Unnikrishnan song. Given her interest in music, I would like to announce that we are establishing a trust in her name. This trust will be focused on teaching young children music and other art forms. We are still in the process of establishing the trust, etc. We will provide details of the trust and other relevant information at the following URL: http://bit.ly/FBSKM. I have contacted the founders of shradhanjali.com. They have agreed to include Amma's profile. I am in the process of creating and sending this to them. These details will also be available at http://bit.ly/FBSKM.

I would like to conclude by playing a lullaby. You may think of this symbolically as a lullaby for her soul. She used to sing this lullaby to us when we were children. Later, she would sing this to her grandchildren - when we recorded it (on cassette tapes) and digitally transcoded it. Another tidbit about this song is that it was originally composed by Irayimman Thampi - a composer from Kerala. When Bombay Jayashri was nominated for the Oscar for Pi's Lullaby, Michael Danna and Jayashri were sued by the Irayimman estate for plagiarism. It was finally determined that the lyrics are generic enough that it would not constitute any intent to plagiarize . In essence the lyrics imply that a child sleeps not because he is sleepy, but because he feels safe. In some sense, by playing this lullaby, we are wishing Amma's soul a safe passage. There was no Wikipedia when this song was recorded. So, she did not know the lyrics of the whole song. In her own creative style, she concludes the song with a verse from another Neelambari song!






Sunday, June 1, 2014

Go to your room, Mother - a play by Sanjiv Desai

When I got word a month or so back that two of my class-mates (from undergraduate in BITS, Pilani) were involved in a play, I had to get tickets in a show of support. I had seen an earlier play, Noor, from the same production unit a year or so back and had enjoyed it thoroughly - particularly the acting of Sanjiv Desai who did a fabulous job in the role of a eunuch (in whose eyes the story unfolds).

This time, Sanjiv Desai had written the play. I learned that he was not making the trip from Delhi this time around. Another friend, Amit Sheth, had designed the sets and the sound.

The play was at Montgomery Theater in downtown San Jose. It was being done as a benefit for Maitri - a non-profit organization helping families from South Asia facing domestic violence.

The play starts with us learning of the demise of a successful businessman, Mr. Dalmia and how his widow, Purnima Dalmia (played by Seema Karnik) is suddenly faced with the realities of having to deal with finances, bills, etc. In a moment of weakness, her daughter-in-law, Rita Mansingh (played by Sindhu Singh) asks her to move in with them. The next, delightful, sequence is played out between Rita Mansingh and her husband, Rajiv Dalmia (played by Basab Pradhan) - where she tells him that (since they are empty nesters), she invited his mother to stay with them. She leaves the task of informing her own mother, Sheela Mansingh (played by Rashmi Rustagi) to her husband, Rajiv. By the time Rajiv informs his mother-in-law, Purnima has already gloated about her "status" to Sheela. Rajiv is left with no choice but to ask Sheela to move in with them as well.

The rest of the play is the adjustments that the one-big-happy-family need to make. Some scenes seemed a bit long drawn (such as the one where Purnima and Sheela decide which room they will take), while the others were witty and had some very clever dialogs - "If God has any sense, he will stay away from this place". Overall, the play was fast paced and the plot was well developed. The acting by the four main characters - Basab, Sindhu, Seema and Rashmi was simply awesome. Given that the entire play is set in the kitchen and bedroom of Rajiv and Rita's house, the set change between scenes was minimalist. Yet, it was quite amazing to see how quickly the actors changed clothes between scenes.

I noticed that the audience was mostly South Asian. At the intermission, I asked some of the ushers (who seemed to be the only caucasians watching) if they were liking the play. She said, "It is nice to see that the issues are the same across cultures. I am enjoying every bit of the play."

Overall, a thoroughly enjoyable evening. Some of the dialog was risque with double entendre. However, it was never "out of place". The characters were definitely believable in a modern day context. Congratulations, Sanjiv. Wishing more successes to this play and look forward to your next one.

Sunday, April 6, 2014

Panel on "Is Analytics the Killer App for Big Data"

TiE Silicon Valley hosted a panel on "Is Analytics the 'Killer App' for Big Data" on Wednesday, April 2, 2014.

The panel was moderated by IBM's Piyush Malik. Piyush started the meeting by setting the stage and providing context. Enterprises have been using analytical technique and statistical modeling for quite some time. A typical path has been  Reporting -> Discovery -> Analysis -> Alerting -> Forecasting -> Simulation ->Modeling -> Optimization -> Stochastic Optimization -> Cognitive Analytics.

The democratization of Data (due to the "Big Data" phenomenon) has allowed the application of analytics to take this leap forward. In the 1990s the data was on premise and technologies like Terradata were used to provide Descriptive Analytics. The cost for this was a prohibitive $1m / TB. In the 2000s, companies like Neteeza (acquired by IBM in 2010), Greenplum, asterData (acquired by Terradata in 2011) and Informatica used massively parallel architectures to bring this cost down to $100k / TB. Today, using Hadoop and Cassandra stacks with NoSQL and Cloud platforms, companies like MapR and Datastax and able to deliver Predictive Analytics moving towards Cognitive Analytics for a mere $1000 / TB.

While answering the question, "Why BigData?", Piyush said, "1 in 2 business leaders do not have access to the data that they need".

I was reminded of a talk (in the late 90s) by Bill Perry that I had attended (while I was at Sun Microsystems). He was recounting his experience when he was Secretary of Defense. He said that often times, he was asked to make decisions when he did not have all the required data. Yet, the impact of his decision could mean life or death for the soldier. "You have to trust your gut," he said. "Don't always lean towards what will make you 'popular'".

Arif Janmohamed, Partner at Lightspeed Ventures spoke next. He spoke to LSVP's investments in MapR and Datastax affirming their commitment to BigData and the opportunity that the VC community saw in BigData.

Chris Jones, Director of Analytics at AAA was the next speaker. He spoke of his experiences as a consumer and producer of Big Data through his career that spanned Intuit, Adobe, Zynga and AAA.

Sai Devulapalli, Director of Analytics at Ericsson spoke about how they use BigData at Ericsson to make predictions about:

  • Customers - Behavior, Experience, Churn
  • Devices - Uptake, Performance, Failure
  • Networks - Performance, Failure
  • Campaigns - Uptake, Network Impact
  • Services - Uptake, Performance, Failure, Network Impact
Janet George, Managing Director of Accenture Technology Labs spoke next. Previously, Janet worked at eBay and on Hadoop while at Yahoo. She spoke about life events for Small and Medium Businesses and the importance of data in the stages of Expansion, Consolidation and Change in Ownership.

The panel provided an overarching viewpoint on the impact of BigData in enterprises today. However, I felt that some of the bigger questions were left unanswered. No one was willing to talk about solving "Grand Challenge" problems. How about using Big Data to:
  • Find a cure for Cancer
  • Predict the next Earthquake
  • Drug Discovery
If, at the end of the day, Enterprises are able to use "Big Data" to predict which brand of toothpaste I am likely to purchase next, we should be pooling our collective resources elsewhere.









Friday, April 4, 2014

A Modest Proposal to the SF Giants Management

Unless you have been living under a rock, we are all familiar with the 12th Man in Football. SuperBowl winners, Seattle Seahawks, have a page dedicated to the 12th Man on their web site. Pete Carroll, in fact, specifically mentioned appreciation to the 12th Man in his interview after winning SuperBowl XLVIII.

If the 12th Man in Seattle was "instrumental" in their winning the ultimate trophy, the same should apply to other sports as well. Here in the Bay Area, we have an ardent fan following of the MLB team SF Giants. How about we anoint these fans as the "10th Man" in Baseball. My proposal to the SF Giants Management is to have an entire campaign around this - with a flag, hashtag (#sfgiants10), shirts, mugs, etc. A suggested design for a banner is included here.

Who knows, this may bring the World Series trophy back to the Giants!

Saturday, March 22, 2014

Why can't we all just get along without prejudices?

Last week I was out grocery shopping at the local Safeway. As I was walking out, I noticed a young (probably in her late 20s, early 30s) Vietnamese woman, carrying a young (2 to 3 year old) child in one arm and negotiating a (rather full) grocery cart with the other hand. Now, this grocery cart was equipped with a "locking wheel" - which prevents the user to take the cart outside a certain boundary. (The mechanism used by these carts is explained here). So, this hapless lady was trying to take the cart to her car and the cart would not budge! (I learned later that she had given her car for an Oil Change at the nearby Service Station - which is the reason why she had to push the cart so far).

My immediate reaction was to explain to her that the cart has a locking wheel and help her with moving her groceries. Just as I was doing this, I heard a loud voice bellowing, "This is the reason they have locks on these carts. To prevent trash like you from moving the carts outside the store premises". I turned around and saw this short, portly, elderly, caucasian gentleman with grey hair, a grey stubble and glasses. His face was flush with anger and his eyes were almost popping out of his glasses. I suspect that the lady did not even comprehend what he was saying. She seemed quite flustered and was desperately trying to empty her cart and move the grocery bags inside to the sidewalk. I walked up to the gentleman and told him, "Come on, back off. She did not know that the cart wheels have locks." He then turned his anger towards me saying, "Why should I back off. I live in this neighborhood and regularly find these carts inside my complex. These people should go back to their country and trash it anyway they want." I determined that I could not "reason" with his. So I just said, "I don't think you are being fair. She genuinely did not know. So just back off, OK." I then turned towards the lady, helped her empty the cart and then moved the locked cart back to the grocery store. The lady picked up the bags that I had helped her move to the sidewalk and quickly walked towards her car after mumbling her thanks to me.

Luckily, everything ended well in this situation. However, I could not help thinking later about the choice of words used by the gentleman and his initial reaction. It rankled me that his first reaction was to refer to her as "trash like you". Should I have given him the "If you prick us, do we not bleed" speech (from Shakespeare's Merchant of Venice)? Are people truly walking around with such prejudices? Whatever happened to the "melting pot" and living in a "cosmopolitan" society. If this the case in California, what would be the "plight" of the immigrant population in the Southern Bible Belt states?

Am I reading too much into this (possibly isolated) incident? Share your thoughts as comments to this post.

Yet another loss to Cancer

We learned of yet another tragic loss to Cancer earlier this evening when we were informed of the passing of cousin Mohan's spouse, Radhika. I certainly hope this is the last time I will have to write about losing a close relative / friend to Cancer. Radhika's battle with cancer was long and protracted. Each time there was hope that she was going into remission, the doctors would discover a recurrence in an altogether new area. Yet, through all of this, Radhika would stay positive. Many times, during our phone conversations, she would be the one offering words of encouragement - saying, "Arre, Yeh Kuch nahin hai. Sab teek ho jayega" (This is nothing. Soon, it will all become good)


Radhika was so full of life in everything that she undertook - whether it was getting the children of Chicago to perform in a dance program that she choreographed or participating in a dance program herself. Her smiling countenance would fill every room that she walked into. She would somehow find a way to uplift you with her vibrant conversation and bright smile. Radhika would find something positive in every situation. You will be missed, Radhika - not just by your young children and spouse, but by larger group of family and friends that you have left behind. Most certainly, your passing has relieved you of the pain and suffering caused by this deadly disease. However, there is a huge void that you have left behind which will be hard to fill. May your soul rest in peace.