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

32 views
Skip to first unread message

kenny

unread,
Feb 7, 2020, 6:42:07 AM2/7/20
to Google Cloud SQL discuss
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)

unread,
Feb 7, 2020, 11:40:10 AM2/7/20
to Google Cloud SQL discuss
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.

Reply all
Reply to author
Forward
0 new messages