〇、經(jīng)驗(yàn)總結(jié)：

• 在關(guān)注業(yè)務(wù)接口的TPS時(shí)，也要關(guān)注數(shù)據(jù)庫(kù)服務(wù)器的QPS。如果一個(gè)業(yè)務(wù)流程里包含多條查詢，那么業(yè)務(wù)接口TPS的上升對(duì)數(shù)據(jù)庫(kù)服務(wù)器QPS的放大效應(yīng)會(huì)很明顯。
• 如果查詢結(jié)果集不大，盡量使用一條查詢語(yǔ)句，通過(guò)子查詢返回多個(gè)結(jié)果集，避免將多個(gè)結(jié)果集拆分成多次數(shù)據(jù)庫(kù)查詢，否則會(huì)造成過(guò)多的數(shù)據(jù)庫(kù)連接/查詢操作，消耗IO資源，降低TPS，提高CPU占用率。
• 在業(yè)務(wù)代碼中，盡量避免在循環(huán)語(yǔ)句里寫(xiě)數(shù)據(jù)庫(kù)查詢。
• 依據(jù)SQL語(yǔ)句的使用頻率來(lái)建立索引，查詢條件字段順序按照聯(lián)合索引的字段順序來(lái)寫(xiě)（從左到右的匹配順序）
• 關(guān)注慢查日志

一、背景說(shuō)明

接著上一篇Grpc性能調(diào)優(yōu)的文章繼續(xù)寫(xiě)，我們這次壓測(cè)的是一個(gè)查詢用戶群組列表信息的接口，該接口里需要查詢某個(gè)用戶的所有群組信息，包括每個(gè)群組的名稱、成員數(shù)量等。
經(jīng)過(guò)之前對(duì)業(yè)務(wù)機(jī)器的JVM參數(shù)等進(jìn)行優(yōu)化后，現(xiàn)在已經(jīng)不存在業(yè)務(wù)機(jī)器的頻繁GC、CPU占用率過(guò)高、TPS上不去等問(wèn)題了。但是我們遇到了兩個(gè)新問(wèn)題：在業(yè)務(wù)接口并發(fā)50、TPS600左右時(shí)，壓測(cè)接口出現(xiàn)了超時(shí)錯(cuò)誤，而且數(shù)據(jù)庫(kù)服務(wù)器CPU占用率超過(guò)了93%！

成都創(chuàng)新互聯(lián)公司網(wǎng)站建設(shè)公司是一家服務(wù)多年做網(wǎng)站建設(shè)策劃設(shè)計(jì)制作的公司,為廣大用戶提供了網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作，成都網(wǎng)站設(shè)計(jì)，廣告投放平臺(tái)，成都做網(wǎng)站選成都創(chuàng)新互聯(lián)公司，貼合企業(yè)需求，高性價(jià)比，滿足客戶不同層次的需求一站式服務(wù)歡迎致電。二、測(cè)試過(guò)程

數(shù)據(jù)準(zhǔn)備：

• t_info_group群組表：
  

• t_info_group_member群組成員表：
  

數(shù)據(jù)約定：測(cè)試數(shù)據(jù)中，每個(gè)群組里面有3000個(gè)成員；每個(gè)成員有20個(gè)群組。

1、第一次壓測(cè)

我們首先進(jìn)行一次摸底測(cè)試，使用兩臺(tái)壓測(cè)機(jī)器共同發(fā)起300個(gè)并發(fā)，持續(xù)壓測(cè)2分鐘。（如果只用一臺(tái)壓測(cè)機(jī)發(fā)起300個(gè)并發(fā)，會(huì)由于機(jī)器端口受限，TPS超過(guò)5W后不能發(fā)起新請(qǐng)求，壓測(cè)機(jī)將報(bào)錯(cuò)）
（1）數(shù)據(jù)庫(kù)連接池配置
c3p0.initialPoolSize=15
c3p0.minPoolSize=15
c3p0.acquireIncrement=10
c3p0.maxPoolSize=32
（2）數(shù)據(jù)庫(kù)慢查日志、數(shù)據(jù)庫(kù)服務(wù)器監(jiān)控指標(biāo)

top命令顯示CPU使用率超過(guò)了91%，而且慢查日志一直在刷！
通過(guò)分析慢查日志里面的SQL語(yǔ)句，以及對(duì)應(yīng)的數(shù)據(jù)庫(kù)表，發(fā)現(xiàn)查詢語(yǔ)句中有“where a=.. and b=.. and c=..”，但是有一個(gè)聯(lián)合索引的字段是“a, c"。根據(jù)聯(lián)合索引的匹配規(guī)則，這條sql用不了索引，導(dǎo)致慢查。經(jīng)過(guò)將索引更換成"a, b, c"，單條sql查詢效率提高一倍。

