ClearDB最大连接数限制问题排查求助

每小时收集数据并导入Heroku上的ClearDB MySQL表，明明只在插入时建立少量连接，却频繁触发最大连接数限制（40）。报错后查看ClearDB仪表板，活跃连接数远低于限制值。dataArray仅包含12个对象，执行一次函数居然用到40个连接？试过连接池但触发限制的速度更快，用connection.end()又会出现插入前连接已关闭的错误。

错误信息 #

{
  code: 'ER_USER_LIMIT_REACHED',
  errno: 1226,
  sqlMessage: "User 'xxxxxxx' has exceeded the 'max_user_connections' resource (current value: 20)",
  sqlState: '42000',
  fatal: true
}

现有代码 #

const connection = mysql.createConnection({
  host: 'us-cluster-xxxx-xxxxxxx',
  user: 'xxxxxx',
  password: 'xxxxx',
  database: 'heroku_xxxxxxx'
});

const insertDataPromise = (query, values) => {
  return new Promise((resolve, reject) => {
    connection.query(query, values, (error, results) => {
      if (error) {
        reject(error);
      } else {
        resolve(results);
      }
    });
  });
};

const getTheData = async () => {

  const dataArray = [
    {
      locationName: 'The Bay - 463044',
      tableName: 'the_bay_wind_wave_data',
    },
    {
      locationName: 'Big Shoal - 43331',
      tableName: 'big_shoal_wind_wave_data',
    },
    {
      locationName: 'Silva Strait - 43303',
      tableName: 'silva_strait_wind_wave_data',
    },
    ......
  ]

    const locationVariables = {
      direction: 'SW',
      speed: 12,
      gustSpeed: 20,
      waveHeight: 2,
      wavePeriod: 1.2,
      barometer: 112,
      airTemp: 56,
      waterTemp: 58,
    };

    try {

    dataArray.forEach((buoy) => {

      const insertDataQuery = `
        INSERT INTO ${buoy.tableName}
        (direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      `;

      insertDataPromise(insertDataQuery, [direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp])
    })
    // Close the connection after all operations.. this ends the connection before the loop completes so I removed it
    // connection.end();
    console.log(`Data inserted successfully at ${currentPDTTime}.`);
  } catch (err) {
    console.error('Error inserting data:', err);
  } finally {
    // Close the connection after all operations.. this ends the connection before the loop completes so I removed it
    // connection.end();
  }
};

核心问题1：异步操作未等待，连接泄漏 #

用forEach循环调用insertDataPromise但未等待Promise完成，会导致：

• 代码直接跳过插入操作执行console.log，甚至可能在插入完成前尝试关闭连接
• 若连接出现异常或未正确复用，会导致连接无法释放，积累后触发连接数限制

核心问题2：连接池使用方式错误 #

之前用连接池更快触发限制，大概率是每次查询都新建连接、未正确复用。连接池的核心是复用连接，而非每次创建新连接。

修复步骤 #

1. 改用for...of循环并等待异步操作完成
   替换forEach为for...of，用await确保每个插入操作完成后再执行下一步，这样就能安全关闭连接：

   try {
     for (const buoy of dataArray) {
       const insertDataQuery = `
         INSERT INTO ${buoy.tableName}
         (direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       `;
       // 解构变量避免未定义错误
       const { direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp } = locationVariables;
       await insertDataPromise(insertDataQuery, [direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp]);
     }
     console.log(`Data inserted successfully at ${currentPDTTime}.`);
   } catch (err) {
     console.error('Error inserting data:', err);
   } finally {
     // 所有插入完成后再关闭连接
     connection.end();
   }
   

2. 正确使用连接池（推荐方案）
   配置合理的连接池上限，复用连接而非每次创建新连接：

   // 创建连接池，设置远低于ClearDB限制的连接数
   const pool = mysql.createPool({
     host: 'us-cluster-xxxx-xxxxxxx',
     user: 'xxxxxx',
     password: 'xxxxx',
     database: 'heroku_xxxxxxx',
     connectionLimit: 5 // 关键：限制池内最大连接数
   });
   
   // 封装池的查询Promise
   const insertDataPromise = (query, values) => {
     return new Promise((resolve, reject) => {
       pool.query(query, values, (error, results) => {
         if (error) {
           reject(error);
         } else {
           resolve(results);
         }
       });
     });
   };
   
   const getTheData = async () => {
     // dataArray和locationVariables定义不变
     try {
       for (const buoy of dataArray) {
         const insertDataQuery = `
           INSERT INTO ${buoy.tableName}
           (direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)
         `;
         const { direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp } = locationVariables;
         await insertDataPromise(insertDataQuery, [direction, speed, gustSpeed, waveHeight, wavePeriod, barometer, airTemp, waterTemp]);
       }
       console.log(`Data inserted successfully at ${currentPDTTime}.`);
     } catch (err) {
       console.error('Error inserting data:', err);
     }
     // 连接池无需手动关闭，会自动管理连接复用
   };
   

3. 额外注意事项

   • 检查是否有其他代码重复创建连接但未释放，比如定时任务重复执行时是否重复实例化连接
   • ClearDB的连接数限制是用户级而非实例级，要确保所有应用实例的总连接数不超过限制
   • 绝对禁止在循环内创建新连接实例，必须复用同一个连接或连接池

内容的提问来源于stack exchange，提问作者fugazzi