I'm currently using Google Cloud SQL for MySQL
but when I do a heavy query it gets read ops above the limit and then the query gets cancelled.
How to increase this read ops limit?
I'm using a SSD with 50 GB of storage.
Thank you.
Best Answer
Based upon your graph, your disk read quota is about 1500 IOPS.
Persistent disk performance is related to provisioned size. Google Cloud SQL SSD Zonal and Regional Persistent Disks receive 30 read and 30 write IOPS per GB.
For a 50 GB SSD PS - 50 * 30 = 1500.
To increase the read and write performance, resize the disk larger. This is easily performed in the Google Cloud Console GUI by editing the instance configuration Storage capacity. Resizing does not cause downtime.
The chart shows a peak of about 3500 IOPS. Resize the 50 GB SSD to 120 GB.
Note: disks also have a quota for MB/sec. SSD has a quota of 0.48 MB/sec per GB. Increasing the disk size will also increase the speed of data transfers.
Use Google Cloud SQL API to change the collation value of your GCS databases. In this case, use patch method as you want to do a partial update of the databases settings. You can do this through APIs Explorer Try it!.
As already mentioned by @justbeez, I believe the best way to go is using second generation instances and Cloud SQL Proxy. In case this is not possible, first generation instances can be setup to only allow SSL connections and whitelisting any origin as explained here.
A more complex approach would be to create an instance template so that a startup-script runs at boot time and authorizes the instance IP address via gcloud. The IP could be removed in the same way at shutdown time.
The IP address of the GCE VM can be obtained from the metadata server
Best Answer
Based upon your graph, your disk read quota is about 1500 IOPS.
Persistent disk performance is related to provisioned size. Google Cloud SQL SSD Zonal and Regional Persistent Disks receive 30 read and 30 write IOPS per GB.
For a 50 GB SSD PS - 50 * 30 = 1500.
To increase the read and write performance, resize the disk larger. This is easily performed in the Google Cloud Console GUI by editing the instance configuration Storage capacity. Resizing does not cause downtime.
The chart shows a peak of about 3500 IOPS. Resize the 50 GB SSD to 120 GB.
Note: disks also have a quota for MB/sec. SSD has a quota of 0.48 MB/sec per GB. Increasing the disk size will also increase the speed of data transfers.
Block Storage Performance