Nikolay Samokhvalov:CREATE TEMP TABLE 的隐藏代价:系统目录膨胀与 LWLock:SInvalWrite

Nikolay Samokhvalov:CREATE TEMP TABLE 的隐藏代价:系统目录膨胀与 LWLock:SInvalWrite

💡 原文英文,约6300词,阅读约需23分钟。
📝

内容提要

文章实测了PostgreSQL频繁执行CREATE TEMP TABLE的隐藏代价:每次建删两列表会写入并删除31行系统目录、创建删除3个文件,导致目录膨胀和死元组;每事务还产生132条共享失效消息,所有会话(含空闲)都须读取。5000个空闲连接下,临时表DDL吞吐从500 TPS降至127–249 TPS,写锁等待占91%。建议将临时表移出热代码,改用CTE或PL/pgSQL变量,并用连接池减少空闲会话。

🔎

延伸解读

临时表DDL的固定目录开销

每次创建并删除一个两列临时表,PostgreSQL都会向系统目录插入并删除31行记录,同时创建和移除3个文件(表、TOAST表及其索引)。这些操作与业务数据量无关,属于固定开销。即使没有空闲会话,每事务建删临时表的吞吐也仅为3783 TPS,而会话级保留临时表可达17866 TPS,CTE则高达86997 TPS。这意味着在热代码中频繁使用临时表,会持续消耗目录写入和文件系统操作资源。

空闲连接放大共享失效队列争用

每个事务建删临时表会产生132条共享失效消息,所有后端(包括空闲会话)都必须读取。空闲会话不主动读取,导致队列槽位无法回收;当队列过半时,写入者发送追赶信号,但一次只能唤醒一个会话。在5000个空闲连接下,临时表DDL吞吐从500 TPS降至127–249 TPS,写锁等待占活跃时间的91%。因此,连接池减少空闲会话,对缓解此类争用至关重要。

替代方案与适用边界

文章建议将临时表移出热代码:CTE适用于多数场景,复杂逻辑可用PL/pgSQL变量(如数组和unnest),避免目录变更。若必须保留临时表,应每会话创建一次并使用ON COMMIT DELETE ROWS,避免每事务建删。但该模式每次提交仍取三个ACCESS EXCLUSIVE锁并发送5条失效消息,且需要直连或会话模式连接池;在事务池模式下,临时表只能随事务创建和删除,回到高开销模式。

监控与调优的实操信号

文章给出三个检查点:在活跃会话历史中观察LWLock:SInvalWrite(PostgreSQL 12及更早为SInvalWriteLock);对比pg_stat_activity中idle会话数与max_connections;定期查询pg_stat_sys_tables中pg_class、pg_attribute等目录表的n_tup_del变化,pg_attribute的删除速率可估算临时表建删频率。降低空闲连接可通过连接池、缩小应用连接池及设置idle_session_timeout实现。

❓

Q&A

频繁在事务里创建临时表对PostgreSQL性能影响有多大?

在16 vCPU、无空闲会话的测试中,每个事务创建并删除临时表的模式吞吐为3783 TPS,而每个会话保留一张临时表可达17866 TPS,使用CTE可达86997 TPS。创建删除临时表本身导致约4.7倍的性能下降,而使用临时表(相比CTE)又带来约4.9倍的下降。

CREATE TEMP TABLE 每次会写入多少系统目录行和文件?

对于两列(id int, v text)的临时表,每次创建会插入31行系统目录记录(包括pg_class、pg_type、pg_attribute、pg_depend等),并创建3个文件(表、TOAST表、TOAST索引)。删除时同样删除31行并移除3个文件。

为什么空闲会话会拖慢临时表DDL的吞吐?

因为每个事务的临时表DDL会产生132条共享失效消息,所有后端(包括空闲会话)都必须读取这些消息。空闲会话不主动读取,导致消息在队列中堆积,写进程需要等待LWLock:SInvalWrite锁。在5000个空闲会话下,临时表DDL吞吐从500 TPS降至127-249 TPS,写锁等待占活跃时间的91%。

如何检测数据库中临时表导致的目录膨胀和锁等待?

可以检查三个信号:1) 在活跃会话历史中查看LWLock:SInvalWrite(PG12及更早为SInvalWriteLock);2) 查询pg_stat_activity中state='idle'的会话数与max_connections对比;3) 间隔一分钟查询pg_stat_sys_tables中pg_class、pg_attribute等表的n_tup_del变化,pg_attribute的删除速率除以19约等于每秒删除的临时表数量。

有哪些替代方案可以避免频繁创建临时表?

推荐将临时表移出热代码。简单场景可用CTE替代;复杂逻辑可用PL/pgSQL变量(如select t from t、array()、unnest())避免目录变更;批量插入可用数组参数配合unnest()。如果必须使用临时表,应每个会话创建一次并设置ON COMMIT DELETE ROWS,避免每个事务都创建删除。

临时表对主备复制有什么影响?

在测试中,主库以2000 TPS创建删除临时表持续60秒,备库落后731 MiB WAL(约24秒),需要33秒追赶;若有3000个空闲会话,延迟达1065 MiB。备库回放时需重放目录行、3个ACCESS EXCLUSIVE锁记录、129条失效消息和一次控制文件fsync,回放进程60-64%的CPU用于获取锁。

🏷️

标签

➡️

继续阅读