2、第二次壓測(cè)

使用兩臺(tái)壓測(cè)機(jī)器共同發(fā)起50個(gè)并發(fā)，持續(xù)壓測(cè)2分鐘。
（1）其他配置均不做改變。
（2）數(shù)據(jù)庫(kù)慢查日志、數(shù)據(jù)庫(kù)服務(wù)器監(jiān)控指標(biāo)
經(jīng)過(guò)上述調(diào)整，慢查日志沒(méi)有了，但是CPU使用率依然還是超過(guò)了90%。這是一個(gè)不能容忍的數(shù)據(jù)，如果并發(fā)繼續(xù)提高，數(shù)據(jù)庫(kù)服務(wù)器很快將被撐爆。

由于其他涉及到sql查詢的接口在壓測(cè)時(shí)都沒(méi)有出現(xiàn)過(guò)CPU占用率這么高的情況，所以我們排除了數(shù)據(jù)庫(kù)服務(wù)器安裝配置的問(wèn)題。
我們開(kāi)始思考出現(xiàn)問(wèn)題的可能原因：

• 數(shù)據(jù)庫(kù)連接池等參數(shù)配置有缺陷，導(dǎo)致連接池不夠用，一直在建立新的連接。
• 業(yè)務(wù)代碼邏輯可能存在缺陷，導(dǎo)致查詢語(yǔ)句消耗過(guò)多IO或內(nèi)存資源。
• 并發(fā)數(shù)量高，數(shù)據(jù)庫(kù)服務(wù)器CPU正常就需要占用這么多。（這是最無(wú)奈最不想接受的結(jié)局）
  我們先查看show processlist; 的結(jié)果，發(fā)現(xiàn)有很多連接其實(shí)是空閑的，也就是說(shuō)目前的連接池已經(jīng)夠用了。
  而當(dāng)我們把連接池嘗試放大，配置改為：
  c3p0.initialPoolSize=20
  c3p0.minPoolSize=20
  c3p0.acquireIncrement=20
  c3p0.maxPoolSize=128
  在這個(gè)配置下，對(duì)CPU的占用率沒(méi)有絲毫影響。那么排除連接池配置的問(wèn)題。
  那有沒(méi)有可能是在當(dāng)前并發(fā)量條件下，就需要消耗這么多的CPU呢？
  我們對(duì)比了另一個(gè)涉及到SQL查詢的接口，在60個(gè)并發(fā)下，那個(gè)接口的TPS達(dá)到了1100，而現(xiàn)在壓測(cè)的接口，TPS只有600。那么在同等并發(fā)下，相同的查詢語(yǔ)句復(fù)雜度和查詢結(jié)果數(shù)據(jù)量條件下，現(xiàn)在壓測(cè)的接口不僅TPS低，還占用了過(guò)多的CPU，可能就不是數(shù)據(jù)庫(kù)的問(wèn)題。這就排除了因并發(fā)量高而必然導(dǎo)致CPU占用率超過(guò)90%的假設(shè)。
  我們繼續(xù)看數(shù)據(jù)庫(kù)服務(wù)器的監(jiān)控指標(biāo)，使用阿里的orzdba腳本監(jiān)控MySQL服務(wù)器。
  當(dāng)前壓測(cè)接口的MySQL服務(wù)器指標(biāo)數(shù)據(jù)：
  

對(duì)照接口的指標(biāo)數(shù)據(jù)：

從上述兩個(gè)對(duì)比圖可以看到，當(dāng)前壓測(cè)接口的數(shù)據(jù)庫(kù)QPS高達(dá)3000。對(duì)比數(shù)據(jù)匯總一下，可以看出一些問(wèn)題：
當(dāng)前接口：
并發(fā)：60，TPS：600，數(shù)據(jù)庫(kù)CPU：92%，數(shù)據(jù)庫(kù)QPS：3000
對(duì)照接口：
并發(fā)60，TPS：1000，數(shù)據(jù)庫(kù)CPU：20%，數(shù)據(jù)庫(kù)QPS：1400
當(dāng)前壓測(cè)接口處理更耗時(shí)，可能原因是一次接口業(yè)務(wù)里涉及到了多次數(shù)據(jù)庫(kù)操作。
那么接下來(lái)就是排查業(yè)務(wù)代碼里的數(shù)據(jù)庫(kù)操作了。進(jìn)行code review！
核心業(yè)務(wù)偽代碼：
//查詢用戶的群組列表
List groupList = groupDao.selectGroups(userId);
for(Dto dto:groupList){
//查詢每個(gè)群組的用戶數(shù)，會(huì)循環(huán)20次！
int userNumber = groupDao.getGroupNumber(dto.getAsIong(groupId));
}

