数据库性能测试实战：从JMeter压测到瓶颈定位与调优

简介一份面向数据库管理员、开发人员及性能调优工程师的完整数据库性能测试报告系统讲解基于Tpch、Jmeter、Nmon的测试方法论从硬件与软件测试环境搭建、DDL脚本和平面数据文件准备到查询SQL编写、工具开发及压力测试执行逐步呈现一套可复用的性能评估流程帮助读者定位吞吐量、响应时间、资源利用率等方面的瓶颈并优化。资源以单个PDF文件封装体积仅2.45MB排版清晰、目录结构完整既适合下载存档也便于打印或团队传阅。目前已有243人学习该文档。报告还包含测试结果章节给出1GB数据量级下的装载时间对比等实测数据并梳理了插入/删除数据功能开发、测试步骤及注意事项对于正在建立数据库性能评估体系或准备开展基准压测的团队是一份兼具方法指导与实战参考价值的资料。 我一向是那种“先看数据、再下结论”的人所以拿到“数据库性能测试报告-1.0.0.pdf”这个文件名的时候脑子里首先跳出来的不是格式和排版而是这份报告背后那两周睡不着觉的压测经历。当时业务方报了个需求一套对外提供查询服务的系统马上要上线数据库层到底扛不扛得住预期流量谁也不敢拍胸口。于是任务落到了我头上——用JMeter搭压测环境对数据库做一轮完整的性能测试最后把结果整理成一份可交付、可追溯的报告。这篇内容就是把我这次从零产出“数据库性能测试报告-1.0.0.pdf”的完整过程拆开来讲从环境搭建、测试场景设计到压测执行、瓶颈定位和调优验证中间踩了不少坑也发现了一些只有实际操作才会暴露的问题。如果你正准备做数据库压测或者正在为“怎么把测试结果组织成一份像样的报告”发愁这篇应该能给你省下不少弯路。1. 起因一份性能测试报告是怎么被逼出来的先说背景。系统上线前的压测往往不是技术驱动的更多是业务和运维“逼”出来的。这次也一样业务方的预估是高峰期每天有几十万次查询集中在上午和晚上各两个小时峰值QPS按两倍冗余算。他们想知道的不是“能不能跑”而是“数据库到底能跑多快、扛多少并发、在什么条件下会崩”这些只有真实压测才能回答光靠开发拍脑袋没有说服力。我当时的切入点很简单先把被测系统的核心链路弄清楚。这个系统不复杂Web层做请求转发业务逻辑主要是查询订单、用户信息和基础配置数据数据量大概在百万级左右数据库用的是MySQL 8.0。既然核心是读多写少那我就把压测重点放在“查询混合场景”上同时加了一组纯写入测试用来摸清数据库的写入瓶颈。这里有个关键点做性能测试前必须搞清楚被测系统的数据特征不能上来就随便压否则压出来的数字没有参考价值。报告为什么叫“1.0.0”因为这是第一轮完整的基线测试。我习惯把第一轮结果叫做基线版本后续所有优化、配置调整、代码改动都要拿它当对照组。1.0.0的意义不是“测试做完了”而是“数据库当前状态的性能底数已经有人量过了”以后谁再改连接池参数、加索引、换硬件都可以直接拿这版报告做对比省得每次都在口头上扯“变快了还是变慢了”。另外多说一句客户那边还在推国产数据库替代所以我在环境里也装了一套达梦数据库做兼容性对比。这个后面会提到有些国产库的并发表现和MySQL确实不太一样压测的时候别当成同一类东西来对待。2. 压测环境搭建与数据库选型2.1 环境参数与被测库的初始化压测环境必须尽量贴近生产这是原则。如果你在测试机上压出来的数据和生产差了十万八千里那报告就是废纸。这次我用的是一台独立物理机做数据库服务避免和其他应用抢资源JMeter压测机单独放了一台和数据库服务通过千兆内网互通减少网络抖动对结果的影响。数据库服务器的配置大概是32核CPU、128GB内存、NVMe固态盘操作系统是CentOS 7。MySQL 8.0按生产参数做了初始化重点调了几个关键参数——innodb_buffer_pool_size设到64Ginnodb_flush_log_at_trx_commit设为2允许每秒批量刷日志提升写入性能max_connections设成2000。这些都是常规配置但压测前必须确认它们和生产一致否则测出来的连接数上限毫无参考价值。我还初始化了测试库包含三张表订单表约300万行、用户表约100万行、配置表几百行。订单表专门模拟了典型查询场景包含订单号、用户ID、状态、创建时间、金额等字段。为了体现“真实”我没有把全部索引都建上而是保留了线上已有的索引状态——这个细节很重要因为很多压测报告“好看”是因为测试表建了一堆生产上没有的索引压出来当然快但上线就打回原形。2.2 为什么选JMeter而不是其他压测工具压测工具我选了JMeter理由很实在开源、免费、社区资料多而且通过JDBC Request可以直接压数据库层不需要经过应用服务正好符合这次“只关心数据库性能”的目标。网上关于jmeter性能测试步骤的教程一抓一把但大部分人拿它压HTTP接口压JDBC的反而不多我在这次实践里把JDBC压测的完整流程都走了一遍后面会详细写。当时也考虑过Sysbench它是专门做数据库压测的工具生成脚本很容易跑出来的指标也挺标准。但问题在于Sysbench的场景偏通用想模拟我们这个业务系统的查询特征很多带条件的组合查询需要写不少Lua脚本反而比JMeter更绕。还有一个原因团队里其他同事对JMeter更熟后面需要他们自己去复跑测试、补充场景用大家都熟悉的工具维护成本最低。关于jmeter性能测试操作说明网上很多教程会告诉你“线程数设100循环次数设100”就完事了实际上远没这么简单。线程数只是模拟用户数还得考虑每个线程里事务的间隔时间、是否加思考时间、数据怎么参数化这些都会直接影响结果。我会在下一节讲场景设计时展开。2.3 国产数据库的对比环境这次测试还加了一个达梦数据库的对比项因为业务方有国产化适配的要求。达梦的安装过程和MySQL有些差别但JDBC驱动连起来并不复杂把驱动包放到JMeter的lib目录就行。压测时我单独跑了一套同样的场景方便后面做横向对比。这里提醒一句对比测试最关键的是保证场景完全一致包括数据量、SQL语句、并发模型否则对比出来的差异没法归因。我当时在达梦库里也灌了同样规模的测试数据SQL语句几乎原样照搬个别函数语法做了微调后面看结果的时候就能比较放心地说“这个差异是数据库实现导致的不是测试方法不一致导致的”。3. 测试场景设计与JMeter脚本封装3.1 场景怎么定才不偏设计测试场景前我先列了业务方关心的几个核心问题系统在多少并发下开始变慢高峰期能不能撑住数据库的容量上限大概在哪根据这些问题我把测试场景分成了三类单条SQL压力测试、混合业务模拟测试、稳定性测试。单条SQL压力测试针对订单查询、用户查询、订单写入各写一组SQL分别压测观察单项操作的吞吐量和耗时。 混合业务模拟测试按真实业务比例混合查询和写入比如70%查询、30%写入模拟高峰时段混合负载。 稳定性测试固定中等并发连续跑8小时观察TPS和响应时间是否稳定有没有内存泄漏或慢查询累积的问题。这三个场景对应报告里的三张核心数据表各有各的用途。很多新手做压测只做第一项测出单条SQL的极限就觉得万事大吉结果忽略了混合场景才是最接近真实生产的稳定性问题更是要跑一段时间才能暴露。3.2 JMeter脚本的具体配置以查询订单场景为例JMeter配置上要把关键步骤说清楚添加线程组设置线程数为50Ramp-Up时间为30秒也就是30秒内50个线程分批启动循环次数根据测试时长来定。添加JDBC Connection Configuration配置数据库连接URL、驱动类、用户名密码连接池最大连接数设成20。添加JDBC RequestSQL语句写实际的业务查询比如按订单号和用户ID组合查询。添加CSV Data Set Config做参数化从外部文件读入订单号、用户ID模拟不同用户不同订单避免每次查同一行导致数据全被缓存测出来的性能虚高。添加聚合报告和结果树用来统计TPS、平均响应时间、错误率这些核心指标。参数化这一步我特别想强调。很多人压测时SQL里写死一个订单号比如WHERE order_id 100001那么第一次查完这条记录就已经在InnoDB缓冲池里了后面所有请求都走内存速度当然快但这不是真实性能。数据量大、缓存命中率低的场景才是真实的。所以我在CSV里放了10万条不同的订单号每个请求查不同数据结果会诚实很多。阶梯加压也很重要。我不建议一上来直接把线程数固定在某个值JMeter里可以用“Stepping Thread Group”插件实现逐步加压比如每30秒增加25个线程观察TPS和RT随并发升高的变化曲线。这样能清晰看到“拐点”——也就是响应时间突然恶化、TPS不再线性增长的那个临界并发数这个数据在报告里非常值钱。3.3 监控指标到底该看哪几个监控指标我固定看四类TPS、响应时间平均RT和P99、错误率和数据库侧指标连接数、CPU、IO、锁等待。前面三个JMeter的聚合报告里直接有数据库侧指标需要在测试机上用系统命令或监控工具采集比如top看CPUiostat看磁盘IOMySQL的SHOW GLOBAL STATUS看Threads_running和Lock_wait等。P99响应时间我特意每次都记因为平均响应时间很容易被“大部分请求都很快极少数请求慢成狗”这种分布骗过去P99才能真正暴露长尾问题。比如平均RT只有120ms看起来挺好但P99到了1.5秒说明有1%的用户体验已经到了卡顿程度这才是问题所在。4. 压测执行与结果明细4.1 分轮压测的完整过程压测不是一次跑完拉倒我按“基线 → 混合 → 稳定性”的顺序分了三轮每轮之间留出时间观察数据库状态。第一轮是基线单场景压测。50并发下查询订单的TPS稳定在3200左右平均RT约15msP99约40ms写入场景稍微差一些TPS约1800平均RT约27ms。这个结果算是中规中矩MySQL 8.0在百万级数据量下这个表现与预期基本吻合没有惊喜也没有意外。第二轮是混合场景压测。我按7:3的比例混合查询和写入总并发设到80。这一轮开始暴露问题了TPS没有像预想的那样叠加上去反而只有2200左右而且数据库侧的Threads_running时常飙到几百说明连接和线程池压力不小。更明显的是JMeter结果里开始出现少量的查询超时和连接失败错误虽然错误率只有0.8%左右但已经是个值得警惕的信号。第三轮稳定性测试我固定60并发跑8小时。前4个小时各项指标都平稳到第5个小时左右开始出现一个有趣的现象TPS整体没掉但P99响应时间从90ms慢慢涨到了400ms以上而且这个趋势没有回落。查数据库慢查询日志发现有些查询的执行计划因为数据统计信息的变化开始走错索引导致耗时从原来的几十毫秒变成几百毫秒这种问题短时压测根本发现不了只有长时间跑才会冒出来。4.2 压测过程中碰到的坑这次压测撞到的最典型的坑是数据库连接池耗尽。混合场景跑到一半JMeter里突然冒出大量“Cannot get a connection”的错误第一反应以为是连接数设置太小调到500还是有问题。后来查MySQL的max_connections发现已经是2000但Threads_running经常超过800大量线程处于Lock wait状态。这时候我才意识到问题不是连接不够而是有SQL在等锁把连接全占住了。用SHOW ENGINE INNODB STATUS查的时候看到了不少记录处于“LOCK WAIT”状态再配合information_schema.innodb_trx里的事务列表发现是业务代码里一个“先更新订单状态再查询订单列表”的组合操作为了保证一致性开了事务但事务里还夹着两次无关的查询把锁持有时间拉长了导致并发一高就互相等锁甚至出现了两个事务互相等对方的锁接近死锁的临界状态。另外国产数据库那条线也有点意思。达梦数据库在同等硬件和相同并发模型下查询TPS大约只有MySQL的70%左右但在低并发20以下时响应时间差距很小。这说明很多国产数据库在小规模应用下性能差异不大真正拉开差距的是并发上来之后的锁机制和优化器能力这个结论在报告里给了业务方很重要的参考。5. 瓶颈分析与定位思路5.1 从现象倒推嫌疑点压测执行完最核心的工作其实是排查和分析。我复盘时的思路是从现象倒推问题表象是连接池耗尽、P99涨高、锁等待多那嫌疑点就有几个了——SQL本身写得不好、索引缺失或失效、事务范围过大、连接池参数不合理。先看SQL。把慢查询日志打开找出耗时Top10的SQL逐条用EXPLAIN看执行计划。结果发现一个非常典型的问题订单查询SQL里用了WHERE status 1 AND create_time BETWEEN ...但索引只有idx_create_time导致数据库筛选完时间范围后还要再对status做一次过滤虽然不是全表扫描但扫描的行数比预期多不少在数据量上了百万后这个代价就很明显了。再看事务。前面提到那个“更新订单状态再查询”的组合操作我翻了一下业务代码发现事务边界确实太随意。一个只需要执行UPDATE的操作代码里开事务后做了三次查询、一次更新最后才提交中间查询还查了一张关联表。锁的持有时间被无谓拉长并发上来自然堵车。5.2 锁和死锁问题的排查链路锁等待问题排查起来要比SQL优化更绕。当时我怀疑有死锁但没抓到现场后来专门加了一段监控脚本每5秒采集一次information_schema.innodb_trx和performance_schema.data_locks的数据落盘等复现后再分析。复现之后看到了比较明确的锁冲突链路线程A持有订单表某行的UPDATE锁接着想查配置表线程B持有了配置表的读锁接着想更新同一行订单。两个事务互相等待对方的锁虽然没有立刻触发死锁检测但处于一种“谁都别想跑”的状态时间一长连接池就被占满。这是典型的锁顺序不一致导致的问题。解决办法是统一业务里所有涉及多张表更新操作的加锁顺序或者在代码设计上降低事务粒度。我当时给出的优化建议是后者——把配置表查询挪到事务外面让事务里只保留必要的UPDATE操作锁持有时间一下就降下来了。5.3 数据库侧的系统资源验证锁问题排查完之后我又回过去验证了一些“不背锅”的项。CPU和内存的使用率虽然在压测高峰期到了70%以上但没有长期打满的情况磁盘IO的读写速率也远没到瓶颈。这些数据也要写进报告里不然别人会怀疑“是不是服务器性能不够才导致数据库慢”测试报告的价值有一部分就在于排除干扰项把问题定位到它真正该待的地方。6. 调优验证与报告产出6.1 优化措施和前后对比基于上面的分析我做了几项调整拆分组合操作事务把配置表查询挪出事务只保留必要的UPDATE事务时间从平均30ms降到了5ms左右。增加联合索引在订单表上新增idx_status_create_time(status, create_time)联合索引查询扫描行数减少约80%。调整JMeter连接池参数JDBC Connection Configuration的最大连接数设成50并启用连接回收避免JMeter侧连接泄漏干扰测试。调优后重新跑混合场景结果变化非常明显。场景调优前TPS调优后TPSP99优化前P99优化后查询订单80并发22004100320ms70ms混合读写80并发18503600450ms110ms稳定性8小时2000左右3900左右P99爬升至400msP99稳定在95ms这组对比写进报告之后业务方的反馈是“终于知道之前为什么怕高峰期了”。其实优化的手段都很常规但只有压测才能逼着你去把这些细节处理掉平时光靠代码走查很难发现一次无谓查询对高并发的影响这么大。6.2 报告1.0.0的组织结构和编写要点最后说说报告本身。我的报告的目录结构是这样的测试概述测试目的、范围、参考资料测试环境硬件拓扑、软件版本、数据库参数、被测数据量测试场景与工具场景描述、JMeter配置、监控指标测试结果与分析分场景列数据、瓶颈发现过程、根因分析优化建议与验证结果问题清单、优化措施、前后对比结论与风险项当前能力评估、上线前必须处理的风险写报告的时候有个心得数据表格一定要有但更重要的是在表格旁边写清楚“为什么会出现这个数据”。比如TPS掉了一半不写原因的报告就是一堆数字审阅的人看不懂还要来找你一个个解释写清楚“因为锁等待导致连接池耗尽”之后报告才能真的被拿去当决策依据。另外报告里要把“环境参数”和“测试工具版本”写全。JMeter版本、JDBC驱动版本、测试数据量、数据库参数文件这些信息不写的话两个月后自己回来复测都可能对不上环境更别说别人来复测了。版本号“1.0.0”就是为了方便以后出2.0.0、3.0.0时能追溯当时的环境基线。6.3 我留在这个版本里的个人备注报告最后还有一小节是我自己加的个人经验备注不放进正式结论只作为团队内部参考资料。备注里记了两条一条是“达梦数据库在并发超80后TPS骤降需要确认生产并发是否可能越过这个值如果要长期运行建议单独做一轮深度压测”另一条是“稳定性测试第5小时后P99爬升初步怀疑是优化器统计信息更新导致执行计划漂移后续观察一下自动统计信息收集的调度”。这两条其实不是必须写的但我觉得作为测试报告把没有完全解决的问题也记录下来比只报喜不报忧更有价值。业务决策者看的是结论但迭代优化的同事需要的是线索这些备注给他们省了重新踩坑的时间。这份1.0.0的报告交付之后团队里最直观的变化是再讨论数据库性能的时候大家不再凭感觉说“应该没问题”或者“可能要出事”而是直接拿报告里的数据说话。这也是我为什么坚持把压测结果沉淀成报告的原因——性能问题如果没有量化就等于不存在等上线出了事故才去看数据库状态那成本就完全不一样了。最后再分享一个实际操作中的小技巧压测报告里的截图和日志片段不要贴太多但关键问题一定要有现场证据。比如我的报告里附了一张SHOW ENGINE INNODB STATUS的输出片段截取了“LOCK WAIT”相关的信息读者看到那段日志就能秒懂当时的锁冲突有多严重。干说“有锁等待”说服力不足附上日志才是完整的链路。本文还有配套的精品资源点击获取