Re: DIsk I/O from pg_stat_activity

От: Vladimir Borodin
Тема: Re: DIsk I/O from pg_stat_activity
Дата: ,
Msg-id: 95464A0D-8654-45C7-A251-DFCA65454415@simply.name
(см: обсуждение, исходный текст)
Ответ на: DIsk I/O from pg_stat_activity  (Artem Tomyuk)
Список: 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%'
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…


В списке pgsql-performance по дате сообщения:

От: Vladimir Borodin
Дата:
Сообщение: Re: DIsk I/O from pg_stat_activity
От: Tom Lane
Дата:
Сообщение: Re: Merge joins on index scans