Just to give an update, changing pg_log to a different drive that is write cache enabled (and to further make it fast, kept it data=writeback), helped quite a bit.
The average time for several clients hitting concurrently was 15ms each for east-coast as well as west-coast clients. Still some impact of network is still at play as the time taken directly on database server was much less at 10ms.
Not logging at all is still better (the time on database with log_min_duration=-1, is 5.4ms) but putting the log on a different drive adds only minimal overhead and ability to measure query time reasonably by discarding most of the network variance.
From: A J <s5aly@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin@postgresql.org
Sent: Thu, September 2, 2010 3:21:24 PM
Subject: Re: [ADMIN] Confused by 'timing' results
PostgreSQL 8.4
CentoOS 5.5
I have got WCE=0, on the drive that mounts the data directory with all its subdirectory (including pg_log)
Maybe I should try to mount pg_log to a different drive and have write cache enabled on that one.
From: Tom Lane <tgl@sss.pgh.pa.us>
To: A J <s5aly@yahoo.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin@postgresql.org
Sent: Thu, September 2, 2010 3:17:42 PM
Subject: Re: [ADMIN] Confused by 'timing' results
A J <
s5aly@yahoo.com> writes:
> Do you think changing log_destination to syslog may make a difference
It's worth trying alternatives anyway. It is odd that you are seeing
such a slowdown when using the collector --- many people push very high
log volumes through the collector without problems. What PG version is
this exactly, and on what platform?
regards, tom lane
