Toast是insert性能杀手？
问题现象
业务反馈一个简单的insert跑了半小时以上还没结束，吐槽数据库性能太差。老杨心里嘀咕，大概率又是锁等待之类的阻塞呗。查看等待事件后，恐怕不是那么简单呦，又一次遇到了OidGen。
psql (18.1)
Type "help" for help.
postgres=# select pid,state,wait_event,wait_event_type,usename, EXTRACT(EPOCH FROM (now()-query_start)), substr(query, 0, 150) from pg_stat_activity where state != 'idle' and EXTRACT(EPOCH FROM (now()-query_start)) > 1;
pid | state | wait_event | wait_event_type | usename | extract | substr
--------+---------------------+---------------+-----------------+-------------------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------
194603 | active | OidGen | LWLock | ikun | 11477.398844 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
59450 | active | OidGen | LWLock | ikun | 11615.275271 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
167559 | active | | | ikun | 14632.717235 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
89479 | active | OidGen | LWLock | ikun | 17749.547152 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
63593 | active | | | ikun | 17750.637583 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
195777 | active | OidGen | LWLock | ikun | 16565.933308 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
21036 | active | OidGen | LWLock | ikun | 11803.266466 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
91034 | active | | | ikun | 11741.360506 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
174046 | active | OidGen | LWLock | ikun | 17750.606075 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
153549 | active | | | ikun | 11514.392464 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
151813 | active | OidGen | LWLock | ikun | 17749.627277 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
189482 | active | | | ikun | 11563.017702 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
46653 | active | OidGen | LWLock | ikun | 17748.996777 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
132967 | active | | | ikun | 11547.572100 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
18857 | active | OidGen | LWLock | ikun | 17748.824389 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
184788 | active | OidGen | LWLock | ikun | 11599.806458 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
131596 | active | OidGen | LWLock | ikun | 11541.901097 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
问题分析
OidGen这个等待事件，我之前写过一篇[1]。当创建的对象足够多时，再建对象获取oid的时间会比较长，就会有这个等待事件。不过这次是insert，涉及创建什么对象呢？
先打堆栈看看， heap_insert -> heap_toast_insert_or_update -> toast_save_datum -> GetNewOidWithIndex调用链交代的很清楚，看起来是表有大字段，插入toast元组时获取oid，且在等待LWLock。
pstack 167559 #0 0x00007fdb0f801b3b in do_futex_wait.constprop.1 () from /lib64/libpthread.so.0 #1 0x00007fdb0f801bcf in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0 #2 0x00007fdb0f801c6b in sem_wait@@GLIBC_2.2.5 () from /lib64/libpthread.so.0 #3 0x0000000000725602 in PGSemaphoreLock () #4 0x00000000007a9ec4 in LWLockAcquire () #5 0x000000000051d163 in GetNewObjectId () #6 0x00000000005452a8 in GetNewOidWithIndex () #7 0x00000000004a290a in toast_save_datum () #8 0x000000000050eb88 in toast_tuple_externalize () #9 0x00000000004dddcc in heap_toast_insert_or_update () #10 0x00000000004cfd46 in heap_insert () #11 0x00000000004db838 in heapam_tuple_insert () #12 0x0000000000670e8c in ExecInsert () #13 0x000000000067231b in ExecModifyTable () #14 0x0000000000645ce2 in standard_ExecutorRun () #15 0x00007fdb08ebe62d in pgss_ExecutorRun () from /data/postgresql/lib/pg_stat_statements.so #16 0x00000000007bee3a in ProcessQuery () #17 0x00000000007bf8a4 in PortalRunMulti () #18 0x00000000007bfc18 in PortalRun () #29 0x00000000007bd7f5 in PostgresMain () #20 0x000000000048ed77 in ServerLoop () #21 0x0000000000738689 in PostmasterMain () #22 0x000000000048fe92 in main ()
为什么插入toast元组会获取oid呢？这是由于chunk_id字段是oid类型，其值就是调用GetNewOidWithIndex获取。
kundb=> \d+ pg_toast.pg_toast_1143412359 Column | Type | Storage ------------+---------+--------- chunk_id | oid | plain chunk_seq | integer | plain chunk_data | bytea | plain Owning table: "public.operation_log" Indexes: "pg_toast_1143412359_index" PRIMARY KEY, btree (chunk_id, chunk_seq) Access method: heap
toast的管理是数据库自动进行的，当大字段值大小超过1/4 pagesize左右就会触发toast。toast相关可以看下灿灿写的这篇[2]刚好这篇中也提到了这个问题。
走读下代码。
在toast_save_datum函数[3]中，有两种场景：
1、普通插入（!OidIsValid(rel->rd_toastoid)）：
调用 GetNewOidWithIndex：遍历当前toast表的索引条目从中获取一个未使用的OID
2、重写表场景逻辑（OidIsValid(rel->rd_toastoid)）：
a)尝试复用旧toast值的OID：
oldexternal：原 heap 元组中的 toast 指针（指向旧 toast 表）VARATT_EXTERNAL_GET_POINTER：安全地提取对齐的结构体如果旧值的va_toastrelid等于当前表的rd_toastoid，说明来自同一个toast表于是复用旧的 va_valueid但还要检查这个新 toast 表里是否已经存在该 OID（防止重复插入）如果存在，设置 data_todo = 0，跳过写入 chunk，避免浪费空间。
b)无法复用，则生成不冲突的新OID：
如果无法复用旧 OID（如旧值不存在，或来自不同toast表）则循环生成新 OID，直到找到一个既不在新 toast 表也不在旧 toast 表中存在的OIDtoastid_valueid_exists(rel->rd_toastoid, ...)：检查旧 toast 表中是否存在该 OID 。
/*
* Insert the correct table OID into the result TOAST pointer.
*
* Normally this is the actual OID of the target toast table, but during
* table-rewriting operations such as CLUSTER, we have to insert the OID
* of the table's real permanent toast table instead. rd_toastoid is set
* if we have to substitute such an OID.
*/
if (OidIsValid(rel->rd_toastoid))
toast_pointer.va_toastrelid = rel->rd_toastoid;
else
toast_pointer.va_toastrelid = RelationGetRelid(toastrel);
/*
* Choose an OID to use as the value ID for this toast value.
*
* Normally we just choose an unused OID within the toast table. But
* during table-rewriting operations where we are preserving an existing
* toast table OID, we want to preserve toast value OIDs too. So, if
* rd_toastoid is set and we had a prior external value from that same
* toast table, re-use its value ID. If we didn't have a prior external
* value (which is a corner case, but possible if the table's attstorage
* options have been changed), we have to pick a value ID that doesn't
* conflict with either new or existing toast value OIDs.
*/
if (!OidIsValid(rel->rd_toastoid))
{
/* normal case: just choose an unused OID */
toast_pointer.va_valueid =
GetNewOidWithIndex(toastrel,
RelationGetRelid(toastidxs[validIndex]),
(AttrNumber) 1);
}
else
{
/* rewrite case: check to see if value was in old toast table */
toast_pointer.va_valueid = InvalidOid;
if (oldexternal != NULL)
{
struct varatt_external old_toast_pointer;
Assert(VARATT_IS_EXTERNAL_ONDISK(oldexternal));
/* Must copy to access aligned fields */
VARATT_EXTERNAL_GET_POINTER(old_toast_pointer, oldexternal);
if (old_toast_pointer.va_toastrelid == rel->rd_toastoid)
{
/* This value came from the old toast table; reuse its OID */
toast_pointer.va_valueid = old_toast_pointer.va_valueid;
/*
* There is a corner case here: the table rewrite might have
* to copy both live and recently-dead versions of a row, and
* those versions could easily reference the same toast value.
* When we copy the second or later version of such a row,
* reusing the OID will mean we select an OID that's already
* in the new toast table. Check for that, and if so, just
* fall through without writing the data again.
*
* While annoying and ugly-looking, this is a good thing
* because it ensures that we wind up with only one copy of
* the toast value when there is only one copy in the old
* toast table. Before we detected this case, we'd have made
* multiple copies, wasting space; and what's worse, the
* copies belonging to already-deleted heap tuples would not
* be reclaimed by VACUUM.
*/
if (toastrel_valueid_exists(toastrel,
toast_pointer.va_valueid))
{
/* Match, so short-circuit the data storage loop below */
data_todo = 0;
}
}
}
if (toast_pointer.va_valueid == InvalidOid)
{
/*
* new value; must choose an OID that doesn't conflict in either
* old or new toast table
*/
do
{
toast_pointer.va_valueid =
GetNewOidWithIndex(toastrel,
RelationGetRelid(toastidxs[validIndex]),
(AttrNumber) 1);
} while (toastid_valueid_exists(rel->rd_toastoid,
toast_pointer.va_valueid));
}
}
/*
* Initialize constant parts of the tuple data
*/
t_values[0] = ObjectIdGetDatum(toast_pointer.va_valueid);
t_values[2] = PointerGetDatum(&chunk_data);
t_isnull[0] = false;
t_isnull[1] = false;
t_isnull[2] = false;
给新的chunk_id匹配unused OID，需要通过toast索引遍历整个toast表的所有数据条目，当toast表的数据条目比较多时，匹配的过程自然就比较久。
受限于system global oid最大值2^32大约42亿，当toast条目足够大没有unused oid时，insert的进程会一直循环获取，直到有free oid。
当命中这个场景时pglog中会有打印“OID candidates have been checked xxxxxxxxxxx times, but no unused OID has been found yet.” 我们这个toast表有28亿条记录，因此遍历一次时间久，所以insert大部分时间都在寻找unused OID。
kundb=# select count(1) from pg_toast.pg_toast_1143412359; count ------------ 2882740871 (1 row)
至此问题非常清晰了，看了下表结构，更窝火了，真踏马… 这个业务表有249个字段，全是text类型…那么优化方案就是清理历史冗余数据，让toast条目数缩减下来；同时建议业务整改表结构。
小结
本篇我们分析了又一起OidGen等待事件，本次的场景是toast数据条目多导致insert插入慢。设计表时要合理，不要在一个表上整太多变长大字段，如果是业务实际需求可以拆分到多个表。
Reference
[1] https://mp.weixin.qq.com/s/QWtyN8F_8MAZrlISJ-uLAA
[2] https://mp.weixin.qq.com/s/QfUAoh kzgwgIICX5tnuKbQ
[3] https://github.com/postgres/postgres/blob/REL_18_0/src/backend/access/common/toast_internals.c#L385
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70033867/viewspace-3107619/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
执行计划跑偏，你了解背后的根因吗？
下一篇：
一条SQL引发的惨案，内存耗尽？
请登录后发表评论
登录
全部评论
