{"id":162,"date":"2017-05-01T02:06:00","date_gmt":"2017-05-01T02:06:00","guid":{"rendered":"http:\/\/blog.staginginstance.com\/?p=162"},"modified":"2018-04-30T22:12:29","modified_gmt":"2018-04-30T22:12:29","slug":"using-percona-with-aws-rds-editing-big-db-tables","status":"publish","type":"post","link":"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/","title":{"rendered":"Using percona with AWS RDS – Editing Big DB tables"},"content":{"rendered":"

I\u00a0recently had\u00a0a situation where i had to alter huge tables(having >3 Million records). Normally we go with standard process of altering the table, you go to SQL editor, type in your SQL command, execute it and table is altered. But when it comes to huge tables, you may want to alter your approach.<\/p>\n

There are 2 ways to alter big table:<\/p>\n

    \n
  1. Create a new table(copy of original table), make necessary changes to schema, stop the original table to store new data, copy the original table data to new table, rename tables. And we are done.
    \nNote : when you have table constantly changing then going with this option is not ideal. It will result in data loss and inconvenience for the users.<\/li>\n
  2. Use\u00a0Percona toolkit<\/a><\/li>\n<\/ol>\n

    Percona is a reliable tool to alter your tables without any data loss and minimum downtime(almost 0 minutes). Percona doesn’t stop alter, modify options while running, and takes into account the changes done while it is performing the operation.<\/p>\n

    Long story short, this clever tool automates the following process:<\/p>\n

      \n
    1. Create new, empty table with altered schema<\/li>\n
    2. Create insert, delete and update triggers on old table to make sure all changes are automatically replicated to the new table<\/li>\n
    3. Perform copy of old table data into new table in chunks<\/li>\n
    4. When in sync, replace the new table with the old one by renaming both one by one.<\/li>\n<\/ol>\n

      Because of the nature of this operation, there are some risks and limitations so it\u2019s critical to familiarise with documentation<\/a>.<\/p>\n

      Here are the steps to update\/alter table:<\/p>\n

        \n
      1. First ssh to EC2 instance.<\/li>\n
      2. Install percona toolkit using following commands:\n
        \n
        \n
        $ <\/span>sudo yum install http:\/\/www.percona.com\/downloads\/percona-release\/redhat\/0.1-4\/percona-release-0.1-4.noarch.rpm\r\n\r\nCheck that the packages are available:\r\n$ yum list | grep percona-toolkit\r\n\r\nInstall the package:\r\nsudo yum install percona-toolkit\r\n\r\n<\/pre>\n<\/div>\n<\/div>\n<\/li>\n
      3. Start altering table with \u2018pt-online-schema-change\u2019
        \npt-online-schema-change \u2014 execute \u2014 recursion-method none \u2014 progress percentage,1 \u2014 alter \u201cADD COLUMN [column] INT DEFAULT NULL\u201d h=[db_host],D=[database],t=[table],u=[user],p=[password]<\/li>\n
      4. Alteration has started. At this point you should see new table in database called _[tablename]_new.<\/li>\n
      5. Once the process has finished, At this point you should see similar report:
        \nCopied rows OK.
        \nAnalyzing new table\u2026
        \nSwapping tables\u2026
        \nSwapped original and new tables OK.
        \nDropping old table\u2026
        \nDropped old table [old_table] OK.
        \nDropping triggers\u2026
        \nDropped triggers OK.
        \nSuccessfully altered [table]<\/li>\n
      6. Done! At this point everything be ready and your table is now updated.<\/li>\n<\/ol>\n
        How easy was this. Percona is a great, try it for big tables.<\/p>\n","protected":false},"excerpt":{"rendered":"
        I\u00a0recently had\u00a0a situation where i had to alter huge tables(having >3 Million records). Normally we go with standard process of altering the table, you go to SQL editor, type in your SQL command, execute it and table is altered. But when it comes to huge tables, you may want to alter your approach. There are… Continue reading Using percona with AWS RDS – Editing Big DB tables<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[6,4,7,5],"yoast_head":"\nUsing percona with AWS RDS - Editing Big DB tables - Learner at life<\/title>\n<meta name=\"description\" content=\"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using percona with AWS RDS - Editing Big DB tables\" \/>\n<meta property=\"og:description\" content=\"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"Learner at life\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/abhij89\" \/>\n<meta property=\"article:published_time\" content=\"2017-05-01T02:06:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-04-30T22:12:29+00:00\" \/>\n<meta name=\"author\" content=\"Abhishek Jain\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"Using percona with AWS RDS - Editing Big DB tables\" \/>\n<meta name=\"twitter:description\" content=\"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.\" \/>\n<meta name=\"twitter:creator\" content=\"@abhij89\" \/>\n<meta name=\"twitter:site\" content=\"@abhij89\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Abhishek Jain\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/\",\"url\":\"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/\",\"name\":\"Using percona with AWS RDS - Editing Big DB tables - Learner at life\",\"isPartOf\":{\"@id\":\"https:\/\/geekabhi.com\/blog\/#website\"},\"datePublished\":\"2017-05-01T02:06:00+00:00\",\"dateModified\":\"2018-04-30T22:12:29+00:00\",\"author\":{\"@id\":\"https:\/\/geekabhi.com\/blog\/#\/schema\/person\/75cf5ee8cc7d8c1a9ba81628bb57443e\"},\"description\":\"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.\",\"breadcrumb\":{\"@id\":\"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/geekabhi.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using percona with AWS RDS – Editing Big DB tables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/geekabhi.com\/blog\/#website\",\"url\":\"https:\/\/geekabhi.com\/blog\/\",\"name\":\"Learner at life\",\"description\":\"Blog to help you learn stuff\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/geekabhi.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/geekabhi.com\/blog\/#\/schema\/person\/75cf5ee8cc7d8c1a9ba81628bb57443e\",\"name\":\"Abhishek Jain\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/geekabhi.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/85387a40fe2c6954082743d1a8aa6c0f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/85387a40fe2c6954082743d1a8aa6c0f?s=96&d=mm&r=g\",\"caption\":\"Abhishek Jain\"},\"description\":\"Techie with 10+ years of experience and counting.\",\"sameAs\":[\"http:\/\/www.geekabhi.com\",\"https:\/\/twitter.com\/abhij89\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using percona with AWS RDS - Editing Big DB tables - Learner at life","description":"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/","og_locale":"en_US","og_type":"article","og_title":"Using percona with AWS RDS - Editing Big DB tables","og_description":"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.","og_url":"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/","og_site_name":"Learner at life","article_publisher":"http:\/\/abhij89","article_published_time":"2017-05-01T02:06:00+00:00","article_modified_time":"2018-04-30T22:12:29+00:00","author":"Abhishek Jain","twitter_card":"summary_large_image","twitter_title":"Using percona with AWS RDS - Editing Big DB tables","twitter_description":"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.","twitter_creator":"@abhij89","twitter_site":"@abhij89","twitter_misc":{"Written by":"Abhishek Jain","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/","url":"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/","name":"Using percona with AWS RDS - Editing Big DB tables - Learner at life","isPartOf":{"@id":"https:\/\/geekabhi.com\/blog\/#website"},"datePublished":"2017-05-01T02:06:00+00:00","dateModified":"2018-04-30T22:12:29+00:00","author":{"@id":"https:\/\/geekabhi.com\/blog\/#\/schema\/person\/75cf5ee8cc7d8c1a9ba81628bb57443e"},"description":"Using percona to edit large\/huge tables on AWS RDS without interrupting the websites normal operation with few easy steps.","breadcrumb":{"@id":"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/geekabhi.com\/blog\/using-percona-with-aws-rds-editing-big-db-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/geekabhi.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Using percona with AWS RDS – Editing Big DB tables"}]},{"@type":"WebSite","@id":"https:\/\/geekabhi.com\/blog\/#website","url":"https:\/\/geekabhi.com\/blog\/","name":"Learner at life","description":"Blog to help you learn stuff","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/geekabhi.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/geekabhi.com\/blog\/#\/schema\/person\/75cf5ee8cc7d8c1a9ba81628bb57443e","name":"Abhishek Jain","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/geekabhi.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/85387a40fe2c6954082743d1a8aa6c0f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/85387a40fe2c6954082743d1a8aa6c0f?s=96&d=mm&r=g","caption":"Abhishek Jain"},"description":"Techie with 10+ years of experience and counting.","sameAs":["http:\/\/www.geekabhi.com","https:\/\/twitter.com\/abhij89"]}]}},"_links":{"self":[{"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/posts\/162"}],"collection":[{"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/comments?post=162"}],"version-history":[{"count":4,"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/posts\/162\/revisions"}],"predecessor-version":[{"id":196,"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/posts\/162\/revisions\/196"}],"wp:attachment":[{"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/media?parent=162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/categories?post=162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/geekabhi.com\/blog\/wp-json\/wp\/v2\/tags?post=162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}