Cloud SQL Tuning - SQL Server 2017 :: [ Response times are really high,CPU Usage is double for GKE + Cloud SQL (SQL SERVER 2017) ]

[kenny]

Hello,

Performance test with GCP GKE + Cloud SQL BETA (SQL Server 2017)

The response times in 400 user test for GKE + Cloud SQL are really high,CPU Usage is double.

• We are seeing high Wait time for the Transaction log. ( we do write too much on disk )
• We can not implement couple of option since we have limited control on instance

CloudSQL Configuration:

Cloud SQL configuration:

Database flags	optimize for ad hoc workloads  = on agent xps = on cost threshold for parallelism = 15
CPU	24
Memory	104 GB
N/W Throughput (MB/s)	2000 of 2000
Disk throughput (MB/s)	Read: 491.5 of 1,200.0 Write: 400.0 of 400.0
IOPS	Read: 25,000 of 25,000 Write: 25,000 of 25,000
JDBC Driver	7 (Compatible with SQL 2017)
CursorMethod	Select (Default)
Cardinality	130 ( Default with SQL 2017

 

To improve the response time and disk throughput, what are the best options to tune the Cloud SQL instance ??

and what are the upcoming feature enhancements in the general release ?

Please suggest the best configuration with database flags for optimal performance for CLOUD SQL (SQL SERVER 2017).

[Katayoon (Cloud Platform Support)]

Hi Kenny,

Cloud SQL cannot achieve the same performance as a well-tuned traditional configuration, which may have local disks, dedicated DB hardware/vms, or zero latency to application servers. I should also note that, Cloud SQL does not support SUPER privileges for security reasons. However, Cloud SQL provides you with a fully Google managed, integrated and reliable database. If you require more access and control, you may either run and configure your database on a Cloud Compute Engine VM or select other storage options as per your usecase. 

In order to decrease the latency and improve the disk throughput, You may check the location of the writer and database. Sending data a long distance will introduce latency, and larger disks and upgrading your machine type have more IOPS available. I also recommend to take a look at the best practices for getting the best performance from Cloud SQL.  I would like to add that, for getting the optimal performance, it is important to run tests and measure.

We don't have any roadmap on public, but you may consistently check the release note for production updates.