postgresql等待事件之wait_event为空解析
2021/9/7 2:06:41
本文主要是介绍postgresql等待事件之wait_event为空解析，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
查看某个语句的当前等待事件可以查询pg_stat_activity表（类似于Oracle的v$session），如下所示：
select wait_event_type,wait_event,state,query,a.* from pg_stat_activity a where query like 'insert%';
wait_event_type|wait_event|state |query |datid |datname|pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event|state |backend_xid|backend_xmin|query |backend_type |
---------------+----------+------+---------------------------------------------------------------------+------+-------+-----+----------+--------+-------+------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+----------+------+-----------+------------+---------------------------------------------------------------------+--------------+
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test|24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql>|172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18| | |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
奇怪的是，虽然没有任何阻塞，又是I/O密集型操作，居然只有state等于active，没有等待事件。此时唯一的可能是在做计算密集型操作。可以通过pstack查看PID查看语句在做什么，如下：
[zjh@lightdb1 pgsql13.2]$ pstack 24870 #0 0x000000000049553c in ginTraverseLock () #1 0x0000000000495cf9 in ginFindLeafPage () #2 0x0000000000498764 in ginInsertItemPointers () #3 0x000000000049e670 in ginEntryInsert () #4 0x000000000049a7c4 in ginInsertCleanup () #5 0x000000000049b1e8 in ginHeapTupleFastInsert () #6 0x000000000049ee98 in gininsert () #7 0x000000000061b8aa in ExecInsertIndexTuples () #8 0x00000000006445fa in ExecInsert () #9 0x0000000000645949 in ExecModifyTable () #10 0x000000000061c482 in standard_ExecutorRun () #11 0x00007f1da44e5f97 in pgss_ExecutorRun (queryDesc=0x1cac8f8, direction=ForwardScanDirection, count=0, execute_once=true) at pg_stat_statements.c:1045 #12 0x0000000000772bda in ProcessQuery () #13 0x0000000000772e14 in PortalRunMulti () #14 0x000000000077382d in PortalRun () #15 0x0000000000771412 in PostgresMain () #16 0x0000000000483069 in ServerLoop () #17 0x00000000006fe0b3 in PostmasterMain () #18 0x0000000000483cae in main ()
注：上述示例中，除了pg_stat_statements插件外，pg没有使用-g选项编译，所以缺少源文件信息。可通过pg_config（或lightdb lt_config）查看编译选项，该选项是后续所有基于本实例的插件的编译。
原来在更新gin索引。
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+-------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+-------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |DataFileWrite|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |DataFileWrite|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18| | |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event|state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event|state |backend_xid|backend_xmin|query |backend_type |
---------------+----------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+----------+------+-----------+------------+---------------------------------------------------------------------+--------------+
LWLock |WALWrite |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|LWLock |WALWrite |active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead|idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead|idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
LWLock |WALWrite |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|LWLock |WALWrite |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |DataFileRead|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |DataFileRead|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18| | |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+--------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+--------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |DataFileExtend|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |DataFileExtend|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
IO |DataFileRead |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|IO |DataFileRead |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+-------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+-------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20| | |active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
LWLock |BufferContent|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|LWLock |BufferContent|active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |WALInitWrite|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |WALInitWrite|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
LWLock |WALWrite |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|LWLock |WALWrite |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
这个信息是PG官方手册和DBA不会告诉你的。
参考：
https://www.postgresql.org/docs/14/monitoring.html
这篇关于postgresql等待事件之wait_event为空解析的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-20实战：30 行代码做一个网页端的 AI 聊天助手
- 2024-11-185分钟搞懂大模型的重复惩罚后处理
- 2024-11-18基于Ollama和pgai的个人知识助手项目：用Postgres和向量扩展打造智能数据库
- 2024-11-15我用同一个提示测试了4款AI工具，看看谁设计的界面更棒
- 2024-11-15深度学习面试的时候，如何回答1x1卷积的作用
- 2024-11-15检索增强生成即服务：开发者的得力新帮手
- 2024-11-15技术与传统：人工智能时代的最后一袭纱丽
- 2024-11-15未结构化数据不仅仅是给嵌入用的：利用隐藏结构提升检索性能
- 2024-11-15Emotion项目实战：新手入门教程
- 2024-11-157 个开源库助你构建增强检索生成（RAG）、代理和 AI 搜索
