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-12-22程序员出海做 AI 工具：如何用 similarweb 找到最佳流量渠道？
- 2024-12-20自建AI入门：生成模型介绍——GAN和VAE浅析
- 2024-12-20游戏引擎的进化史——从手工编码到超真实画面和人工智能
- 2024-12-20利用大型语言模型构建文本中的知识图谱：从文本到结构化数据的转换指南
- 2024-12-20揭秘百年人工智能：从深度学习到可解释AI
- 2024-12-20复杂RAG（检索增强生成）的入门介绍
- 2024-12-20基于大型语言模型的积木堆叠任务研究
- 2024-12-20从原型到生产：提升大型语言模型准确性的实战经验
- 2024-12-20啥是大模型1
- 2024-12-20英特尔的 Lunar Lake 计划：一场未竟的承诺
