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