Fastify中处理MySQL PROTOCOL_CONNECTION_LOST错误的最佳方案（GCP部署）

问题背景 #

部署在GCP Cloud Run上的Fastify生产项目，执行SQL查询时频繁触发PROTOCOL_CONNECTION_LOST错误。已调高MySQL连接数限制但问题未解决，当前流量不高但担心流量增长后问题恶化。相关配置如下：

• Cloud SQL实例：高可用配置，4vCPU、26GB内存、100GB SSD
• Cloud Run容器：4核CPU、2GB内存、3600秒请求超时、1000并发请求、启用启动CPU加速，实例数最小1个、最大100个
• MySQL插件代码：

import fp from 'fastify-plugin'
import mysql from '@fastify/mysql'

const plugin = async (fastify, options, done) => {
    const { config } = fastify;

    fastify.register(mysql, {
        host: config.DB_HOST,
        socketPath: config.DB_SOCKET,
        port: config.DB_PORT,
        user: config.DB_USERNAME,
        password: config.DB_PASSWORD,
        database: config.DB_NAME,
        namedPlaceholders: true,
        promise: true,
        connectionLimit: 100,
        waitForConnections: true
    })

    done()
}

export default fp(plugin)

• 错误信息：

Error: Connection lost: The server closed the connection.
    at PromisePoolConnection.query (/app/node_modules/mysql2/promise.js:93:22)
    at Object.getErrorSyncedTransactions (file:///app/models/cron.js:33:41)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async Object.<anonymous> (file:///app/routes/v1/cron.js:89:39) {
  code: 'PROTOCOL_CONNECTION_LOST',
  errno: undefined,
  sql: undefined,
  sqlState: undefined,
  sqlMessage: undefined
}

• 示例查询代码：

import fp from 'fastify-plugin'

const model = (fastify, options, done) => {

    const getPendingPayments = async () => {
        const connection = await fastify.mysql.getConnection()
        const [rows] = await connection.query(
            `SELECT
            t.id,
            t.uuid,
            t.status_code,
            y.consent_token AS consent_token,
            y.payment_payload->>'$.data.id' AS payment_id,
            m.id AS merchant_id,
            m.uuid AS merchant_uuid,
            m.webhook_url,
            m.webhook_status
            FROM transactions t 
            JOIN transactions_yapily y 
            ON t.id = y.transaction_id
            JOIN merchants m
            ON m.id = t.merchant_id
            WHERE y.payment_payload IS NOT NULL
            AND t.status_code IN ('ACCP', 'ACSP', 'ACTC', 'ACWC', 'ACWP', 'ACFC', 'RCVD', 'PDNG')
            AND t.created_at >= DATE_SUB(NOW(), INTERVAL 3 HOUR)`
        )
        connection.release()
        return rows
    }

    fastify.decorate('cron', {
        getPendingPayments,
    })

    done()
}

export default fp(model)

最佳处理方案 #

1. 调整连接池参数，适配Cloud Run扩缩容特性 #

Cloud Run实例会自动扩缩容，每个实例维护独立连接池，需避免全局连接数过载，同时处理闲置连接被Cloud SQL主动断开的情况：

fastify.register(mysql, {
    // 保留原有基础配置
    host: config.DB_HOST,
    socketPath: config.DB_SOCKET,
    port: config.DB_PORT,
    user: config.DB_USERNAME,
    password: config.DB_PASSWORD,
    database: config.DB_NAME,
    namedPlaceholders: true,
    promise: true,
    // 调整连接池参数
    connectionLimit: 20, // 降低单实例连接上限，按100个Cloud Run实例算，总连接数控制在2000内（匹配4vCPU Cloud SQL默认max_connections）
    waitForConnections: true,
    idleTimeout: 60000, // 1分钟闲置超时，提前于Cloud SQL默认8小时断开时间
    acquireTimeout: 30000, // 获取连接超时30秒，避免无限等待
    enableKeepAlive: true, // 启用TCP保活，维持连接活性
    keepAliveInitialDelay: 30000, // 30秒后开始发送心跳
    // 添加连接有效性验证
    validateConnection: async (conn) => {
        try {
            await conn.ping();
            return true;
        } catch (err) {
            return false;
        }
    }
})

• 核心作用：validateConnection会在获取连接前自动检查有效性，失效则丢弃并重建；idleTimeout提前回收闲置连接，避免被Cloud SQL或中间代理断开。

2. 确保连接100%回收，避免泄漏 #

当前查询代码未处理异常场景，若查询报错，connection.release()不会执行，导致连接泄漏、连接池耗尽：

• 使用try/finally强制释放连接：

const getPendingPayments = async () => {
    let connection;
    try {
        connection = await fastify.mysql.getConnection()
        const [rows] = await connection.query(/* 查询语句 */)
        return rows
    } finally {
        if (connection) {
            connection.release()
        }
    }
}

• Node.js 18+可使用using语法简化：

const getPendingPayments = async () => {
    await using connection = await fastify.mysql.getConnection()
    const [rows] = await connection.query(/* 查询语句 */)
    return rows
}

3. 同步Cloud SQL闲置超时配置 #

调整Cloud SQL的wait_timeout和interactive_timeout，设置为比连接池idleTimeout稍长（如90秒），避免Cloud SQL主动断开连接池中的可用连接：

-- 临时生效，重启实例后失效
SET GLOBAL wait_timeout = 90;
SET GLOBAL interactive_timeout = 90;

• 永久生效：在Cloud SQL控制台的「数据库标志」中添加wait_timeout=90和interactive_timeout=90，然后重启实例。

4. 添加针对性错误重试机制 #

针对PROTOCOL_CONNECTION_LOST这类连接类错误，添加自动重试逻辑，避免单次错误导致请求失败：

const getPendingPayments = async (retryCount = 2) => {
    let connection;
    try {
        connection = await fastify.mysql.getConnection()
        const [rows] = await connection.query(/* 查询语句 */)
        return rows
    } catch (err) {
        if (err.code === 'PROTOCOL_CONNECTION_LOST' && retryCount > 0) {
            // 销毁无效连接，避免放回连接池
            if (connection) connection.destroy()
            return getPendingPayments(retryCount - 1)
        }
        throw err
    } finally {
        if (connection) {
            connection.release()
        }
    }
}

5. 优化Cloud Run实例并发配置 #

当前单实例1000并发过高，会导致连接池瞬间耗尽，建议调整为100-200，让Cloud Run通过扩缩容应对流量，而非单实例扛高负载。

6. 优化查询性能，减少连接占用时间 #

慢查询会长期占用连接，加剧连接池资源紧张，甚至被Cloud SQL强制断开：

• 为查询的JOIN和过滤字段添加索引：

CREATE INDEX idx_transactions_status_created ON transactions(status_code, created_at);
CREATE INDEX idx_transactions_yapily_transaction_id ON transactions_yapily(transaction_id);
CREATE INDEX idx_transactions_yapily_payload ON transactions_yapily(payment_payload);

• 提前计算时间戳传入查询，避免在SQL中使用DATE_SUB(NOW(), INTERVAL 3 HOUR)，提升索引利用率。

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