How to run Google Cloud SQL only when I need it?

Question

Google Cloud SQL advertises that it's only $0.0150 per hour for the smallest machine type, and I'm being charged for every hour, not just hours that I'm connected. Is this because I'm using a pool? How do I setup my backend so that it queries the cloud db only when needed so I don't get charged for every hour of the day?

const mysql      = require('mysql');
const pool = mysql.createPool({
    host : process.env.SQL_IP,
    user     : 'root',
    password : process.env.SQL_PASS,
    database : 'mydb',
    ssl      : {
          [redacted]
    }
});

function query(queryStatement, cB){
  pool.getConnection(function(err, connection) {
    // Use the connection
    connection.query(queryStatement, function (error, results, fields) {
      // And done with the connection.
      connection.destroy();
      // Callback
      cB(error,results,fields);

    });
  });
}

Sphinx

Answer 1

This is not so much about the pool as it is about the nature of Cloud SQL. Unlike App Engine, Cloud SQL instances are always up. I learned this the hard way one Saturday morning when I'd been away from the project for a week. :)

There's no way to spin them down when they're not being used, unless you explicitly go stop the service.

There's no way to schedule a service stop, at least within the GCP SDK. You could alway write a cron job, or something like that, that runs a little gcloud sql instances patch [INSTANCE_NAME] --activation-policy NEVER command at, for example, 6pm local time, M-F. I was too lazy to do that, so I just set a calendar reminder for myself to shut down my instance at the end of my workday.

Here's the MySQL Instance start/stop/restart page for the current SDK's docs: https://cloud.google.com/sql/docs/mysql/start-stop-restart-instance

On an additional note, there is an ongoing 'Feature Request' in the GCP Platform to start/stop the Cloud SQL (2nd Gen), according to the traffic as well. You can also visit the link and provide your valuable suggestions/comments there as well.

Lapides

sorry where would you write such a cron job which has access to gcloud? – Keble

@AlexanderShubert you can run cron on linux VM on GCP. VMs can be easily scheduled to start and stop using cloud-functions, pubsub, and scheduler in GCP – Piscina

Answer 2

I took the idea from @ingernet and created a cloud function which starts/stops the CloudSQL instance when needed. It can be triggered via a scheduled job so you can define when the instance goes up or down.

The details are here in this Github Gist (inspiration taken from here). Disclaimer: I'm not a python developer so there might be issues in the code, but at the end it works.

---

Basically you need to follow these steps:

1. Create a pub/sub topic which will be used to trigger the cloud function.
2. Create the cloud function and copy in the code below.
   1. Make sure to set the correct project ID in line 8.
   2. Set the trigger to Pub/Sub and choose the topic created in step 1.
3. Create a cloud scheduler job to trigger the cloud function on a regular basis.
   1. Choose the frequency when you want the cloud function to be triggered.
   2. Set the target to Pub/Sub and define the topic created in step 1.
   3. The payload should be set to start [CloudSQL instance name] or stop [CloudSQL instance name] to start or stop the specified instance (e.g. start my_cloudsql_instance will start the CloudSQL instance with the name my_cloudsql_instance)

Main.py:

from googleapiclient import discovery
from oauth2client.client import GoogleCredentials
import base64
from pprint import pprint

credentials = GoogleCredentials.get_application_default()
service = discovery.build('sqladmin', 'v1beta4', credentials=credentials, cache_discovery=False)
project = 'INSERT PROJECT_ID HERE'

def start_stop(event, context):
  print(event)
  pubsub_message = base64.b64decode(event['data']).decode('utf-8')
  print(pubsub_message)
  command, instance_name = pubsub_message.split(' ', 1)

  if command == 'start':
    start(instance_name)
  elif command == 'stop':
    stop(instance_name)
  else:
    print("unknown command " + command)

def start(instance_name):
  print("starting " + instance_name)
  patch(instance_name, "ALWAYS")

def stop(instance_name):
  print("stopping " + instance_name)
  patch(instance_name, "NEVER")

def patch(instance, activation_policy):
  request = service.instances().get(project=project, instance=instance)
  response = request.execute()

  dbinstancebody = {
    "settings": {
      "settingsVersion": response["settings"]["settingsVersion"],
      "activationPolicy": activation_policy
    }
  }

  request = service.instances().patch(
    project=project,
    instance=instance,
    body=dbinstancebody)
  response = request.execute()
  pprint(response)

Requirements.txt

google-api-python-client==1.10.0
google-auth-httplib2==0.0.4
google-auth==1.19.2
oauth2client==4.1.3

Sterilize

nice work! i'll be interested to try this out in my sandbox. – Lapides