Обсуждение: DIsk I/O from pg_stat_activity

Поиск
Список
Период
Сортировка

DIsk I/O from pg_stat_activity

От
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. 

Thanks for any advice.

Re: DIsk I/O from pg_stat_activity

От
Vladimir Borodin
Дата:

13 марта 2016 г., в 20:39, Artem Tomyuk <admin@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…