目錄
- mysql語句性能優(yōu)化
- c3P0架構(gòu)簡介
- c3P0連接池優(yōu)化
- 網(wǎng)絡(luò)抓包優(yōu)化
- cpu打滿問題
mysql語句性能優(yōu)化
背景
- 業(yè)務(wù)場景有四張表需要一起查，并且要查出所有數(shù)據(jù)，沒法用應(yīng)用側(cè)關(guān)聯(lián)，這張表索引都相對比較合理。這里只能用join或者in來處理關(guān)聯(lián)關(guān)系，這是個定時任務(wù)查詢，允許sql查詢基于業(yè)務(wù)結(jié)果慢一點(diǎn)
最后優(yōu)化后sql
- 最開始先只join兩張表，IN查詢另外一張表，IN所需要的數(shù)據(jù)量很大導(dǎo)致查詢結(jié)果很慢，第一步優(yōu)化就是將IN改成JOIN，因?yàn)镴OIN能用JOIN的Buffer
- 后面用三次join， 使用客戶端查結(jié)果很快，但是這邊有個點(diǎn)要注意，業(yè)務(wù)是要返回全部數(shù)據(jù)，用客戶端一般有默認(rèn)限制返回數(shù)量比如1k，所以要加大limit測試，這個sql最后返回了3萬左右的數(shù)據(jù)。所以還需要繼續(xù)優(yōu)化
from A a
left join C c on a.xxx = c.xxx
left join D d on a.xxxx = d.id
left join B b on x.xxx = b.name
- 通過不斷調(diào)整第一層select的join表最終發(fā)現(xiàn)下面sql性能最好，數(shù)據(jù)盡可能的小, JOIN太多笛卡爾積不好，最后一個join的是比較小的表，弄出去變成新的一層select性能提升很多從200s -> 15s,15s看著也不少，但這個業(yè)務(wù)場景下能接受的，跟我們普遍的高并發(fā)后端查詢場景是不一樣
select x.xxxx
from (select xxxx
from A a
left join C c on a.xxx = c.xxx
left join D d on a.xxxx = d.id
where a.XXX = '0'
and a.AREA IN ('東北', '華東', '....')
and a.XXXX NOT IN (XXX十多個)
and d.xxx = '0'
and (FIND_IN_SET(1, a.xx三個))
and a.xxx NOT LIKE '%xxx not like也有3個%') as x
left join B b on x.xxx = b.name
where FIND_IN_SET(0, b.xxxx) OR (FIND_IN_SET(3, b.xxx) and x.xxx IN (子查詢));
c3P0架構(gòu)簡介
- C3P0連接池的架構(gòu)主要包含以下幾個部分：
- 數(shù)據(jù)源（Data Source）：C3P0的數(shù)據(jù)源是應(yīng)用程序直接使用的對象，用于獲取數(shù)據(jù)庫連接。為了適應(yīng)不同的應(yīng)用需求，C3P0提供了多種數(shù)據(jù)源實(shí)現(xiàn)
- 連接池管理器（Pool Manager）：負(fù)責(zé)管理和控制連接池的生命周期，包括創(chuàng)建、銷毀連接，以及連接的獲取和歸還
- 連接池（Connection Pool）：這是C3P0的核心，包含一組預(yù)先創(chuàng)建的數(shù)據(jù)庫連接。應(yīng)用程序從連接池中獲取連接，使用完后歸還給連接池
- 連接測試器（Connection Tester）：用于定期檢測連接池中的連接是否有效，無效的連接會被移除并建立新的連接
- 配置管理器（Configuration Manager）：提供了一系列參數(shù)用于配置數(shù)據(jù)源和連接池，如最大/最小連接數(shù)、連接超時時間、連接測試頻率等
c3P0連接池優(yōu)化
- 排查問題的業(yè)務(wù)特征是有時有任務(wù)來，有時沒有，mysql用的是5.5.59版本，c3P0版本也很老時16年的。這段時間一直都有這個問題，獲取不到連接，看幾個數(shù)據(jù)庫連接telnet看網(wǎng)絡(luò)都正常，服務(wù)端看著沒啥問題，因?yàn)椴皇谴竺娣e出現(xiàn)，只是間斷的出現(xiàn)，其他功能也正常，時不時有幾個小功能爆出來，頻率不高。這里并不是所有最大最小連接都要設(shè)置成一樣有高峰低谷期，除非都非常平穩(wěn)都要那么多
-
數(shù)據(jù)庫連接沒有設(shè)置minPoolSize，只設(shè)置了initialPoolSize 10 maxPoolSize設(shè)置80，業(yè)務(wù)特性一段時間后導(dǎo)致連接變成minPoolSize 默認(rèn)值3, 業(yè)務(wù)來時很多地方就要創(chuàng)建連接，數(shù)據(jù)庫連接池如果在沒超過最大連接時會去創(chuàng)建連接，當(dāng)在規(guī)定時間內(nèi)能獲取連接則放入連接池并返回連接池中數(shù)據(jù)，默認(rèn)值是dump堆然后觀察快照得到的結(jié)果，這邊通過線上dump堆來查看c3P0配置是為了排除配置不一致導(dǎo)致的問題，dump下來有6個G，直接從機(jī)器sc下下來經(jīng)常斷，所以這邊有個小技巧先tar壓縮下變成不到1個G，然后再win10解壓縮，本地mat解析hprof
2.1 查看默認(rèn)值最小連接3的步驟，Histogram搜JDBCTemplate代碼里面是這個類作為數(shù)據(jù)庫連接配置類(本質(zhì)上是C3P0)
Histogram搜JDBCTemplate
2.2 右鍵list objects -> withoutgoings
目前解決方案設(shè)置最小連接池大小解決，就不要去獲取連接了，這個獲取不到數(shù)據(jù)庫連接問題得到比較大的解決
還有個參數(shù)preferredTestQuery 舊版本C3P0有bug, 不同數(shù)據(jù)庫版本不同，使用的檢測也不一樣，再加上mysql版本較老，有時候發(fā)現(xiàn)select 1在mysql服務(wù)的processlist里面沒執(zhí)行，導(dǎo)致等待結(jié)果時間很長，如果放到客戶端去就會導(dǎo)致數(shù)據(jù)庫連接獲取超時而報連接池獲取不到數(shù)據(jù)，之前代碼還有個參數(shù)preferredTestQuery設(shè)置為Null, 這邊設(shè)置 select 1 from dual, 做個優(yōu)化觀察效果良好。 對于之前的版本c3p0默認(rèn)在連接關(guān)聯(lián)的DatabaseMetaData上使用getTables()方法,這個方法非常穩(wěn)定并且適用于不同的數(shù)據(jù)庫,但是比正常的query慢很多,使用它做連接測試可能會嚴(yán)重影響連接池性能。c3p0在0.9.5版本以上,建議放手讓driver進(jìn)行連接測試,c3p0會首先嘗試使用JDBC4引入的isValid()方法進(jìn)行測試,這個方法速度快并且可靠。這邊是檢測連接是否有效，無效則銷毀連接重新創(chuàng)建連接
強(qiáng)調(diào)下dump下載過程這邊是在有問題的java客戶端dump hprof有七個G左右，然后gzip壓縮變不到1個G，使用xshell下到本地，本地mat分析dump快照調(diào)度參數(shù)，注意xshell限制每次傳輸4個G, 之前使用tabby terminal傳輸一直有bug 提示hprof文件損壞，或者是因?yàn)闆]gzip先壓縮再傳輸把，畢竟tabby termianl沒限制傳輸大小
網(wǎng)絡(luò)抓包優(yōu)化
- 發(fā)現(xiàn)了個新問題報無法獲取連接C3P0 BasicResourcePool.prelimCheckoutResource在?；顧z測時報錯了，大概率是網(wǎng)絡(luò)問題，通過tcpdump抓包發(fā)現(xiàn)tcp握手時沒響應(yīng)，幾臺客戶端都是這樣，于是換了臺機(jī)器，之前機(jī)器年紀(jì)也大了有些硬件問題，觀察了一段時間問題解決
-
同時也發(fā)現(xiàn)域名解析也有點(diǎn)問題，換了一臺namseserver
域名解析.png - 抓包用eth4是網(wǎng)卡，然后本地wireshark查看
tcpdump -i eth4 -s 0 -w xxx.pcap host 域名
cpu打滿問題
- 現(xiàn)象，mysql所在機(jī)器cpu全部打滿了，32C的都打滿了，mysql機(jī)器有跟其他腳本等混部署
-
mysql占比不大，但發(fā)現(xiàn)超級多的sh在跑，積少成多
mysql占比不大.png -
有個腳本有個奇怪的邏輯，經(jīng)過確定這個腳本可以停掉，也不知道咋觸發(fā)的
無限循環(huán).png - 批量停止命令,xxx腳本名稱
ps aux|grep xxx|grep -v "grep"|grep -v "xxx"|awk '{print $2}'|xargs kil
