0

Hello everyone this is my first time here but I'm a quite desperate with this situation, I hope you can help me.

I have a nodejs server with express and sequelize. When I was in development enviroment I didn't have any problem then in production enviroment sometimes the entire app throw this error in everyrequest ConnectionError [SequelizeConnectionError]: connect ETIMEDOUT for a few minutes then the server starts to work normally.

In the server I use pm2 cluster mode(8 instances) to handle all the requests.

Is the first that I manage a server and a DB and I read some articles, I've tried changing mysql enviroment variables and the sequelize pool options but the problem persists.

I'm not sure if the problem is in the variables or the server specs or pm2 config. I hope someone can give a hint of the problem.

mysql variables

skip_name_resolve = 1

join_buffer_size = 50M

tmp_table_size = 2G

max_heap_table_size = 2G

thread_cache_size = 40

max_connections=1600

key_buffer_size=195k

innodb_buffer_pool_size = 4G

innodb_log_file_size = 512M

key_buffer_size = 1M

innodb_buffer_pool_instances = 4

sequelize configuration

{
    host: config.bd_host,
    dialect: 'mysql',
    logging: false,
    pool: {
      max: 100,
      min: 0,
      acquire: 1000000,
      idle: 100000,
      evict: 2000,
    },
    dialectOptions: {
      decimalNumbers: true,
    },
  }

Server specifications

  • 64gb RAM
  • 16 cores proccesor
  • ubuntu 18.04.5 lts
  • mysql version 14.14 Distrib 5.7.40, for Linux (x86_64) using EditLine wrapper
  • express 4.17.1
  • sequelize 6.19.1
  • node version 14.17.2
4
  • 84GB is of RAM is a strange number.
    – Rick James
    Commented Dec 14, 2022 at 15:58
  • What engine is being used for database tables? Set innodb_buffer_pool_size to be about 70% of available RAM and key_buffer_size to 50M.
    – Rick James
    Commented Dec 14, 2022 at 16:02
  • Hi @RickJames the current engine is InnoDB and the server ram is 64gb, what is the impact of key_buffer_size variable ? Commented Dec 14, 2022 at 17:38
  • A few system tables are still MyISAM, hence the need for the key_buffer (for caching indexes). InnoDB caching needs the buffer_pool. If it is too small, I/O will kill performance.
    – Rick James
    Commented Dec 15, 2022 at 7:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.