To: pgsql-performance@lists.postgresql.org
From: Patrick Hemmer <postgresql@stormcloud9.net>
Subject: performance statistics monitoring without spamming logs
Openpgp: preference=signencrypt
Message-ID: <7ffb9dbe-c76f-8ca3-12ee-7914ede872e6@stormcloud9.net>
Date: Tue, 10 Jul 2018 13:54:12 -0400
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:52.0)
 Gecko/20100101 Thunderbird/52.1.0
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------BA5588256C1DD6AA464919C5"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------BA5588256C1DD6AA464919C5
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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

--------------BA5588256C1DD6AA464919C5
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    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.<br>
    <br>
    There are a couple ideas that pop into mind that would make these
    stats more usable:<br>
    1. Only log when the statement would otherwise already be logged.
    Such as due to the `log_statement` or `log_min_duration_statement`
    settings.<br>
    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.<br>
    <br>
    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.<br>
    <br>
    Do these seem like reasonable requests? And if so, what's the
    procedure for getting them implemented?<br>
    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.<br>
    <br>
    -Patrick<br>
  </body>
</html>

--------------BA5588256C1DD6AA464919C5--