Thread: Confused by 'timing' results

A J <s5aly@yahoo.com> wrote:

> time echo '\timing \\select * from  table1 where id = 123;' | psql

> In the above query. the 'timing' will time the database time and
> the 'time' command at the very start will time the complete time
> for the query including network time.

No, the 'timing' will say how long it took to send the query from
psql to the server and get the complete response back from the
server.  The 'time' command will also include the time to start
psql, establish the connection to the database, read from the pipe,
and close the connection to the database.

-Kevin

A J <s5aly@yahoo.com> wrote:

> log_min_duration_statement output should stay constant for all the
> different clients across different geographic locations.

I'm not sure timings there will be totally immune to network speed.
The whole execution engine is designed around the top level pulling
rows from other levels.  If it is sending those rows over the wire
as it pulls them, it could block at times, causing a delay before
completion of the query.

I haven't looked at the PostgreSQL wire protocol in detail, but
other database products where I became familiar with the wire
protocol had many round trips per query run -- making run time
sensitive to the network latency.  I've generally gone with a thin
tier on the database server to field requests and provide responses
using stream-oriented protocols with moving windows so I can keep
the database protocol to the fastest, lowest latency connection I
can arrange.  I like to use queues in that middle tier for incoming
requests and outgoing responses, to minimize the impact of network
throughput and latency.

-Kevin

*ukasz Brodziak<lukasz.brodziak@hotmail.com> wrote:

> I would like to perform some tasks on my linux (ubuntu) using db
> from work unfortunatly the DB has WIN1250 encoding and the
> PostgreSQL in linux has UTF-8 is it possible to somehow change the
> server's locale as I may not change the encoding of the DB.

Are you trying to connect to a PostgreSQL server which runs on
Windows using a client on Linux, or are you trying to copy the
database and run your own server on Linux?  What database
version(s), exactly?

-Kevin

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)"

*ukasz Brodziak<lukasz.brodziak@hotmail.com> wrote:

> The version in 8.4. I'm trying to copy the database from Windows
> to Linux.  If it is of any importance the version of "Windows
> Postgres" is 8.2

Use pg_dump from your 8.4 machine.  I would use the --encoding
switch when I ran pg_dump to get UTF-8 encoding in the dump file.
Make sure you initialize your new database with a character set
which supports all the characters from the old database.

-Kevin

>> time echo '\timing \\select * from  table1 where id = 123;' | psql
>> In the above query. the 'timing' will time the database time and
>> the 'time' command at the very start will time the complete time
>> for the query including network time.
>
> No, the 'timing' will say how long it took to send the query from
> psql to the server and get the complete response back from the
> server.  The 'time' command will also include the time to start
> psql, establish the connection to the database, read from the pipe,
> and close the connection to the database.

if you'd want to get the real execution time for the query from a
faraway client (without network latency), you could open an ssh
session and start psql on the database server.


--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw

A J <s5aly@yahoo.com> wrote:
> I am conducting the test with several concurrent clients.

I didn't see a question in your latest email.  Do you now understand
why the network affects timings?  Do you have any other questions?
Is there some particular problem you're trying to solve, for which
you don't yet have a solution?  (If so, please describe the problem
you're trying to solve; someone might be able to suggest a solution
you won't get to by asking narrower questions.)

-Kevin

A J <s5aly@yahoo.com> writes:
> 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.

Do you have any actual evidence for that being the problem, as opposed
to some other effect?  Because I don't recall anybody else complaining
about this.

Personally I'd wonder for example about whether the server has a really
slow gettimeofday()...

            regards, tom lane

On Thu, Sep 2, 2010 at 11:34 AM, A J <s5aly@yahoo.com> wrote:
> Kevin
> 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.

A J <s5aly@yahoo.com> writes:
> On second try, by trying to log to log_directory/log_filename by
> setting log_min_duration_statement=0, seems to be doing something weird. The
> durations are very very high in the file and cannot be true.

You're not being very clear here.  Did the logged durations not
correspond to reality?  Or did the performance as seen from the clients
drop substantially when you turned on extra logging?  Also, exactly
how are you doing logging (ie, what are your settings for
log_destination and related parameters)?

            regards, tom lane

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

A J <s5aly@yahoo.com> writes:
> The performance as seen from the clients dropped substantially after turning on
> the extra logging. The numbers were real but the performance dropped
> significantly.

> All the log related settings in postgresql.conf are below:

Hmm, what about logging_collector?  (Or it might be called
redirect_stderr, depending on which PG version this is.)
If it's currently off, see whether turning it on improves matters.

            regards, tom lane

A J <s5aly@yahoo.com> wrote:

> 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.

As I explained in an earlier post, the query can block on the server
due to network bandwidth or latency.  So the "wall time" for query
execution can indeed be different based on location, especially if
you are returning a large result set.  But why would you want to
separate out this type of network delay from the others?

If you want to eliminate it as a factor, you need some tier to
receive requests and queue them, pull them from the queue and run
them with another queue for results, and send the results from the
queue back to the requester.  This is what we do, BTW, and it does
give us the ability to totally isolate run time from network
influences.

-Kevin

On Thu, Sep 2, 2010 at 1:02 PM, A J <s5aly@yahoo.com> wrote:
> 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) ?

At least try putting it on a different drive if you can, or remote
logging if you can't plug another drive in to the machine to put the
logs on.


--
To understand recursion, one must first understand recursion.

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

On Thu, Sep 2, 2010 at 1:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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?

We push a ton through the collector (up to 10 Megs a second at peak)
with no problems. but it doesn't share a drive with the main store or
pg_xlog.