Thread: Meaning of query age in pg_stat_activity
Hi,
I'm using the following to detect long running queries in a webapp that is high on cpu:
SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, state from pg_stat_activity order by age;
When the cpu is 100% and the app slowing down, i can see that some queries have a long age.
Running the same queries in psql is very fast, however. The db itself is low on cpu.
Is the age value as i calculate it representing the time spent by the database to execute the query or does it also include the time to read the result ? In other words, if the client is starving on cpu, will i see higher values of age ?
Regards,
Jan
On Mon, Jan 29, 2018 at 3:06 PM, Jan De Moerloose <jan@sensolus.com> wrote:
...
SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, state from pg_stat_activity order by age; When the cpu is 100% and the app slowing down, i can see that some queries have a long age.
What is the value of "state" column for those queries?
On 01/29/2018 03:06 PM, Jan De Moerloose wrote: > Hi, > > I'm using the following to detect long running queries in a webapp that > is high on cpu: > > SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, > state from pg_stat_activity order by age; I would add WHERE state = 'active' > > When the cpu is 100% and the app slowing down, i can see that some > queries have a long age. > Running the same queries in psql is very fast, however. The db itself is > low on cpu. > > Is the age value as i calculate it representing the time spent by the > database to execute the query or does it also include the time to read > the result ? In other words, if the client is starving on cpu, will i > see higher values of age ? > > Regards, > Jan -- Adrian Klaver adrian.klaver@aklaver.com
The state is 'idle in transaction'.
On Tue, Jan 30, 2018 at 12:10 AM, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
On Mon, Jan 29, 2018 at 3:06 PM, Jan De Moerloose <jan@sensolus.com> wrote:...SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, state from pg_stat_activity order by age; When the cpu is 100% and the app slowing down, i can see that some queries have a long age.What is the value of "state" column for those queries?
On Mon, Jan 29, 2018 at 3:19 PM, Jan De Moerloose <jan@sensolus.com> wrote:
The state is 'idle in transaction'.
So you have long-running *transactions*, not queries. This is not good for an OLTP system, because some transaction can wait of others, which are "idle in transaction" but do nothing at the moment. Think how you can make them shorter, commit faster.
Also, if your server version is 9.6+ consider setting idle_in_transaction_session_timeout to some low value:
So the query is just the latest query and the time is the transaction time since this query, i suppose ?
Thanks for your answer, i will try to make the transaction shorter as you suggest.
On Tue, Jan 30, 2018 at 12:29 AM, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
On Mon, Jan 29, 2018 at 3:19 PM, Jan De Moerloose <jan@sensolus.com> wrote:The state is 'idle in transaction'.So you have long-running *transactions*, not queries. This is not good for an OLTP system, because some transaction can wait of others, which are "idle in transaction" but do nothing at the moment. Think how you can make them shorter, commit faster.Also, if your server version is 9.6+ consider setting idle_in_transaction_session_timeout to some low value:
On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose <jan@sensolus.com> wrote:
So the query is just the latest query and the time is the transaction time since this query, i suppose ?Thanks for your answer, i will try to make the transaction shorter as you suggest.
Yep. This is a very common confusion, however it usually happens regarding records with "state" = 'idle'
– notice that they also have something in "query" column while they are actually doing nothing.
On Mon, Jan 29, 2018 at 9:52 PM, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose <jan@sensolus.com> wrote:So the query is just the latest query and the time is the transaction time since this query, i suppose ?Thanks for your answer, i will try to make the transaction shorter as you suggest.Yep. This is a very common confusion, however it usually happens regarding records with "state" = 'idle'– notice that they also have something in "query" column while they are actually doing nothing.
One correction:
"the time is the transaction time since this query" is not an accurate statement.
You mentioned query_
There are other timestamps:
backend_start, xact_start, and state_change.
All of them are useful in different contexts.