Laravel+AWS Aurora 1000并发负载测试性能优化问询

问题背景 #

• 基于Laravel开发的答题应用平台，500个并发机器人测试时平均响应时间为0.333s，AWS平台无异常告警；扩展至1000个并发机器人后，平均响应时间升至2.61s
• AWS Aurora数据库CPU峰值仅为10%，无明显资源瓶颈，但监控工具Sentry.io告警显示，一条通过slug参数查询答题数据的SQL语句耗时达785.25ms，该查询已配置合理索引，本应具备高性能

关键异常SQL语句 #

select *
from `quizzes`
where `slug` = ?
limit 1

观测到的AWS监控异常 #

• 测试期间wait/io/table/sql/handler等待事件导致数据库总负载飙升至120，但此时仅使用2个CPU，远低于6个ACUs（12 GiB）的上限，不应构成瓶颈
• 怀疑问题源于AWS侧（如冷启动、数据库/Lambda连接配额限制），但未在AWS控制台找到相关证据

负载测试脚本 #

const urlApiBase = 'https://xxxxxx.com/api/v1/';
const roundId = bot.getVariable('RoundId');
const quizId = bot.getVariable('QuizId');

// 0. Capture round and quiz id
// https://urlApiBase + /v1/quiz/IxqyIDNN-loadster-test-quiz-24th-april-2024

 // 1, Participate in quiz
const participantName = bot.getVariable('participant', 0);

http.post(urlApiBase + 'quiz/' + quizId + '/participate', 
    {
        name: participantName + '_' + new Date().toISOString()
    },
        {
     validators: [
        response => {
                var body = response.json();
                bot.setVariable('ParticipantId', body.participant_id);
                return true;
            }
        ]
    }
)


const participantId = bot.getVariable('ParticipantId');

// 2. Participate in quiz round
http.post(urlApiBase + 'quiz/' + quizId + '/participate/' + participantId + '/round/' + roundId, {}, {
     validators: [
        response => {
            var body = response.json();
            if (!body.round_id) {
                console.error('Cannot participate in round. Exiting early.')
                bot.exit();
            } 
           return true;
        }
    ]
});
        
// 2. Play quiz round
let hasCompleted = false;
let questionId = null;
let answerId = null;
    
// Get first question
http.get(
    urlApiBase + 'quiz/' + quizId + '/round/' + roundId + '/participant/' + participantId + '/question', 
    {
     validators: [
        response => {
            var body = response.json();
        
            if (body.question) {
                   let answers = body.question.answers; 
                   questionId = body.question.id;
                   answerId = answers[answers.length * Math.random() | 0].id;
            } else {
                hasCompleted = true;
            }
            return true;
        }
     ]
    }
);

bot.wait(getWaitTime());

while (!hasCompleted) {
    http.post(
        urlApiBase + 'quiz/' + quizId + '/round/' + roundId + '/participant/' + participantId + '/answer', 
        {
          answer_id: answerId,
          question_id: questionId
        },
        {
         validators: [
            response => {
                var body = response.json();
                if (body.next_question.length == 0) {
                   hasCompleted = true;
                } else {
                    let answers = body.next_question.question.answers; 
                    questionId = body.next_question.question.id;
                    answerId = answers[answers.length * Math.random() | 0].id;
                }
                return true;
            }
        ]
        }
    );
    
    bot.wait(getWaitTime());
}

bot.exit();

function getWaitTime() {
    return (Math.floor(Math.random() * 4) + 1) * 1000
}

性能优化建议 #

数据库层优化 #

• 验证索引有效性：执行EXPLAIN ANALYZE查看该slug查询的执行计划，确认是否真的命中索引，排查索引失效场景（如隐式类型转换、索引碎片）
• 减少查询字段：替换select *为业务必需字段，降低数据传输量与内存占用
• 启用查询缓存：配置Aurora查询缓存，对高频访问的quiz数据进行缓存，减少磁盘IO开销
• 排查锁等待：通过Aurora Performance Insights分析wait/io/table/sql/handler事件，确认是否存在表锁/行锁竞争，比如并发写入导致的锁阻塞

Laravel应用层优化 #

• 模型缓存：利用Laravel缓存（如Redis）缓存通过slug查询的Quiz模型，设置合理过期时间，避免重复查询数据库
• 连接池管理：若使用Laravel + Lambda，配置RDS Proxy管理数据库连接池，避免Lambda冷启动时创建过多连接
• 路由逻辑精简：检查quiz/{slug}相关路由的中间件与前置逻辑，移除不必要的额外查询
• 异步处理非实时操作：将答题参与后的日志、统计更新等非实时操作放入Laravel队列异步执行，缩短请求响应链路

AWS基础设施优化 #

• Lambda配额检查：确认Lambda并发执行配额是否满足1000并发需求，避免请求排队
• 只读副本分流：部署Aurora只读副本，将slug查询等读请求分流到副本，减轻主库压力
• 网络延迟排查：检查应用与Aurora是否跨AZ部署，尝试将二者放在同一AZ降低网络延迟
• Lambda预热配置：启用Lambda Provisioned Concurrency，减少冷启动频率与耗时

负载测试脚本优化 #

• 移除无效注释行https://urlApiBase + /v1/quiz/IxqyIDNN-loadster-test-quiz-24th-april-2024，避免潜在错误
• 确认quizId变量的获取逻辑，可预先缓存该值，避免重复请求查询

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