1

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 :)

Ali Fumagalli
  • 239
  • 1
  • 2
  • 10

1 Answers1

0

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

Kevin Potgieter
  • 672
  • 10
  • 23
  • In the meantime, thank you for your reply. If I have not misunderstood I should set up a nodejs server and write REST APIs that interact with the database, using the cloud functions to invoke these endpoints as in a server to server call. it's correct? But if it were set up this way, what is the point of using cloud functions? at that point I could directly call the REST API ... this is not clear to me ... my initial idea was to create a serverless application that interacted with a database relationship (MySQL) ... You can direct me to the right solution ? – Ali Fumagalli Jul 30 '21 at 12:54
  • Yes, you understand correct. The point of using cloud functions is to quickly scale on demand (demand of http requests in this case). Your cloud function should not increase the load on your backend resources (database connections in this case) but simply increase the availability to your service. The service that handles your database communication should use its own logic (connection pooling and query optimizing in this case) to reduce the load the public api (firebase functions) puts on your database and backend processes. This way you can easily add additional functions in the future. – Kevin Potgieter Jul 31 '21 at 11:36
  • So I could use cloud functions like "pivot" to route client requests to the database via a server to server REST call. This way I would make my application highly available for potentially endless requests from numerous clients. The point that remains open to me is that I should manage the scalability of the server it maintains on my database. My goal was to create an application that auto scaled by itself. Cloud functions (serverless) seemed to me the perfect solution, but maybe I misunderstood how to use them ... – Ali Fumagalli Aug 03 '21 at 08:14
  • What is the sense of using serverless to not manage the scalability of a server if I then have to manage the scalability of the various servers that hold up my services (eg calls to the database)? – Ali Fumagalli Aug 03 '21 at 08:14
  • Databases like Firebase dont have this issue, thats why they are so expensive to use. They handle a lot of connections because they have a server api between you and their database. if you are running your own setup to a database, you need the same, so you need to add it. its very easy. If you still dont understand, ask yourself this. If you get 1000 users on your website, and each one connects to your database, how many connections is that open to your database? MySQL has a limit of 150 https://stackoverflow.com/questions/22297773/how-to-increase-mysql-connectionsmax-connections – Kevin Potgieter Aug 03 '21 at 10:23
  • I imagine one per user ... so 1000 users = 1000 connections. So the cloud function called 1000 times by the client will always be available, whatever the number of requests. While the server holding up the REST communication with my MySQL database will have to handle 1000 concurrent connections? i should manage the scalability of the service ... is that correct? – Ali Fumagalli Aug 03 '21 at 17:18
  • On the API that talks to your database, you have `npm mysql` installed and use the `pool` option with `max_connections` to manage connections, each request makes a new connection in the pool. If the pool is full, it waits for one to become available. So if functions makes 1000 requests, your functions API will not kill your database and your database API will only allow a small amount of connections. If your queries are fast, you wont notice it. – Kevin Potgieter Aug 03 '21 at 21:41