Optimizing RDS and SQL queries in Data Analytics System
Optimizing RDS and SQL queries in Data Analytics System We had a Data Analytics system that was firing SQL queries on AWS RDS instance. The queries took time where the longest recorded time was 1.5 hours. We looked both at the SQL queries and AWS Infrastructure to analyze the issue. A. AWS INFRASTRUCTURE 1. What is the issue ? : The RDS instance was under-provisioned. The current workload was more than the capacity of the instance. 2. Why did this issue occur ? : We monitored below RDS performance metrics using CloudWatch : - ReadLatency (The average amount of time taken per disk I/O operation.) - WriteLatency (The average amount of time taken per disk I/O operation.) - DiskQueueDepth (The number of outstanding IOs (read/write requests) waiting to access the disk.) - ReadIOPS (The average number of disk I/O operations per second) - WriteIOPS (The average number of disk I/O operations per second) - SwapUsage (The amount of swap space used on the DB ins...