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