... | @@ -80,7 +80,11 @@ Source: [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Disk_Usage). See also |
... | @@ -80,7 +80,11 @@ Source: [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Disk_Usage). See also |
|
If the server seems slow, it's possible to inspect running queries
|
|
If the server seems slow, it's possible to inspect running queries
|
|
with this query:
|
|
with this query:
|
|
|
|
|
|
SELECT datid,datname,pid,backend_start,query FROM pg_stat_activity;
|
|
SELECT datid,datname,pid,query_start,now()-query_start as age,state,query FROM pg_stat_activity;
|
|
|
|
|
|
|
|
If the `state` is `waiting`, it might be worth looking at the
|
|
|
|
`wait_event`, and `wait_event_type` columns as well. We're looking for
|
|
|
|
deadlocks here.
|
|
|
|
|
|
## Killing a slow query
|
|
## Killing a slow query
|
|
|
|
|
... | @@ -116,9 +120,11 @@ WHERE |
... | @@ -116,9 +120,11 @@ WHERE |
|
|
|
|
|
Some ideas from the `#postgresql` channel on Libera:
|
|
Some ideas from the `#postgresql` channel on Libera:
|
|
|
|
|
|
* look at `age(query_start)` and `state`, and if `state` is `waiting`,
|
|
* look at `query_start` and `state`, and if `state` is `waiting`,
|
|
`wait_event`, and `wait_event_type`, in [`pg_stat_activity`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW),
|
|
`wait_event`, and `wait_event_type`, in [`pg_stat_activity`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW),
|
|
possibly looking for locks here
|
|
possibly looking for locks here. this is done by the query above,
|
|
|
|
in [Show running queries](#show-running-queries)
|
|
|
|
|
|
* enable [`pg_stat_statements`](https://www.postgresql.org/docs/14/pgstatstatements.html) to see where the time is going,
|
|
* enable [`pg_stat_statements`](https://www.postgresql.org/docs/14/pgstatstatements.html) to see where the time is going,
|
|
and then dig into the queries/functions found there, possibly with
|
|
and then dig into the queries/functions found there, possibly with
|
|
[`auto_explain`](https://www.postgresql.org/docs/current/auto-explain.html) and `auto_explain.log_nested_statements=on`
|
|
[`auto_explain`](https://www.postgresql.org/docs/current/auto-explain.html) and `auto_explain.log_nested_statements=on`
|
... | | ... | |