CODEWITHSUNDEEP
mysqlnode.jsgoogle-cloud-platformgoogle-cloud-functionsgoogle-cloud-sql
Ali Fumagalli
Ali Fumagalli

Reputation: 289

How to fix Cloud SQL (MySQL) & Cloud functions slow queries

I have an application that, through the Firebase Cloud Functions, connects to a Cloud SQL database (MySQL).

The SQL CLOUD machine I am using is the free and lowest level one. (db-f1-micro, shared core, 1vCPU 0.614 GB)

I report below what is my architecture of use for the execution of a simple query.

I have a file called "database.js" which exports my connection (pool) to the db.

const mysqlPromise = require('promise-mysql');
const cf = require('./config');

const connectionOptions = {
    connectionLimit: cf.config.connection_limit, // 250
    host: cf.config.app_host,
    port: cf.config.app_port,
    user: cf.config.app_user,
    password: cf.config.app_password,
    database: cf.config.app_database,
    socketPath: cf.config.app_socket_path
};

if(!connectionOptions.host && !connectionOptions.port){
    delete connectionOptions.host;
    delete connectionOptions.port;
}

const connection = mysqlPromise.createPool(connectionOptions)
exports.connection = connection

Here instead is how I use the connection to execute the query within a "callable cloud function"

Note that the tables are light (no more than 2K records)

// import connection
const db = require("../Config/database");

// define callable function
exports.getProdottiNegozio = functions
  .region("europe-west1")
  .https.onCall(async (data, context) => {
    const { id } = data;
    try {
      const pool = await db.connection;
      const prodotti = await pool.query(`SELECT * FROM products WHERE shop_id=? ORDER BY name`, [id]);
      return prodotti;
    } catch (error) {
      throw new functions.https.HttpsError("failed-precondition", error);
    }
  });

Everything works correctly, in the sense that the query is executed and returns the expected results, but there is a performance.

Query execution is sometimes very slow. (up to 10 seconds !!!).

I have noticed that some times in the morning they are quite fast (about 1 second), but sometimes they are very slow and make my application very slow.

Checking the logs inside the GCP console I noticed that this message appears.

severity: "INFO"  
textPayload: "2021-07-30T07:44:04.743495Z 119622 [Note] Aborted connection 119622 to db: 'XXX' user: 'YYY' host: 'cloudsqlproxy~XXX.XXX.XXX.XXX' (Got an error reading communication packets)"

At the end of all this I would like some help to understand how to improve the performance of the application.

Is it just a SQL CLOUD machine problem? Would it be enough to increase resources to have decent query execution? Or am I wrong about the architecture of the code and how I organize the functions and the calls to the db?

Thanks in advance to everyone :)

Upvotes: 1

Views: 1010

Answers (1)

Kevin Potgieter
Kevin Potgieter

Reputation: 798

Don't connect directly to your database with an auto scaling solution:

  • You shouldn't use an auto scaling web service (Firebase Functions) to connect to a database directly. Imagine you get 400 requests, that means 400 connections opened to your database if each function tries to connect on startup. Your database will start rejecting (or queuing) new connections. You should ideally host a service that is online permanently and let Firebase Function tell that service what to query with an existing connection.

Firebase functions takes its sweet time to start up:

  • Firebase Functions takes 100~300ms to start (cold start) for each function called. So add that to your wait time. More so if your function relies on a connection to something else before it can respond.

Functions have a short lifespan:

  • You should also know that Firebase Functions don't live very long. They are meant to be single task microservices. Their lifespan is 90 seconds if I recall correctly. Make sure your query doesn't take longer than that

Specific to your issue:

  1. If your database gets slow during the day it might be because the usage increases.

  2. You are using a shared core, which means you share resources on the lowest tier with the the other lower tier databases in that region/zone. You might need to increase resources, like move to a dedicated core, or optimize your query(ies). I'd recommend bumping up your CPU. The cost is really low for small CPU options

Upvotes: 0

Related Questions