ASP.NET项目Ubuntu云服务器查询超时问题求助

现象 #

• 本地环境：ASP.NET项目的GetRecommendedShows推荐接口约12秒即可返回结果
• Ubuntu云生产服务器：该接口调用约20秒后触发超时异常，其他接口（如/api/input）运行正常

接口代码 #

[HttpGet("suggest/{id1}/{id2}/{id3}")]
public async Task<List<int>> GetRecommendedShows(int id1, int id2, int id3)
{
    List<int> showIds = new List<int>() { id1, id2, id3 };

    // Fetch all shows from the database
    var allShows = await _context.Shows
        .Select(s => new { s.Id, s.VectorDouble })
        .ToListAsync();

    // Filter shows by the specified IDs
    var selectedShows = allShows.Where(s => showIds.Contains(s.Id)).ToList();

    if (selectedShows.Any())
    {
        // Calculate average vector
        double[] averageVector = VectorEngine.CalculateAverageVector(selectedShows.Select(s => s.VectorDouble).ToList());

        // Calculate similarities and get recommended show IDs
        List<int> recommendedShowIds = VectorEngine.GetSimilarities(allShows.Select(s => new ShowInfo { Id = s.Id, VectorDouble = s.VectorDouble }).ToList(), averageVector, 8);

        return recommendedShowIds;
    }
    else
    {
        // Return the input IDs if no shows are found
        return showIds;
    }
}

生产环境错误日志片段 #

Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]: fail: Microsoft.EntityFrameworkCore.Query[10100]
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:       An exception occurred while iterating over the results of a query for context type 'ShowPulse.Models.ShowContext'.
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:       Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:       A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 2 - Connection was terminated: Connection was terminated)
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:        ---> System.ComponentModel.Win32Exception (258): Unknown error 258

已尝试的解决方法 #

• 优化查询语句
• 延长EF Core命令超时时间至120秒：

builder.Services.AddDbContext<ShowContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("prodConnection"), sqlOptions =>
    {
        // Set the command timeout (in seconds)
        sqlOptions.CommandTimeout(120); 

        // Enable retry on failure (if needed)
        sqlOptions.EnableRetryOnFailure();
    });
});

问题根源分析 #

1. 全表查询性能瓶颈：接口中全量加载Shows表数据到内存，生产环境数据量更大时，网络传输和内存加载开销远高于本地环境。
2. 网络延迟：Ubuntu服务器与数据库之间的网络带宽或延迟高于本地，全量数据传输耗时过长触发超时。
3. 超时配置未完全生效：EF Core的CommandTimeout设置可能被数据库端的连接超时限制覆盖。

优化方案 #

1. 减少数据库查询数据量 #

优先仅查询目标ID对应的向量，避免全表加载：

[HttpGet("suggest/{id1}/{id2}/{id3}")]
public async Task<List<int>> GetRecommendedShows(int id1, int id2, int id3)
{
    List<int> showIds = new List<int>() { id1, id2, id3 };

    // 仅查询需要的ID对应的向量，而非全表
    var selectedShows = await _context.Shows
        .Where(s => showIds.Contains(s.Id))
        .Select(s => new { s.Id, s.VectorDouble })
        .ToListAsync();

    if (selectedShows.Any())
    {
        double[] averageVector = VectorEngine.CalculateAverageVector(selectedShows.Select(s => s.VectorDouble).ToList());
        
        // 若数据量仍大，建议将相似性计算逻辑下推到数据库或使用向量数据库
        var allShows = await _context.Shows
            .Select(s => new ShowInfo { Id = s.Id, VectorDouble = s.VectorDouble })
            .ToListAsync();
            
        List<int> recommendedShowIds = VectorEngine.GetSimilarities(allShows, averageVector, 8);

        return recommendedShowIds;
    }
    else
    {
        return showIds;
    }
}

2. 数据库端优化 #

• 确保Shows.Id字段已建立主键索引，加速WHERE IN查询
• 若使用SQL Server 2022或Azure SQL，创建向量索引将相似性计算下推到数据库：

-- 示例：创建HNSW向量索引（需根据实际向量维度调整）
CREATE VECTOR INDEX IX_Shows_VectorDouble ON Shows(VectorDouble) USING HNSW (DIMENSION = 1536, METRIC = COSINE);

3. 排查超时配置 #

• 检查数据库服务器的远程查询超时设置，确保其值大于EF Core的CommandTimeout：

EXEC sp_configure 'remote query timeout', 120;
RECONFIGURE;

• 确认连接字符串中未存在覆盖EF Core配置的超时参数：

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Connect Timeout=120;

4. 资源监控与异步优化 #

• 监控生产服务器的CPU、内存和网络带宽，排查资源瓶颈
• 考虑将向量计算逻辑异步化，避免阻塞请求线程

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