<?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 Priyabrata Jena on Medium]]></title>
        <description><![CDATA[Stories by Priyabrata Jena on Medium]]></description>
        <link>https://medium.com/@priyabrata.jena?source=rss-9037f9272758------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*hYRH7tgAuBCX4fLuiYYHqA.jpeg</url>
            <title>Stories by Priyabrata Jena on Medium</title>
            <link>https://medium.com/@priyabrata.jena?source=rss-9037f9272758------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 05:14:43 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@priyabrata.jena/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[MySQL Query Optimization: Top few tips that helped me]]></title>
            <link>https://medium.com/@priyabrata.jena/mysql-query-optimization-top-few-tips-that-helped-me-f3e23c785c30?source=rss-9037f9272758------2</link>
            <guid isPermaLink="false">https://medium.com/p/f3e23c785c30</guid>
            <dc:creator><![CDATA[Priyabrata Jena]]></dc:creator>
            <pubDate>Sun, 18 Aug 2024 15:21:09 GMT</pubDate>
            <atom:updated>2024-08-18T15:21:09.168Z</atom:updated>
            <content:encoded><![CDATA[<blockquote>Requirement understanding and Designing schema, Data volume analysis, Indexing, Join, and Subquery</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7XOYZVsy1Fo3Nuw53AkhsQ.png" /><figcaption>[Source]plesk.com</figcaption></figure><p>Query Optimization is a process of choosing the most efficient way of designing DB and executing a query to get the desired result</p><h3>Requirement understanding and Designing schema</h3><ul><li><strong>Optimize Schema for Speed</strong><br>A well-structured schema is crucial for performance. Poorly designed schemas can lead to slow queries and excessive resource usage. Ensure your schema is optimized for the best possible query speed.</li><li><strong>Focus on Core Application Needs</strong><br>When designing a schema, prioritize the primary goal of the application, not reporting or analytics. Separate systems should handle analysis and reporting to avoid compromising performance.</li><li><strong>Efficient Table Structure</strong><br>For example, the “product” table should store frequently queried fields directly in the main table. Less commonly used fields can be placed in a separate reference table. This reduces the size of data blocks that need to be read, improving performance by minimizing disk access.</li><li><strong>Flat Table Structure for Stability</strong><br>In certain cases, a flat table structure can be beneficial to avoid costly JOIN operations, particularly when dealing with fields that are frequently read and rarely change.</li><li><strong>Example: User Field in Comment Table</strong><br>Frequently read fields like “user_id” in a “comment” table are best kept in a flat structure to minimize joins.</li><li><strong>Maintain Consistent Data Types Across Tables</strong><br>Always declare columns with identical information using the same data types across tables. This speeds up JOIN operations and ensures consistency.</li><li><strong>Avoid String Conversions</strong><br>When comparing column values, ensure they share the same character set and collation to avoid unnecessary conversions during query execution.</li><li><strong>Avoid NULL Values</strong><br>NULL values can disrupt query results and hinder performance. For instance, calculations like sums may misbehave with NULLs unless handled explicitly with the IFNULL function. Design columns to minimize the need for NULLs wherever possible.</li></ul><h3>Data volume analysis</h3><ul><li><strong>Analyze Data Volume Before JOINs</strong><br>When considering a JOIN query, always assess the data volume in each table. If one table contains a large dataset (e.g., Table A) and the other has only a few records (e.g., Table B), a subquery may be more efficient than a JOIN.</li><li><strong>Avoid Cartesian Products in JOINS</strong><br>When joining two tables (e.g., Table T1 with X rows and Table T2 with Y rows), the potential result is a Cartesian product of X*Y rows, leading to redundant data and potential memory issues. To minimize this, always use an appropriate ON condition (e.g., T1.id = T2.fid). Additionally, consider refining the query further with conditions on fields like &quot;status&quot; to reduce the result set and improve performance.</li></ul><h3>Techniques highlights</h3><ol><li><strong>Avoid Unnecessary Columns in SELECT</strong><br>Only select the columns you need. Retrieving extra data consumes more memory and slows down the query.</li><li><strong>Index Key Predicates</strong><br>Ensure all key fields in WHERE, JOIN, ORDER BY, and GROUP BY clauses are properly indexed to avoid full table scans.</li><li><strong>Proper Indexing to Prevent Table Scans</strong><br>Improper indexing can trigger table scans, leading to performance issues and potential locking problems.</li><li><strong>Avoid Functions in Predicates</strong><br>Using functions on indexed columns in predicates prevents MySQL from utilizing the index, leading to slower queries.</li><li><strong>Avoid Wildcards at the Beginning of a Predicate(expression)</strong><br>Starting a LIKE clause with % (e.g., LIKE &#39;%abc&#39;) forces a full table scan. Always try to structure queries to avoid this.</li><li><strong>Optimize Pagination</strong><br>Instead of using LIMIT with an offset, fetch one more row than needed. For the next page, start from that extra row, improving performance for large datasets.</li><li><strong>Utilize MySQL Query Cache</strong><br>MySQL provides query caching to speed up repeated queries. Check if caching is enabled using:</li></ol><pre>SHOW VARIABLES LIKE &#39;have_query_cache&#39;;</pre><ul><li>Adjust the cache size with:</li></ul><pre>SET GLOBAL query_cache_size = 40000;</pre><p>8.<strong> Subquery Optimizations</strong><br>Instead of using LEFT JOIN or IN with subqueries, consider materializing the subquery into a temporary table and then joining on it.</p><h3>Techniques in Details MySQL Indexing:</h3><ol><li><strong>Covering Index [!!! Important One!!!]</strong><br>Create indexes that cover all columns in the WHERE, GROUP BY, ORDER BY, and SELECT clauses to improve query performance by reducing the need to read from the table. <br><strong>Benefits</strong>:<br> — Since all the columns used in the query are present in the index, MySQL can retrieve the data directly from the index without having to access the actual table (this is called an “index-only scan”).<br> — This reduces the number of disk I/O operations and improves the query performance.</li><li><strong>Indexing with Proper Ordering</strong><br>When indexing based on specific query patterns, consider ordering, e.g., INDEX(timecreated DESC) for queries that require ordered results.</li><li><strong>Composite Index</strong><br>When multiple columns are frequently used in the WHERE clause, create a composite index (e.g., INDEX(f1, f2)) to speed up query execution.</li><li><strong>Simple Index</strong><br>For queries with multiple equality conditions (e.g., WHERE f1 = ? AND f2 = ? AND f3 = ?), using simple indexes or even forcing an index on specific fields can help MySQL optimize the query.</li><li><strong>Handling </strong><strong>IN Queries</strong><br>MySQL may not automatically use indexes with IN queries (e.g., WHERE id IN (1, 2, 3, 4)). Forcing an index can sometimes be necessary for performance.</li><li><strong>Index Prefixes</strong><br>You can use prefixes to index only the first N characters of a string column, making the index file smaller and faster. For example, for BLOB or TEXT columns, you must specify a prefix length.</li><li><strong>Index Considerations</strong><br>Indexes improve retrieval but can slow down INSERT and UPDATE operations. To optimize performance: <br>- Create indexes only when necessary.<br>- If searching by a combination of columns, use a composite index rather than separate indexes.<br>- Place the most frequently used column at the beginning of the composite index for better compression and performance.</li><li><strong>String Prefix Indexing</strong><br>When indexing long string columns, it is often more efficient to index only a unique prefix. Shorter indexes result in faster lookups, improved cache efficiency, and fewer disk seeks.</li></ol><h3>Analyzing Queries with EXPLAIN</h3><p>MySQL provides a powerful EXPLAIN statement that allows you to analyze how queries are executed. You can use it alongside SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.</p><p>For example, let’s analyze a simple query:</p><pre>EXPLAIN SELECT customer_id, customer_name <br>FROM customers <br>WHERE customer_id=&#39;140385&#39;;</pre><p>The result might look like this:</p><p>— — + — — — — — — -+ — — — — — -+ — — — — — — + — — — + — — — — — — — -+ — — — +<br>| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |<br>+ — — + — — — — — — -+ — — — — — -+ — — — — — — + — — — + — — — — — — — -+ — — +<br>| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 500 | 10.00 | Using where |<br>+ — — + — — — — — — -+ — — — — — -+ — — — — — — + — — — + — — — — — — — -+ — — +</p><p>This output tells us:</p><ul><li><strong>type: ALL</strong> means a <strong>full table scan</strong> is being performed because no index is used (indicated by key: NULL).</li><li>MySQL will scan all 500 rows in the table to find the matching record.</li></ul><p>To optimize the query, we can add an index to the customer_id column:</p><pre>CREATE INDEX customer_id_idx ON customers (customer_id);</pre><p>After adding the index, running the EXPLAIN statement again gives us:</p><p>+ — — + — — — — — — -+ — — — — — -+ — — — — — — + — — — + — — — — — — — -+ — — +<br>| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |<br>+ — — + — — — — — — -+ — — — — — -+ — — — — — — + — — — + — — — — — — — -+ — — +<br>| 1 | SIMPLE | customers | NULL | ref | customer_id | customer_id | 13 | const | 1 | 100.00 | NULL |<br>+ — — + — — — — — — -+ — — — — — -+ — — — — — — + — — — + — — — — — — — -+ — — +</p><p>Now, MySQL uses the customer_id index (key: customer_id), significantly reducing the number of rows it needs to scan to just 1.</p><p>This small change drastically improves query performance, especially in larger datasets.</p><h3>Query Profiling in MySQL</h3><p>MySQL provides useful tools for monitoring and improving query performance, such as <strong>query profiling</strong> and <strong>slow query logging</strong>. These features allow you to identify bottlenecks and inefficient queries in your database.</p><h4>The INFORMATION_SCHEMA.PROFILING Table</h4><p>The INFORMATION_SCHEMA.PROFILING table stores profiling information for queries executed during the current session. This feature is disabled by default, but it can be enabled with the following command:</p><pre>SET SESSION profiling = 1;</pre><p>Once profiling is enabled, you can run your query without using the EXPLAIN statement. Afterward, to view profiling information, use the SHOW PROFILES command:</p><pre>SHOW PROFILES;</pre><p>This will display a list of queries that have been executed in the session along with their durations. To get detailed information about a specific query, use the following command, replacing 4 with the actual query ID:</p><pre>SELECT * <br>FROM INFORMATION_SCHEMA.PROFILING <br>WHERE QUERY_ID = 4;</pre><p>This detailed output shows each stage of query execution, such as sending data, waiting for the table, or executing functions, which helps identify which part of the query is consuming the most time.</p><h4>Slow Query Log</h4><p>Another effective way to optimize queries is by enabling MySQL’s <strong>slow query log</strong>, which records all queries that exceed a specified execution time. This helps identify which queries are taking the longest to execute and may need optimization.</p><p>To enable the slow query log and set the threshold for long queries, use the following commands:</p><pre>SET GLOBAL slow_query_log = &#39;ON&#39;;<br>SET GLOBAL long_query_time = 2;  -- Log queries taking longer than 2 seconds</pre><p>Once enabled, any query that takes longer than the defined long_query_time will be recorded in the slow query log, allowing you to review and optimize it.</p><p>By using these profiling tools, you can continuously monitor query performance and pinpoint areas for optimization, ensuring better resource usage and improved overall system performance.</p><p>The profiling techniques we have discussed won’t be complete without mentioning EXPLAIN ANALYZE.It works similarly to the EXPLAIN statement. The key difference is that the former is more verbose. You get more statistics about the query execution.</p><p>Please leave your feedback in the comments :)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f3e23c785c30" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deploy your NextJs APP on Google Cloud Run]]></title>
            <link>https://medium.com/@priyabrata.jena/deploy-your-nextjs-app-on-google-cloud-run-dfdc0367dd36?source=rss-9037f9272758------2</link>
            <guid isPermaLink="false">https://medium.com/p/dfdc0367dd36</guid>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[google-cloud-run]]></category>
            <category><![CDATA[deployment-automation]]></category>
            <category><![CDATA[nextjs]]></category>
            <dc:creator><![CDATA[Priyabrata Jena]]></dc:creator>
            <pubDate>Fri, 14 Oct 2022 20:03:11 GMT</pubDate>
            <atom:updated>2022-10-14T20:20:49.742Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-nQO-7G6FQp1htG4HsTBrQ.png" /><figcaption>(NextJS + Google Cloud Run)</figcaption></figure><p>NextJs is one of the best ReactJs-based web frameworks with Serverside rendering and generating static websites.</p><p>Google Cloud Run is serverless. It enables us to run a container that can be invocable via HTTP requests and events. It supports <strong>custom domains</strong> as well in limited regions.</p><p>We will cover all steps with sample code and commands. I am assuming you have sound knowledge of the NextJS setup &amp; Docker.</p><h3>Steps</h3><ol><li>Setup your NextJs project locally first and test it</li><li>Keep the below Dockerfile in your project root directory (filename should be <em>Dockerfile</em>)</li><li>Build your docker container docker build -t &lt;tagname&gt;</li><li>Run the container using this command docker run -p 3000:3000 &lt;tagname&gt; to test.</li><li>You need to install <a href="https://cloud.google.com/sdk/docs/install"><strong>gcloud CLI</strong> </a>on your system. Below are commands for gcloud setup.<br>gcloud auth login<br>gcloud auth configure-docker (once only)<br>Select project first using <strong><em>gcloud config set project PROJECT_ID</em></strong></li><li><strong>Pre-requisites:</strong> Enable google services -&gt; API [run.googleapis.com]</li><li>Run deployment script to build the project and push it into Google Cloud Run [Goto: <strong>Deployment script -&gt; Google Cloud Run </strong>section below]</li></ol><blockquote>Few gcloud useful commands:</blockquote><blockquote>gcloud config set project &lt;PROJECTNAME&gt;<br>gcloud config set project &lt;PROJECTNAME&gt; --configuration=&lt;configuration-name&gt;<br>gcloud config configurations list<br>gcloud config list<br>gcloud config configurations activiate &lt;configuration-name&gt;<br>gcloud config set run/region &lt;region&gt;</blockquote><h4><a href="https://docs.docker.com/build/building/multi-stage/"><strong>Multi stage Dockerfile</strong></a><strong> for the NextJs app</strong>.</h4><p>I have kept a provision to pass the environment value (production/test) as a build argument $RENV while running the docker command in the deployment script.</p><p>Need to add build commands environment-wise in your package.json as below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NciMxxAnA-JipdYdQTKcUg.png" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/89306b2d0143b33f4e7d8a5d1a5153fa/href">https://medium.com/media/89306b2d0143b33f4e7d8a5d1a5153fa/href</a></iframe><h4>Deployment script -&gt; Google Cloud Run</h4><p>I would recommend running the below commands manually (one by one) to check once.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/42f3838b16b990d23728d6c854ae1d6b/href">https://medium.com/media/42f3838b16b990d23728d6c854ae1d6b/href</a></iframe><blockquote><em>See below the deployment output. If the process succeeds then only it will route all traffic to the new deployment, in this way it’s safer.</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nXrLPu9xTk-Y3B0Wontzlw.png" /><figcaption>Screenshot of Deployment final output</figcaption></figure><h4><strong>Reference for troubleshooting</strong></h4><p><a href="https://cloud.google.com/run/docs/troubleshooting#container-failed-to-start">Troubleshoot Cloud Run issues | Cloud Run Documentation | Google Cloud</a></p><h4>Google CDN Setup to serve my APP’s static content</h4><p>I am using Google Cloud CDN for static files like images/JSON content. I do manually update App’s assets to Cloud storage, but we can also write a script to deploy along with the above deployment process as the first step. Also, we need to invalidate existing files, if the content changes.</p><p>One of the Good reference of Google Cloud CDN setup ⬇️</p><p><a href="https://medium.com/techintoo/serving-static-files-using-google-cloud-cdn-storage-bucket-db1287cb5e40">Serving static files using Google Cloud CDN + Storage Bucket</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dfdc0367dd36" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL: Replication on Google Cloud SQL from AWS RDS master]]></title>
            <link>https://medium.com/@priyabrata.jena/mysql-replication-on-google-cloud-sql-from-aws-rds-master-a501762dcccd?source=rss-9037f9272758------2</link>
            <guid isPermaLink="false">https://medium.com/p/a501762dcccd</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[mysql-binlog]]></category>
            <category><![CDATA[cross-cloud-replication]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[gcp]]></category>
            <dc:creator><![CDATA[Priyabrata Jena]]></dc:creator>
            <pubDate>Thu, 13 Oct 2022 19:09:20 GMT</pubDate>
            <atom:updated>2022-10-13T19:38:56.084Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BHatbCS6H5cSr094e1MzUg.png" /></figure><p><strong>Why did I need replication on a different cloud?</strong></p><p>My use case was a very specific case and a personal choice<br>1) I was exploring Google Data Platform (BigQuery, Data Studio)[had some free credit]<br>2) My Apps and Databases are on AWS Cloud<br>3) To extend MySQL data for reporting on Google Cloud, which can be also synced to Bigquery for further analysis.<br>4) Also, I was exploring Google cloud serverless services to build some Apps. So it helped to play around using the replica database.</p><p><strong>Key points to remember</strong></p><blockquote>AWS Cloud RDS:<br>To do the replication we need to enable <strong>binlong. </strong>If we enable Automatic backup then binglog enables automatically.</blockquote><blockquote>Generally, we do Master Slave setup for our production environment and keep Master as private (I mean can be accessible within VPC) and Slave can be access from public(whitelisting IP address)</blockquote><blockquote>We should enable <strong>Binglog on Master. </strong>Let me explain why binlogs are important on master <br>— — Whenever the master’s database is modified or updated, the changes are written to a file, which is binary log, or binlog. <br>— — The master has a thread, called the dump thread, that continuously reads the master’s binlog &amp; sends it to the slave. <br>— — So if the binlogs are not enabled on the master the dump thread wont be able to read the binlog file and wont be able to send it to slave. Hence binlog is important.</blockquote><blockquote><strong>Should we enable binglog on Replica?<br></strong> — — However, enabling binary logs is not recommended for an RDS MySQL read replica as the overhead involved may contribute to replica lag from your original primary source. <br> — — In the replica, a single thread (IO Thread) connects through the network, to the source, to fetch binlogs. It then writes the logs to disk as relay logs. A single thread (SQL Thread) reads relay logs and applies them to the replica’s database. — — If the replica has binlogs enabled, the SQL Thread is also responsible to write new binlogs. This extra overhead can result in replica lag from your original primary source.</blockquote><p>Based on the above points, I enabled binlog only on Master. But now the question is <strong>“How to connect the master instance with public n/w?”<br></strong> — — We need a Proxy server through which we can connect to the master and the Proxy server will have security to allow specific IPs only<br> — — Create a MySQL user(ex: gcp_replication) with below permission <strong>[*P1]</strong></p><pre>CREATE USER ‘gcp_replication’@’%’ IDENTIFIED BY ‘yourpassword’;</pre><pre>GRANT REPLICATION SLAVE, REPLICATION CLIENT, RELOAD,EXECUTE, SELECT, SHOW VIEW ON *.* TO ‘gcp_replication’@’%’;</pre><pre>FLUSH PRIVILEGES;</pre><p>Final STEPS</p><p><strong>→AWS RDS side<br></strong>1) Enable Automatic Backup on Master, it will enable binlog automatically <br>2) Need to set parameter configuration change<br> — enforce_gtid_consistency : ON<br> — gtid-mode: ON<br> — binlog_format : ROW<br>3) Create a new user as mentioned above <strong>[*P1]<br></strong>4) Setup a proxy server on ec2 (ubuntu, Nginx)<br> — go for a small instance type<br> — keep it public with dedicated IP<br> — Add the below code into the Nginx configuration file and restart Nginx</p><pre>listen MySQL<br> bind 0.0.0.0:3306<br> timeout connect 10s<br> timeout client 1m<br> timeout server 1m<br> mode tcp<br> server singleMySQL &lt;rds-endpoint&gt;:3306</pre><p>— Whitelist the Outgoing IP address of newly created GCP cloud SQL (MySQL Replica will be created as part of GCP steps)to access the Proxy server (security)</p><p><strong>→GCP Cloud side<br></strong>1) Go to Google console -&gt; DMS<br>2) Create connection profile, need to add AWS MySQL connection details(hostname, username, password). Here hostname would be the <strong>Proxy server&#39;s Public IP </strong>and add the user details that you create in <strong>[*P1]<br></strong>3) Create migration job, Follow the GCP Console UI and keep the below points in mind<br>— Source DB engine: choose Amazon RDS MySQL<br> — Destination DB engine: choose Cloud SQL MySQL<br> — Migration Type: Continuous (Realtime synchronization)<br> — Define connectivity method: IP Whitelist, Copy the Destination Outgoing IP and add in the proxy server security group</p><p>Cost</p><ol><li>Google DMS: There’s no additional charge for network ingress from external source databases. However, costs can be incurred outside of Google such as platform <strong>egress charges.</strong></li><li>Cloud SQL — <a href="https://cloud.google.com/sql/docs/mysql/pricing">MySQL pricing</a>, based on your instance type.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a501762dcccd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Lambda Container images & How to do quickly?]]></title>
            <link>https://medium.com/@priyabrata.jena/why-lambda-container-images-how-to-do-quickly-93a748ad00f4?source=rss-9037f9272758------2</link>
            <guid isPermaLink="false">https://medium.com/p/93a748ad00f4</guid>
            <category><![CDATA[containers]]></category>
            <category><![CDATA[docker-image]]></category>
            <category><![CDATA[lambda]]></category>
            <category><![CDATA[image-recognition]]></category>
            <dc:creator><![CDATA[Priyabrata Jena]]></dc:creator>
            <pubDate>Sat, 05 Mar 2022 06:02:22 GMT</pubDate>
            <atom:updated>2022-03-05T06:02:22.072Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9hlLQacf59pDve_LGnFgbQ.png" /><figcaption>Lambda functions as a container</figcaption></figure><p>Hope you are familiar with <a href="https://docs.aws.amazon.com/lambda/latest/dg/welcome.html">AWS Lambda</a>. In short, Lambda is an event-driven, serverless compute service that enables you to run your backend code without having a dedicated server/instance.</p><h3>Why Lambda Container images?</h3><ul><li>Most developers use the zipped package as a deployment process of Lambda functions(using manual OR any serverless framework). But there is a limitation in Zipped package size(Max size 50MB), refer to <a href="https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html">AWS Lambda Limitations</a>.</li><li>Use of “Container image” is not because of only size, if you have a good investment of CI/CD, then It would be way easy.</li></ul><h3>How to do it quickly?</h3><p><strong>Prerequisites</strong></p><ul><li>Docker setup for Docker CLI commands</li><li>The AWS CLI for AWS service API operation calls</li><li>Your Function code</li><li>The machine’s environment variables include LAMBDA_TASK_ROOT and LAMBDA_RUNTIME_DIR. This is where any dependencies and function handlers are deployed so that the Lambda runtime can execute them when invoked.</li></ul><p><strong>Steps that I have followed</strong></p><p>I had done the setup to solve Image Moderation using AWS Rekognition and Profanity check using <a href="https://github.com/vzhou842/profanity-check">profanity_check</a> Python package(This package use scikit-learn which is big in size)</p><ul><li>Setup Python <a href="https://docs.python.org/3/library/venv.html">virtual env</a></li><li>Go to your working code directory [/&lt;yourpath&gt;/py_serverless/image_moderation]</li><li>Run this command to start virtual env <br>[source &lt;path-to-env&gt;/bin/activate]</li><li>Go to the working directory where Dockerfile is present OR you have to use `-f &lt;dockerfile/path&gt;` [see docker file towards the end of article ⬇️]</li><li>docker build -t image_moderation</li><li>Command to run docker and pass environment variable</li></ul><pre>docker run -p 9000:8080 -e AWS_ACCESS_KEY=’sdakfjksdfj’ -e AWS_SECRET_KEY=’sdfdfdf’ -e REGION_NAME=’us-east-2&#39; image_moderation</pre><ul><li>Command to invoke lambda function locally and pass message body as JSONStringfy</li></ul><pre>curl -XPOST “<a href="http://localhost:9000/2015-03-31/functions/function/invocations">http://localhost:9000/2015-03-31/functions/function/invocations</a>&quot; -d ‘{“Records”:[{“messageId”:1,”body”:”{\”id\”:1124,\”media\”:[{\”id\”:3107,\”path\”:\”image-path/2556_692bcadcf46b4c5696fd96610a5a07c6_1629986414535.jpeg\”}]}”}]}’</pre><p><strong>How to Push to ECR</strong></p><ol><li>Using CLI you need to log in to ECR</li></ol><pre>aws — profile &lt;yourprofilename&gt; ecr get-login-password — region us-east-1 | docker login — username AWS — password-stdin &lt;yourAccountId&gt;.dkr.ecr.us-east-1.amazonaws.com</pre><p>2. Tag your docker image and Push it to ECR</p><pre>docker tag image_moderation:latest &lt;yourAccountId&gt;.dkr.ecr.us-east-1.amazonaws.com/image_moderation:latest</pre><pre>---------------</pre><pre>docker push &lt;yourAccountId&gt;.dkr.ecr.us-east-1.amazonaws.com/image_moderation:latest</pre><pre>Note: &lt;yourAccountId&gt;.dkr.ecr.&lt;region&gt;.amazonaws.com</pre><p><strong>Docker.file</strong></p><pre>FROM public.ecr.aws/lambda/python:3.9</pre><pre># Copy function code<br>COPY image_moderation.py ${LAMBDA_TASK_ROOT}<br>COPY database.py ${LAMBDA_TASK_ROOT}<br>COPY communication.py ${LAMBDA_TASK_ROOT}</pre><pre># Install the function’s dependencies using file requirements.txt<br># from your project folder.<br>COPY requirements.txt .<br>RUN pip3 install -r requirements.txt — target “${LAMBDA_TASK_ROOT}”</pre><pre># Set the CMD to your handler (could also be done as a parameter override outside of the Dockerfile)<br>CMD [ “image_moderation.handler” ]</pre><p>Please do follow me to be notified about the next topic on image moderation and sample code 🔜 .</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=93a748ad00f4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[EC2 Ubuntu — NodeJS APP Log files ingest to AWS Cloudwatch Logs]]></title>
            <link>https://medium.com/@priyabrata.jena/ec2-ubuntu-nodejs-app-log-files-ingest-to-aws-cloudwatch-logs-766a254feded?source=rss-9037f9272758------2</link>
            <guid isPermaLink="false">https://medium.com/p/766a254feded</guid>
            <category><![CDATA[cloudwatch]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[ec2]]></category>
            <dc:creator><![CDATA[Priyabrata Jena]]></dc:creator>
            <pubDate>Thu, 09 Sep 2021 12:21:58 GMT</pubDate>
            <atom:updated>2021-09-09T12:21:58.191Z</atom:updated>
            <content:encoded><![CDATA[<h3>EC2 Ubuntu — NodeJS APP Log files ingest to AWS Cloudwatch Logs</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1u-JdYc9cGZL7oC3PTZgAw.png" /></figure><p><strong>Steps required to achieve log ingest into cloudwatch from EC2</strong></p><blockquote>1) Create IAM Role for Cloudwatch<br>2) Install Cloudwatch Agent<br>3) Configure your file path<br>4)Start the Agent</blockquote><h4>1) Create an IAM role to run the CloudWatch agent on your EC2 instance: (<a href="https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/create-iam-roles-for-cloudwatch-agent-commandline.html">AWS Reference link</a>)</h4><blockquote>Open the <a href="https://console.aws.amazon.com/iam/">IAM console</a>.<br> In the navigation pane, choose Roles.<br> Choose Create role.<br> For Choose the service that will use this role, choose EC2.<br> Choose Next: Permissions.<br> In the list of policies, select the CloudWatchAgentServerPolicy check box.<br> Choose Next: Tags, and then choose Next: Review.<br> For Role name, enter a name for the role, such as CloudWatchAgentServerRole.<br> (Optional) Provide a role description.<br> Confirm that CloudWatchAgentServerPolicy appears next to Policies.<br> Choose Create role.<br> <a href="https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/iam-roles-for-amazon-ec2.html#attach-iam-role">Attach</a> this newly created IAM role to the EC2 instance.</blockquote><h4>2) <strong>Download and install the unified CloudWatch agent on your EC2 instance:</strong></h4><pre>$ wget https://s3.amazonaws.com/amazoncloudwatch-agent/ubuntu/amd64/latest/amazon-cloudwatch-agent.deb</pre><pre>$ sudo dpkg -i -E ./amazon-cloudwatch-agent.deb</pre><h4>3) Cofigure — Manually create config.json</h4><pre>sudo vim /opt/aws/amazon-cloudwatch-agent/bin/config.json</pre><pre>{<br>     &quot;agent&quot;: {<br>         &quot;run_as_user&quot;: &quot;root&quot;<br>     },<br>     &quot;logs&quot;: {<br>         &quot;logs_collected&quot;: {<br>             &quot;files&quot;: {<br>                 &quot;collect_list&quot;: [<br>                     {<br>                         &quot;file_path&quot;: &quot;/var/log/path-to-logfile/error.log&quot;,<br>                         &quot;log_group_name&quot;: &quot;groupname-error-log&quot;,<br>                         &quot;log_stream_name&quot;: &quot;{instance_id}&quot;<br>                     }<br>                 ]<br>             }<br>         }<br>     }<br> }</pre><h4>[Or] By Cloudwatch Wizard</h4><pre>$ sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-config-wizard</pre><h4>3) Start the Agent &amp; check the status</h4><pre>$ sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -a fetch-config -m ec2 -c file:/opt/aws/amazon-cloudwatch-agent/bin/config.json -s</pre><pre>service amazon-cloudwatch-agent status</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=766a254feded" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Serverless Architecture to send email via AWS SES]]></title>
            <link>https://medium.com/@priyabrata.jena/serverless-architecture-to-send-email-via-aws-ses-69feef06b1a8?source=rss-9037f9272758------2</link>
            <guid isPermaLink="false">https://medium.com/p/69feef06b1a8</guid>
            <category><![CDATA[serverless-architecture]]></category>
            <category><![CDATA[aws]]></category>
            <dc:creator><![CDATA[Priyabrata Jena]]></dc:creator>
            <pubDate>Thu, 16 Jan 2020 17:30:24 GMT</pubDate>
            <atom:updated>2020-01-16T17:30:24.586Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lPpUm0ZyqsHECn7J_E_zzw.png" /></figure><p>Serverless architecture(known as Serverless computing) is a cloud computing model in which the Cloud provider runs the server and dynamically manages the allocation of machine resources.</p><p><strong>Focus on your Application, not on the Infrastructure.</strong></p><p>Generally we keep the “Email sending business logic” code as part of the main Application. It includes <em>email template management, dynamic values replacement, send mail etc</em>. It might put more load(processing) on the Application and probably leads to some other issues as well.</p><p>The new Serverless Architecture(see the above diagram) is a better fit. This can offload all “email sending business logic” from your main application.</p><p>Let me explain the Architecture Diagram from Left to Right</p><p><strong>Main Application : </strong>It could be your backend application, that will raise the request(REST API) to send mail on an event as per your business need. We should post required dynamic values data and Email Template Name in this request.</p><p><strong>Cloudwatch Event / API Gateway : </strong>Here we have two different ways (1) Cloudwatch Event (2) API Gateway</p><blockquote>Cloudwatch Event<strong>:</strong> This is required to schedule an event that will be executed at a given future time. On trigger of this event the Lambda-1 will be triggered.</blockquote><blockquote>For example, “let’s say we have to send an email to the customers on his/her 2nd day of the registration on our portal”. In this case we can set an event in the “Cloudwatch” with the event time and required metadata.</blockquote><blockquote><strong>API Gateway: </strong>This is required to send the mail immediately. This triggers the Lambda-1. We can use the Cloudwatch event for this with a very near future time. But I suggest to use API Gateway</blockquote><p><strong>Lambda — 1 : </strong>It receives the data from the tigger point(Cloudwatch event/API Gateway). We can put the logic here to fetch more data if required(based on your use cases). It writes all data into DynamoDB.</p><p>Note: we have to create a new lambda-1 and email template for each different new email.</p><p><strong>DynamoDB :</strong> We can call it our “Email Database”. Probable fields are “Application name, Recipient Emails, Dynamic values, Email template name, time, status, retry count”.</p><p>It triggers Lambda-2 whenever data changes(insert/update)</p><p><strong>Lambda — 2 :</strong> It receives data from DynamoDB and pushes to SQS(mail queue). We can put our business logic if required. This is a generic lambda for all emails.</p><p><strong>SQS : </strong>It triggers Lambda-3</p><p><strong>Lambda — 3 : </strong>It receives the message from SQS and binds the email “Dynamic Values” with the “Email Template” and then calls SES. This is a generic lambda for all emails.</p><p><strong>SES : </strong>It does it’s magic to send the mail to the recipient.</p><p><strong>Recipient :</strong> Happy :) Clap Clap!!!!!</p><p><strong>*SES settings and Email Template creation :</strong></p><ul><li>Need to do a few settings like add “From email-id” and verify it. (<a href="https://aws.amazon.com/ses/getting-started/">ref</a>)</li><li>Create the email Templates. There are two ways to <a href="https://aws.amazon.com/blogs/messaging-and-targeting/introducing-email-templates-and-bulk-sending/">create email template</a> :</li></ul><p>— — (1). AWS Console (2). SES create template API</p><p>I will update the sample code(NodeJS) very soon</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=69feef06b1a8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bigquery Cost Optimization]]></title>
            <link>https://medium.com/@priyabrata.jena/bigquery-cost-optimization-c35e35c055bc?source=rss-9037f9272758------2</link>
            <guid isPermaLink="false">https://medium.com/p/c35e35c055bc</guid>
            <category><![CDATA[google-bigquery]]></category>
            <dc:creator><![CDATA[Priyabrata Jena]]></dc:creator>
            <pubDate>Mon, 13 Jan 2020 17:02:55 GMT</pubDate>
            <atom:updated>2020-01-13T17:07:33.342Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*V8fi_7j6b6GrXCeF6JQdzQ.png" /></figure><p>BigQuery is a Data warehouse that solves storing and querying massive datasets very fast and efficiently. We use very extensively to store all events data for our Analytics Team.</p><p><strong>Challenges we had:</strong></p><p>We were very happy from the beginning. But as our data grow it cost more than we had expected.</p><p>Let me explain our initial setup, <em>One Project(p1) -&gt; One dataset (d1) -&gt; Multiple tables based on our application environment(Prod_Table, Testing_Table).</em></p><ol><li>Incur more cost</li><li>There is no table base permission. So we are unable to restrict user permission for production tables.</li><li>We can’t differentiate bigquery costs based on different teams’ usage.</li><li>For testing any query, Our team uses the production table directly.</li></ol><p><strong>Changed the Bigquery setup</strong></p><p>Under one project(P1), we created multiple datasets based on different Team and Application environment (d1 -&gt; for Analytics, d2 -&gt; for Production,d3-&gt;for Testing)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/590/1*45qP-WHvOuwfwowbW862pA.png" /></figure><p><strong>Cost optimization</strong></p><ol><li>After changing the initial setup, we are able to provide the permission based on the dataset. Here limited people will have access to production table(where large dataset available)</li><li>We can take backup of the production table(last x months data) and import into testing env(It can be done using BigQuery UI). All initial/test query can be run from test env.</li><li>Don’t write all data into a single table, better you split the data Quarterly/Monthly wise.</li><li>To optimize the cost, better use table <strong>decorators</strong> for test queries</li></ol><p>SELECT * FROM [project:dataset.table@-86400000-]; //last 1 day</p><p><strong>Prepare a guideline to write a query.</strong></p><ol><li>Check the amount of data BQ will process during the query before actually running that query. Simply click on the green exclamation mark (Validator) in the right bottom of the window.</li><li>We can add the <strong>BQ Mate extension</strong> to our Chrome browser, we will see how much the query will cost, in USD.</li><li>Try to avoid <strong>SELECT *</strong> option on big datasets. Don’t be lazy to write down the name of the columns you really need. BQ is a columnar storage. You pay for the amount of data your query needs to read. Do the math: fewer columns selected means fewer columns need to be read, which means lower costs.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/0*6vA2q5v4nwjuP6Fb" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/0*EAAIkUZ3f-D16h64" /></figure><p><strong>Query Performance Tips</strong></p><ol><li>Denormalize tables for performance</li><li>Select only needed columns — Do not use Select *</li><li>Schedule batch queries at off-peak hours using jobs</li><li>Use caching when possible</li><li>Caching is best effort</li><li>If table data changes, cache is invalidated</li><li>Use jobs.getQueryResults to page through cached query results in a temporary table (no charge)</li><li>Try to use ORDER BY and LIMIT in outermost queries</li><li>LIMIT is applied to results by Master</li><li>Build queries from the inside out by using subqueries</li><li>Filter data in subqueries</li><li>Perform arithmetic, ordering, case logic in outer query</li><li>Use queries to create materialized intermediate tables</li><li>Create subset of complex data in destination table</li><li>Partially aggregate data in destination table</li><li>Move heavyweight filters, such as regexp, to the end</li><li>Avoid grouping on unbounded possible values</li><li>Example: Web logs with arbitrary GET parameters in the suffix</li><li>Consider using IF/CASE instead of self-joins because IF/CASE has lower processing overhead</li><li>Self-joins require multiple disk reads</li><li>Apply WHERE filters prior to JOINs</li><li>Predicate pushdown</li></ol><p><strong>Other high end Optimization:</strong></p><p>1. Project-level cost control</p><p>Speaking of the maximum billing tier, you can also request a project-level default value by submitting the High-Compute queries form.</p><p>Set the project-level soft limit by requesting a billing alert. When you exceed the threshold you have set for the current month, the billing administrators will receive an email notification.</p><p>Set the project-level hard limit by maximizing the number of bytes processed per day within the project. If you enable the BQ custom quotas feature, you can specify this limit in 10 TB increments. To activate this feature or to modify the value, submit the BQ Custom Quota Request form. When you exceed the quota, queries will return errors.</p><p>2. User-level cost control</p><p>Set the user-level hard limit within a project by maximizing the number of bytes processed per day by a given user. The method is the same as in case of the project-level hard limit, except that the error message is different when you exceed the quota.</p><p>3. Billing-account level cost control</p><p>If you have multiple projects, and you want to monitor the overall budget usage, set billing alerts for your entire billing account. The process is the same as point 2b.</p><p><strong>How to monitor the costs</strong></p><p>There are also options to monitor your spending:</p><p>Follow your monthly data usage on your project’s Cloud Console dashboard on the Billing panel. Be aware though, that the costs will appear only within a couple of days of usage, not in real time.</p><p>Under the Billing/History menu on the Cloud Console, you can get a more detailed view of your spending.</p><p>Export your detailed daily spending to Cloud Storage in csv or json format at Billing/Billing export.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c35e35c055bc" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>