class: center, middle # pg_stat_activity and friends --- # What is pg_stat_activity? pg_stat_activity is a PostgreSQL system view that is a good first place to start when you want to find out what is going on with your PostgreSQL installation. --- # Aside 1 If you need to make a connection to a PostgreSQL installation, a good first place to start is 1. username postgres 2. dbname postgres Any default PostgreSQL install will have both this user and database. Tool makers know this convention, and design tools that connect using these defaults. [http://www.postgresql.org/docs/9.4/interactive/creating-cluster.html](http://www.postgresql.org/docs/9.4/interactive/creating-cluster.html) --- # Aside 2 Here's how you list all the databases in a PostgreSQL cluster: ``` mwood@mwod-ThinkPad-X220:~$ psql -U postgres -l List of databases ┌───────────┬──────────┬──────────┬─────────┬───────┬───────────────────────┐ │ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │ ├───────────┼──────────┼──────────┼─────────┼───────┼───────────────────────┤ │ postgres │ postgres │ UTF8 │ C │ C │ │ │ pqgotest │ postgres │ UTF8 │ C │ C │ │ │ template0 │ postgres │ UTF8 │ C │ C │ =c/postgres ↵│ │ │ │ │ │ │ postgres=CTc/postgres │ │ template1 │ postgres │ UTF8 │ C │ C │ =c/postgres ↵│ │ │ │ │ │ │ postgres=CTc/postgres │ └───────────┴──────────┴──────────┴─────────┴───────┴───────────────────────┘ (4 rows) ``` (A PostgreSQL installation is generally called a cluster.) --- # Let's make three connections to PostgreSQL: terminal1: ```bash $ psql -U postgres -d postgres ``` terminal2: ```bash $ psql -U postgres -d postgres ``` terminal3: ```bash $ psql -U postgres -d postgres ``` --- # Determine number of active connections to the cluster: ``` postgres@[local]:5432/postgres# select count(*) from pg_stat_activity; ┌───────┐ │ count │ ├───────┤ │ 3 │ └───────┘ (1 row) ``` --- # Show which database each connection is using: ``` postgres@[local]:5432/postgres# select datname from pg_stat_activity; rollback; ┌──────────┐ │ datname │ ├──────────┤ │ postgres │ │ postgres │ │ postgres │ └──────────┘ (3 rows) ``` --- # Identify our connections using the set command. Terminal 1 ``` postgres@[local]:5432/postgres# set application_name to worker1; SET ``` Terminal 2 ``` postgres@[local]:5432/postgres# set application_name to worker2; SET ``` Terminal 3 ``` postgres@[local]:5432/postgres# set application_name to worker3; SET ``` --- # Now we can identify or workers in pg_stat_activity: ``` postgres@[local]:5432/postgres# select application_name from pg_stat_activity; ┌──────────────────┐ │ application_name │ ├──────────────────┤ │ worker3 │ │ worker2 │ │ worker1 │ └──────────────────┘ (3 rows) ``` --- # set application_name often For long-lived connections, (re)setting application name can reveal useful state information: Terminal 1 ``` postgres@[local]:5432/postgres# set application_name = 'worker1 on job 31'; SET ``` Terminal 2 ``` postgres@[local]:5432/postgres# set application_name = 'worker2 on job 32'; SET ``` Terminal 3 ``` postgres@[local]:5432/postgres# set application_name = 'worker3 on job 33'; SET ``` --- # That's better ``` postgres@[local]:5432/postgres# select application_name from pg_stat_activity; rollback; ┌───────────────────┐ │ application_name │ ├───────────────────┤ │ worker3 on job 33 │ │ worker2 on job 32 │ │ worker1 on job 31 │ └───────────────────┘ (3 rows) ``` --- # Aside: The set command - The set command has both `set foo to` and `set foo =` variants. - The opposite of `set` is `show`: Terminal 1 ``` postgres@[local]:5432/postgres# show application_name; ┌───────────────────┐ │ application_name │ ├───────────────────┤ │ worker1 on job 31 │ └───────────────────┘ (1 row) ``` - 'show all' will show all the settings for your session (there are 243 settings as of PostgreSQL 9.4.4!) [http://www.postgresql.org/docs/9.4/interactive/sql-set.html](http://www.postgresql.org/docs/9.4/interactive/sql-set.html) --- class: center, middle # pg_stat_activity and transactions --- # Let's disable autocommit for our three sessions Terminal 1 ``` postgres@[local]:5432/postgres# \set AUTOCOMMIT off ``` Terminal 2 ``` postgres@[local]:5432/postgres# \set AUTOCOMMIT off ``` Terminal 3 ``` postgres@[local]:5432/postgres# \set AUTOCOMMIT off ``` --- # Wait, what is `\set`? `\set` is a meta-command used to set psql variables. It is client-side (not server-side) and specific to the psql client. When we say ``` postgres@[local]:5432/postgres# \set AUTOCOMMIT off ``` we are telling psql to take its connection out of autocommit mode. --- # Aside: cool psql prompts using `\set` This is the `\set` command to create the psql prompts you see in this demonstration: ``` -- [user]@[host]:[port]/[db] -- ['*' if we are in a transaction] -- ['#' if we are root-like; '>' otherwise] \set PROMPT1 '%n@%m:%>/%/%x%# ' ``` Later in the demo, the appearance of * at the prompt for non-committed transactions will be important. --- # Let's create a table ``` postgres@[local]:5432/postgres# create table t ( id int constraint t_pk primary key not null, name text not null); commit; CREATE TABLE COMMIT postgres@[local]:5432/postgres# insert into t (id, name) values (1, 'foo'); commit; INSERT 0 1 COMMIT ``` --- # Let's update the same row from two connections Terminal 1 ``` postgres@[local]:5432/postgres# update t set name = 'bar' where id = 1; UPDATE 1 postgres@[local]:5432/postgres*# <-- prompt shows uncommited transaction! ``` Note that we have NOT committed! Terminal 2 ``` postgres@[local]:5432/postgres# update t set name = 'zzz' where id = 1; <-- blocked; we did not get a prompt back! ``` --- # Let's use Terminal 3 to investigate what is happening. ``` postgres@[local]:5432/postgres# select application_name, state, waiting, query from pg_stat_activity; rollback; ┌───────────────────┬─────────────────────┬─────────┬───────────────────────────────────────────────────────────────────────┐ │ application_name │ state │ waiting │ query │ ├───────────────────┼─────────────────────┼─────────┼───────────────────────────────────────────────────────────────────────┤ │ worker3 on job 33 │ active │ f │ select application_name, state, waiting, query from pg_stat_activity; │ │ worker2 on job 32 │ active │ t │ update t set name = 'zzz' where id = 1; │ │ worker1 on job 31 │ idle in transaction │ f │ update t set name = 'bar' where id = 1; │ └───────────────────┴─────────────────────┴─────────┴───────────────────────────────────────────────────────────────────────┘ (3 rows) ``` Worker 2 is blocked waiting on worker 1 --- # Let's pretend worker 1 is stuck Worker 1 is holding open its transaction, but doing nothing with it! Let's get the pid of worker 1's connection: Terminal 3 ``` postgres@[local]:5432/postgres# select pid, application_name from pg_stat_activity where state = 'idle in transaction'; rollback; ┌──────┬───────────────────┐ │ pid │ application_name │ ├──────┼───────────────────┤ │ 2350 │ worker1 on job 31 │ └──────┴───────────────────┘ (1 row) ``` --- # PostgreSQL users with admin rights can terminate sessions The postgres user is one such user: Terminal 3 ``` postgres@[local]:5432/postgres# select pg_terminate_backend(2350); commit; ┌──────────────────────┐ │ pg_terminate_backend │ ├──────────────────────┤ │ t │ └──────────────────────┘ (1 row) ``` --- # Meanwhile, back in terminal 2: ``` postgres@[local]:5432/postgres# update t set name = 'zzz' where id = 1; UPDATE 1 postgres@[local]:5432/postgres*# ``` Our update completed and we got our prompt back. We are also now in an uncommitted transaction. --- # Back to terminal 3: - Worker 1's connection is gone - Worker 2 is idle in transaction because he has not committed yet ``` postgres@[local]:5432/postgres# select application_name, state, waiting, query from pg_stat_activity; rollback; ┌───────────────────┬─────────────────────┬─────────┬───────────────────────────────────────────────────────────────────────┐ │ application_name │ state │ waiting │ query │ ├───────────────────┼─────────────────────┼─────────┼───────────────────────────────────────────────────────────────────────┤ │ worker3 on job 33 │ active │ f │ select application_name, state, waiting, query from pg_stat_activity; │ │ worker2 on job 32 │ idle in transaction │ f │ update t set name = 'zzz' where id = 1; │ └───────────────────┴─────────────────────┴─────────┴───────────────────────────────────────────────────────────────────────┘ (2 rows) ``` --- # Let's commit terminal 2's transaction ``` Terminal 2 postgres@[local]:5432/postgres*# commit; COMMIT ``` --- # What's going on with psql in terminal 1? Seems OK. Let's try to commit. ``` postgres@[local]:5432/postgres*# commit; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. postgres@[local]:5432/postgres# ``` `pg_terminate_backend` worked as advertised --- # pg_stat_activity can also show us the times of various events for any session. Let's return to terminal 2: ``` postgres@[local]:5432/postgres# update t set name = 'yyz' where id = 1; UPDATE 1 postgres@[local]:5432/postgres*# <-- open transaction ``` --- # Terminal 3 ``` postgres@[local]:5432/postgres# \x Expanded display is on. postgres@[local]:5432/postgres# select backend_start, xact_start, query_start, state_change, state, query from pg_stat_activity where application_name like 'worker2%'; rollback; ┌─[ RECORD 1 ]──┬─────────────────────────────────────────┐ │ backend_start │ 2015-10-12 12:46:22.735595-04 │ │ xact_start │ 2015-10-12 13:52:40.471049-04 │ │ query_start │ 2015-10-12 13:52:40.47122-04 │ │ state_change │ 2015-10-12 13:52:40.47169-04 │ │ state │ idle in transaction │ │ query │ update t set name = 'yyz' where id = 1; │ └───────────────┴─────────────────────────────────────────┘ ``` Terminal 2's session (backend_start) started a while ago, but our current query started more recently, ran very quickly, and then become idle in transacton (query_start versus state_change). --- # Terminal 2, open transaction ``` postgres@[local]:5432/postgres*# insert into t (id, name) values (2, 'yhz'); INSERT 0 1 postgres@[local]:5432/postgres*# <-- transaction still open ``` --- # Terminal 3 ``` postgres@[local]:5432/postgres# select backend_start, xact_start, query_start, state_change, state, query from pg_stat_activity where application_name like 'worker2%'; rollback; ┌─[ RECORD 1 ]──┬─────────────────────────────────────────────┐ │ backend_start │ 2015-10-12 12:46:22.735595-04 │ │ xact_start │ 2015-10-12 13:52:40.471049-04 │ │ query_start │ 2015-10-12 14:01:10.644656-04 │ │ state_change │ 2015-10-12 14:01:10.645041-04 │ │ state │ idle in transaction │ │ query │ insert into t (id, name) values (2, 'yhz'); │ └───────────────┴─────────────────────────────────────────────┘ ``` Terminal 2's query_start is different from its transaction_start because query_start is now for terminal 2's second query. But again, `state_change` shows we transitioned very quickly to `idle in transaction`. --- # Terminal 2 ``` postgres@[local]:5432/postgres*# commit; COMMIT postgres@[local]:5432/postgres# <-- no longer in transaction ``` --- # Terminal 3 ``` postgres@[local]:5432/postgres# select backend_start, xact_start, query_start, state_change, state, query from pg_stat_activity where application_name like 'worker2%'; rollback; ┌─[ RECORD 1 ]──┬───────────────────────────────┐ │ backend_start │ 2015-10-12 12:46:22.735595-04 │ │ xact_start │ [NULL] │ │ query_start │ 2015-10-12 14:05:20.204116-04 │ │ state_change │ 2015-10-12 14:05:20.209775-04 │ │ state │ idle │ │ query │ commit; │ └───────────────┴───────────────────────────────┘ ``` Terminal 2 is no longer in a transaction (xact_start is null) and is idle. Note that idle is a perfectly fine state for a session to be in, unless you are worried about connections to your database never being cleaned up. --- # All of the PostgreSQL states: - __active__: The backend is executing a query. - __idle__: The backend is waiting for a new client command. - __idle in transaction__: The backend is in a transaction, but is not currently executing a query. - __idle in transaction (aborted)__: This state is similar to idle in transaction, except one of the statements in the transaction caused an error. - __fastpath function call__: The backend is executing a fast-path function. - __disabled__: This state is reported if track_activities is disabled in this backend. [http://www.postgresql.org/docs/9.4/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW](http://www.postgresql.org/docs/9.4/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW) --- # A final fun `set` command Enforce the amount of time any single query is supposed to take, on a per-connection basis: Terminal 2 ``` postgres@[local]:5432/postgres# set statement_timeout to 1000; SET postgres@[local]:5432/postgres*# commit; COMMIT postgres@[local]:5432/postgres# select pg_sleep(2); ERROR: 57014: canceling statement due to statement timeout postgres@[local]:5432/postgres!# rollback; ROLLBACK ``` --- class: center, middle # Questions?