The GCP Database Migration Service (DMS) is designed to facilitate seamless data migration to Google Cloud databases, catering to new workloads. It enables continuous migrations from MySQL, PostgreSQL, and SQL Server to Cloud SQL, as well as Oracle workloads to Cloud SQL for PostgreSQL and AlloyDB, thereby modernizing legacy systems. This article delves into strategies to enhance the speed of Cloud SQL migrations specifically for PostgreSQL and AlloyDB workloads.
Large-scale Database Migration Challenges
The primary objective of the Database Migration Service is to ensure a smooth transition of databases with minimal downtime. In scenarios involving substantial production workloads, the speed of migration becomes critical. Prolonged migration durations can lead to several issues for PostgreSQL databases, including:
- Extended periods for the destination to synchronize with the source after replication.
- Long-running copy operations that interfere with vacuum processes, potentially causing source transaction wraparound.
- Increased size of Write-Ahead Logs (WAL), resulting in elevated disk usage on the source.
Boosting Migration Speeds
To expedite migrations, Google recommends fine-tuning specific settings to mitigate the aforementioned challenges. The following adjustments can be made for Cloud SQL and AlloyDB destinations:
- DMS can parallelize the initial load and change data capture (CDC).
- Configure parameters for both source and target PostgreSQL databases.
- Enhance machine and network settings.
Parallel Initial Load and CDC with DMS
The latest functionality of DMS employs multiple subscriptions in PostgreSQL to facilitate parallel data migration by establishing pglogical subscriptions between source and destination databases. This capability allows for simultaneous data migration during both the initial load and CDC phases.
By default, the DMS user interface and Cloud SQL APIs are set to OPTIMAL, which balances performance with the load on the source database. However, selecting the MAXIMUM option can significantly increase migration speeds. Under this setting:
- DMS determines the optimal number of subscriptions based on database and instance size.
- Tables are allocated to distinct replication sets according to their size, ensuring balanced replication.
- Individual subscription connections facilitate simultaneous data copying, enhancing CDC efficiency.
Google’s findings indicate that the MAXIMUM mode can accelerate migration speeds substantially compared to MINIMAL or OPTIMAL modes. However, it is essential to monitor the source’s resource usage, as this setting may impact application performance if the source is already under significant load.
Configuring PostgreSQL Parameters
Both CDC and initial load processes can be optimized through specific database configurations. Here are some recommended settings:
- max_wal_size: Set between 20GB and 50GB to limit WAL growth during automatic checkpoints, thereby reducing checkpoint frequency and enhancing migration resource allocation.
- pglogical.synchronous_commit: Disable this feature to allow asynchronous commits, which can speed up CDC DML modifications but may reduce durability in case of a crash.
- wal_buffers: Adjust to 32–64 MB for 4 vCPU machines and 64–128 MB for 8–16 vCPU machines to optimize unwritten WAL data handling.
- maintenance_work_mem: Suggested value of 1GB or the size of the largest index, if feasible, to enhance memory allocation for maintenance operations.
- max_parallel_maintenance_workers: Set proportional to the CPU count to optimize parallel index creation during migration.
- max_parallel_workers: Ensure this is equal to or greater than max_worker_processes to maximize parallel worker limits.
- autovacuum: Disable this feature during the CDC phase if there is significant data to catch up on, and re-enable it post-migration.
Source Instance Configurations
For fine-tuning the source instance, consider the following configurations:
- Shared_buffers: Set to 60% of the RAM to enhance initial load performance and buffer SELECT queries.
- Machine and Network Settings: Larger configurations for both source and destination instances (RAM, CPU, Disk IO) can lead to faster migrations.
In summary, optimizing DMS migrations involves a combination of fine-tuning source and destination configurations, leveraging optimal machine and network settings, and closely monitoring workflow steps. By adhering to best practices and addressing potential issues, organizations can achieve significantly faster migration outcomes.
