Reusing MYSQL conection Pools in cloudcode

Best Practice
1

We have a cc task which queries a mysql database. We are reaching db connection limit constraints during high demand periods and I suspect the reason is because currently our code opens a connection pool with each request and closes the pool thereafter.

I think it would be better practice to reuse the same connection pool with each cc task request, instead of starting a new one, however I’m unsure where and how to save the pool globally and persistently in order to re-use it??

Assuming ‘global’ were a journey object that persists even after requests finish, I would like open and save the connection pool to the global object and then reuse as required e.g:

const mysql = require(‘mysql2’);

// Check if a pool already exists
if (!global.mysqlPool) {
// Create a connection pool
global.mysqlPool = mysql.createPool({
host: ‘your_database_host’,
user: ‘your_database_user’,
password: ‘your_database_password’,
database: ‘your_database_name’,
waitForConnections: true,
connectionLimit: 10, // Adjust the number based on your requirements
queueLimit: 0,
});

// Add an event listener to handle pool errors
global.mysqlPool.on(‘error’, (err) => {
console.error(‘MySQL Pool Error:’, err);
});
}

function executeQuery() {
global.mysqlPool.getConnection((err, connection) => {
if (err) {
console.error(‘Error getting connection from pool:’, err);
return;
}

// Use the connection for database operations
connection.query('SELECT * FROM your_table', (queryError, results) => {
  // Release the connection back to the pool when done with it
  connection.release();

  if (queryError) {
    console.error('Error executing query:', queryError);
    return;
  }

  // Process the results here
  console.log('Query results:', results);
});

});
}

// Example: Execute the query function
executeQuery();

If there is a better way of interfacing with a DB from cc tasks, please let me know.
Many thanks.

1 Like
2

Hey @martin, I love your idea, but unfortunately you cannot persist a SQL connection between subsequent CC invocations. At least not currently

Firstly, you cannot store a SQL connection pool, the ‘entity’ for lack of a better word, in the JourneyApps DB, and secondly CC invocations are currently completely independent and no data can persist across multiple invocations - except that which can be stored and retrieved from the JourneyApps DB (or some other DB, via an API call for example)

That means that what you are asking for would be some kind of variable that is stored in CC memory that can be used across multiple invocations, but I don’t think that is possible with the CC invocation architecture and technology stack.

So, let’s rather look at trying to optimize the way you are currently connecting to SQL. For example, if you have a bunch of really short lived CC invocations and each of them are creating it’s own connection, then it may be better to look at batching those operations. Or, if you have lots of CC invocations that are running concurrently, then it may be best to either batch them or serialize them.

Again, these recommendations are obviously hard to make, and may not be useful at all, without knowing your exact integration “architecture” and data flow.

I’ll end with a potential stupid question: Can you not increase the DB connection limit on the SQL side to keep up with peak demand?

3

Hi Tielman

Thanks for getting back to me and confirming what I suspected.

I think the best solution will be to use single connections instead of creating pools. I will also increase the connection limit on the AWS RDS instance.

Regards,
Martin

1 Like