Save money by scheduling Cloud SQL

With a difficult year 2020, companies are more focused on what they spend, and, because the cloud becomes bigger and bigger every year in companies, the cloud billing is getting a lot of interest.
For all the companies, the ideal model is to pay only what they use; and not more. The serverless products perfectly fit this expectation.

However, some services can’t adopt this model, especially for technical reason. Relational databases for example are liked for their low latency achieved thank to, at least, 2 factors

• Instances are always running to avoid cold start
• Indexes are kept in memory, and rebuild regularly to ensure optimal performances

These technical constrains prevent the pay-as-you-use pattern. Of course, the managed solutions like this already save money and prevent constraints, such as to have skilled teams to deploy, optimize, perform backups, set up replicas,… And this in 24/7!

For the companies, the problem is not really to pay, it’s paying for nothing. And with multiple environments per project (at least Dev, Staging and Production), having a production-ready databases in all environment is useless. Only the production needs this, the databases in the other environments can be shut down the night and the weekend to save money.

With Cloud SQL and Cloud Scheduler, it’s quite easy to deploy. Let’s get started!

The Cloud SQL API

The Cloud SQL service has admin APIs to interact with. In our case we want to update the existing instance and start or shut it down. For this

• We will use the instance PATCH endpoint of the API
• We will update the settings activationPolicy and set it to NEVER to stop the database and to ALWAYS to start it.

A very short JSON body describes the settings

{
  "settings": {
    "activationPolicy": "NEVER"
  }
}

To call the PATCH API url, we need the projectID and the instanceName. And then we can put all together and have a try with CURL

curl -X PATCH -d '{"settings":{"activationPolicy": "NEVER"}}' \
  -H "content-type: application/json" \
  -H "Authorization: Bearer $(gcloud auth print-access-token)" \
https://sqladmin.googleapis.com/sql/v1beta4/projects/<PROJECT_ID>/instances/<INSTANCE_NAME>

Cloud Scheduler configuration

Cloud Scheduler is a serverless product that allows to schedule API calls. The scheduling pattern is based on the Linux CRON expression.

To start and stop automatically the Cloud SQL database, we need 2 schedules:

• Shutdown the database everyday at 8pm. We can consider that after 8pm all the developers has finished to work. And if one chooses to work on Saturday, he will start manually the Cloud SQL instance. The scheduler is here to prevent an unstopped instance during the weekend.
  Here the CRON expression will be 0 20 * * *
• Start it only from Monday to Friday at 8am. We could want to have an started database when the developers start to work, for a better experience and an higher efficiency.
  Here the CRON expression will be 0 8 * * 1-5
  For the 1–5, it’s the day of the week. The 0 is the Sunday, the - is to set an interval.

Cloud Scheduler authorization

As we can see in the CURL command, the API call needs to be authenticated. The same thing on Cloud Scheduler.
To achieve this, we must use a service account - an existing one or we can create a new one - with at least the role roles/cloudsql.editor granted. This role has the permission to update the Cloud SQL instance.

During the set up of Cloud Scheduler authentication, we have to choose between 2 types of token

• OAuth: designed to provide the permission scopes, perfect to call Google Cloud API
• OIDC: designed to provide the identity of the requester, required for secure Cloud Run, Cloud Functions and App Engine.

In our case, we will use OAuth token.

Set up the final configuration

Finally, we can create the Cloud Scheduler jobs with all the pieces

• The API URL, with path parameter and the PATCH HTTP verb
• The body content
• The scheduler CRON expression
• The security definition

Let’s do this with the console(to start the instance)

Or do this with command line (to stop the instance)

gcloud beta scheduler jobs create http stop-instance \
  --oauth-service-account-email=<SERVICE_ACCOUNT_EMAIL> \
  --message-body='{"settings":{"activationPolicy": "NEVER"}}' \
  --schedule="0 20 * * *" --time-zone=Europe/Paris \
  --http-method=PATCH \
  --uri=https://sqladmin.googleapis.com/sql/v1beta4/projects/<PROJECT_ID>/instances/<INSTANCE_NAME>

To test, we can manually start the Cloud Scheduler job by clicking on Run now button.

It’s also a good solution to allow any developers to start and stop the instance when then want, and when they think about it!

Smart managed service to save money, and more!

Now, we have it. We are able to start and to stop the instance to minimize their useless running time. Like this, we are able to save money, but not only.
If we save resources, we prevent the waste of energy and we share the unused CPU/memory with the other Google Cloud customers. Therefore, there is less physical “carbon” resources consumed and it’s better for the earth !

By thinking smartly the usage of the managed services, we achieve better things for all of us!