How I reduced our AWS billing cost from $7000+ to under $2000 dollar?
As a Senior Technical Product Manager (hands-on with day-to-day coding and contribute to features as an IC), working on a large scale distributed microservice architecture for an ecommerce platform, I was faced with the challenge of reducing our AWS bill which was exceeding $7000+ per month.
After conducting a thorough analysis of our infrastructure and codebase, I implemented several measures to bring down the costs to under $2000 per month.
Here are the steps I took:
Identifying and optimizing slow MySQL queries for improved application performance
MySQL queries can be a major bottleneck in application performance, leading to slow response times and increased resource utilization. By identifying and optimizing slow queries, you can significantly improve application performance and reduce AWS costs.
To identify slow MySQL queries, you can use a variety of tools, including Laravel Telescope, MySQL’s slow query log, and performance monitoring tools like New Relic or Datadog.
For my applications, I used Laravel Telescope since our applications runs on Laravel. By using Laravel Telescope to identify slow queries, I was able to focus my optimization efforts on the parts of the application that would provide the most significant performance gains.
Analyzing slow MySQL queries using the EXPLAIN operator for improved performance
The EXPLAIN operator is a useful tool that helps to analyze and optimize MySQL queries. By running slow queries with the EXPLAIN operator, I was able to identify the areas of the queries that needed optimization. Here’s an example of how to use the EXPLAIN operator:
EXPLAIN SELECT * FROM customers WHERE customer_id = 1;The EXPLAIN operator is a powerful tool for analyzing the performance of MySQL queries. It provides detailed information about how MySQL is executing a given query, including which indexes are being used, how tables are being joined, and how data is being sorted.
By running slow queries with the EXPLAIN operator, I was able to identify areas of the queries that needed optimization, such as missing indexes or inefficient join operations.
Improving MySQL query performance with proper indexing and string matching in WHERE clauses
Indexes are an essential tool for optimizing database performance. By adding indexes to the columns used in WHERE clauses, I was able to speed up the execution of MySQL queries. Here’s an example of how to add an index to a column:
ALTER TABLE customers ADD INDEX (customer_id);By creating indexes on frequently queried columns, you can significantly improve query performance and reduce AWS costs.
When working with alphanumeric string columns, it’s important to use proper indexing and string matching techniques to improve query performance. One common mistake is to use the wrong data type when querying the column, which can lead to poor performance and increased resource utilization.
Important
In the case of an alphanumeric string column like “sku”, a query that uses a string value with proper quotation marks (i.e., WHERE sku = “1882900”) will match the index and significantly improve query performance. This is because MySQL can use the B-Tree index type to perform an efficient string match and quickly find the rows that match the query condition.
However, if the query uses a numeric value without proper quotation marks (i.e., WHERE sku = 1882900), MySQL will treat the value as a number rather than a string. This means that MySQL will need to perform a full table scan to search for rows that match the query condition, which can be slow and resource-intensive.
To avoid this issue, it’s important to use proper string matching techniques and data types when querying alphanumeric string columns. By using proper quotation marks and string matching techniques, you can improve query performance and reduce resource utilization, leading to a more efficient and cost-effective database infrastructure.
Decreasing lambda timeouts and kept it at hard 28 sec
One way to optimize costs when using AWS Lambda is to decrease the function timeouts. AWS charges for the duration of a function’s execution, rounded up to the nearest 100ms. By reducing the timeout, you can decrease the amount of time AWS charges you for.
For example, if you have a Lambda function that runs for an average of 60 seconds and you reduce the timeout from 60 seconds to 30 seconds, you’ll only be charged for 30 seconds of execution time instead of 60. This can lead to significant cost savings over time, especially if you have many functions running for long periods of time.
Since the queries were already optimized, it didn’t need that long a timeout, thus it was convenient for me to do so, for other it might be a different number.
API gateway caching
API Gateway caching is a powerful tool for reducing the load on your backend by caching the responses to frequently accessed API endpoints. By enabling caching for our most frequently accessed endpoints, we were able to reduce the number of requests hitting our backend, resulting in lower AWS costs.
Reducing RDS sizes post MySQL query optimization
After optimizing our MySQL queries and adding proper indexes, we were able to reduce the size of our RDS instances, which lowered our monthly costs. It’s important to monitor the performance of your RDS instances and adjust their sizes accordingly to avoid overprovisioning and incurring unnecessary costs.
Decreasing CloudWatch logs retention
CloudWatch logs can quickly accumulate, resulting in high storage costs. By reducing the retention period for CloudWatch logs, we were able to save on storage costs. It’s important to balance the need for log retention with the cost of storage and adjust the retention period accordingly.
CloudFront caching for most read APIs where there is no frequent data changes with proper TTL
CloudFront caching is a powerful tool for reducing the load on your backend by caching the responses to frequently accessed API endpoints. By using CloudFront caching for our most frequently accessed APIs, we were able to lower our AWS costs. However, it’s important to consider the cacheability of your API responses and adjust the caching strategy accordingly.
Reducing number of queries for our search service for each search drastically
When it comes to an ecommerce search service, it can involve generating filters, synonyms, fuzzy search, aliasing, and masking. Each of these tasks requires querying the database to get the necessary information, which can result in a large number of queries being executed for each search.
To optimize the search service, it’s important to reduce the number of queries being executed while still maintaining the necessary functionality. This can be achieved through techniques such as caching and optimization of the search algorithm.
For example, instead of querying the database for every filter and synonym needed for a search query, the service can pre-cache commonly used filters and synonyms, and only query the database for those that are not cached. Additionally, the search algorithm can be optimized to reduce the number of queries needed to generate the search results.
By reducing the number of queries needed for each search, the search service can be made more efficient, leading to faster search results and lower resource utilization. This can ultimately result in cost savings and improved user experience for customers.
Selecting only those columns in MySQL that are needed and removing all “*” queries
Another way to optimize your database performance is by selecting only the columns that you actually need in your queries, rather than using the wildcard (*) to select all columns. This can reduce the amount of data that needs to be retrieved from the database and improve query performance.
For example, instead of using the query
“SELECT * FROM orders”
you could use
“SELECT order_id, customer_id, order_date FROM orders”
to retrieve only the columns that are needed.
Turning off staging between 8pm to 8am every day (non-working hours) and on weekends
When running a staging environment, it’s important to conserve resources and minimize costs when the environment is not being used. One way to do this is by turning off the staging environment during non-working hours and weekends, when developers are not actively working on it.
For example, you could use AWS Lambda functions to automatically start and stop the staging environment based on a schedule. This can help reduce costs by only running the environment when it’s needed.
Reducing joins and normalizing tables
When designing your database schema, it’s important to minimize the number of table joins required for your queries. Each join adds additional complexity and can slow down query performance. One way to do this is by normalizing your tables, which involves breaking down your data into smaller, related tables.
In conclusion, optimizing a large scale distributed microservice architecture for an ecommerce platform is a challenging task that requires continuous monitoring and improvement. By using the basic techniques discussed in this blog, you can significantly reduce your AWS bill and improve the overall performance and efficiency of your infrastructure.
It’s important to keep in mind that these techniques are just the beginning, and there are many other ways to optimize your infrastructure depending on your specific needs and use cases. However, by implementing the basic techniques outlined in this blog, you can start to see significant improvements in your AWS bill and infrastructure performance.
Remember to always monitor your infrastructure and continue to make improvements over time. By adopting a continuous improvement mindset and staying up to date with the latest best practices and tools, you can build a high-performance, cost-effective infrastructure that meets the needs of your business and customers.
Shameless Plug 🤪:
Hey everyone!
I’m excited to announce the launch of my first book, “Building Serverless Applications on AWS: A Step-by-Step Guide !”
You can get my book in multiple formats, including Kindle, paperback (both in India and internationally), and Gumroad.
So, whether you prefer a physical book or a digital copy, you can choose the format that works best for you.
If you wish to buy it, please use the link below:
About the book : https://bit.ly/about-my-book
Kindle : https://bit.ly/buy-kindle-version
Paperback India : https://bit.ly/buy-paperback-version-india
Gumroad : https://bit.ly/buy-on-gumroad
Paperback International : https://bit.ly/buy-paperback-version-international
I’m excited to hear what you think!
