共享主机下SequelizeConnectionAcquireTimeoutError问题排查求助

DevOps 小满

2026-7-1

SequelizeConnectionAcquireTimeoutError 排查求助

问题概述

基于MySQL与Sequelize的Node.js后端已稳定运行6个月，在共享主机环境中添加特定搜索查询逻辑后，每24-72小时会出现SequelizeConnectionAcquireTimeoutError导致服务崩溃，其余功能运行正常。服务拥有约200万用户，搜索接口每小时请求量约100万次，该问题已持续2个月。

服务器资源统计

Physical Memory Usage 3.04 GB / 6 GB (50.68%)
--
Entry Processes 137 / 400 (34.25%)
Disk Usage 287.92 GB / 1,000 GB (28.79%)
MySQL® Disk Usage 189.78 GB / 901.86 GB (21.04%)
Bandwidth 28.32 GB / ∞
Addon Domains 0 / ∞
Subdomains 3 / ∞
Alias Domains 0 / ∞
Email Accounts 1 / ∞
Mailing Lists 0 / ∞
Autoresponders 0 / ∞
Forwarders 0 / ∞
Email Filters 0 / ∞
MySQL® Databases 4 / ∞
CPU Usage0 / 100 (0%)
I/O Usage0 bytes/s / 200 MB/s (0%)

错误详情

SequelizeConnectionAcquireTimeoutError: Operation timeout
at ConnectionManager.getConnection (/home/truecall/nodevenv/public_html/truecaller/14/lib/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:206:15)

项目package.json配置

{
  "name": "truecaller",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./app.js",
    "start:dev": "nodemon ./app.js"
  },
  "dependencies": {
    "agentkeepalive": "^4.5.0",
    "axios": "^0.19.2",
    "body-parser": "^1.20.2",
    "cookie-parser": "^1.4.6",
    "express": "^4.18.2",
    "form-data": "^4.0.0",
    "joi": "^17.11.0",
    "moment": "^2.29.4",
    "morgan": "^1.9.1",
    "multer": "^1.4.5-lts.1",
    "mysql2": "^2.3.3",
    "nodemailer": "^6.9.7",
    "rootpath": "^0.1.2",
    "sequelize": "^6.35.1",
    "sequelize-auto": "^0.8.8"
  }
}

触发问题的搜索逻辑代码

const { getNetwork } = require("./network.service");
const sequelize = require("../_helpers/sequelize");
module.exports = {
  getAll,
  getById,
  search,
  update,
  delete: _delete,
  getContactDetailsFromDatabase1,
  getContactDetailsFromDatabase2,
};
async function getAll() {
  return new Promise(async (resolve, reject) => {
    reject({
      status: false,
      message: "GET is not allowed",
    });
  });
}

async function getById(id) {
  return new Promise(async (resolve, reject) => {
    reject({
      status: false,
      message: "GET_BY_ID is not allowed",
    });
  });
}

async function search(params) {
  return new Promise(async (resolve, reject) => {
    return checkifAppUser(params).then((data) => {
      resolve(data)
    }).catch(() => {
      getContactDetailsFromDatabase1(params).then((data) => {
        resolve(data)
      }).catch((err) => {
        getContactDetailsFromDatabase2(params).then((data) => {
          resolve(data)
        }).catch(async (err) => {
          resolve({
            status: false,
            db: "db2",
            message: "Not Data Found",
            appuser: false,
            appuserresults: null,
            results: null,
            location_info: await getNetwork(params),
          });
        })
      })
    })
  });
}

async function update() {
  return new Promise(async (resolve, reject) => {
    reject({
      status: false,
      message: "PUT is not allowed",
    });
  });
}

async function _delete() {
  return new Promise(async (resolve, reject) => {
    reject({
      status: false,
      message: "DELETE is not allowed",
    });
  });
}

