Re: DIsk I/O from pg_stat_activity

From: Vladimir Borodin
Subject: Re: DIsk I/O from pg_stat_activity
Date: ,
(view: Whole thread, Raw)
In response to: DIsk I/O from pg_stat_activity  (Artem Tomyuk)
List: pgsql-performance

13 марта 2016 г., в 20:39, Artem Tomyuk <> написал(а):

Hi all.

Is there any way of how to retrieve information from pg_stat_activity (its not very comfort to get it from iotop, because its not showing full text of query) which query generates or consumes the most IO load or time. 

Probably this can be done with pg_stat_kcache. Installing it with pg_stat_statements and querying it something like below will give stats per query:

rpopdb01d/postgres R # SELECT rolname, queryid, round(total_time::numeric, 2) AS total_time, calls,
    pg_size_pretty(shared_blks_hit*8192) AS shared_hit,
    pg_size_pretty(int8larger(0, (shared_blks_read*8192 - reads))) AS page_cache_hit,
    pg_size_pretty(reads) AS physical_read,
    round(blk_read_time::numeric, 2) AS blk_read_time,
    round(user_time::numeric, 2) AS user_time,
    round(system_time::numeric, 2) AS system_time
FROM pg_stat_statements s
    JOIN pg_stat_kcache() k USING (userid, dbid, queryid)
    JOIN pg_database d ON s.dbid = d.oid
    JOIN pg_roles r ON r.oid = userid
WHERE datname != 'postgres' AND datname NOT LIKE 'template%'
 rolname |  queryid   |  total_time  |  calls   | shared_hit | page_cache_hit | physical_read | blk_read_time | user_time | system_time
 rpop    | 3183006759 | 309049021.97 | 38098195 | 276 TB     | 27 TB          | 22 TB         |   75485646.81 | 269508.98 |    35635.96
(1 row)

Time: 18.605 ms
rpopdb01d/postgres R #

Query text may be resolved by queryid something like SELECT query FROM pg_stat_statements WHERE queryid = 3183006759.

Works only with 9.4+ and gives you statistics per query for all the time, not the current state.

Thanks for any advice.

May the force be with you…

pgsql-performance by date:

From: Vladimir Borodin
Subject: Re: DIsk I/O from pg_stat_activity
From: Tom Lane
Subject: Re: Merge joins on index scans