<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Sajeban Antonyrex on Medium]]></title>
        <description><![CDATA[Stories by Sajeban Antonyrex on Medium]]></description>
        <link>https://medium.com/@sajebanantonyrex?source=rss-ce122e8ab868------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*RdryhVAyPtrt1Jvz</url>
            <title>Stories by Sajeban Antonyrex on Medium</title>
            <link>https://medium.com/@sajebanantonyrex?source=rss-ce122e8ab868------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 02:05:28 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sajebanantonyrex/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Build an Alert System for Monitoring AWS ECS Task Termination]]></title>
            <link>https://awstip.com/build-an-alert-system-for-monitoring-aws-ecs-task-termination-6b46fd3848eb?source=rss-ce122e8ab868------2</link>
            <guid isPermaLink="false">https://medium.com/p/6b46fd3848eb</guid>
            <category><![CDATA[fargate]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[monitoring]]></category>
            <category><![CDATA[serverless]]></category>
            <category><![CDATA[eventbridge]]></category>
            <dc:creator><![CDATA[Sajeban Antonyrex]]></dc:creator>
            <pubDate>Tue, 20 Feb 2024 04:47:03 GMT</pubDate>
            <atom:updated>2024-03-04T14:19:23.961Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pY1ZkjZn5Ci5mhBATwDy-A.jpeg" /></figure><h3>Overview</h3><p>In this article, we’ll dive into how to create an effective alert system for monitoring AWS Elastic Container Service (ECS) task terminations. Whether you’re managing critical applications or ensuring the smooth operation of your services, it’s vital to stay informed about the status of your ECS tasks. By leveraging AWS Lambda and Simple Email Service (SES), we can develop a <a href="https://www.redhat.com/en/topics/cloud-native-apps/what-is-serverless"><strong><em>serverless</em></strong></a> solution to send notifications whenever an ECS task stops.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tRnENsCGoIuSAi1Q843hQQ.png" /></figure><p>This guide will provide you with a step-by-step approach, from setting up to writing and deploying a Lambda function, ensuring you’re promptly alerted about any task terminations.</p><h4>Pre-requisite</h4><p>Both sender and receiver email address verified in AWS SES dashboard.<br>Refer <a href="https://docs.aws.amazon.com/ses/latest/dg/creating-identities.html"><strong><em>AWS documentation</em></strong></a>.</p><h3>Writing the lambda function</h3><p>We will use <a href="https://www.serverless.com/framework/docs"><strong>serverless framework</strong></a> to create the lambda function</p><h4>Configuration</h4><ol><li>Configure serverless</li></ol><ul><li>Install serverless if you haven’t already</li></ul><pre>npm install -g serverless</pre><ul><li>Initialize the project</li></ul><pre>serverless</pre><ul><li>Choose Node.js starter from the list</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/863/1*LduK3WPs2jUB4b-Tk7hOsg.png" /></figure><ul><li>Provide a name</li><li>No option for all other</li></ul><p>2. Install AWS SDK</p><pre>npm init -y<br>npm install aws-sdk</pre><p>3. Configure Serverless file</p><ul><li>Update the Provider with the following values</li></ul><pre>provider:<br>  name: aws<br>  runtime: nodejs18.x<br>  region: ap-southeast-1<br>  stage: ${opt:stage, &#39;dev&#39;}</pre><p>Here we are setting dev as the default value for stage.</p><ul><li>Let us create roles and policies needed to execute the lambda<br>Lambda should be able to send email, have logs and get information from ECS task.</li></ul><pre>resources:<br>  Resources:<br>    NotifyAlertLambdaExecutionRole:<br>      Type: &quot;AWS::IAM::Role&quot;<br>      Properties:<br>        RoleName: !Sub ${self:provider.stage}-notify-alert-lambda-execution-role<br>        AssumeRolePolicyDocument:<br>          Version: &quot;2012-10-17&quot;<br>          Statement:<br>            - Effect: &quot;Allow&quot;<br>              Principal:<br>                Service:<br>                  - &quot;lambda.amazonaws.com&quot;<br>              Action: &quot;sts:AssumeRole&quot;<br>        Policies:<br>          - PolicyName: !Sub ${self:provider.stage}-notify-alert-lambda-execution-policy<br>            PolicyDocument:<br>              Version: &quot;2012-10-17&quot;<br>              Statement:<br>                - Effect: &quot;Allow&quot;<br>                  Action:<br>                    - &quot;ses:SendEmail&quot;<br>                    - &quot;ses:SendRawEmail&quot;<br>                  Resource: &quot;*&quot;<br>                - Effect: &quot;Allow&quot;<br>                  Action:<br>                    - &quot;logs:CreateLogGroup&quot;<br>                    - &quot;logs:CreateLogStream&quot;<br>                    - &quot;logs:PutLogEvents&quot;<br>                  Resource: &quot;*&quot;<br>                - Effect: &quot;Allow&quot;<br>                  Action:<br>                    - &quot;ecs:DescribeTasks&quot;<br>                    - &quot;ecs:ListTasks&quot;<br>                  Resource: &quot;*&quot;</pre><p>Now we can configure the lambda configurations</p><pre>functions:<br>  notification-alerts:<br>    handler: src/notifyAlerts.notifyAlerts<br>    role: NotifyAlertLambdaExecutionRole<br>    environment:<br>      STAGE: ${self:provider.stage}<br>    events:<br>      - cloudwatchEvent:<br>          event:<br>            source:<br>              - &#39;aws.ecs&#39;<br>              - &quot;ecs.amazonaws.com&quot;<br>            detail-type:<br>              - &#39;ECS Task State Change&#39;<br>            detail:<br>              lastStatus:<br>                - &quot;STOPPED&quot;<br>              clusterArn:<br>                - !Sub arn:aws:ecs:${self:provider.region}:${AWS::AccountId}:cluster/peace-${self:provider.stage}-cluster</pre><p>In this process, we attach a role and pass the ‘<strong><em>STAGE</em></strong>’ environment variable to our code. This allows the code to behave differently based on the environment. For example, we can send emails to different recipients depending on whether we’re in a<em> development, staging, or production</em> environment. <br>However, if your codebase will have the same implementation regardless of the environment, you don’t have to pass this variable.</p><p>We are creating a Event Bridge rule which will expect the last status of the the ECS tasks in the given cluster to be ‘STOPPED’. This event will trigger the lambda to execute.</p><blockquote><strong><em>Note:</em></strong><em><br>In our event configuration, we’ve specified ‘aws.ecs’ and ‘ecs.amazonaws.com’ as event sources. We will discuss this under testing.</em></blockquote><h4>Implementation</h4><ul><li>Create directory src and create a file called notifyAlerts.js</li></ul><pre>const aws = require(&#39;aws-sdk&#39;);<br>// Change the region as needed<br>const ses = new aws.SES({region: &#39;ap-southeast-1&#39;});<br><br>exports.notifyAlerts = async (event) =&gt; {<br>    const env = process.env.STAGE<br><br>    const params = {<br>        // Verify this email in SES<br>        Source: `critical-alerts-${env}@email-address.com`,<br><br>        // Verify recipient email as well<br>        Destination: {<br>            ToAddresses: [&#39;email-address&#39;],<br>        },<br>        Message: {<br>            Subject: {Data: `Server Stopped at ${event.time}`},<br>            Body: {<br>                Text: {Data: `More details: ${JSON.stringify(event)}`}<br>            }<br>        }<br>    };<br><br>    try {<br>        await ses.sendEmail(params).promise();<br>        console.log(&#39;Email sent successfully&#39;);<br>    } catch (error) {<br>        console.error(&#39;Error sending email&#39;, error);<br>        throw error;<br>    }<br>};</pre><h3>Execution</h3><p>Execute the below command.</p><pre>sls deploy </pre><h3>Testing</h3><ol><li>Stop the ECS task, you should get a mail</li><li>Test the Event Bridge Rule</li></ol><p>Navigate to default Event bus in the Event Bridge dashboard.<br>Click on to Send events button on the top.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*24Hm1BE6FmfoepEvYVEvjQ.png" /></figure><p>This is a sample testing setup.</p><ul><li><strong><em>Update the clusterArn with the relevant value.</em></strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*roqailWOCDSbp5o1FqZkjg.png" /></figure><p>Once you click send event, you might encounter an error as shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CXPX-mc4FwRezfX_M2Qc3w.jpeg" /></figure><p>This happens because custom event sources shouldn’t start with ‘aws.’, as this prefix is reserved for AWS service events. Hence, we are using ‘ecs.amazonaws.com’ for our custom test events. We can use anything as source name, even your name.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ae4tnCjdun-poXRbzc24rg.jpeg" /></figure><p>We have to specify ‘<strong><em>ecs.amazonaws.com</em></strong>’ in the serverless.yml file as a source for this to work which we have done already.</p><h3>Conclusion</h3><p>We have used AWS Lambda due to its capacity for extensive customization. This approach provides a foundational design that can be scaled and optimised according to the size and specific requirements of the project.</p><h3>PS</h3><p>Find the complete code in this repository:<br>Github link: <a href="https://github.com/Sajeban/ECSStateNotifier"><strong><em>ECS State Notifier</em></strong></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6b46fd3848eb" width="1" height="1" alt=""><hr><p><a href="https://awstip.com/build-an-alert-system-for-monitoring-aws-ecs-task-termination-6b46fd3848eb">Build an Alert System for Monitoring AWS ECS Task Termination</a> was originally published in <a href="https://awstip.com/">AWS Tip</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Verifying if an Email Already Exists in a Cognito User Pool]]></title>
            <link>https://levelup.gitconnected.com/verifying-if-an-email-already-exists-in-a-cognito-user-pool-14dd655af6a6?source=rss-ce122e8ab868------2</link>
            <guid isPermaLink="false">https://medium.com/p/14dd655af6a6</guid>
            <category><![CDATA[cognito-triggers]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[cognito]]></category>
            <category><![CDATA[sign-up]]></category>
            <dc:creator><![CDATA[Sajeban Antonyrex]]></dc:creator>
            <pubDate>Thu, 19 Oct 2023 08:43:28 GMT</pubDate>
            <atom:updated>2024-05-05T15:14:08.460Z</atom:updated>
            <content:encoded><![CDATA[<h3>Description</h3><p>Even with setting email to unique and as a sign in alias, cognito will allow users to sign up with an email address that’s already confirmed and associated with an existing account.</p><p>To ensure email uniqueness, a pre sign-up trigger to throw an error if a user with that email already exists was implemented.</p><p>This trigger will invoke a lambda which will check for the email in the user pool.</p><h3>A Step-By-Step Implementation:</h3><ol><li><strong>User Registration Attempt</strong>: <br>When a user attempts to create an account with an email address, AWS Cognito triggers a pre-sign-up Lambda function prior to the completion of the registration process.</li><li><strong>Lambda Execution for Validation</strong>: <br>The invoked Lambda function systematically scans the user pool, scrutinizing whether the provided email address is already affiliated with a confirmed account.</li><li><strong>Assessing &amp; Handling Email Uniqueness</strong>: <br>If the email address is not found within the pool, the registration process proceeds.<br>Conversely, if the address is associated with an existing account, the Lambda function terminates the registration process, transmitting an error highlighting the existence of an account with the provided email.</li></ol><h3>Pre-Signup Trigger Lambda</h3><p>Version: Node.js 14.x</p><pre>const AWS = require(&#39;aws-sdk&#39;);<br>const cognitoIdServiceProvider = new AWS.CognitoIdentityServiceProvider();<br><br>exports.handler = async (event) =&gt; {<br>  try {<br>    const params = {<br>      UserPoolId: &#39;userpool-id&#39;,<br>      Filter: `email = &quot;${event.request.userAttributes.email}&quot;`,<br>    };<br><br>    const users = await cognitoIdServiceProvider.listUsers(params).promise();<br><br>    if (users.Users.length &gt; 0) {<br>      throw new Error(&#39;Email already exists&#39;);<br>    }<br><br>    return event;<br>  } catch (error) {<br>    throw new Error(&#39;Error: &#39; + error.message);<br>  }<br>};</pre><h3>Lambda IAM Policy</h3><p>Also ensure lambda is enforced with correct policy.</p><pre>{<br>    &quot;Version&quot;: &quot;2012-10-17&quot;,<br>    &quot;Statement&quot;: [<br>        {<br>            &quot;Sid&quot;: &quot;VisualEditor0&quot;,<br>            &quot;Effect&quot;: &quot;Allow&quot;,<br>            &quot;Action&quot;: &quot;cognito-idp:ListUsers&quot;,<br>            &quot;Resource&quot;: &quot;user-pool-arn&quot;<br>        }<br>    ]<br>}</pre><h3>Associating the Lambda Function with Cognito User Pool</h3><ol><li>Go to the AWS Cognito console.</li><li>Click on the desired user pool.</li><li>Under User Pool Properties, find the “Add Lambda Trigger” Button</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eC-xaBft9LKjxvjVyVO7Yg.jpeg" /></figure><p>4. Under “Sign-up”, find the “Pre sign-up” trigger.</p><p>5. Assign your Lambda function to this trigger.</p><p>6. Click “Add Lambda Trigger”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/835/1*jftnHaZAkJ63yWYc--1dpg.jpeg" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14dd655af6a6" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/verifying-if-an-email-already-exists-in-a-cognito-user-pool-14dd655af6a6">Verifying if an Email Already Exists in a Cognito User Pool</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Configuring Lambda Triggers for Amazon Cognito User Pools with CloudFormation]]></title>
            <link>https://medium.com/@sajebanantonyrex/configuring-lambda-triggers-for-amazon-cognito-user-pools-with-cloudformation-57893b95da88?source=rss-ce122e8ab868------2</link>
            <guid isPermaLink="false">https://medium.com/p/57893b95da88</guid>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[aws-lambda-trigger]]></category>
            <category><![CDATA[cognito]]></category>
            <dc:creator><![CDATA[Sajeban Antonyrex]]></dc:creator>
            <pubDate>Thu, 19 Oct 2023 07:48:01 GMT</pubDate>
            <atom:updated>2024-02-15T04:18:15.683Z</atom:updated>
            <content:encoded><![CDATA[<p>Cognito allows you to integrate custom logic using AWS Lambda, which can be triggered by Cognito events. This article guides you through the process of setting up a Lambda trigger for an Amazon Cognito User Pool using AWS CloudFormation.</p><h3>1. Preparing Your Lambda Function</h3><p>Before integrating the Lambda function into CloudFormation, ensure you’ve coded your function to handle the desired Cognito event. Once your function is ready:</p><ul><li>Zip your Lambda function code.</li><li>Upload the zipped file to an S3 bucket.</li></ul><h3>2. Define the Lambda Function in CloudFormation</h3><p>Create your lambda function and specify needed properties and the location of the code in s3.</p><pre>  CognitoTriggerFunction:<br>    Type: AWS::Lambda::Function<br>    Properties:<br>      FunctionName: !Sub cognito-custom-mail-lambda<br>      Handler: CognitoTriggerLambda.handler<br>      Role: !GetAtt LambdaExecutionRole.Arn<br>      Runtime: nodejs14.x<br>      Code:<br>        S3Bucket: myproject-lambda<br>        S3Key: CognitoTriggerLambda.js.zip</pre><p>Create a role based on the permission required by lambda.</p><pre>  LambdaExecutionRole:<br>    Type: AWS::IAM::Role<br>    Properties:<br>      AssumeRolePolicyDocument:<br>        Version: &#39;2012-10-17&#39;<br>        Statement:<br>          - Effect: Allow<br>            Principal:<br>              Service:<br>                - lambda.amazonaws.com<br>            Action:<br>              - sts:AssumeRole<br>      Policies:<br>        - PolicyName: LambdaBasicExecution<br>          PolicyDocument:<br>            Version: &#39;2012-10-17&#39;<br>            Statement:<br>              - Effect: Allow<br>                Action:<br>                  - logs:CreateLogGroup<br>                  - logs:CreateLogStream<br>                  - logs:PutLogEvents<br>                Resource: &#39;*&#39;</pre><h3>3. Link the Lambda Trigger to the Cognito User Pool</h3><p>Refer the lambda function in the userpool configuration</p><pre>  CognitoUserPool:<br>    Type: &quot;AWS::Cognito::UserPool&quot;<br>    Properties:<br>      UserPoolName:<br>        Fn::Sub: &quot;myproject-user-pool&quot;<br>      LambdaConfig:<br>        CustomMessage: !GetAtt CognitoTriggerFunction.Arn</pre><h3>4. Grant Cognito Permission to Invoke the Lambda Function</h3><p>Create Permission for Cognito to invoke the Lambda function.</p><pre>  CognitoLambdaInvokePermission:<br>    Type: &quot;AWS::Lambda::Permission&quot;<br>    Properties:<br>      Action: &quot;lambda:InvokeFunction&quot;<br>      FunctionName: !GetAtt CognitoTriggerFunction.Arn<br>      Principal: &quot;cognito-idp.amazonaws.com&quot;<br>      SourceArn: !Sub arn:aws:cognito-idp:${AWS::Region}:${AWS::AccountId}:userpool/${CognitoUserPool}</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=57893b95da88" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Seamless Content Creation — Dynamic Value Generation for dependant attribute]]></title>
            <link>https://medium.com/@sajebanantonyrex/seamless-content-creation-dynamic-value-generation-for-dependant-attribute-78ded76192bb?source=rss-ce122e8ab868------2</link>
            <guid isPermaLink="false">https://medium.com/p/78ded76192bb</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[headless-cms]]></category>
            <category><![CDATA[cms]]></category>
            <category><![CDATA[content-creation]]></category>
            <category><![CDATA[strapi]]></category>
            <dc:creator><![CDATA[Sajeban Antonyrex]]></dc:creator>
            <pubDate>Wed, 07 Jun 2023 10:52:17 GMT</pubDate>
            <atom:updated>2023-08-31T02:22:28.667Z</atom:updated>
            <content:encoded><![CDATA[<h3>Seamless Content Creation — Dynamic Value Generation for dependant attribute</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TjMpFdsGXEBbn4hof-6eXA.png" /></figure><p><strong>Synopsis</strong> — Updating an attribute value automatically based on other attribute values in a headless CMS.</p><p><strong>Story-</strong> We use Strapi, headless Content Management System (CMS), to manage our content (articles). <br>Our article table contain these attributes: nav_title, sub_nav_title, content, link. The link attribute value points to the front end article link where the article can be viewed by front end users. Link attribute values depends on the values of nav_title and sub_nav_title. <br>eg: <br>nav _title— vehicles<br>sub_nav_title — cars<br>content- cars are awesome.<br>link: <strong>website_url?<em>nav=vehicles&amp;sub_nav=cars</em></strong></p><p>Our content creators wanted to see the content on the front end while they create/edit content. So it would be easy for them to have the link in the CMS so they can easily click and navigate straightly to the article page in the front end.</p><p>To meet this requirement, we decided to generate links automatically so they will be readily available for the content creators. To accommodate this requirement, we have to generate link values for existing articles and new articles as soon as their respective nav_title and sub_nav_title values are provided.</p><p><strong>Tech stack</strong>- strapi and mysql</p><p><strong>Solution-</strong> we will be using mysql triggers to achieve this.</p><h3>Approach</h3><ol><li>create links for existing articles</li><li>populate link for new articles once the user creates an article</li></ol><h3>Implementation</h3><h4>1. create links for existing articles</h4><p>An update query to update the link for existing articles</p><pre>UPDATE articles SET link = <br>CONCAT(&#39;https://website_url?nav=&#39;, <br>REPLACE(nav_title, &#39; &#39;, &#39;%20&#39;), &#39;&amp;sub_nav=&#39;, <br>REPLACE(sub_nav_title, &#39; &#39;, &#39;%20&#39;));</pre><h4>2.populate link for new articles</h4><ol><li>MySQL trigger will create a link as soon as a user create a new article.</li></ol><pre>DELIMITER $$<br>CREATE TRIGGER insert_link_trigger_in_articles<br>BEFORE INSERT ON articles<br>FOR EACH ROW<br>BEGIN<br>  SET NEW.link = CONCAT(&#39;https://website_url?nav=&#39;, <br>  REPLACE(NEW.nav_title, &#39; &#39;, &#39;%20&#39;), &#39;&amp;sub_nav=&#39;, <br>  REPLACE(NEW.sub_nav_title, &#39; &#39;, &#39;%20&#39;));<br>END$$<br>DELIMITER ;</pre><p>2. The below trigger will update the link if there are any changes to nav_title or sub_nav_title</p><pre>DELIMITER $$<br>CREATE TRIGGER update_link_triggerin_articles<br>BEFORE UPDATE ON articles<br>FOR EACH ROW<br>BEGIN<br>  IF NEW.nav_title != OLD.nav_title OR NEW.sub_nav_title != OLD.sub_nav_title THEN<br>    SET NEW.link = CONCAT(&#39;https://website_url?nav=&#39;, REPLACE(NEW.nav_title, &#39; &#39;, &#39;%20&#39;), &#39;&amp;sub_nav=&#39;, REPLACE(NEW.sub_nav_title, &#39; &#39;, &#39;%20&#39;));<br>  END IF;<br>END$$<br>DELIMITER ;</pre><p>By implementing these triggers, we managed to automate the process of link generation in our headless CMS, improving efficiency for our content creators while ensuring that links always reflect the most current attribute values.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=78ded76192bb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to delete 100 million rows in a MySQL table within 30 minutes]]></title>
            <link>https://medium.com/sysco-labs/how-to-delete-100-million-rows-in-a-mysql-table-within-30-minutes-ba39065e5928?source=rss-ce122e8ab868------2</link>
            <guid isPermaLink="false">https://medium.com/p/ba39065e5928</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[bulk]]></category>
            <dc:creator><![CDATA[Sajeban Antonyrex]]></dc:creator>
            <pubDate>Tue, 13 Sep 2022 04:38:44 GMT</pubDate>
            <atom:updated>2023-06-02T16:53:09.413Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to delete 100 million rows in a MySQL table within 30 minutes?</h3><h3>Hook</h3><p>Recently, I got into a scenario where I had to delete 104 million rows in a table which contained 293 million rows based on a condition.</p><p>I was able to delete them in less than 30 minutes using a Java application and by making parallel calls to stored procedure. Let’s see how to do this.</p><p>Stack I used: Java, AWS RDS with r6g.4xlarge size.</p><p>You can apply the same approach to your choice of tech stack.</p><h3>Beginning</h3><p>Deleting a row in MySQL table is slower compared to other operations. This depends on the primary key, indexes, size of the table and the computational power of the database service.</p><p>If we want to delete all the rows in a table, it’s best to do a truncation. A single delete will cause blocking and other potential issues, so when dealing with millions of data, this might consume a huge amount of time.</p><p>If we want to delete a large number of rows based on a condition, the best way to do is by calling a stored procedure as shown below.</p><p>Let’s say our business is going to shut down all operations in Columbia. So, we are going to delete all the customers in that area.</p><pre>DELIMITER $$<br>CREATE PROCEDURE delete_customer()<br>BEGIN<br>    set foreign_key_checks=0;<br>    SET sql_safe_updates=0;<br>    set innodb_lock_wait_timeout=1000;<br>    <br>    deletion_loop:LOOP<br>        SELECT GROUP_CONCAT(customer_id)<br>        FROM (SELECT customer_id FROM customer<br>             where state = &#39;Columbia&#39; limit 100000) as t INTO <a href="http://twitter.com/data">@data</a>;</pre><pre>if <a href="http://twitter.com/data">@data</a> is null then<br>            leave deletion_loop;<br>        end if;<br>        SET <a href="http://twitter.com/gQuery">@gQuery</a> =CONCAT(&quot;delete FROM customer where customer_id            in (&quot;,<a href="http://twitter.com/data">@data</a>,&quot;)&quot;);<br>        PREPARE stmt1 FROM <a href="http://twitter.com/gQuery">@gQuery</a>;<br>        EXECUTE stmt1;<br>    end LOOP;</pre><pre>set foreign_key_checks=1;SET sql_safe_updates=1;<br>END$$<br>DELIMITER ;</pre><p>This caused multiple issues. Why? Read below.</p><h3>Pinch Point</h3><p>This stored procedure will delete 100,000 rows at a time. Depending on the number of data we have, this will consume time (a lot of time). For me it took 6–7 hours to complete the deletion. I noticed that this utilized less than 10% of database CPU. Yes, I tweaked the values, increased the limit to millions, and decreased it to thousands, but things didn’t work in my favor. They didn’t make much of a difference in the time consumed or at times, caused timeout issues.</p><p>What’s happening in the background? MySQL engine is going for a full table lookup, than an index lookup every time which causes a huge time consumption and since this is single threaded, database service resources are underutilized.</p><p>What can we do now?</p><h3>The Big Reveal</h3><p>Rather than calling one stored procedure to handle the whole table, we can call multiple stored procedures in parallel feeding them a part of the table.</p><p>Are you with me? OK! Let me use an example.</p><p>Let’s say our table has 5 million rows. We’ll call one stored procedure to handle the first million rows, then another stored procedure to handle the second million.</p><p>Let’s make some changes to the stored procedure to receive the start pointer, end pointer of each million and also the number of rows to be deleted at once.</p><pre>DELIMITER $$<br>CREATE PROCEDURE delete_customer_by_chunks(IN start_pointer INT, IN end_pointer INT, IN limit_amt INT)<br>BEGIN</pre><pre>set foreign_key_checks=0;SET SQL_SAFE_UPDATES=0;</pre><pre>deletion_loop:LOOP<br>        SELECT GROUP_CONCAT(customer_id)<br>        FROM (SELECT customer_id FROM customer<br>              where customer_id between start_pointer and      end_pointer and state =&#39;Columbia&#39; limit limit_amt) as t INTO <a href="http://twitter.com/data">@data</a>;<br>       <br> if <a href="http://twitter.com/data">@data</a> is null then<br>            leave deletion_loop;<br>        end if;</pre><pre>SET <a href="http://twitter.com/gQuery">@gQuery</a> =CONCAT(&quot;delete FROM customer where customer_id in (&quot;,<a href="http://twitter.com/data">@data</a>,&quot;)&quot;);<br>        PREPARE stmt1 FROM <a href="http://twitter.com/gQuery">@gQuery</a>;<br>        EXECUTE stmt1;<br>    end LOOP;<br>    set foreign_key_checks=1;SET SQL_SAFE_UPDATES=1;<br>END$$<br>DELIMITER ;</pre><p>Are we finished? Not yet!</p><p>We need something to execute this.</p><p>Here we go! We have to create a Java application which will make parallel calls to the stored procedure and also feeding them values.</p><p>First let’s get the minimum and maximum primary ID of customers from Columbia so we can avoid the overhead of looking into unnecessary rows. This applies only if you have incremental primary ID or any unique ID’s since we are using a <strong>Seek Approach</strong> to achieve this. (checkout this <a href="https://vladmihalcea.com/sql-seek-keyset-pagination/">article</a> to know more about Seek)</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e8a246d249094fc59c71faafbbc26ed5/href">https://medium.com/media/e8a246d249094fc59c71faafbbc26ed5/href</a></iframe><p>Now let’s divide the table equally based on any size we want. As I said earlier, I will be feeding the stored procedure with 1 million rows (<em>CHUNK_SIZE</em>). manipulationRebateIdPeriodQueue which will contain the equally divided parts (starting primary key and ending primary key of a chunk).</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f117901856ede3f1a01f20b9c9f40fa2/href">https://medium.com/media/f117901856ede3f1a01f20b9c9f40fa2/href</a></iframe><p>Now, let’s create the 15 threads (THREAD_SIZE) as shown above.</p><p>Let’s look into what the CustomerDeletionRunnable has.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8fb62e7b71951498c137f70d32e63ce3/href">https://medium.com/media/8fb62e7b71951498c137f70d32e63ce3/href</a></iframe><p>Here we call the stored procedure with the start and end ID of the rows that it has to look into. We are also passing the limit which is the number of rows it will delete at a time. We can set the limit in the stored procedure itself but we have more control of the stored procedure if we pass from the application. It is also easy to tweak the values and test.</p><p>You must have noticed by now that we are setting the group CONCAT max length. Since the primary key can be long, the CONCAT length in the stored procedure can be very long. It was throwing error when I tested it, and I had to set this to the max value it can take.</p><h3>Resolution</h3><p>If we have a powerful database service, we can then make parallel deletion calls to stored procedure utilizing the database performance to the fullest.</p><p>You may have to increase or decrease the thread count, Chunk size and limit depending on the computational power of the database service and the number of rows in the table. These are the values I used.</p><pre>public static final int CHUNK_SIZE = 1000000;  <br>public static final int THREAD_SIZE = 15;  <br>public static final int LIMIT = 8000;</pre><p>I too tested a lot of times (I say it again, a lot of times) to find the threshold values for these attributes. If we give values beyond the threshold values, there are high chances of getting a timeout error.</p><p>Go ahead and try this approach (At your own risk 😉)</p><p>Happy deletion Folks!</p><h3>Final Credits</h3><p>Find the complete code in this <a href="https://github.com/Sajeban/bulk-data-parallel-deletion-mysql">repository</a>.</p><p>Thanks to <a href="https://medium.com/u/95970ee16fb3">Pasindu Senanayake</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ba39065e5928" width="1" height="1" alt=""><hr><p><a href="https://medium.com/sysco-labs/how-to-delete-100-million-rows-in-a-mysql-table-within-30-minutes-ba39065e5928">How to delete 100 million rows in a MySQL table within 30 minutes</a> was originally published in <a href="https://medium.com/sysco-labs">Sysco LABS Sri Lanka</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>