async function checkifAppUser(params) {
  return new Promise(async (resolve, reject) => {
    try {
      const [results, metadata] = await sequelize.query(
        `select users.*, countries.name, countries.iso2, filtered_contacts.name_check as spam_count from users  join countries on users.country_id = countries.id join filtered_contacts on filtered_contacts.mobile_number = users.mobile_number where users.mobile_number = ${params.mobile_number}`
      );

      if (results.length > 0) {
        
        resolve({
          status: true,
          db: "db1",
          appuser: true,
          message: "Contact found successfully",
          appuserresults: results[0],
          results:null,
          network: await getNetwork(params),
        });
      } else {
        reject();
      }
    } catch (error) {
      reject();
    }
  });
}

async function getContactDetailsFromDatabase1(params) {
  return new Promise(async (resolve, reject) => {
    try {
      const [results, metadata] = await sequelize.query(
        `![Capture](https://github.com/sequelize/sequelize/assets/72066902/bb024ea5-9bce-409d-87e6-c1fc76d89bbf)`
      );

      if (results.length > 0) {
        resolve({
          status: true,
          db: "db1",
          appuser: false,
          message: "Contact found successfully",
          appuserresults: null,
          results: {
            filtered_contact_id: results[0].filtered_contact_id,
            filtered_contact_name: results[0].filtered_contact_name,
            spam_check:
              results[0].spam_check == null ? 0 : results[0].spam_check,
            suggested_names: await removeAttributes(results.reverse()),
          },
          location_info: await getNetwork(params),
        });
      } else {
        reject();
      }
    } catch (error) {
      reject();
    }
  });
}

async function getContactDetailsFromDatabase2(params) {
  return new Promise(async (resolve, reject) => {
    const [results, metadata] = await sequelize.query(
      `SELECT id , personName from truecall_central_caller_database.contactsdb where nationalNumber = '${params.mobile_number}' and countryId = (SELECT id from countries WHERE countries.iso2 = '${params.iso2}') LIMIT 1`
    );
    if (results.length > 0) {
      resolve({
        status: true,
        db: "db2",
        appuser: false,
        message: "Contact found successfully",
        appuserresults: null,
        results: {
          filtered_contact_id: results[0].id,
          filtered_contact_name: results[0].personName,
          spam_check: 0,
          suggested_names: [
            {
              suggested_name: results[0].personName,
              count: 1,
            },
          ],
        },
        location_info: await getNetwork(params),
      });
    } else {
      const [results, metadata] = await sequelize.query(
        `SELECT id , personName from truecall_central_caller_database.contactsdb2 where nationalNumber = '${params.mobile_number}' and countryId = (SELECT id from countries WHERE countries.iso2 = '${params.iso2}') LIMIT 1`
      );
      if (results.length > 0) {
        resolve({
          status: true,
          db: "db2",
          appuser: false,
          message: "Contact found successfully",
          appuserresults: null,
          results: {
            filtered_contact_id: results[0].id,
            filtered_contact_name: results[0].personName,
            spam_check: 0,
            suggested_names: [
              {
                suggested_name: results[0].personName,
                count: 1,
              },
            ],
          },
          location_info: await getNetwork(params),
        });
      } else {
        const [results, metadata] = await sequelize.query(
          `SELECT id , personName from truecall_central_caller_database.contactsdb3 where nationalNumber = '${params.mobile_number}' and countryId = (SELECT id from countries WHERE countries.iso2 = '${params.iso2}') LIMIT 1`
        );
        if (results.length > 0) {
          resolve({
            status: true,
            db: "db2",
            appuser: false,
            message: "Contact found successfully",
            appuserresults: null,
            results: {
              filtered_contact_id: results[0].id,
              filtered_contact_name: results[0].personName,
              spam_check: 0,
              suggested_names: [
                {
                  suggested_name: results[0].personName,
                  count: 1,
                },
              ],
            },
            location_info: await getNetwork(params),
          });
        } else {
          reject();
        }
      }
    }
  });
}

