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'

Answer 1

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