GCLOUD SQL Performance improvement

Question

I've below configuration on google cloud SQL. I didn't get enough speed on queries. its considerably slower then i run on my local machine.

We are using InnoDB engine. we have user table over 50k~ records. and if we do simple update query to update single row.

UPDATE User SET notificationToken="string token" WHERE id = "String ID";

Here id column is varchar and we have assign index primary key + unique key. and the above query took 1+ sec to update record. same query if i try on my local machine it tooks 0.0200ms. write operation seems too much slow in cloud sql.

Any guidance on below configuration or need to configure DB flags for InnoDB?

Cloud SQL Configuration:

Machine Type:

db-n1-standard-2
vCPUs: 2
Memory: 7.5 GB

Network throughtput (MB/s): 500 of 2,000 Storage Type: SSD Storage Capacity: 10GB

Disk throughtput:

Read: 4.8 MAX:240.0
Write: 4.8 MAX:144.0

IOPS:

Read:300 MAX:15,000
Write:300 MAX:9,000

DB Flags:

slow_query_log='on'
long_query_time = '1'
query_cache_size='223338299392'
query_cache_limit='223338299392'
stored_program_cache=524288
sql_mode='ERROR_FOR_DIVISION_BY_ZERO'
log_bin_trust_function_creators='on'
innodb_write_io_threads=20
innodb_read_io_threads='8'

Have you measured a round trip to the region where your DB was created to see what sort of internet-latency you're experiencing? gcping.com — Gabe Weiss
DB Region=europe-north1-a I've checked gcping.com and below is the result. Hamina, Finland europe-north1 506 ms — Pravin M. Dabhi
Ok, so depending on what your application is doing (are you ONLY measuring the UPDATE call, or is there a subsequent READ you're doing as well in your timing?), a one second lag time would be potentially correct if you're waiting on two round trips. It's not really the story measuring a local call vs. a call to the Cloud to then say Cloud SQL is slow. The internet is a big part of your measuring now. Be sure when you're comparing times that you're comparing only the DB processing time, not the internet round trip. Having said that, check Samuel's advice below, it's also good. — Gabe Weiss
By setting up DB flag solved issue with slow query in my case innodb_change_buffer_max_size = 40 — Pravin M. Dabhi

Samuel Romero Samuel Romero · Accepted Answer · 2020-08-11T16:02:52

Generally the advice on performance tuning is difficult to give because of many things that should be considered. (and some of which is known only from your side). But there are some points you can take into consideration:

drop the indexes

It is a good idea if indexes are not used. But it is a very bad idea if dropping them makes other parallel workloads switch to sequential scans.

Dropping the indexes will impact the queries on the existing data. Also, the recreation of the indexes is something that will also take time in the future.

increasing memory or changing to a larger machine type.

This option may or may not help - it will definitely help if it increases un-throttled network and disk speed.

To diagnose query performance, it is recommended enabling query logging. Cloud SQL logs (like all Google Cloud services) are sent to Stackdriver Logging and can be retrieved via the API. To enable database logging in Cloud SQL, please follow these steps. For example, to enable slow query logging (set slow_query_log flag to 'On') and enable logging to Stackdriver Logging (set log_output flag to 'FILE') on their Cloud SQL instance.

You can also enable the performance schema database flag. The performance_schema flag can be enabled for larger instances (db-n1-standard-8, db-n1-highmem-4, and larger) via the API.

The performance_schema flag, like the other database flags, can be set in the settings.databaseFlags array (ref). Here is an example on how to set it on an existing instance using curl.

curl --header "Authorization: Bearer $(gcloud auth print-access-token)" \
  --header "Content-Type: application/json" \
    --data '{
      "settings": {
        "databaseFlags": [
          {
            "name": "performance_schema",
            "value": "on"
          }
        ]
      }
}' -X PATCH https://www.googleapis.com/sql/v1beta4/projects/[PROJECT_NAME]/instances/myinstance

GCLOUD SQL Performance improvement

1 Answers