Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id CC984632BC7 for ; Thu, 2 Sep 2010 16:02:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 08015-05 for ; Thu, 2 Sep 2010 19:02:35 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from n1-vm1.bullet.mail.gq1.yahoo.com (n1-vm1.bullet.mail.gq1.yahoo.com [67.195.23.153]) by mail.postgresql.org (Postfix) with SMTP id F28F9632B76 for ; Thu, 2 Sep 2010 16:02:35 -0300 (ADT) Received: from [67.195.9.81] by n1.bullet.mail.gq1.yahoo.com with NNFMP; 02 Sep 2010 19:02:34 -0000 Received: from [98.137.27.221] by t1.bullet.mail.gq1.yahoo.com with NNFMP; 02 Sep 2010 19:02:34 -0000 Received: from [127.0.0.1] by omp131.mail.gq1.yahoo.com with NNFMP; 02 Sep 2010 19:02:34 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 922568.19509.bm@omp131.mail.gq1.yahoo.com Received: (qmail 30023 invoked by uid 60001); 2 Sep 2010 19:02:34 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1283454154; bh=3o2s67spWSd1fDTlT5YgGNccYAnSXQQyCKuijNj5WCg=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=Wemnz+4LAjUTUgIMpkkLd1O5mXrvS7CThtRPF4nD5R+7M1t1Gh9Cj3xa6nj40uSBB8fxceyi9CopU3YkQK+8GF2MkmL25rp2dmh59KIWc3MZs9PJHR741kFLw9GC5B5/6mVdQgXWSCBps/0dWS5QgYjDQsKl0HqvigqCfKdNVBU= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=ymbW8E0CI7Ujr+QibV/49Cy8jocVh3nwfaxN7b2hKhFd9UlfVDeUavWsK7zSbA/4/1YB1Zyq5oKoAbRUHtsv7C9B9kBSedLIMml1iN3Y21HCOoYljEhg/6hfW2gcxZnkel9Jma5uAlK51d5MfLXghIqFRj8bQHIs0Xz+ioL4YmE=; Message-ID: <458444.29196.qm@web120007.mail.ne1.yahoo.com> X-YMail-OSG: WTtG0VwVM1nyso17eyLG9tfW2IHU.TfdM87QO_3PIa0CNia FkFSriBlvVFyS_a7551irrQts1a7s_osWn5sGrRC6KGYVtInTk0oIVzT16l8 ltzpDmjtYce7N_ODaaZWjT2eOJCQ5FOJj8xaH.12ubUZJG0H.ok4bF.GL7Kr lF6fCdzpogfsSccEyjvBa4KgS6hmche5.I1UGg9_HqhjYUPdIkr5nN6wWlTV fRYTZiw8mnlcT8nVAxQ-- Received: from [68.36.215.11] by web120007.mail.ne1.yahoo.com via HTTP; Thu, 02 Sep 2010 12:02:34 PDT X-Mailer: YahooMailRC/470 YahooMailWebService/0.8.105.279950 References: <788866.21140.qm@web120020.mail.ne1.yahoo.com> <4C7D00330200002500034ED2@gw.wicourts.gov> <19320.56933.qm@web120013.mail.ne1.yahoo.com> <529329.89885.qm@web120007.mail.ne1.yahoo.com> <4C7F8F2A0200002500035037@gw.wicourts.gov> <294404.58402.qm@web120007.mail.ne1.yahoo.com> <4C7FA72C0200002500035095@gw.wicourts.gov> Date: Thu, 2 Sep 2010 12:02:34 -0700 (PDT) From: A J Subject: Re: Confused by 'timing' results To: Kevin Grittner , Scott Marlowe Cc: pgsql-admin@postgresql.org In-Reply-To: <4C7FA72C0200002500035095@gw.wicourts.gov> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1868173971-1283454154=:29196" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/24 X-Sequence-Number: 35568 --0-1868173971-1283454154=:29196 Content-Type: text/plain; charset=us-ascii 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 To: Scott Marlowe ; A J Cc: pgsql-admin@postgresql.org Sent: Thu, September 2, 2010 2:31:24 PM Subject: Re: [ADMIN] Confused by 'timing' results Scott Marlowe wrote: > On Thu, Sep 2, 2010 at 11:34 AM, A J 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 --0-1868173971-1283454154=:29196 Content-Type: text/html; charset=us-ascii
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.Grittner@wicourts.gov>
To: Scott Marlowe <scott.marlowe@gmail.com>; A J <s5aly@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.marlowe@gmail.com> wrote:
> On Thu, Sep 2, 2010 at 11:34 AM, A J <s5aly@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

--0-1868173971-1283454154=:29196--