> 13 марта 2016 г., в 20:39, Artem Tomyuk <ad...@leboutique.com> написал(а):
>
> 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%'
ORDER BY reads DESC LIMIT 1;
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…
https://simply.name
