With this approach, I will be assuming that the query time does not change due
to client location, which though reasonable, is still an assumption. If I could
have tested without making this assumption (or any) , it would have been better.
But looks like there is no choice as getting to query time measurement for
queries fired by clients is not possible.
I would still be firing concurrent clients across the different locations but
measuring the 'psql timing' for only the queries fired on the database server.
Will extrapolate the outlier % of the queries on database server (say queries
that take more than 200 ms due to flushing of checkpoints etc) to get to the
total outliers.
This is good enough for the time being and will try it. If you can think of
alternatives where I don't have to assume/extrapolate, please let me know.
Do you think changing log_destination to syslog may make a difference (Kevin
mentioned even this timing is not totally immune from network effects but if
possible to measure should be very close to the query time) ?
________________________________
From: Kevin Grittner <kevin.gritt...@wicourts.gov>
To: Scott Marlowe <scott.marl...@gmail.com>; A J <s5...@yahoo.com>
Cc: pgsql-admin@postgresql.org
Sent: Thu, September 2, 2010 2:31:24 PM
Subject: Re: [ADMIN] Confused by 'timing' results
Scott Marlowe <scott.marl...@gmail.com> wrote:
> On Thu, Sep 2, 2010 at 11:34 AM, A J <s5...@yahoo.com> wrote:
>> The problem I am trying to solve is:
>> measure accurately both the database server time + network time
>> when several clients connect to the database from different
>> geographic location. All the clients hit the database
>> simultaneously with a long script each of insert/update/select
>> queries.
>
> Then that's what you should test. create long scripts, run them
> from different locales, and measure the overall time differences,
> if any, of the same file from different locales.
I'm inclined to agree with Scott. The effects of the network come
into play in several different ways, and I can't think of a better
way to isolate those effects from the query run time itself than to
run exactly the same queries on the server itself and from the
various remote locations. Subtract the server-based time from each
location's time to find the impact of the network. Doesn't that
address your problem fairly directly and accurately?
-Kevin
