performance statistics monitoring without spamming logs

Поиск
Список
Период
Сортировка
От Patrick Hemmer
Тема performance statistics monitoring without spamming logs
Дата
Msg-id 7ffb9dbe-c76f-8ca3-12ee-7914ede872e6@stormcloud9.net
обсуждение исходный текст
Ответы Re: performance statistics monitoring without spamming logs  (Justin Pryzby <pryzby@telsasoft.com>)
Список pgsql-performance
I'm looking for a way of gathering performance stats in a more usable way than turning on `log_statement_stats` (or other related modules). The problem I have with the log_*_stats family of modules is that they log every single query, which makes them unusable in production. Aside from consuming space, there's also the problem that the log system wouldn't be able to keep up with the rate.

There are a couple ideas that pop into mind that would make these stats more usable:
1. Only log when the statement would otherwise already be logged. Such as due to the `log_statement` or `log_min_duration_statement` settings.
2. Make stats available in `pg_stat_statements` (or alternate view that could be joined on). The block stats are already available here, but others like CPU usage, page faults, and context switches are not.

To answer why I want this data: I want to be able to identify queries which are consuming large amounts of CPU time so that I can either optimize the query or optimize the application making the query, and free up CPU resources on the database. The `pg_stat_statements` view provides the `total_time` metric, but many things can contribute to query time other than CPU usage, and CPU usage is my primary concern at the moment.

Do these seem like reasonable requests? And if so, what's the procedure for getting them implemented?
Any thoughts on whether they would be hard to implement? I'm unfamiliar with the PostgresQL code base, but might be willing to attempt an implementation if it wouldn't be terribly difficult.

-Patrick

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

Предыдущее
От: Lincoln Swaine-Moore
Дата:
Сообщение: Improving Performance of Query ~ Filter by A, Sort by B
Следующее
От: Justin Pryzby
Дата:
Сообщение: Re: performance statistics monitoring without spamming logs