日期：2025年 10月 11日

標籤： Mysql C# Kafka

摘要：效能議題

第一部分：說明問題

Step 1：原因 - 收到此錯誤信

關鍵字如下：

Unhandled exception. MySql.Data.MySqlClient.MySqlException (0x80004005): 
The client was disconnected by the server because of inactivity. 
See wait_timeout and interactive_timeout for configuring this behavior.

Mysql 預設是連線後超過 8 個小時候會斷線，但實務上沒有軟體工程師會將連線與 Mysql 保持連線後，超過 8 小時都不執行 SQL

Step 2：原因 - 檢查 Mysql 資料庫設定

檢查連線閒置時間是否太短，wait_timeout 或 interactive_timeout

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

可以確定都是預設值，那麼問題的原因多半在代碼層面上

第二部分：重現此問題代碼說明

Step 1： 測試代碼

以下是測試用的範例代碼，有 5 個部分

using MySql.Data.MySqlClient;

class Program
{
    static async Task Main()
    {
        // 替換成你的 MySQL 連線字串
        string connStr = "Server=192.168.51.93;Port=3800;Database=demo;User ID=XXXX;Password=XXXX;max pool size=100";

        try
        {
            using (var conn = new MySqlConnection(connStr))
            {
                await conn.OpenAsync();
                Console.WriteLine("連線成功");

                // 1. 設定 SESSION wait_timeout = 10 秒
                using (var cmd = new MySqlCommand("SET SESSION wait_timeout=10;", conn))
                {
                    await cmd.ExecuteNonQueryAsync();
                    Console.WriteLine("SESSION wait_timeout 設為 10 秒");
                }

                // 2. 第一次查詢 (應該正常)
                using (var cmd = new MySqlCommand("SELECT NOW();", conn))
                {
                    var result = await cmd.ExecuteScalarAsync();
                    Console.WriteLine($"第一次查詢結果: {result}");
                }

                // 3. 等待 15 秒，超過 wait_timeout
                Console.WriteLine("等待 15 秒，模擬閒置超時...");
                await Task.Delay(TimeSpan.FromSeconds(15));

                try
                {
                    // 4. 第二次查詢 (此時連線應該已被 MySQL 斷掉)
                    using (var cmd = new MySqlCommand("SELECT NOW();", conn))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        Console.WriteLine($"第二次查詢結果: {result}");
                    }
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("捕捉到 MySqlException:");
                    Console.WriteLine(ex);
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }
        // 5. 結束
        Console.WriteLine("程式結束");
    }
}

Step 2： 首次查詢 - 開始閒置

我們使用測試代碼，進入閒置狀態 (執行 Open 連線後的等待)

Step 3： 二次查詢 - 報錯

因為我們 Session Timeout 設為 10 秒，閒置 15 秒後再次繼續執行 SQL 因此報錯，重現了此問題
工作環境上竟然會有 8 小時等待，這相當匪夷所思

第三部分：工作環境中如何出現

Step 1：問題代碼1 - 服務生命週期宣告

在自己的工作環境中在 C# Dotnet Worker 服務啟動後，會讓 Kafka 持續監聽，以便執行消費者處理
這段 scope 生命週期錯誤，在消費者外部執行 CreateScope();

public class MYWorkerService
{
    private IUnitOfWorkFactory? mywork;

    public async Task ExecuteAsync(CancellationToken cancellationToken)
    {
       
        try
        {
            // 略...

             // 【問題點 1】
            using var scope = scopeFactory.CreateScope();
            mywork = scope.ServiceProvider.GetRequiredService<IUnitOfWorkFactory>(); 

            await kafkaConsumer.SubscribeAsync<MyKafkaConsumer>(async (data) =>
            {
                try
                {                    
                    await Process();                 
                }
                catch (Exception ex)
                {
                    // 略...
                }
            });
        }
        catch (Exception ex)
        {
           // 略...
        }
    }

    private void Process()
    {
         // 【問題點 1】
        mywork.SqlQuery();
    }
}

Step 2：問題代碼2 - 資料庫實例造成

並且 mywork.SqlQuery() 真正執行底層的資料庫操作時，就會發生此問題了。

最大的謬誤：直覺地看，用 Kafka 會持續監聽，可能會有高併發突然數百萬的消費者，用同個連線應該很合理?

但實際上生命週期中，沒有在 Kafka 每次執行消費者時觸發 scopeFactory.CreateScope(); 從而導致此緩存會讓每次都 使用相同的 DbConnection
最大的併發量是不可能提高的，用的都是同一個連線池

public class DbConnectionFactory(MyOptions myOptions, ILogger<DbConnectionFactory> logger)
    : FactoryBase(new MySqlConnection(DbConnectionString)), IDbConnectionFactory
{
    // 【問題點 2】
    private readonly ConcurrentDictionary<(int AId, int BId, int ControlId, CategoryEnum ResourceType), DbConnection> _dbConnectionDict = [];

    // 大量的 Mysql Method 略...

    private DbConnection GetConnection((int AId, int BId, int ControlId, CategoryEnum ResourceType) key)
    {
        if (!_dbConnectionDict.TryGetValue(key, out var dbConnection))
        {
            //略...
         
            var connectionString = GetDbConnectionStringAsync(
                key.ControlId,
                key.AId, key.BId, key.ResourceType).Result;

            dbConnection = new MySqlConnection(connectionString);

            // 略...

            // 【問題點 3】
            _dbConnectionDict.TryAdd(key, dbConnection);

            // 略...
        }

        return dbConnection;
    }
}

Step 3：問題出現解釋 - 圖解說明

現在已定位出資料庫緩存與生命週期的影響，現在回到此錯誤不難理解
如圖，第一次 Kafka 觸發消費者，並且記錄了此 DbConnection 緩存

 See wait_timeout and interactive_timeout for configuring this behavior

string connStr = "Server=192.168.51.93;Port=3800;Database=demo;User ID=XXXX;Password=XXXX;max pool size=100";

DB緩存 + 不使用新的生命週期 ==> 相當於 **max pool size=1**