本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 在 Amazon Aurora PostgreSQL 中管理 TOAST OID 爭用 TOAST （超大型屬性儲存技術） 是一種 PostgreSQL 功能，旨在處理超過一般 8KB 資料庫區塊大小的大型資料值。PostgreSQL 不允許實體資料列跨越多個區塊。區塊大小可做為資料列大小的上限。TOAST 透過將大型欄位值分割為較小的區塊來克服此限制。它將它們分別存放在連結到主資料表的專用 TOAST 資料表中。如需詳細資訊，請參閱 [PostgreSQL TOAST 儲存機制和實作文件](https://www.postgresql.org/docs/current/storage-toast.html)。 **Topics** + [了解 TOAST 操作](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.HowWorks) + [識別效能挑戰](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.PerformanceChallenges) + [建議](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Recommendations) + [監控](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Monitoring) ## 了解 TOAST 操作 TOAST 會執行壓縮，並離線存放大型欄位值。TOAST 會將唯一的 OID （物件識別符） 指派給儲存在 TOAST 資料表中的每個超大型資料區塊。主資料表會將 TOAST 值 ID 和關聯 ID 存放在頁面上，以參考 TOAST 資料表中對應的資料列。這可讓 PostgreSQL 有效率地尋找和管理這些 TOAST 區塊。不過，隨著 TOAST 資料表的成長，系統可能會耗盡可用的 OIDs，導致效能降低和因 OID 耗盡而可能停機。 ### TOAST 中的物件識別符 物件識別符 (OID) 是 PostgreSQL 用於參考資料庫物件的全系統唯一識別符，例如資料表、索引和函數。這些識別符在 PostgreSQL 的內部操作中扮演重要角色，可讓資料庫有效率地尋找和管理物件。 對於具有合格資料集的資料表，PostgreSQL 會指派 OIDs，以唯一識別存放在相關聯 TOAST 資料表中的每個超大型資料區塊。系統會將每個區塊與 建立關聯`chunk_id`，這有助於 PostgreSQL 在 TOAST 資料表中有效率地組織和尋找這些區塊。 ## 識別效能挑戰 PostgreSQL 的 OID 管理倚賴全域 32 位元計數器，以便在產生 40 億個唯一值之後進行包裝。當資料庫叢集共用此計數器時，OID 配置會在 TOAST 操作期間涉及兩個步驟： + 用於**配置的全域計數器** – 全域計數器會跨叢集指派新的 OID。 + **本機搜尋衝突** – TOAST 資料表可確保新的 OID 不會與該特定資料表中已使用的現有 OIDs 衝突。 發生下列情況時，可能會發生效能降低： + TOAST 資料表具有高分段或密集的 OID 用量，導致指派 OID 時發生延遲。 + 系統經常在具有大量使用 TOAST 的高資料流失或寬資料表的環境中配置和重複使用 OIDs。 如需詳細資訊，請參閱 [PostgreSQL TOAST 資料表大小限制和 OID 配置文件](https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit)： 全域計數器會產生 OIDs 並包圍每 40 億個值，因此系統偶爾會再次產生已使用的值。PostgreSQL 偵測到 ，並使用下一個 OID 再次嘗試。如果使用過的 OID 值執行很長，且在 TOAST 資料表中沒有間隙，則可能會發生緩慢的 INSERT。隨著 OID 空間填滿，這些挑戰變得更加明顯，導致插入和更新速度變慢。 ### 識別問題 + 簡單`INSERT`陳述式需要比平常更長的時間，方式不一致且隨機。 + 只有涉及 TOAST 操作的 `INSERT`和 `UPDATE`陳述式才會發生延遲。 + 當系統難以在 TOAST 資料表中尋找可用的 OIDs時，以下日誌項目會出現在 PostgreSQL 日誌中： ``` LOG: still searching for an unused OID in relation "pg_toast_20815" DETAIL: OID candidates have been checked 1000000 times, but no unused OID has been found yet. ``` + 績效詳情指出與 `LWLock:buffer_io`和`LWLock:OidGenLock`等待事件相關聯的大量平均作用中工作階段 (AAS)。 您可以執行下列 SQL 查詢，以識別具有等待事件的長期執行 INSERT 交易： ``` SELECT datname AS database_name, usename AS database_user, pid, now() - pg_stat_activity.xact_start AS transaction_duration, concat(wait_event_type, ':', wait_event) AS wait_event, substr(query, 1, 30) AS TRANSACTION, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > INTERVAL '60 seconds' AND state IN ('active', 'idle in transaction', 'idle in transaction (aborted)', 'fastpath function call', 'disabled') AND pid <> pg_backend_pid() AND lower(query) LIKE '%insert%' ORDER BY transaction_duration DESC; ``` 顯示具有延長等待時間的 INSERT 操作的查詢結果範例： ``` database_name | database_user | pid | transaction_duration | wait_event | transaction | state ---------------+-----------------+-------+----------------------+---------------------+--------------------------------+-------- postgres | db_admin_user| 70965 | 00:10:19.484061 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 69878 | 00:06:14.976037 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 68937 | 00:05:13.942847 | : | INSERT INTO "products" (......... | active ``` ### 隔離問題 + **測試小型插入** – 插入小於`toast_tuple_target`閾值的記錄。請記住，壓縮會在 TOAST 儲存體之前套用。如果此操作沒有效能問題，則問題與 TOAST 操作有關。 + **測試新資料表** – 建立具有相同結構的新資料表，並插入大於 的記錄`toast_tuple_target`。如果這樣做沒有問題，問題會本地化為原始資料表的 OID 配置。 ## 建議 下列方法有助於解決 TOAST OID 爭用問題。 + **資料清理和封存** – 檢閱和刪除任何過時或不必要的資料，以釋出 OIDs 以供日後使用，或封存資料。考量下列限制： + 可擴展性有限，因為未來清理可能並不總是可行。 + 可能長時間執行的 VACUUM 操作，以移除產生的無效元組。 + **寫入新資料表** – 為未來的插入建立新的資料表，並使用`UNION ALL`檢視來合併查詢的舊資料和新資料。此檢視會顯示來自舊資料表和新資料表的合併資料，允許查詢以單一資料表的形式存取它們。考量下列限制： + 舊資料表的更新仍可能導致 OID 耗盡。 + **分割區或碎片** – 分割資料表或碎片資料，以獲得更佳的可擴展性和效能。考量下列限制： + 查詢邏輯和維護的複雜性更高，可能需要應用程式變更才能正確處理分割的資料。 ## 監控 ### 使用系統資料表 您可以使用 PostgreSQL 的系統資料表來監控 OID 用量的成長。 **警告** 根據 TOAST 資料表中的 OIDs 數量，可能需要一些時間才能完成。建議您在非上班時間排定監控，將影響降至最低。 下列匿名區塊會計算每個 TOAST 資料表中使用的不同 OIDs 數目，並顯示父資料表資訊： ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the number of distinct used OIDs (chunk IDs) from the TOAST table EXECUTE 'SELECT COUNT(DISTINCT chunk_id) FROM ' || r.toast_table INTO o; -- If there are used OIDs, find the associated parent table and its schema IF o <> 0 THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Number of used OIDs: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` 依 TOAST 資料表顯示 OID 用量統計資料的範例輸出： ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Number of used OIDs: 45,623,317 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Number of used OIDs: 10,000 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Number of used OIDs: 1,000,000 DO ``` 下列匿名區塊會擷取每個非空白 TOAST 資料表的指派 OID 上限： ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the max(chunk_id) from the TOAST table EXECUTE 'SELECT max(chunk_id) FROM ' || r.toast_table INTO o; -- If there's at least one TOASTed chunk, find the associated parent table and its schema IF o IS NOT NULL THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Max chunk_id: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` 顯示 TOAST 資料表最大區塊 IDs的範例輸出： ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Max chunk_id: 45,639,907 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Max chunk_id: 45,649,929 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Max chunk_id: 46,649,935 DO ``` ### 使用 Performance Insights 在需要指派新物件識別符 (OIDs) 的操作期間，等待事件`LWLock:buffer_io`和 `LWLock:OidGenLock`會出現在績效詳情中。這些事件的高平均作用中工作階段 (AAS) 通常會在 OID 指派和資源管理期間指向爭用。這在具有高資料流失、大量大數據用量或頻繁建立物件的環境中特別常見。 #### LWLock：buffer\$1io `LWLock:buffer_io` 是當 PostgreSQL 工作階段正在等待共用緩衝區上的 I/O 操作完成時發生的等待事件。當資料庫將資料從磁碟讀取到記憶體，或將修改過的頁面從記憶體寫入磁碟時，通常會發生這種情況。`BufferIO` 等待事件會防止多個程序在 I/O 操作進行時存取或修改相同的緩衝區，以確保一致性。此等待事件的頻繁出現可能表示資料庫工作負載中的磁碟瓶頸或過多的 I/O 活動。 在 TOAST 操作期間： + PostgreSQL 會為大型物件配置 OIDs，並透過掃描 TOAST 資料表的索引來確保其唯一性。 + 大型 TOAST 索引可能需要存取多個頁面來驗證 OID 唯一性。這會導致磁碟 I/O 增加，特別是當緩衝集區無法快取所有必要的頁面時。 索引的大小會直接影響在這些操作期間需要存取的緩衝區頁面數目。即使索引未膨脹，其透薄大小也會增加緩衝區 I/O，特別是在高並行或高流失環境中。如需詳細資訊，請參閱 [LWLock：BufferIO 等待事件疑難排解指南](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockbufferio.html)。 #### LWLock:OidGenLock `OidGenLock` 是當 PostgreSQL 工作階段正在等待配置新的物件識別符 (OID) 時發生的等待事件。此鎖定可確保循序且安全地產生 OIDs，一次只允許一個程序產生 OIDs。 在 TOAST 操作期間： + **TOAST 資料表中區塊的 OID 配置** – PostgreSQL 會將 OIDs 指派給 TOAST 資料表中的區塊。每個 OID 都必須是唯一的，以防止系統目錄中的衝突。 + **高並行** – 由於對 OID 產生器的存取是循序的，因此當多個工作階段同時建立需要 OIDs物件時，`OidGenLock`可能會發生 的爭用。這會增加工作階段等待 OID 配置完成的可能性。 + **相依於系統目錄存取** – 配置 OIDs 需要更新共用系統目錄資料表，例如 `pg_class`和 `pg_type`。如果這些資料表遇到繁重的活動 （由於頻繁的 DDL 操作），可能會增加 的鎖定爭用`OidGenLock`。 + **高 OID 分配需求** – 具有大型資料記錄的 TOAST 繁重工作負載需要持續的 OID 分配，從而增加爭用。 增加 OID 爭用的其他因素： + **頻繁建立物件** – 經常建立和捨棄物件的工作負載，例如暫存資料表，在全域 OID 計數器上擴增爭用。 + **全域計數器鎖定** – 全域 OID 計數器會循序存取，以確保唯一性，在高並行環境中建立單一爭用點。