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 <admin@leboutique.com>)
Список pgsql-performance

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…

В списке pgsql-performance по дате отправления:

Предыдущее
От: Artem Tomyuk
Дата:
Сообщение: DIsk I/O from pg_stat_activity
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Merge joins on index scans