Re: log_statement vs Statistics Collector (pg_stat_database,, etc)

On Mon, Dec 28, 2015 at 7:03 PM, Nik Tek <niktek2005(at)gmail(dot)com> wrote:

> Thank you Matheus!
>
> Based on the study, there's very little performance impact when enabling log_min_duration_statement,
> right?
>
>
Whoa... :) Careful there!

This all depends on your traffic. Keep in mind, if
log_min_duration_statement is set to something low (say, 0, which means to
log all statements and their data), you are essentially more than doubling
the amount of I/O that your database does. Remember, you would be writing
the data to the database itself as WELL as to the logs. In some cases, on
low-volume systems, this may be a non-issue, but, on even moderately
queried systems, you're going to spend a lot of your IOPS on logging.

For sure, you can mitigate the issue by setting up a separate mount for
logging, even more by running this all through [r]syslog and forwarding
(over a dedicated network) the traffic to a dedicated logging host. Just
keep in mind, this is a fire hydrant you're ready to crank open, be ready
to deal with the water.

--
Scott Mead
Sr. Architect
*OpenSCG*
PostgreSQL, Java & Linux Experts
http://www.openscg.com

> Thank you
> Nik
>
> On Sun, Dec 27, 2015 at 5:35 AM, Matheus de Oliveira <
> matioli(dot)matheus(at)gmail(dot)com> wrote:
>
>>
>> On Thu, Dec 24, 2015 at 7:32 PM, Nik Tek <niktek2005(at)gmail(dot)com> wrote:
>>
>>> I wanted to capture all the DML statements executed in the database
>>> information (execution time, ).
>>>
>>> Question:
>>> Can anyone point me pros and cons on either of the approaches.
>>>
>>> In my testing, I haven't found any performance degrade on application
>>> operations when enabled the additional logging (below logging).
>>>
>>
>> Besides log_min_duration_statement, you can use tools like
>> pg_stat_statements [1] and POWA [2]. POWA authors even did a comparison of
>> performance impact of having log_min_duration_statement (to latter process
>> with pgBadger), using POWA extension and nothing, see [3].
>>
>> [1] http://www.postgresql.org/docs/current/static/pgstatstatements.html
>> [2] http://dalibo.github.io/powa/
>> [3] https://github.com/dalibo/powa/wiki/POWA-vs-pgBadger
>>
>>
>> --
>> Matheus de Oliveira
>>
>>
>>
>
>
> --
> Thank you
> NikTeki
>