LinkedIn
Twitter
Hi All
We are experiencing significant performance challenges with our Cloud SQL database backup strategy. Our goal is to create historical versioned backups and enable single database restores for our 3TB Cloud SQL database using SQL Export to compressed `.bak` files stored in a Cloud Storage bucket.
We have successfully implemented the approach outlined in Google's blog post on scheduling Cloud SQL exports: [https://cloud.google.com/blog/topics/developers-practitioners/scheduling-cloud-sql-exports-using-clo...](https://cloud.google.com/blog/topics/developers-practitioners/scheduling-cloud-sql-exports-using-clo...)
The core problem is the slow backup creation and, more critically, the extremely slow transfer (upload) of the backup file to the Cloud Storage bucket. Our tests show that transferring a 120GB compressed backup file takes approximately 2 hours. This translates to an estimated 40+ hours for our production 3TB database, which is far outside our 12-hour Sunday backup window and would block other backup tasks.
During the 120GB test backup file upload, we observed a network output averaging around 17 MB/s, with a peak of 38 MB/s. We understand that the Cloud SQL Server instance and its network connection should theoretically be capable of much higher throughput (potentially around 2 Gigabit/sec or 256 MB/s). This indicates a significant bottleneck.
The Graph shows the time of the test backup. You see the upload started around 02:30
Here are our environment details:
- Cloud SQL Server: 8 vCPUs, 36GB RAM, 4.7TB SSD Storage, located in `europe-west6-b`
- Database Version: SQL Server 2022 Standard CU14
- Total Storage Usage: 3.66 TB
- Cloud Storage Bucket: Located in region `europe-west6` with the default "Standard" storage class.
- Observed Network Performance: Maximum upload speed (network In + Out graph) of 38 MB/s, with an average of 17 MB/s during the 120GB test upload (resulting in a 2-hour transfer time).
Our key questions are:
- What could be causing this significant bottleneck in the backup file upload speed? Are there any specific Cloud SQL or Cloud Storage configurations we might be overlooking?
- Is there a more efficient best practice for handling historical versioning and point-in-time recovery for a database of this size, considering our limited backup window? We require the ability to retain monthly backups for 12 months and yearly backups for 10 years. Instance backups, as we understand them, don't readily facilitate this level of granular retention and single database restores.
We appreciate any insights and recommendations you can provide!
