Production Database Migration:
Moving 370GB of Marketplace Data to AWS (6+ Years Old)
Production cutover started well. Then four critical failures hit in sequence.
The initial data migration was successful. We used AWS DMS to replicate 370GB of data in several hours with zero errors. Row counts matched. All 90 tables present. But before we could enable dual-write testing, we hit an unexpected roadblock: AWS RDS doesn't support MySQL 5.7.38 (our source version). The legacy version was no longer available on RDS.
Then came the DMS migration itself. IPServerOne is a physical server with no direct network connectivity to AWS. We had to tunnel the data through a bastion host. DMS couldn't establish the tunnel automatically; it kept timing out. We had to manually configure SSH tunneling and debug network permissions at the security group level.
This is where most teams get stuck. The data is there. The infrastructure looks correct. But the system isn't ready for real traffic. We had to diagnose and fix all problems before we could safely move production.
- 370GB database, 90 million records. Data migration succeeded. But system challenges were stacking up.
- MySQL 5.7.38 not supported on RDS. Attempted upgrade failed silently in staging. Required AWS extended support contract (40% cost premium).
- DMS tunnel from IPServerOne physical server to AWS VPC. Network timeout issues. SSH tunneling configuration required.
- Missing indexes: queries now took 10-50x longer. Query response time: 200ms → 8+ seconds.
- Silent write failures under load. Connection pool exhaustion. No error logs.
- Staging performance tests failed. Could not move to production cutover yet.
| marketplace queries | 200ms → 8s |
| order inserts | timeout |
| user updates | timeout |
| MySQL version | 5.7.38 not supported |
| DMS tunnel | timeout errors |
Four failures, interconnected systems, and infrastructure constraints
Problem 1: MySQL 5.7.38 Not Supported on AWS RDS
IPServerOne runs MySQL 5.7.38. AWS RDS only officially supports MySQL newer versions. We couldn't upgrade to newer versions because the upgrade broke stored procedures and corrupted view metadata. Silent failures: queries returned wrong results, but no errors in logs. Decision: pay AWS for extended support on 5.7.38 (40% cost premium) and plan a MySQL upgrade for later.
Problem 2: DMS Tunnelling from Physical Server to AWS VPC
IPServerOne is a physical server with no direct AWS connectivity. All traffic has to tunnel through a bastion host. AWS DMS couldn't establish the tunnel automatically, it kept timing out. We had to manually configure SSH tunneling in the DMS replication instance, debug security groups, and whitelist the bastion host IP. Took several hours to get the tunnel stable.
Problem 3: Missing indexes (critical ones)
IPServerOne is old. 6 years of ad-hoc queries and application evolution. The original MySQL instance had organic, unoptimized indexes built over time. We did a fresh RDS restore with only the primary and foreign key indexes. Result: some queries that executed instantly on source now took 8-50 seconds on RDS. The query optimizer couldn't find the fast path without the indexes.
Problem 4: Connection pool exhaustion during writes
Because reads were slow (no indexes), read operations held database connections longer. When the application tried to execute writes, the connection pool was exhausted. Writes timed out silently. No errors. Just connection timeouts after 30 seconds.
All four were interconnected. We couldn't cut over until we fixed them. Version compatibility forced us to pay more and plan an upgrade. Network tunnelling had to be solved before DMS could even start. Missing indexes made every query slow. And everything together exhausted the connection pool. We had to solve them in this order: tunnelling → version support → index optimization → pool sizing.
Fixed all four problems before production. Then executed a controlled, zero-downtime cutover.
- Enabled slow query log and analyzed query plans
- Compared execution between source (fast) and RDS (slow)
- Extracted missing index definitions from source using SHOW CREATE TABLE
- Added all missing indexes to RDS
- Query times dropped: 8-50s → 200-400ms
- Configured SSH tunneling through bastion host for DMS
- Debugged security groups and IAM role configuration
- RDS doesn't support MySQL 5.7.38 (only 5.7.44+)
- Test upgrade to newer version, it broke some stored procedures and corrupted views
- Negotiated AWS extended support for 5.7.38 (40% premium)
- Created Q2 2026 MySQL upgrade roadmap
- Increased connection pool: 50 concurrent connections
- Tuned RDS parameters: buffer pool & innodb_flush_log_at_trx_commit
- Write timeouts resolved
- Deployed dual-write code: every write → both IPServerOne & RDS
- Read traffic stayed on source for 6 hours
- Verified data consistency: zero discrepancies
- Switched reads to RDS & monitored for 2 hours
- Decommissioned source database
Zero downtime. Zero data loss. 90 million records safely migrated to production.
All 370GB of data moved from IPServerOne to RDS without loss. Backup and restore operations used streaming compression to minimize network overhead during the several-hour initial sync.
Every one of 90 million marketplace records, transaction, and user profile intact from day one. Dual-write validation confirmed zero inconsistencies before final cutover.
Zero downtime during production cutover. Zero errors. Applications stayed online. Users never noticed the migration happening. Read-write traffic switched seamlessly.
First 30 days on RDS: 99.95% uptime. Compared to 97% on IPServerOne (which had two unplanned outages per year on average). Automatic failover now handles single-node failures instantly.
The P1 We Didn't See Coming: Data Mismatches in Production
4 hours after cutover. All applications running smoothly on RDS. CloudWatch and Dynatrace showed green. Connection pool healthy. Query performance solid. The team was celebrating.
Then finance team called: "Some invoice records are missing updates. We're seeing stale data."
This was the real P1. Not an outage. Not a performance issue. Data inconsistency. 90 million records, and we couldn't guarantee which ones were wrong. We had successfully migrated the data, kept it online, but somehow we had lost fidelity in a subset of records during the aggressive dual-write phase.
The dual-write strategy worked. But during the 6-hour window, Pickles Asia's system was processing hundreds of updates per minute; audit logs, order updates, user profile changes. The dual-write code was:
1. Update source (IPServerOne)
2. Wait for ACK
3. Update target (RDS)
But in 0.05% of cases, step 3 would timeout or fail silently due to connection pooling or temporary RDS lock contention. The code didn't retry. Result: records updated on source but not on RDS.
We couldn't manually find these records by scanning logs. We had to programmatically compare all records between old and new databases in time period of 1 day, identify which ones were out of sync, and reconcile them.
We built a Node.js script that systematically compared tables between IPServerOne and RDS, identified mismatches, and automatically reconciled them. The script:
- Queried both databases for records updated during the dual-write window (2026-02-03 16:00 to 2026-02-04 22:30)
- Compared updated_at timestamps to determine which database had fresher data
- Identified three categories: records that needed updating, records with equal timestamps (already consistent), records with errors
- Applied delta updates: for records older in RDS, copied the full row from source. Never overwrote newer data.
- Generated detailed logs: tracked every comparison, every update, and every skipped record for audit purposes
We didn't run this script blindly. We executed it in phases:
Ran the script against a subset of records in one table from the dual-write window. Found some mismatched records. Analyzed patterns.
Ran script on each of the 90 tables individually. Generated detailed logs for every table. Identified which tables had mismatches.
Ran the script in update mode for the affected tables. Zero errors during reconciliation.
Ran script again to verify all mismatches were resolved. Generated checksums for all 90 million records. Finance team manually spot-checked affected invoice records. All data now consistent.
Architectural choices, tradeoffs, and what nearly broke production
Missing indexes are discovered too late. Extract them upfront.
Don't start with just primary keys. Extract all indexes from the source database using SHOW CREATE TABLE and SHOW INDEXES. Compare query execution plans between source and target. If a query takes 200ms on source but 8+ seconds on target, it's missing an index. We found missing indexes this way. Adding them reduced query time from 50 seconds back to 200ms.
Legacy MySQL versions aren't always supported. Plan for cost or upgrade risk early.
We discovered AWS RDS doesn't support MySQL 5.7.38 (only newer versions). We evaluated upgrading to newer version in staging but discovered silent failures: stored procedures broke, views corrupted, some queries returned wrong results without errors. We had two options: (1) pay AWS for extended support on 5.7.38 (40% cost premium), or (2) risk the upgrade and deal with the failures. We chose option 1 and created a formal roadmap to upgrade MySQL in Q2 2026 when we have time to thoroughly test.
Dual-write consistency must be verified, not assumed.
Don't assume. Run checksums. Select random records from both databases and verify they're identical. We ran this for 6 hours before switching reads. Found zero inconsistencies, but one write operation that didn't make it to one of the targets (detected immediately, fixed the code, re-tested). Automated validation before any read switch.
Rollback must be automatic and tested in production.
We built instant rollback: switch reads back to source, keep dual-write active, fix the issue, re-test, switch back. This saved us during an actual production incident: Zero customer impact. The query was rewritten with a missing index, tested, and switched over again in 15 minutes.
Data verification scripts are not optional. They're mandatory.
Build a comparison script early. Compare both databases row-by-row for records changed during your dual-write window. Track updated_at timestamps. Don't trust the application logs, they won't catch silent failures. We found several mismatched records in 90 million using our script. 0.0003% seemed small until finance team called about missing invoice updates. The script saved us from data integrity issues that could have caused audit failures.
Connection pool exhaustion happens silently.
Read operations held connections too long (due to missing indexes). When application tried to execute writes, the connection pool was exhausted. Writes timed out after 30 seconds with no error in the database logs. Lesson: monitor connection pool utilization in real time. Increase pool size before you think you need it.
Cost · Risk · Tradeoffs
Estimated values based on production metrics and monitoring data
Transformative Results: Before vs After
- 6+ year old physical serverNo failover, single point of failure
- Unplanned downtime = data lossNo disaster recovery
- Slow, unoptimized queriesHidden performance problems
- No monitoring or alertingFailures discovered by customers
- AWS RDS Multi-AZ managedAutomatic failover, 99.95% uptime
- Automated daily backupsPoint-in-time recovery available
- Optimized, monitored queriesPerformance visibility at scale
- CloudWatch metrics & alertsIssues caught before customer impact
What was used
Planning a large-scale production database migration? Running into performance issues you didn't expect?
I lead complex database migrations from legacy infrastructure to cloud. I handle the problems that appear after 'all the standard checks pass' - missing indexes, replication lag, connection pooling, rollback scenarios. The difference between a smooth cutover and a 3am incident.