async function removeAttributes(arr) {
  await arr.forEach((obj) => {
    delete obj.filtered_contact_id, delete obj.filtered_contact_name;
    delete obj.spam_check;
  });
  return arr;
}

Sequelize连接配置（db.js）

const { Sequelize } = require("sequelize");
const config = require("../../config.json");

const { database, user, password } = config.database;

const sequelize = new Sequelize({
    host:"localhost",
    database:database, 
    user:user, 
    password:password, 
    dialect: "mysql",
    logging: false,
      pool: {
        max: 1000,
        min: 10,  
        acquire: 30000,
        idle: 1000
      }
});

module.exports = sequelize;

补充排查信息

MySQL进程列表无待处理进程或查询
单条查询执行时间约0.0035秒

排查解决思路

1. 连接池配置优化

当前连接池max:1000设置远超MySQL默认最大连接数（通常为151），共享主机环境下实际可用连接数更低，导致连接池请求无法获取新连接触发超时。建议调整：

pool: {
  max: 50, // 匹配MySQL实际允许的最大连接数，预留系统连接
  min: 5,
  acquire: 60000, // 延长超时时间，避免短时间高并发触发超时
  idle: 30000, // 缩短空闲连接回收周期，释放资源
  evict: 10000 // 定期清理空闲连接
}

2. 修复连接泄漏问题

代码中存在async/await与Promise嵌套混用的情况，可能导致数据库连接未及时归还到池。移除不必要的Promise包装，直接使用async/await：

async function checkifAppUser(params) {
  const [results, metadata] = await sequelize.query(
    `select users.*, countries.name, countries.iso2, filtered_contacts.name_check as spam_count from users  join countries on users.country_id = countries.id join filtered_contacts on filtered_contacts.mobile_number = users.mobile_number where users.mobile_number = ${params.mobile_number}`
  );
  if (results.length > 0) {
    return {
      status: true,
      db: "db1",
      appuser: true,
      message: "Contact found successfully",
      appuserresults: results[0],
      results:null,
      network: await getNetwork(params),
    };
  }
  throw new Error("Not app user");
}

3. 修复SQL注入与查询缓存问题

直接拼接参数到SQL语句会引发注入风险，同时导致查询缓存失效。改用参数化查询：

const [results, metadata] = await sequelize.query(
  `select users.*, countries.name, countries.iso2, filtered_contacts.name_check as spam_count from users  join countries on users.country_id = countries.id join filtered_contacts on filtered_contacts.mobile_number = users.mobile_number where users.mobile_number = ?`,
  { replacements: [params.mobile_number] }
);

4. 分离耗时异步操作

getNetwork函数在查询未完成时调用，会占用数据库连接。将其移到查询完成后执行，避免连接长时间占用：

async function checkifAppUser(params) {
  // 查询完成后连接自动归还池
  const [results, metadata] = await sequelize.query(...);
  // 后续异步操作不占用连接
  const networkInfo = await getNetwork(params);
  if (results.length > 0) {
    return {
      status: true,
      db: "db1",
      appuser: true,
      message: "Contact found successfully",
      appuserresults: results[0],
      results:null,
      network: networkInfo,
    };
  }
  throw new Error("Not app user");
}

5. 确认共享主机限制

联系主机提供商确认以下隐性限制：

MySQL最大允许连接数
进程/线程数量限制
网络带宽或IO的阈值限制

内容的提问来源于stack exchange，提问作者Engr.Aftab Ufaq

共享主机下SequelizeConnectionAcquireTimeoutError问题排查求助

问题概述 #

服务器资源统计 #

错误详情 #

项目package.json配置 #

触发问题的搜索逻辑代码 #

Sequelize连接配置（db.js） #

补充排查信息 #

排查解决思路 #

1. 连接池配置优化 #

2. 修复连接泄漏问题 #

3. 修复SQL注入与查询缓存问题 #

4. 分离耗时异步操作 #

5. 确认共享主机限制 #