I recently switched over an API from an EC2 instance to API Gateway with lambda. I noticed that my CPU usage on the database then shot up quite rapidly.
Below is an image of the active connections on the database, the red arrow is when I switched over to Lambda from an EC2. There where no new customers added so the actual throughput of data remained the same.
I am using the same code that I am using as when I was hosting on the Ec2 instance. This code uses a connection pool to connect to the database which I am not sure is the best idea. Basically I call the query
method when I want to query the database in my main script.
A connection is retrieved from a connection pool for the lambda function to run the query and then released at the end of the query.
Is this a good approach or should one connection be constantly persisted throughout the execution of the lambda function? The CPU usage on a T3 medium is nearly maxed out since I moved to Lambda and I think this is the problem.
// This file is used to get a connection from the mysql connection pool
// The pool is contained in this file and the function to get a connection is exported
// Imports //
const mysql = require('mysql')
// Define the connection pool //
const pool = mysql.createPool({
host: process.env.DBHOST,
user: process.env.DBUSER,
password: process.env.DBPASSWORD,
port: process.env.DBPORT,
database: process.env.DBNAME
})
/// getConnection ///
// Call this function whereever you need a connection
// It gets a connection from the connection pool
function getConnection(){
return new Promise((resolve,reject) => {
pool.getConnection((err,con) => {
if(err){
reject(`Connection Failed\n${err}`);
}else{
console.info(`Successful connection, id ${con.threadId}`)
resolve(con)
}
})
})
}
/// query ///
// This function makes a query on a database
// It accepts and sql statement and a list of args if used in the statemens
// Inputs:
// sql - The sql statement to execute on the database
// args - The arguments to use in the sql statament
// Returns:
// resolve - The result of the sql insert statemen
// reject - An error
async function query(sql,args){
return new Promise(async(resolve,reject) => {
// try/catch is to handle the await for the connection
try{
let con = await getConnection() // If there is an error here it will be caught
// No error this query will execute
con.query(sql,args, function(err,result) {
// If error returned from query will be caught here
if (err){
reject(err);// Reject the promise
}else{
resolve(result);// Resolve the promise
}
// Always release
con.release()
})
}catch(error){
// Error in getting connection reject the error
reject(error)
}
})
}
module.exports = {query}