Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Check for Idle Cloud SQL Database Instances

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: High (not acceptable risk)

Identify any idle Compute Engine Cloud SQL database instances found by Google Cloud Recommender and stop the idle compute resources in order to help lower the cost of your Google Cloud bill. Recommender is a Google Cloud service that provides usage recommendations and insights for GCP resources. The Recommender service generates recommendations about idle Cloud SQL database instances based on certain usage metrics.

Cost
optimisation

Google Cloud Recommender service provides recommendations to help you identify Cloud SQL database instances that have not been used (i.e. idle). These recommendations are generated automatically based on system metrics collected by the Cloud Monitoring service over the previous 30 days. You can use Recommender to find and stop idle Cloud SQL database instances in order to reduce waste of resources and avoid accumulating unnecessary compute charges.

Audit

To determine if there are any idle Cloud SQL database instances available within your GCP projects, perform the following actions:

Note: Getting recommendations about idle Cloud SQL database instances using Google Cloud Management Console is not currently supported.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) with custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your cloud account: 

gcloud projects list
  --format="table(projectId)"

02 The command output should return the requested Google Cloud project IDs: 

PROJECT_ID
cc-web-stack-project-123123
cc-app-stack-project-112233

03 Run recommender recommendations list command (Windows/macOS/Linux) with the ID of the GCP project that you want to examine as the identifier parameter, to list the optimization recommendations provided by Google Cloud Recommender service for the idle SQL database instances identified in the selected location: 

gcloud recommender recommendations list
  --project=cc-web-stack-project-123123
  --location=us-central1-a
  --recommender=google.cloudsql.instance.IdleRecommender 
  --format="yaml"

04 The command output should return the requested optimization recommendations (if there are any): 

operationGroups:
	- operations:
		- action: test
		path: /status
		resource: //sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-stack-project-123123/instances/cc-mobile-db-instance
		resourceType: sqladmin.googleapis.com/Instance
		value: RUNNING
		- action: replace
		path: /status
		resource: //sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-stack-project-123123/instances/cc-mobile-db-instance
		resourceType: sqladmin.googleapis.com/Instance
		value: NEVER
	description:
	etag: '"abcdabcdabcdbacd"'
	lastRefreshTime: '2024-03-04T07:00:00Z'
	name: projects/123456789012/locations/us-central1-a/recommenders/google.cloudsql.instance.IdleRecommender/recommendations/abcd1234-abcd-1234-abcd-1234abcd1234
	primaryImpact:
	category: COST
	costProjection:
		cost:
		currencyCode: USD
		units: '-1'
		duration: 2592000s
	recommenderSubtype: LOW_ACTIVITY

If there are no idle Cloud SQL database instances in the selected location, the command response should be empty. Otherwise, the command response includes the following fields for each optimization recommendation:
  1. operationGroups – describe a set of sequential actions that must be taken to apply the recommendation. Idle SQL instance recommendation operation groups include two operations:
    • A test operation to verify the current status of the database instance. For example: 
      - action: test
	path: /status
	resource: //sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-stack-project-123123/instances/cc-mobile-db-instance
	resourceType: sqladmin.googleapis.com/Instance
	value: RUNNING
    • A replace operation to change the status of the database instance. For example: 
      - action: replace
	path: /status
	resource: //sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-stack-project-123123/instances/cc-mobile-db-instance
	resourceType: sqladmin.googleapis.com/Instance
	value: NEVER
  2. description – a human-readable explanation of the optimization recommendation.
    If the recommender recommendations list command output is listing optimization recommendations, as shown in the example above, there are one or more idle Cloud SQL database instances available within the selected location. You can apply the recommendation in two steps:
    • Check if the database instance is RUNNABLE (i.e. running). You can do this by checking the instance state.
    • Replace the resource's status with a new value: NEVER. You can do this by stopping the idle instance.

05 Repeat steps no. 3 and 4 for each cloud location that you want to list recommendations.

06 Repeat steps no. 3 – 5 for each GCP project deployed in your Google Cloud account.

Remediation / Resolution

To apply the optimization recommendations provided by Google Cloud Recommender service for idle Cloud SQL database instances, perform the following actions:

Step 1 Check the SQL database instances state to determine if the resource is still running:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL console available at https://console.cloud.google.com/sql/instances.

04 Choose the SQL database instance that you want to examine and check the status icon available next to the resource name, in the Instance ID column. If the status icon is green (i.e. Runnable), the selected SQL instance is running.

05 Repeat step no. 4 to determine the state of each SQL instances that you want to examine.

Using GCP CLI

01 Run sql instances describe command (Windows/macOS/Linux) using the name of the SQL database instances that you want to examine as the identifier parameter, to describe the state of the selected instance: 

gcloud sql instances describe cc-mobile-db-instance --format="value(state)"

02 The command output should return the requested resource status: 

RUNNABLE

If the sql instances describe command output returns RUNNABLE, as shown in the example above, the selected SQL database instance is running.

03 Repeat steps no. 1 and 2 to determine the state of each SQL instances that you want to examine.

Remediation / Resolution

Replace the resource's status with a new value: NEVER. You can do this by stopping the idle SQL database instance:

Step 2 Check the SQL database instances state to determine if the resource is still running:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL console available at https://console.cloud.google.com/sql/instances.

04 Click on the name (link) of the database instance that you want to stop.

05 In the Stop database instance? confirmation box, choose STOP to stop the selected SQL instance. This will shut down the SQL instance and set its activation policy to NEVER: it will remain stopped until you start it again manually.

06 Repeat steps no. 4 and 5 to stop other idle Cloud SQL database instances provisioned in the selected Google Cloud project.

Using GCP CLI

01 Run sql instances patch command (Windows/macOS/Linux) using the name of the Cloud SQL database instance that you want to stop as the identifier parameter, to stop the selected instance by setting its activation policy to NEVER: 

gcloud sql instances patch cc-mobile-db-instance --activation-policy=NEVER

02 The command output should return the sql instances patch command request status: 

The following message will be used for the patch API method.
{"name": "cc-mobile-db-instance", "project": "cc-web-stack-project-123123", "settings": {"activationPolicy": "NEVER"}}
Patching Cloud SQL instance...done.   

Updated [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-stack-project-123123/instances/cc-mobile-db-instance].

03 Repeat steps no. 1 and 2 to stop other idle Cloud SQL database instances deployed within the selected Google Cloud project.

References

Publication date Apr 12, 2021

Related CloudSQL rules