Save money by scheduling Cloud SQL

guillaume blaquiere
Jan 15 · 4 min read
Image for post
Image for post

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

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.

Image for post
Image for post

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.

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)

Image for post
Image for post

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.

Image for post
Image for post

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!

Google Cloud - Community

Google Cloud community articles and blogs

guillaume blaquiere

Written by

GDE Google Cloud Platform, scrum master, speaker, writer and polyglot developer, Google Cloud platform 3x certified, serverless addict and Go fan.

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

guillaume blaquiere

Written by

GDE Google Cloud Platform, scrum master, speaker, writer and polyglot developer, Google Cloud platform 3x certified, serverless addict and Go fan.

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store