Contact
Offices

PostgreSQL 9.6 Feature: pg_stat_activity - wait_event

Phillip Warwick

Written by Adrian Vondendriesch.

With PostgreSQL 9.6, the known View pg_stat_activity is extended by two columns: wait_event and wait_event_type. These replace the old column waiting.

Background

PostgreSQL distinguishes internally between two lock types: heavyweight Locks and lightweight Locks (LWLocks). Depending on the expected duration of the lock, either one or the other variant is used.

For heavyweight locks, Locks count relations to database objects. They are used at the SQL level to secure simultaneous, conflicting actions against each other. So it is possible that, for example, a table will only be deleted once there are no more transactions that use them, or two transactions that attempt to UPDATE the same row must wait for each other. These locks can be viewed in the View pg_locks. Furthermore, the view pg_stat_activity can show who is waiting for whom. A sample query is located in the PostgreSQL Wiki.

Lightweight locks, however, are used for short locking operations. For example, this includes filling the WAL buffer (WALBufMappingLock), or waiting for a write operation which uses the WAL buffer to complete (WALWriteLock). Another example is the access to pages within the shared memory segment (buffer_content), as occurs when reading or writing data. The problem now is that lightweight Locks - unlike heavyweight locks - are not recorded in pg_locks or any other system-view. In order to monitor the activity of LWLock during performance analysis, one would have to resort to external tools such as perf.

Function

In addition to the existing status information as pid, query_start or query provided in current versions, PostgreSQL 9.6 maintains two new columns in pg_stat_activity; the columns and wait_event and wait_event_type, which contain detailed information on locking. These replace the column waiting, which reported only TRUE or FALSE depending on the status of each row, signalling that has been waiting for a heavyweight lock.

The new wait_event column can also shed light on whether - and what kind of - LWLocks are waiting. Where a backend is waiting for a lock, the column contains the name of the wait_event events, or defaults to NULL.

Example

To illustrate the new features we strive to, we usePostgreSQL's own benchmark tool pgbench.

First, we create a new test database and fill it with test data:

$ createdb pgbench $ pgbench -i -s 50 pgbench

Once our database is created and filled, we start pgbench with 20 simultaneous connections in 2 threads:

$ Pgbench -c 20 -j -T 2 300 pgbench

 Whilst that's running, let's look at the view of pg_stat_activity; we note that many of the backend to wait for Locks of type LWLockNamed, a WALWriteLock. This highlights that the processes ae waiting for the WAL records to be written to disk:

psql=# select pid, wait_event, wait_event_type, state, query from pg_stat_activity WHERE wait_event is not NULL;
  pid  |  wait_event   | wait_event_type | state  |                                 query                                  
-------+---------------+-----------------+--------+------------------------------------------------------------------------
 25632 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -3359 WHERE bid = 4;
 25633 | WALWriteLock  | LWLockNamed     | active | END;
 25635 | WALWriteLock  | LWLockNamed     | active | END;
 25636 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + 1807 WHERE bid = 1;
 25638 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -603 WHERE bid = 12;
 25639 | transactionid | Lock            | active | UPDATE pgbench_tellers SET tbalance = tbalance + 2794 WHERE tid = 169;
 25640 | WALWriteLock  | LWLockNamed     | active | END;
 25642 | WALWriteLock  | LWLockNamed     | active | END;
 25643 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -4985 WHERE bid = 5;
 25644 | WALWriteLock  | LWLockNamed     | active | END;
 25645 | WALWriteLock  | LWLockNamed     | active | END;
 25646 | WALWriteLock  | LWLockNamed     | active | END;
 25648 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + 2700 WHERE bid = 1;
 25649 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -456 WHERE bid = 5;
 25650 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -617 WHERE bid = 17;
 25651 | WALWriteLock  | LWLockNamed     | active | END;
(16 rows)

If we now, for example, set the synchronous_commit option to off, we note that these WALWriteLocks no longer occur. The backend no longer waits, and that the data is asyncronously written to the hard disk:

psql=# SELECT pid, wait_event, wait_event_type, state, query from pg_stat_activity WHERE wait_event is not NULL;
  pid  |  wait_event   | wait_event_type | state  |                                  query                                  
-------+---------------+-----------------+--------+-------------------------------------------------------------------------
 26201 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -1065 WHERE bid = 7;
 26203 | transactionid | Lock            | active | UPDATE pgbench_tellers SET tbalance = tbalance + -3052 WHERE tid = 173;
 26204 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -2375 WHERE bid = 5;
(3 rows)

Conclusion

The above illustrates the level of detail which can now be obtained from the database regarding the state of existing locks. This offers significant advantages over the existing options, especially for analysis of database performance.

Details

More detailed information about the lock types used, their meaning and use, is available in the PostgreSQL documentation. Where this is not sufficient, the src / backend / storage / lmgr / README file in the source code provides more information on locking in PostgreSQL.

For any further questions, you can also contact our PostgreSQL Competence Centre.

PostgreSQL 9.6 is currently in beta. A release date has currently not yet been fixed. For those who want to test the new version in advance, you can find the latest tarball under www.postgresql.org. For Debian and Ubuntu are available on apt.postgresql.org pre-built packages.

Blog Categories: 

Add new comment

Image CAPTCHA