Thread: log_statement vs Statistics Collector (pg_stat_database,, etc)
Hi pg gurus,
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).
log_error_verbosity = verbose
log_min_duration_statement = 0
log_statement = 'mod'
log_checkpoints = on
log_min_duration_statement = 0
log_statement = 'mod'
log_checkpoints = on
--
Thank you
NikTeki
On Thu, Dec 24, 2015 at 7:32 PM, Nik Tek <niktek2005@gmail.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
[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 Matheus!
Based on the study, there's very little performance impact when enabling log_min_duration_statement, right?
Thank you
Nik
On Sun, Dec 27, 2015 at 5:35 AM, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:
On Thu, Dec 24, 2015 at 7:32 PM, Nik Tek <niktek2005@gmail.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
I am sorry. I am late to this thread. Please do not think there is zero impact.
-------- Original message --------
From: Nik Tek <niktek2005@gmail.com>
Date: 12/28/2015 6:05 PM (GMT-06:00)
To: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] log_statement vs Statistics Collector (pg_stat_database,, etc)
--
-------- Original message --------
From: Nik Tek <niktek2005@gmail.com>
Date: 12/28/2015 6:05 PM (GMT-06:00)
To: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] log_statement vs Statistics Collector (pg_stat_database,, etc)
Thank you Matheus!
Based on the study, there's very little performance impact when enabling log_min_duration_statement, right?
Thank you
Nik
On Sun, Dec 27, 2015 at 5:35 AM, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:
On Thu, Dec 24, 2015 at 7:32 PM, Nik Tek <niktek2005@gmail.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
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com
Austin, TX 78757
www.journyx.com
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
On Mon, Dec 28, 2015 at 7:03 PM, Nik Tek <niktek2005@gmail.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
Scott Mead
Sr. Architect
OpenSCG
PostgreSQL, Java & Linux Experts
Thank youNik--On Sun, Dec 27, 2015 at 5:35 AM, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:On Thu, Dec 24, 2015 at 7:32 PM, Nik Tek <niktek2005@gmail.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 OliveiraThank youNikTeki