We are using pgfouine to try and optimize our database at this time. Is
there a way to have pgfouine show examples or breakout commits?



Queries that took up the most time

Rank Total duration Times executed Av. duration (s)
Query

1 26m54s 222,305 0.01
COMMIT;



Perhaps we need to tweak what is being logged by postgresql?



log_destination = 'syslog'

logging_collector = on

log_directory = 'pg_log'

log_truncate_on_rotation = on

log_rotation_age = 1d



syslog_facility = 'LOCAL0'

syslog_ident = 'postgres'



client_min_messages = notice

log_min_messages = notice

log_error_verbosity = default

log_min_error_statement = notice

log_min_duration_statement = 2

#silent_mode = off



debug_print_parse = off

debug_print_rewritten = off

debug_print_plan = off

debug_pretty_print = off

log_checkpoints = off

log_connections = off

log_disconnections = off

log_duration = off

log_hostname = off

#log_line_prefix = ''

log_lock_waits = on

log_statement = 'none'

#log_temp_files = -1

#log_timezone = unknown



#track_activities = on

#track_counts = on

#update_process_title = on



#log_parser_stats = off

#log_planner_stats = off

#log_executor_stats = off

#log_statement_stats = off



Regards,

Josh

Josh,

On Tue, May 6, 2008 at 11:10 PM, Josh Cole <Josh.Cole@t8design.com> wrote:
> We are using pgfouine to try and optimize our database at this time. Is
> there a way to have pgfouine show examples or breakout commits?

I hesitated before not implementing this idea. The problem is that you
often don't log everything and use log_min_duration_statement and thus
you don't have all the queries of the transaction in your log file
(and you usually don't have the BEGIN; command in the logs).

--
Guillaume

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

We are shipping the postgres.log to a remote syslog repository to take the I/O burden off our postgresql server. As such if we set log_min_duration_statement to 0 this allow us to get more detailed information about our commits using pgfouine...correct?

--
Josh

________________________________

From: Guillaume Smet [mailto:guillaume.smet@gmail.com]
Sent: Tue 5/6/2008 7:31 PM
To: Josh Cole
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pgfouine - commit details?



Josh,

On Tue, May 6, 2008 at 11:10 PM, Josh Cole <Josh.Cole@t8design.com> wrote:
> We are using pgfouine to try and optimize our database at this time. Is
> there a way to have pgfouine show examples or breakout commits?

I hesitated before not implementing this idea. The problem is that you
often don't log everything and use log_min_duration_statement and thus
you don't have all the queries of the transaction in your log file
(and you usually don't have the BEGIN; command in the logs).

--
Guillaume