這段代碼怎么這么別扭？第一個(gè)查詢已經(jīng)去查詢?nèi)航M信息了，還要用for循環(huán)去遍歷20次統(tǒng)計(jì)每個(gè)群組用戶數(shù)？？
這樣操作的話，一次接口請(qǐng)求將操作很多次的數(shù)據(jù)庫(kù)查詢，并帶來(lái)更多網(wǎng)絡(luò)、IO操作。那么CPU占用率過(guò)高的問(wèn)題很可能是這個(gè)情況導(dǎo)致的。
我們的優(yōu)化措施是優(yōu)化groupDao.selectGroups(userId)對(duì)應(yīng)的查詢語(yǔ)句，使用子查詢返回用戶的群組列表和每個(gè)群組的用戶數(shù)，去掉for循環(huán)。

3、第三次壓測(cè)

使用兩臺(tái)壓測(cè)機(jī)器共同發(fā)起50個(gè)并發(fā)，持續(xù)壓測(cè)2分鐘。
（1）其他配置均不做改變。
（2）數(shù)據(jù)庫(kù)慢查日志、數(shù)據(jù)庫(kù)服務(wù)器監(jiān)控指標(biāo)
數(shù)據(jù)庫(kù)慢查日志沒(méi)有提示慢查SQL語(yǔ)句，數(shù)據(jù)庫(kù)服務(wù)器CPU占用率穩(wěn)定在80%以下，數(shù)據(jù)庫(kù)QPS提高到了近7000！這個(gè)優(yōu)化效果可以說(shuō)是非常的明顯了。

4、留給我的疑問(wèn)

也許有人會(huì)問(wèn)：代碼優(yōu)化后數(shù)據(jù)庫(kù)的QPS比之前更高了，那CPU使用應(yīng)該更多啊，但是為什么數(shù)據(jù)庫(kù)的CPU占用率反而降下來(lái)了呢？這是為什么呢？[這個(gè)問(wèn)題，其實(shí)我也沒(méi)有想明白，有知道原因的朋友歡迎留言討論。]
對(duì)于這個(gè)問(wèn)題，我最近一直在思考，并查閱相關(guān)的資料，突然從接口優(yōu)化前后的性能數(shù)據(jù)上可以看到：優(yōu)化前，業(yè)務(wù)接口的TPS才600，數(shù)據(jù)庫(kù)QPS才3000，而優(yōu)化后的業(yè)務(wù)接口TPS達(dá)到了1100，幾乎翻倍，同時(shí)數(shù)據(jù)庫(kù)QPS達(dá)到了6000，也翻倍了，但數(shù)據(jù)庫(kù)CPU使用率反而降低了。從這幾個(gè)數(shù)據(jù)可以得到這樣一條結(jié)論：接口TPS的增長(zhǎng)，自然導(dǎo)致了數(shù)據(jù)庫(kù)QPS的同級(jí)別增長(zhǎng)，說(shuō)明在優(yōu)化前和優(yōu)化后，數(shù)據(jù)庫(kù)的壓力瓶頸還遠(yuǎn)沒(méi)有達(dá)到，那么數(shù)據(jù)庫(kù)服務(wù)器CPU使用率的飆高，也不是由于SQL處理導(dǎo)致的！
數(shù)據(jù)庫(kù)服務(wù)器的資源消耗，除了SQL執(zhí)行、磁盤(pán)IO，其實(shí)還有網(wǎng)絡(luò)連接。
優(yōu)化前的代碼里，一次TPS會(huì)進(jìn)行多次數(shù)據(jù)庫(kù)連接并提交SQL查詢請(qǐng)求，這個(gè)操作帶來(lái)的網(wǎng)絡(luò)連接消耗也是非?？捎^的，優(yōu)化后的代碼里，一次TPS就進(jìn)行一次數(shù)據(jù)庫(kù)連接并提交SQL查詢請(qǐng)求，資源消耗降低了很多。

• 接口優(yōu)化前：
  并發(fā)：60，TPS：600，數(shù)據(jù)庫(kù)CPU：92%，數(shù)據(jù)庫(kù)QPS：3000
  
  

• 接口優(yōu)化后：
  并發(fā)：60，TPS：1100，數(shù)據(jù)庫(kù)CPU：低于80%，數(shù)據(jù)庫(kù)QPS：6000
  

本文標(biāo)題：壓力測(cè)試過(guò)程中MySQL服務(wù)CPU占用率過(guò)高的問(wèn)題排查思路-創(chuàng)新互聯(lián)
文章位置：http://weahome.cn/article/idppo.html