I am conducting the test with several concurrent clients.
The problem I am now facing in using log_min_duration_statement is that all the clients have to write to a single log file in the pg_log directory. So they have to wait for the other writes to happen before completing their write. This seems to be reason why the measured duration in the log file (for several concurrent clients) is way more, infact much more than what was measured by psql timing from the client side.
(The problem with ssh tunnel and then psql on database server is that the database will think the connections are local. I want to mimic real-life where the tcp connections are opened directly by clients from different IPs.)
related settings:
log_destination = 'stderr' # Valid values are combinations of
#log_directory = 'pg_log' # directory where log files are written,
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
From: Scott Marlowe <scott.marlowe@gmail.com>
To: A J <s5aly@yahoo.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin@postgresql.org
Sent: Tue, August 31, 2010 4:02:33 PM
Subject: Re: [ADMIN] Confused by 'timing' results
On Tue, Aug 31, 2010 at 1:01 PM, A J <
s5aly@yahoo.com> wrote:
> OK, thanks Kevin. So to measure just the time take by database server, I
> guess I need to set the log_min_duration_statement and log_statement
> parameters in postgresql.conf
> log_min_duration_statement output should stay constant for all the different
> clients across different geographic locations.
Also, if you want to test turn around time without the disk drives
being an issue, you can do "select 1" instead of "select * from table
(yada)"