人工智能学习
postgresql等待事件之wait_event为空解析
本文主要是介绍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为空解析的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
Emotion学习入门指南11-14
-
获取参数学习：初学者指南11-14
-
受控组件学习：从入门到实践11-14
-
Tailwind使用入门：新手必看的简单教程11-14
-
初学者指南：理解与运用情感11-13
-
AI时代浪潮来袭，项目经理如何应对？11-12
-
点击加载学习：新手入门的简单教程11-11
-
封装学习入门指南11-11
-
别再手动处理数据了！FastGPT 这个新功能让你提前下班11-08
-
大厂算法入门详解11-05
-
朴素贪心算法入门详解11-05
-
深度优先遍历算法详解与实践教程11-05
-
初学者指南：轻松理解树形模型11-05
-
搜索算法入门指南：轻松掌握基础概念与实现方法11-05
-
算法入门指南：轻松掌握编程基础11-05
栏目导航
