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会产生子事务。】于是我们展开了对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 输出中看到它们如何与读取器争夺轻量级锁。”在他的博客文章中，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 的频率是不够的；我们必须完全消除它们。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129586.html
