Overview

TrackIt recently engaged with a customer facing security, cost, and obsolescence challenges with their Amazon RDS for MySQL database. The customer was ingesting several hundred gigabytes of data per day from IoT devices and incurring rising costs due to Amazon RDS Extended Support. They were running MySQL version 5.7.44, an outdated version of MySQL that is partly supported as part of the RDS Extended Support plan, but at a high cost. In addition, their ingestion model processed each IoT event individually, resulting in inefficient resource utilization and excessive database load.

Keeping a database engine current is critical for maintaining security, stability, and long-term support as workloads scale. While Amazon RDS Extended Support allows continued use of deprecated engine versions beyond standard end-of-support dates, it is limited to a three-year extension and introduces substantial additional costs for high-volume environments.

The migration achieved less than one hour of write downtime during the cutover, while maintaining continuous read access throughout the process. AWS DMS, combined with RDS endpoint renaming, made this possible.

For workloads generating high IOPS, batching ingestion through Amazon Kinesis Data Streams significantly reduces operational overhead while maintaining scalability. Furthermore, since MySQL does not natively support TTL functionality, a robust archiving strategy is essential to prevent data bloat and long-term performance degradation.

Solution Approach

TrackIt implemented a comprehensive modernization strategy focused on performance optimization, cost reduction, and long-term operational stability:

• The primary database was migrated from Amazon RDS MySQL 5.7 to Aurora MySQL 3 (MySQL 8) using AWS Database Migration Service (DMS) with Change Data Capture for near-zero downtime and data consistency.
• The ingestion pipeline was rearchitected using Amazon Kinesis Data Streams to batch high-frequency IoT events, dramatically reducing write amplification and IOPS demand.
• An automated data archiving framework was introduced. The pipeline combines AWS Step Functions, Lambda, AWS Glue, and pt-archiver to enforce retention policies and maintain controlled table growth.

Migrating to Aurora for MySQL

Amazon RDS Extended Support allows continued operation of deprecated MySQL versions for up to three additional years, but at a high price. During the first two years, it is billed at $0.10 per vCPU per hour, increasing to $0.20 per vCPU per hour in the third year.

For example, an r6i.2xlarge instance in us-west-2 costs approximately $0.96 per hour for its 6 vCPUs, equating to around $691 per month for compute alone (excluding storage and related costs). Extended Support adds roughly $432 per month, nearly doubling overall database cost.

Beyond the financial impact, Extended Support does not eliminate the inherent risks of running an outdated engine version. While security patches may still be applied, the database lacks the performance improvements, stability enhancements, and modern features available in fully supported versions.

For workloads ingesting high-frequency IoT sensor data, this risk is amplified. Aurora MySQL 3 provides superior throughput, faster failover, optimized storage mechanisms, and improved handling of concurrent writes. MySQL 8 further introduces enhanced indexing, better replication performance, and stronger transactional consistency—critical for maintaining reliable ingestion pipelines at scale.

Source: https://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.rdsmysql.html

Reducing Ingestion Costs with Kinesis Data Streams

To address excessive IOPS consumption, TrackIt redesigned the ingestion architecture to use Amazon Kinesis Data Streams for batching incoming IoT data.

Instead of inserting each sensor record individually, data is grouped into batches of 100 to 1,000 records before being written by the consuming Lambda function. This architectural change reduced the number of database write operations by approximately 40 to 60%, directly lowering write amplification and overall storage I/O consumption.

As a result, the customer observed a 65 to 75% reduction in data transfer and database I/O-related costs, driven by fewer network round trips, reduced write volume, and lower EBS IOPS usage. At the same time, ingestion throughput improved and remained stable even during peak data bursts, allowing the system to scale efficiently without proportional increases in infrastructure spend.

The outcome was a significantly more cost-efficient and scalable ingestion pipeline capable of handling large-scale, real-time IoT workloads without overwhelming the database or inflating operational expenses.

Automated Data Archiving for Long-Term Stability

Since MySQL lacks native TTL functionality, an effective archiving strategy was implemented to control database growth and maintain performance.

TrackIt designed an automated workflow that:

• Applies subscription-based retention rules via AWS Step Functions and Lambda
• Exports historical data to Parquet format using AWS Glue
• Stores archived data in Amazon S3 Glacier for low-cost long-term retention
• Safely removes outdated records using pt-archiver running on AWS Batch

This approach keeps active tables optimized, prevents data bloat, and preserves historical data without impacting production performance, ensuring both efficiency and compliance.

Source: https://docs.aws.amazon.com/prescriptive-guidance/latest/archiving-mysql-data/archive-unpartitioned-tables.html

Ensuring Minimal Downtime with AWS DMS

DMS can operate in Change Data Capture (CDC) mode to replicate ongoing changes from the production database to the target with minimal load impact. By assigning custom DNS names to RDS endpoints, the cutover can be reduced to a very brief interruption or even made seamless by first renaming the production endpoints and then assigning those same names to the target endpoints during the final switchover.

DMS continues to apply changes during this endpoint switch, which means the target remains nearly up to date until the final moment of cutover. Because the migration is homogeneous, the snapshot provides a consistent baseline and DMS fills the gap by replaying all binlog events that occurred after the snapshot was taken. Ensuring that all migrated tables have a primary key also prevents duplicates and guarantees that CDC events are applied in the correct order.

With this combination of snapshot restoration, continuous CDC replication, and endpoint renaming, downtime is limited to the brief moment required to update the DNS references, and the risk of data loss is effectively eliminated.

Source: https://aws.amazon.com/blogs/database/how-to-rename-and-retain-the-endpoint-name-for-amazon-rds/ 

Conclusion

The migration from RDS MySQL 5.7 to Aurora MySQL 3 delivered clear operational, performance, and security improvements for this high-volume IoT environment.

By eliminating reliance on Amazon RDS Extended Support, the customer reduced ongoing database operating costs while moving to a fully supported MySQL 8 engine with access to current security updates and modern features. This transition strengthened the overall security posture and significantly reduced long-term operational risk.

Optimizing the ingestion pipeline through batch processing with Kinesis Data Streams lowered write pressure on the database and reduced storage I/O consumption. This resulted in more efficient resource usage, lower data transfer and storage costs, and more stable performance during sustained high-ingestion workloads.

The introduction of an automated archiving strategy helped control data growth and maintain lean operational tables. By moving historical data to cost-effective storage and enforcing retention policies, the customer was able to limit storage expansion and maintain consistent query performance over time.

Overall, the transformation established a modern, resilient, and secure data platform capable of supporting high-throughput IoT ingestion with minimal downtime. It now provides predictable scalability, improved performance stability, and a strong foundation for long-term growth and evolving application requirements.