To create all function as async is a bad practice with mysql knex

Question

I have used knex.js (with slave and master connection) in node to connect mysql.

My code is working fine for 150 users but when the concurrent user increases, the heap usage in PM2 also reaches to 100% or above and server stop responding or responded very slow.

The AWS CPU (5x large aws ec2 instance with 8 core cpu and 32GB RAM) use is 12-20%.

Application is responding in millisecond with JWT authentication and pm2.

There are lot of queries that depends upon previous query result, so i created all function as async.

Problematic point:

In every hour, a slot is open for all users (approx 150 000) to edit their content (approx 3k live concurrent).

Temporary solution that i did:

Implementation cluster resolve my problem but on cluster kill not closing the DB connection.

My doubt:

When the application is working fine with cluster, why the application does not work with out cluster in same configuration?

async function authorize(req, res) {
  //decode aes-128 request
  let body = await helper.get_decoded_request(req);
  if (!body) {
    return res.status(200).json(await helper.error("Error occurs while decoding."));
  }
  const schema = joi.object({
    "client_id": joi.string().min(1).required(),
    "client_secret": joi.string().min(1).required()
  });
  try {
    await schema.validateAsync(body);
  }
  catch (err) {
    return res.status(200).json(await helper.error("Please send the valid request"));
  }

  try {
    //data base update and add function that writen in other file
    await user.update_user(data);
    await match.add_transaction(data);
  } catch (err) {
    return res.status(200).json(await helper.error("Please send the valid request"));
  }

  return res.status(200).json(await helper.success("Token added successfully.", jsontoken));
}

Model file code:

const { read_db, write_db } = require("./database");
async function add_transaction(data) {
  return await write_db.insert(data).table("users");
}

Database file:

var knex = require("knex");
const read_db = knex({
  client: "mysql",
  connection: {
    database: process.env.SLAVE_MYSQL_DB,
    host: process.env.SLAVE_MYSQL_HOST,
    port: process.env.SLAVE_MYSQL_PORT,
    user: process.env.SLAVE_MYSQL_USER,
    password: process.env.MYSQL_PASSWORD
  },
  pool: { min: 1, max: 10 }
});
const write_db = knex({
  client: "mysql",
  connection: {
    database: process.env.MYSQL_DB,
    host: process.env.MYSQL_HOST,
    port: process.env.MYSQL_PORT,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD
  },
  pool: { min: 1, max: 10 }
});
module.exports = { read_db, write_db };

Answer 1

There is not enough information to give any answers about the root cause of the problem, but maybe these general steps how to start solving the problem helps.

The first and most important part is to replicate the slowdown locally and run your app in profiler to actually find out which parts of the code are actually taking all that CPU. Running your app with node --inspect allows you to run profile the execution of your app in Chrome browsers profiler.

Also sounds like you are running just a single node process, so it is pretty strange how are you able to use 100% CPU from all 8 cores with quite low user count (less than 1000?)... One thing that comes to my mind is if you check for example password hash on every user request, it might actually start to use lots of CPU very fast.

If that indeed is the problem you could implement for example JSON Web Token support to your app, where password is only used to get the access token which is then really fast to verify in comparison to a password authentication.

When your basic app is working fine then you should start thinking how to run multiple server processes on single instance to be able to utilize all the CPU cores better.

EDIT:

Sounds like that 12-20% of 5-core instance is about 100% of single core. So your app seems to be saturating the CPU.

About the heap usage, if you profile the application can you see if it is the garbage collection which starts to halt the application.

One reason could be that you are actually creating too much trash which at some point starts to hurt the performance. You could also try if for example increasing default heap size from 1.7GB to 8GB node --max-old-space-size=8092 helps a bit.

In addition to garbage collection, for example if you are returning tens of thousands rows from DB just parsing them to javascript objects can saturate the CPU. So profiling is still right way to try to find out what is going on. Parsing especially dates is really slow in knex by default (when they are converted to Date objects).