# 管理 Amazon RDS for PostgreSQL 中的 TOAST OID 争用 TOAST（超大属性存储技术）是一项 PostgreSQL 功能，旨在处理超过典型 8KB 数据库块大小的大数据值。PostgreSQL 不允许物理行跨越多个块。块大小是行大小的上限。TOAST 通过将较大的字段值拆分为较小的块来克服这一限制。它将这些块单独存储在链接到主表的专用 TOAST 表中。有关更多信息，请参阅 [PostgreSQL TOAST storage mechanism and implementation documentation](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 为存储在 TOAST 表中的每个超大数据块分配一个唯一的 OID（对象标识符）。主表在页面上存储 TOAST 值的 ID 和关系 ID，以引用 TOAST 表中相应的行。这使得 PostgreSQL 能够高效地定位和管理这些 TOAST 数据块。然而，随着 TOAST 表的增长，系统面临耗尽可用 OID 的风险，进而导致性能下降，甚至因 OID 耗尽而引发潜在的停机。 ### TOAST 中的对象标识符 对象标识符（OID）是 PostgreSQL 中用于引用数据库对象（如表、索引和函数）的一种系统范围内唯一标识符。这些标识符在 PostgreSQL 的内部操作中发挥着关键作用，使数据库能够高效地定位和管理对象。 对于符合 TOAST 条件的数据表，PostgreSQL 会为存储在相关 TOAST 表中的每个超大数据块分配一个 OID，用于唯一标识该数据块。系统还会为每个数据块关联一个 `chunk_id`，以便 PostgreSQL 在 TOAST 表中高效地组织和定位这些数据块。 ## 识别性能方面的挑战 PostgreSQL 的 OID 管理依赖一个全局 32 位计数器，因此在生成 40 亿个唯一值后会发生循环回绕。尽管整个数据库集群共享这一计数器，但在 TOAST 操作中，OID 分配过程包括以下两步： + **全局计数器分配** - 全局计数器在集群范围内分配一个新的 OID。 + **本地冲突检查** – TOAST 表负责确保新分配的 OID 不与该特定表中已有的 OID 冲突。 在以下情况下，可能会出现性能下降问题： + TOAST 表存在高度碎片或 OID 使用密集，导致 OID 分配过程延迟。 + 在高数据变更率或广表结构中频繁使用 TOAST 时，系统将不断分配和重用 OID，从而加剧 OID 管理的负担。 有关更多信息，请参阅 [PostgreSQL TOAST table size limits and OID allocation documentation](https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit)： 全局计数器负责生成 OID，并且每生成 40 亿个值后就会循环回绕，因此系统偶尔会生成一个已经使用过的值。PostgreSQL 会检测到这种情况，并自动尝试使用下一个 OID。如果 TOAST 表中存在一大段连续使用的 OID 而没有空隙，就可能导致 INSERT 操作变慢。随着 OID 空间逐渐填满，这些问题会变得更加明显，导致插入和更新操作的性能下降。 ### 识别问题 + 简单的 `INSERT` 语句会随机出现明显变慢的情况，时快时慢，缺乏一致性。 + 延迟仅发生在涉及 TOAST 操作的 `INSERT` 和 `UPDATE` 语句上。 + 当 PostgreSQL 在 TOAST 表中难以找到可用 OID 时，日志中会出现如下条目： ``` 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 争用问题。 + **数据清理与归档** – 审查并删除任何过期或不必要的数据，以释放 OID 供将来使用，或者将数据归档。请考虑以下限制： + 可扩展性有限，因为将来并非总是能够执行清理操作。 + 可能需要执行耗时的 VACUUM 操作，以清理由此产生的死元组。 + **写入新表** - 为将来的插入操作创建新表，并使用 `UNION ALL` 视图将旧表和新表的数据合并用于查询。该视图呈现来自旧表和新表的组合数据，使查询能够像访问单张表一样访问数据。请考虑以下限制： + 对旧表的更新仍可能导致 OID 耗尽问题。 + **分区或分片** - 对表进行分区或对数据进行分片，以提升可扩展性和性能。请考虑以下限制： + 查询逻辑和维护复杂度增加，可能需要对应用程序进行修改，以正确处理分区或分片数据。 ## 监控 ### 使用系统表 您可以使用 PostgreSQL 的系统表来监控 OID 使用量的增长情况。 **警告** 根据 TOAST 表中 OID 的数量，此操作可能需要一定时间才能完成。建议在非工作时间安排监控，以将对业务的影响降到最低。 以下匿名块统计每个 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 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 表的最大块 ID 的输出示例： ``` 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 ``` ### 使用性能详情 在需要分配新的对象标识符（OID）的操作过程中，性能详情中会显示 `LWLock:buffer_io` 和 `LWLock:OidGenLock` 等待事件。这些等待事件的平均活跃会话（AAS）较高通常表明在 OID 分配和资源管理过程中存在争用。这种情况在数据变更频繁、大型数据使用广泛或对象创建频繁的环境中尤为常见。 #### LWLock:buffer\$1io `LWLock:buffer_io` 是一种等待事件，当 PostgreSQL 会话在等待共享缓冲区的 I/O 操作完成时会发生。通常在以下情况下出现：数据库从磁盘读取数据到内存中将修改后的页面从内存写回磁盘。`BufferIO` 等待事件通过在 I/O 操作进行时阻止多个进程访问或修改同一缓冲区，从而确保数据一致性。该等待事件频繁出现可能表明数据库工作负载中存在磁盘瓶颈或过高的 I/O 活动。 在 TOAST 操作期间： + PostgreSQL 会为大型对象分配 OID，并通过扫描 TOAST 表的索引来确保其唯一性。 + 大型 TOAST 索引可能需要访问多个页面以验证 OID 的唯一性。当缓冲池无法缓存所有所需页面时，会导致磁盘 I/O 增加。 索引的大小直接影响在这些操作中需要访问的缓冲区页面数量。即使索引本身未膨胀，其体积也可能增加缓冲 I/O，尤其在高并发或数据变更频繁的环境中。有关更多信息，请参阅 [LWLock:BufferIO wait event troubleshooting guide](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockbufferio.html)。 #### LWLock:OidGenLock `OidGenLock` 是一种等待事件，当 PostgreSQL 会话在等待分配新的对象标识符（OID）时发生。该锁确保 OID 按顺序且安全地生成，每次仅允许一个进程生成 OID。 在 TOAST 操作期间： + **TOAST 表中数据块的 OID 分配** – PostgreSQL 在管理大数据记录时，为 TOAST 表中的数据块分配 OID。每个 OID 必须唯一，以避免系统目录中发生冲突。 + **高并发** – 由于访问 OID 生成器是顺序进行的，当多个会话同时创建需要 OID 的对象时，可能会发生 `OidGenLock` 争用，增加会话等待 OID 分配完成的概率。 + **依赖系统目录访问** – 分配 OID 需要更新共享系统目录表，如 `pg_class` 和 `pg_type`。如果这些表活动频繁（例如频繁执行 DDL 操作），会加剧 `OidGenLock` 的锁争用。 + **高 OID 分配需求** – 在 TOAST 工作负载中，处理大型数据记录需要持续分配 OID，从而增加争用。 额外导致 OID 争用的因素： + **频繁创建对象** – 经常创建和删除对象（例如临时表）的工作负载，会放大全局 OID 计数器的争用。 + **全局计数器锁定** – 为确保唯一性，全局 OID 计数器采用串行访问机制，这在高并发环境中会形成单一争用点。