PG遇到長(zhǎng)事務(wù)案例分享
點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！
某年某月某日凌晨12點(diǎn)，業(yè)務(wù)反映執(zhí)行查詢SQL慢，單條SQL查詢1000條數(shù)據(jù)需要幾十秒，并且存在3000多個(gè)連接。
1. 首先我們觀察了主機(jī)的情況，發(fā)現(xiàn)主機(jī)的util%達(dá)到了90％。可是我們隨后從我們的主機(jī)大神處了解到，對(duì)于FLASH卡，util的統(tǒng)計(jì)并不準(zhǔn)確，util的統(tǒng)計(jì)只適用于HDD的磁盤，而FLASH卡可以突破100%。于是我們放棄了對(duì)磁盤IO問(wèn)題的懷疑。2. 查看了一下pg_stat_activity的情況，發(fā)現(xiàn)有一個(gè)執(zhí)行了4個(gè)多小時(shí)的進(jìn)程。SELECT PID,NOW()-QUERY_START AS TIME ,
WAIT_EVENT_TYPE,WAIT_EVENT,STATE FROM PG_STAT_ACTIVITY WHERE
STATE=ACTIVE ORDER BY TIME DESC;
LWLock | SubtransControlLock | active
| 17****1513 | 16****4053 | select * from
F_PETRI_INTERFACE($1,$2,$3,$4,$5,$6) as result
咨詢到業(yè)務(wù)，屬于業(yè)務(wù)的一個(gè)函數(shù)調(diào)用，可以殺掉。殺掉之后，明顯庫(kù)快了不少。我們看到活躍進(jìn)程只有77個(gè)了。到這里我們就以為事情結(jié)束了，并且我們忽略了一個(gè)最大的問(wèn)題，就是出現(xiàn)在pg_stat_activity中的大量的subtran 3. 過(guò)了30分鐘之后，業(yè)務(wù)反映，查詢有變慢了，這里發(fā)現(xiàn)查詢只對(duì)一個(gè)schema下面的一個(gè)表慢。于是我們找到對(duì)應(yīng)的表進(jìn)行了查詢，發(fā)現(xiàn)確實(shí)非常慢，該表只有1000行數(shù)據(jù)，卻需要14-15秒。于是我們對(duì)表進(jìn)行了表的vacuum full table。在我們完成vacuum full table之后，查詢依然沒(méi)有變化。于是我們查看了表結(jié)構(gòu)，懷疑是PG中的check約束和外鍵約束影響查詢（當(dāng)然，外鍵約束應(yīng)該對(duì)查詢沒(méi)有影響，當(dāng)時(shí)只是懷疑PG的機(jī)制不同）。最后我們禁止了外鍵約束，和check約束，并重建了表，發(fā)現(xiàn)還是非常慢。我們開(kāi)始采用最原始的辦法，一步一步的停止到該庫(kù)的連接。業(yè)務(wù)連接停完后，發(fā)現(xiàn)還是非常慢。發(fā)現(xiàn)等待事件還是非常多。但此時(shí)我們依然關(guān)注在了ClientRead上面，認(rèn)為是慢查詢導(dǎo)致的，知道業(yè)務(wù)連接停完之后，我們才想起來(lái)了一個(gè)關(guān)鍵的因素，那就是到PG的數(shù)據(jù)匯聚同步。停掉同步之后，數(shù)據(jù)庫(kù)立馬快了起來(lái)，原本需要14S查詢的表，只需要幾毫秒就能夠完成查詢。到這里我們陷入了思考。于是我們注意到了subtran以及subtransControllock，懷疑和同步的savepoint機(jī)制有關(guān)系。【同步工具到PG庫(kù)初期，出現(xiàn)了數(shù)據(jù)丟失的問(wèn)題，發(fā)現(xiàn)是因?yàn)镻OSTGRESQL的機(jī)制不同，在PG的事務(wù)中，若遇到?jīng)_突，client仍然能夠commit，但PG會(huì)將事務(wù)回滾，導(dǎo)致部分事務(wù)丟失，為了解決這一個(gè)問(wèn)題，我們采用了savepoint的機(jī)制，但savepoint會(huì)產(chǎn)生子事務(wù)。】于是我們展開(kāi)了對(duì)savepoint、subtran、subtransControlLock的研究。
第二天，第三天我們對(duì)當(dāng)時(shí)的情況進(jìn)行了復(fù)現(xiàn)。發(fā)現(xiàn)非常的奇特，當(dāng)開(kāi)啟INTF任務(wù)組的時(shí)候，PG庫(kù)中馬上出現(xiàn)大量的subtran、subtransControlLock等待事件，反應(yīng)變慢。但開(kāi)啟其他任務(wù)組的時(shí)候，對(duì)數(shù)據(jù)庫(kù)似乎并沒(méi)有影響。##查詢PG等待事件 select backend_type,now()-query_start as
time,usename,application_name,
client_addr,client_hostname,client_port,pid,wait_event_type,
wait_event, state,substr(query,0,30) as query from
pg_stat_activity where state = active and query not like
autovacuum% and usename <>repl and wait_event is not
null order by time desc;
于是我們觀察到，INTF用戶的執(zhí)行SQL全是對(duì)同一張表的update。 于是我們懷疑是在這個(gè)表上有鎖的存在，查看了該表的表結(jié)構(gòu)，發(fā)現(xiàn)果然該表并沒(méi)有主鍵，但同步的執(zhí)行SQL是以主鍵為條件的UPDATE。LWLock | SubtransControlLock | active | 2050454905 |
2050321843 | update TIF_FEE_BILL set AREA_CODE = $1 ,
SERIALNUMBER = $2 , ORDER_NO = $3 , ACCT_ID = $4 , USER_ID =
$5 , ACCT_ITEM_TYPE = $6 , PAY_CHARGE = $7 , PAY_TIMES = $8 , FEE_DATE = $9 ,
EFF_DATE = $10 , PROC_DATE = $11 , STATE = $12 , NOTES =
$13 , TAX_ITEM_ID = $14 where FEE_SERIAL = $15 | client backend
對(duì)于沒(méi)有主鍵的表進(jìn)行update，會(huì)造成全表掃描，若按照mysql的概念，會(huì)產(chǎn)生表鎖。于是我們對(duì)該表加上了主鍵，發(fā)現(xiàn)加上主鍵之后，同步順暢了，性能起來(lái)了，且PG庫(kù)中，也沒(méi)有等待事件了。至此，我們懷疑是因?yàn)橥降谋頉](méi)有主鍵，導(dǎo)致的性能下降，慢SQL導(dǎo)致的subtran累計(jì)，最終造成的PG庫(kù)卡死的情況。第二天，我們又進(jìn)行了一次排查，將原本正常的任務(wù)中的一張頻繁update的表【TIF_TO_SMS_12111】進(jìn)行了drop primary key的操作，再開(kāi)啟同步，我們看到了同樣的情況。最初是因?yàn)閡pdate慢，導(dǎo)致累積了大量的update。隨后，我們看到之前的情況復(fù)現(xiàn)了。至此，我們確定了這個(gè)問(wèn)題出現(xiàn)的原因：同步任務(wù)中的某些表，不存在主鍵，導(dǎo)致同步的SQL進(jìn)行了全表掃描并持有表級(jí)鎖，且同步包含savepoint操作，相互影響，導(dǎo)致子事務(wù)溢出。到了這里，我們基本摸清了現(xiàn)象出現(xiàn)的原因，但深層次的，什么是subtran，什么是save point，我們還需要繼續(xù)了解。在這個(gè)問(wèn)題中，我們看到的是沒(méi)有主鍵的表、savepoint和子事務(wù)將數(shù)據(jù)庫(kù)卡死，那么，我們是否也可以認(rèn)為，當(dāng)數(shù)據(jù)庫(kù)有阻塞，慢SQL多的時(shí)候也會(huì)出現(xiàn)這個(gè)問(wèn)題呢？首先我們來(lái)解釋一下什么是savepoint，為什么我們要使用：我們知道在數(shù)據(jù)庫(kù)中，執(zhí)行DML操作都是一個(gè)事務(wù)，而事務(wù)又分為顯式事務(wù)和隱式事務(wù)，如果我們使用begin，commit，rollback。那這就是一個(gè)顯式開(kāi)啟的事務(wù)，但要注意，在顯示開(kāi)啟的事務(wù)中，使用DDL語(yǔ)句，會(huì)隱式提交。同時(shí)，事務(wù)滿足原子性、一致性、隔離性、持久性的ACID原則，什么是原子性呢？就是指一個(gè)事務(wù)里面的所有操作，要么全都成功，要么全部失敗。MySQL中，事務(wù)的原子性是通過(guò)undo來(lái)保證的【參考連接:https://blog.csdn.net/yu757371316/article/details/81081669】，當(dāng)事務(wù)回滾時(shí)，能夠撤銷事務(wù)中所有已經(jīng)執(zhí)行的語(yǔ)句。在MySQL的事務(wù)中，若遇到?jīng)_突，需要我們顯示提交rollback，如果我們提交commit，則會(huì)保留事務(wù)中其他成功是的操作，在PG的事務(wù)中，若遇到?jīng)_突，PG會(huì)判定此事務(wù)已經(jīng)abort，就算commot也會(huì)自動(dòng)回滾整個(gè)事務(wù)。首先我們來(lái)看在MySQL中事務(wù)的表現(xiàn)，存在u2ser表。表中共有兩行數(shù)據(jù)。可以看到，我們顯示開(kāi)啟了一個(gè)事務(wù)，在事務(wù)中插入了一條（3，‘xieyuxin3’），插入成功，再插入一條沖突數(shù)據(jù)（3，‘xieyuxin4’），提示主鍵沖突，但提交后，發(fā)現(xiàn)事務(wù)中的（3，‘xieyuxin3’）成功保留了。其次，我們來(lái)看下在PG中事務(wù)的表現(xiàn)，存在u2ser表。表中共有兩行數(shù)據(jù)。開(kāi)啟一個(gè)事務(wù)，并往其中插入一條數(shù)據(jù)，并查詢，可以看到插入成功。此時(shí)，如果我們?cè)俨迦胍粭l主鍵沖突的數(shù)據(jù)，就會(huì)出現(xiàn)現(xiàn)象。可以看到，提示報(bào)錯(cuò)了，我們現(xiàn)在來(lái)查看，并提交一下這個(gè)事務(wù)。可以看到，我們?cè)賵?zhí)行操作，被提示當(dāng)前事務(wù)已經(jīng)被aborted了，隨后我們使用commit，發(fā)現(xiàn)事務(wù)被回滾了，我們?cè)俨樵円幌逻@個(gè)表，發(fā)現(xiàn)果然，第一次插入的（3，‘xieyuxin3’）這個(gè)數(shù)據(jù)丟失了。與MySQL不同，PostgreSQL仿佛更加符合原子性本來(lái)的定義，這恐怕也是有人說(shuō)PostgreSQL是學(xué)院派風(fēng)格的原因之一。我們的同步工具正是遇到了這個(gè)問(wèn)題，導(dǎo)致在批量提交的時(shí)候，以為成功提交的數(shù)據(jù)卻被PG回滾，造成了數(shù)據(jù)丟失，所以我們使用了savepoint的方式，來(lái)設(shè)置回滾點(diǎn)，保證批量事務(wù)中正常的事務(wù)可以成功提交。SAVEPOINT操作，允許定義回滾點(diǎn)，用戶可以回滾到任意一個(gè)回滾點(diǎn)。我們發(fā)現(xiàn)，與先前的例子不一樣，使用savepoint操作，允許用戶在沖突后回滾到保存點(diǎn)，提交批量事務(wù)中成功的操作。我們用這個(gè)方式，解決了同步批量數(shù)據(jù)提交的數(shù)據(jù)丟失問(wèn)題。而使用SAVEPOINT name;就是在一個(gè)事務(wù)中開(kāi)啟子事務(wù)。所以我們來(lái)解釋一下什么是子事務(wù)Subtrans：子事務(wù)在長(zhǎng)事務(wù)中有非常大的作用。在PostgreSQL中，事務(wù)中任何一個(gè)錯(cuò)誤都會(huì)中斷整個(gè)事務(wù)，對(duì)于一個(gè)做了很多工作的事務(wù)來(lái)說(shuō)，這是非常煩人的，因?yàn)檫@意味著失去到目前為止完成的所有工作。子事務(wù)可以幫助我們從這種情況中進(jìn)行恢復(fù)。一種是上文提到的savepoint操作，ROLLBACK TO SAVEPOINT回滾一個(gè)舊事務(wù)a的時(shí)候，會(huì)重新開(kāi)始一個(gè)新的子事務(wù)。另一種是使用PL/pgSQL中每次輸入帶有EXCEPTION子句的語(yǔ)句塊時(shí)，都會(huì)開(kāi)啟一個(gè)新的子事務(wù)。當(dāng)離開(kāi)這個(gè)塊的時(shí)候會(huì)提交該子事務(wù)，進(jìn)入異常處理分支的時(shí)候表示回滾。下面引用一篇文章的內(nèi)容《PostgreSQL子事務(wù)及性能分析》：當(dāng)從這樣的數(shù)據(jù)庫(kù)遷移或移植到PostgreSQL中時(shí)，你可能需要在子事務(wù)中包裝每個(gè)語(yǔ)句，以模擬上面的行為。PostgreSQL JDBC驅(qū)動(dòng)程序中有一個(gè)連接參數(shù)“autosave”，如果將其設(shè)置為“always”，就會(huì)在每條語(yǔ)句之前自動(dòng)設(shè)置一個(gè)保存點(diǎn)，方便在失敗的時(shí)候回滾。如下所示，這種轉(zhuǎn)換技巧存在嚴(yán)重的性能瓶頸。每個(gè)子事務(wù)包含一個(gè)事務(wù)或者子事務(wù)（“父親”）。一個(gè)數(shù)據(jù)庫(kù)會(huì)話中有且只能有一個(gè)事務(wù)，但是可以有多個(gè)子事務(wù)。存儲(chǔ)給定子事務(wù)的父信息相關(guān)的（子）事務(wù)信息持久化存儲(chǔ)在數(shù)據(jù)目錄下的pg_subtrans子目錄。由于這些信息隨著包含事務(wù)結(jié)束后立即變成過(guò)去時(shí)，因此不必在關(guān)閉或者崩潰期間保留這些數(shù)據(jù)。快照通過(guò)查詢進(jìn)程數(shù)組（process array）信息來(lái)進(jìn)行初始化，進(jìn)程數(shù)組保存在共享內(nèi)存中并包含有當(dāng)前運(yùn)行進(jìn)程的相關(guān)信息。當(dāng)前，它也包含后端進(jìn)程的當(dāng)前事務(wù)ID，并且每個(gè)會(huì)話最多可以容納64個(gè)未中止的子事務(wù)。如果有超過(guò)64個(gè)這樣的子事務(wù)，那么快照被標(biāo)記為子事務(wù)溢出（suboverflowed）。一個(gè)子溢出的快照不會(huì)包含檢測(cè)可見(jiàn)性的所有數(shù)據(jù)信息，所以PostgreSQL有時(shí)將不得不求助于pg_subtrans。這些頁(yè)緩存在共享內(nèi)存中，但是在perf中可以看到SimpleLruReadPage_ReadOnly函數(shù)排在前面輸出。其它事務(wù)必須更新pg_subtrans后才能注冊(cè)子事務(wù)，可以在perf輸出中看到如何與讀進(jìn)程爭(zhēng)奪輕量級(jí)鎖。XID VXID SubTransactionidXID:事務(wù)和子事務(wù)，分配XID的時(shí)候，如果子事務(wù)需要XID，那么會(huì)先給父事務(wù)分配一個(gè)XID，保障子事務(wù)的XID在父事務(wù)之后。分配事務(wù)號(hào)還需要做的事情是在XID獲取鎖/寫入到pg_subtrans和PG_PROC中。VXID沒(méi)有XID的事務(wù)仍然需要進(jìn)行標(biāo)識(shí),特別是需要持有鎖的時(shí)候.，出于這個(gè)目的,我們分配了"虛擬事務(wù)號(hào)"(即VXID)給每一個(gè)頂層事務(wù).VXIDs由兩個(gè)域組成,后臺(tái)進(jìn)程ID和后臺(tái)進(jìn)程本地計(jì)數(shù)器;VXID=后臺(tái)進(jìn)程PID+后臺(tái)進(jìn)程本地計(jì)數(shù)器這樣的編號(hào)方法不需要共享內(nèi)存爭(zhēng)用就可以進(jìn)行新VXID的分配。為了確保在后臺(tái)進(jìn)程退出后VXID不會(huì)過(guò)快的被使用,PG會(huì)把最后的本地計(jì)數(shù)器值存儲(chǔ)到共享內(nèi)存中，對(duì)于同一個(gè)后臺(tái)進(jìn)程ID,分配先前存儲(chǔ)的計(jì)數(shù)器值給這個(gè)新的后臺(tái)進(jìn)程，在共享內(nèi)存重新初始化后這些計(jì)數(shù)器會(huì)歸零,由于不會(huì)出現(xiàn)落盤,因此這樣的處理沒(méi)有任何問(wèn)題。SubTransactionid在內(nèi)部實(shí)現(xiàn)上,不論子事務(wù)是否擁有XIDs,后臺(tái)進(jìn)程需要標(biāo)識(shí)子事務(wù)的方法;只要父頂級(jí)事務(wù)存在這種需求就好一直存在，因此,產(chǎn)生了SubTransactionId,該字段類似于CommandId,在每次頂層事務(wù)都會(huì)重置的計(jì)數(shù)器，頂層事務(wù)本身的SubTransactionId設(shè)定為1,其他子事務(wù)的ID為2或更大(0保留用于InvalidSubTransactionId)，注意子事務(wù)沒(méi)有VXIDs;它們使用頂層事務(wù)的VXID.其次我們來(lái)了解一下子事務(wù)相關(guān)的等待事件：在pg_stat_activity視圖中，存在wait_event_type（Lock、LWLock、Client等）和wait_event（XidGenLock、SubtransControlLock、subtrans等）。用于描述當(dāng)前等待事件的等待類型和等待事件。 Waiting for I/O a subtransaction buffer。 Waiting to read or update subtransaction information。 SubtransControlLock 表示查詢正在等待 PostgreSQL 將子事務(wù)數(shù)據(jù)從磁盤加載到共享內(nèi)存中。 Waiting to allocate or assign a transaction id。 等待釋放或注冊(cè)一個(gè)事務(wù)ID。 Waiting for SLRU data to reach durable storage during a checkpoint or database shutdown。 在檢查點(diǎn)或數(shù)據(jù)庫(kù)關(guān)閉期間等待 SLRU 數(shù)據(jù)到達(dá)持久存儲(chǔ)。 Waiting for a read of an SLRU page。 Waiting for SLRU data to reach durable storage following a page write. SLRUWrite Waiting for a write of an SLRU page。 等待 SLRU 數(shù)據(jù)在頁(yè)面寫入后到達(dá)持久存儲(chǔ)。SLRUWrite 等待寫入 SLRU 頁(yè)。前面說(shuō)到SubtransControlLock 表示查詢正在等待 PostgreSQL 將子事務(wù)數(shù)據(jù)從磁盤加載到共享內(nèi)存中。為什么需要這么做呢？文章鏈接：https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/例如，當(dāng)客戶端運(yùn)行 SELECT 時(shí)，PostgreSQL 需要決定行的每個(gè)版本（稱為元組）是否在當(dāng)前事務(wù)中實(shí)際可見(jiàn)。元組可能已被刪除或尚未被另一個(gè)事務(wù)提交。由于只有頂級(jí)事務(wù)才能真正提交數(shù)據(jù)，因此 PostgreSQL 需要將子事務(wù) ID（subXID）映射到其父 XID。subXID 到父 XID 的映射存儲(chǔ)在磁盤上的 pg_subtrans 目錄中。由于從磁盤讀取速度較慢，PostgreSQL 為每個(gè)后端進(jìn)程在前面添加了一個(gè)簡(jiǎn)單的最近最少使用 (SLRU) 緩存。如果所需的頁(yè)面已經(jīng)緩存，查找速度會(huì)很快。然而，正如 Laurenz Albe 在他的博客文章中所討論的，如果給定事務(wù)中的活動(dòng)子事務(wù)數(shù)量超過(guò) 64，PostgreSQL 可能需要從磁盤讀取，這是 PostgreSQL 術(shù)語(yǔ)子溢出的條件。把它想象成如果你吃了太多賽百味三明治可能會(huì)有的感覺(jué)。子溢出會(huì)降低性能，因?yàn)檎?Laurenz 所說(shuō)，“其他事務(wù)必須更新 pg_subtrans 以注冊(cè)子事務(wù)，您可以在 perf 輸出中看到它們?nèi)绾闻c讀取器爭(zhēng)奪輕量級(jí)鎖。”這篇文章中，也闡釋了長(zhǎng)事務(wù)和子事務(wù)的影響。在他的博客文章中，Nikolay 將問(wèn)題稱為 Subtrans SLRU 溢出。在繁忙的數(shù)據(jù)庫(kù)中，子事務(wù)日志的大小可能會(huì)增長(zhǎng)到工作集不再適合內(nèi)存的程度。這會(huì)導(dǎo)致大量緩存未命中，進(jìn)而導(dǎo)致大量磁盤 I/O 和 CPU，因?yàn)?PostgreSQL 瘋狂地嘗試從磁盤加載數(shù)據(jù)以跟上所有查找。如前所述，子事務(wù)緩存保存了子 XID 到父 XID 的映射。當(dāng) PostgreSQL 需要查找 subXID 時(shí)，它會(huì)計(jì)算此 ID 將位于哪個(gè)內(nèi)存頁(yè)，然后在內(nèi)存頁(yè)中進(jìn)行線性搜索。如果頁(yè)面不在緩存中，它會(huì)驅(qū)逐一頁(yè)并將所需的頁(yè)面加載到內(nèi)存中。下圖顯示了子事務(wù) SLRU 的內(nèi)存布局。默認(rèn)情況下，每個(gè) SLRU 頁(yè)是一個(gè) 8K 緩沖區(qū)，其中包含 4 字節(jié)的父 XID。這意味著每個(gè)頁(yè)面可以存儲(chǔ) 8192/4 = 2048 個(gè)交易 ID。請(qǐng)注意，每頁(yè)可能存在空白。PostgreSQL 會(huì)根據(jù)需要緩存 XID，因此單個(gè) XID 可以占用整個(gè)頁(yè)面。有 32 (NUM_SUBTRANS_BUFFERS) 個(gè)頁(yè)面，這意味著最多可以在內(nèi)存中存儲(chǔ) 65K 個(gè)事務(wù) ID。Nikolay 演示了在一個(gè)繁忙的系統(tǒng)中，填滿所有 65K 條目需要大約 18 秒。然后性能急劇下降，使數(shù)據(jù)庫(kù)副本無(wú)法使用。令我們驚訝的是，我們的實(shí)驗(yàn)還表明，如果同時(shí)發(fā)生許多寫入，則在長(zhǎng)事務(wù)期間的單個(gè) SAVEPOINT 可能會(huì)引發(fā)此問(wèn)題。也就是說(shuō)，僅僅降低 SAVEPOINT 的頻率是不夠的；我們必須完全消除它們。
本文來(lái)源：IT那活兒（上海新炬王翦團(tuán)隊(duì)）
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://m.specialneedsforspecialkids.com/yun/129586.html
