pg_stat_activity View explanation in postgresSql
The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.
Note: The waiting and state columns are independent. If a backend is in the active state, it may or may not be waiting. If the state is active and waiting is true, it means that a query is being executed, but is being blocked by a lock somewhere in the system.
The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.
Column
|
Type
|
Description
|
datid
|
oid
|
OID of the
database this backend is connected to
|
datname
|
name
|
Name of the
database this backend is connected to
|
pid
|
integer
|
Process ID of
this backend
|
usesysid
|
oid
|
OID of the
user logged into this backend
|
usename
|
name
|
Name of the
user logged into this backend
|
application_name
|
text
|
Name of the
application that is connected to this backend
|
client_addr
|
inet
|
IP address of
the client connected to this backend. If this field is null, it indicates
either that the client is connected via a Unix socket on the server machine
or that this is an internal process such as autovacuum.
|
client_hostname
|
text
|
Host name of
the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null
for IP connections, and only when log_hostname is enabled.
|
client_port
|
integer
|
TCP port
number that the client is using for communication with this backend, or -1 if a Unix socket is used
|
backend_start
|
timestamp with time zone
|
Time when
this process was started, i.e., when the client connected to the server
|
xact_start
|
timestamp with time zone
|
Time when
this process' current transaction was started, or null if no transaction is
active. If the current query is the first of its transaction, this column is
equal to the query_start column.
|
query_start
|
timestamp with time zone
|
Time when the
currently active query was started, or if state is not active, when the last query was started
|
state_change
|
timestamp with time zone
|
Time when
the state was last changed
|
wait_event_type
|
text
|
The type of
event for which the backend is waiting, if any; otherwise NULL. Possible
values are:
|
wait_event
|
text
|
Wait event
name if backend is currently waiting, otherwise NULL. See Table 28-4 for details.
|
state
|
text
|
Current
overall state of this backend. Possible values are:
|
backend_xid
|
xid
|
Top-level
transaction identifier of this backend, if any.
|
backend_xmin
|
xid
|
The current
backend's xmin horizon.
|
query
|
text
|
Text of this
backend's most recent query. If state is active this field shows the currently executing query.
In all other states, it shows the last query that was executed.
|
Note: The wait_event and state columns are independent. If a backend is in the activestate, it may or may not be waiting on some event. If the state is active and wait_eventis non-null, it means that a query is being executed, but is being blocked somewhere in the system.
current running query find out
database1=# SELECT count(*) as cnt, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC; cnt | usename | current_query -----+---------------+--------------- 7 | freddykrueger | <IDLE> 3 | freddykrueger | SELECT name FROM users WHERE id=50; 1 | postgres | <IDLE> (3 rows)Here is an example of how wait events can be viewed
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL; pid | wait_event_type | wait_event ------+-----------------+--------------- 2540 | Lock | relation 6644 | LWLockNamed | ProcArrayLock (2 rows)Note: For tranches registered by extensions, the name is specified by extension and this will be displayed as wait_event. It is quite possible that user has registered the tranche in one of the backends (by having allocation in dynamic shared memory) in which case other backends won't have that information, so we display extension for such cases.
wait_event Description:
Wait
Event Type
|
Wait
Event Name
|
Description
|
LWLockNamed
|
ShmemIndexLock
|
Waiting to find or allocate space in shared
memory.
|
OidGenLock
|
Waiting to allocate or assign an OID.
|
|
XidGenLock
|
Waiting to allocate or assign a transaction
id.
|
|
ProcArrayLock
|
Waiting to get a snapshot or clearing a
transaction id at transaction end.
|
|
SInvalReadLock
|
Waiting to retrieve or remove messages from
shared invalidation queue.
|
|
SInvalWriteLock
|
Waiting to add a message in shared
invalidation queue.
|
|
WALBufMappingLock
|
Waiting to replace a page in WAL buffers.
|
|
WALWriteLock
|
Waiting for WAL buffers to be written to
disk.
|
|
ControlFileLock
|
Waiting to read or update the control file
or creation of a new WAL file.
|
|
CheckpointLock
|
Waiting to perform checkpoint.
|
|
CLogControlLock
|
Waiting to read or update transaction
status.
|
|
SubtransControlLock
|
Waiting to read or update sub-transaction
information.
|
|
MultiXactGenLock
|
Waiting to read or update shared multixact
state.
|
|
MultiXactOffsetControlLock
|
Waiting to read or update multixact offset
mappings.
|
|
MultiXactMemberControlLock
|
Waiting to read or update multixact member
mappings.
|
|
RelCacheInitLock
|
Waiting to read or write relation cache
initialization file.
|
|
CheckpointerCommLock
|
Waiting to manage fsync requests.
|
|
TwoPhaseStateLock
|
Waiting to read or update the state of
prepared transactions.
|
|
TablespaceCreateLock
|
Waiting to create or drop the tablespace.
|
|
BtreeVacuumLock
|
Waiting to read or update vacuum-related
information for a B-tree index.
|
|
AddinShmemInitLock
|
Waiting to manage space allocation in
shared memory.
|
|
AutovacuumLock
|
Autovacuum worker or launcher waiting to
update or read the current state of autovacuum workers.
|
|
AutovacuumScheduleLock
|
Waiting to ensure that the table it has
selected for a vacuum still needs vacuuming.
|
|
SyncScanLock
|
Waiting to get the start location of a scan
on a table for synchronized scans.
|
|
RelationMappingLock
|
Waiting to update the relation map file
used to store catalog to filenode mapping.
|
|
AsyncCtlLock
|
Waiting to read or update shared
notification state.
|
|
AsyncQueueLock
|
Waiting to read or update notification
messages.
|
|
SerializableXactHashLock
|
Waiting to retrieve or store information
about serializable transactions.
|
|
SerializableFinishedListLock
|
Waiting to access the list of finished
serializable transactions.
|
|
SerializablePredicateLockListLock
|
Waiting to perform an operation on a list
of locks held by serializable transactions.
|
|
OldSerXidLock
|
Waiting to read or record conflicting
serializable transactions.
|
|
SyncRepLock
|
Waiting to read or update information about
synchronous replicas.
|
|
BackgroundWorkerLock
|
Waiting to read or update background worker
state.
|
|
DynamicSharedMemoryControlLock
|
Waiting to read or update dynamic shared
memory state.
|
|
AutoFileLock
|
Waiting to update the postgresql.auto.conf file.
|
|
ReplicationSlotAllocationLock
|
Waiting to allocate or free a replication
slot.
|
|
ReplicationSlotControlLock
|
Waiting to read or update replication slot
state.
|
|
CommitTsControlLock
|
Waiting to read or update transaction
commit timestamps.
|
|
CommitTsLock
|
Waiting to read or update the last value
set for the transaction timestamp.
|
|
ReplicationOriginLock
|
Waiting to setup, drop or use replication
origin.
|
|
MultiXactTruncationLock
|
Waiting to read or truncate multixact
information.
|
|
OldSnapshotTimeMapLock
|
Waiting to read or update old snapshot
control information.
|
|
LWLockTranche
|
clog
|
Waiting for I/O on a clog (transaction
status) buffer.
|
commit_timestamp
|
Waiting for I/O on commit timestamp buffer.
|
|
subtrans
|
Waiting for I/O a subtransaction buffer.
|
|
multixact_offset
|
Waiting for I/O on a multixact offset
buffer.
|
|
multixact_member
|
Waiting for I/O on a multixact_member
buffer.
|
|
async
|
Waiting for I/O on an async (notify)
buffer.
|
|
oldserxid
|
Waiting to I/O on an oldserxid buffer.
|
|
wal_insert
|
Waiting to insert WAL into a memory buffer.
|
|
buffer_content
|
Waiting to read or write a data page in
memory.
|
|
buffer_io
|
Waiting for I/O on a data page.
|
|
replication_origin
|
Waiting to read or update the replication
progress.
|
|
replication_slot_io
|
Waiting for I/O on a replication slot.
|
|
proc
|
Waiting to read or update the fast-path
lock information.
|
|
buffer_mapping
|
Waiting to associate a data block with a
buffer in the buffer pool.
|
|
lock_manager
|
Waiting to add or examine locks for
backends, or waiting to join or exit a locking group (used by parallel
query).
|
|
predicate_lock_manager
|
Waiting to add or examine predicate lock
information.
|
|
Lock
|
relation
|
Waiting to acquire a lock on a relation.
|
extend
|
Waiting to extend a relation.
|
|
page
|
Waiting to acquire a lock on page of a
relation.
|
|
tuple
|
Waiting to acquire a lock on a tuple.
|
|
transactionid
|
Waiting for a transaction to finish.
|
|
virtualxid
|
Waiting to acquire a virtual xid lock.
|
|
speculative token
|
Waiting to acquire a speculative insertion
lock.
|
|
object
|
Waiting to acquire a lock on a non-relation
database object.
|
|
userlock
|
Waiting to acquire a userlock.
|
|
advisory
|
Waiting to acquire an advisory user lock.
|
|
BufferPin
|
BufferPin
|
Waiting to acquire a pin on a buffer.
|
Comments
Post a Comment