Обсуждение: postgreSQL query via JDBC in different OS taking different running time?

Aftab Ahmed Chandio <aftabac@siat.ac.cn> wrote:

> My query processes from JDBC (Java Program) to PostgreSQL. I use
> system time by invoking java function, I collect one time unit
> before the query statement perform and second after the execution
> of query statement.
> I found 85 ms time unit in DOS (win7) (laptop 4cores). both Java
> and PostgreSQL installed and invoked on the same machine,
> respectively.
> On the other hand, I use same process (separate installation) on
> linux on 8 cores physical machine with 2times greater then
> laptop.
> I found 150 ms. (which is a question for me because the time in
> Linux environment should give me half of the time taking on
> laptop)
> I also make same setting  of postgresql.conf in the linux setup,
> which is available same in the win7  setup, because win7 setup
> gives better performance of the query.
> What do u suggest me, where I need to make performance tuning?
> which configuration setting must need to modify in the linux?
> * laptop RAM 4 GB and Linux machine 32 GB

Given a little time, I could probably list 100 plausible reasons
that could be.  For my part, load balancing a production system
between PostgreSQL on Windows and on Linux hitting identical
databases on identical hardware, I saw 30% better performance on
Linux.

I would start by getting timings for query execution using EXPLAIN
ANALYZE, to see how PostgreSQL itself is performing on the two
environments.  I would test raw connect/disconnect speed.  I would
benchmark RAM using STREAM and disk using bonnie++.

You might want to review this page, and post a more detailed report
to the pgsql-performance list:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

Posting to multiple lists is generally considered bad form.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 10/7/2013 6:48 PM, Aftab Ahmed Chandio wrote:
> I found 85 ms time unit in DOS (win7) (laptop 4cores). both Java and
> PostgreSQL installed and invoked on the same machine, respectively.
> On the other hand, I use same process (separate installation) on linux
> on 8 cores physical machine with 2times greater then laptop.
> I found 150 ms. (which is a question for me because the time in Linux
> environment should give me half of the time taking on laptop)

a single connection session will only use a single core at a time.

depending on the nature of this query, it may have been CPU or Disk IO
bound, without knowing the query, the database schema, and the hardware
specification of both systems, its impossible to guess.

first thing to do is run..

     explain analyze  ...your query here...;

on both platforms, and verify they are doing the same thing.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Aftab Ahmed Chandio wrote
> I found 85 ms time unit in DOS (win7) (laptop 4cores). both Java and
> PostgreSQL installed and invoked on the same machine, respectively.
> On the other hand, I use same process (separate installation) on linux on
> 8 cores physical machine with 2times greater then laptop.
> I found 150 ms. (which is a question for me because the time in Linux
> environment should give me half of the time taking on laptop)

I'm not particularly performance measuring experienced but a few items come
to mind:

A single (or handful) of manual runs is not going to provide good data for
comparison
Hard drive characteristics can make a difference

A single query uses a single process/thread so core count is irrelevant
RAM is likely immaterial though depends heavily on the dataset

These last two factors is why your "2times greater" system is in fact nearly
identical to the laptop with respect to its ability to run and single query.
Your Linux system is probably capable of handling twice the data and
simultaneous connections but each connection is limited.

Lastly, the execution times - while relatively different - are both quite
small and subject to considerable system noise - which is why a single run
is insufficient to draw conclusions.

I'm not really sure what kind of positive response you want.  There may be
room to improve the Linux setup, and using the same configuration on two
difference OS is not going to mean they should be expected to provide the
same performance, but you need to be much more detailed in what you are
testing and your measurement procedure if you expect any actionable advice.

In order to do performance tuning you need to setup a realistic environment
within with to perform measurements.  You are either lacking that or have
failed to describe it adequately.  Once you can measure those measurements
will guide you to where to need to either tweak settings or improve
hardware.

Finally, the ability and need for configuration changes is highly dependent
upon the version of PostgreSQL you are running - that should be the first
thing you disclose.

And then, you presume that it is differences in PostgreSQL that are to be
solved but you have the entire Java VM to be concerned with as well.
Running your queries in psql removes that variable and helps pin-point where
the tuning likely needs to occur.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgreSQL-query-via-JDBC-in-different-OS-taking-different-running-time-tp5773618p5773637.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, Oct 8, 2013 at 3:48 AM, Aftab Ahmed Chandio <aftabac@siat.ac.cn> wrote:
> What do u suggest me, where I need to make performance tuning? w hich
> configuration setting must need to modify in the linux?

Well, others have already pointed out that you should first measure
your query on the server. I would point out that the JVM itself could
be different or differently configured on Linux/win machines, and this
will lead to different results.
Second it is not clear to me why are you measuring the same query on
different machines and OSs, or better, why are you assuming the
resulting time should be the same.

Luca

On Mon, Oct 7, 2013 at 10:35 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Aftab Ahmed Chandio <aftabac@siat.ac.cn> wrote:
>
>> My query processes from JDBC (Java Program) to PostgreSQL. I use
>> system time by invoking java function, I collect one time unit
>> before the query statement perform and second after the execution
>> of query statement.
>> I found 85 ms time unit in DOS (win7) (laptop 4cores). both Java
>> and PostgreSQL installed and invoked on the same machine,
>> respectively.
>> On the other hand, I use same process (separate installation) on
>> linux on 8 cores physical machine with 2times greater then
>> laptop.
>> I found 150 ms. (which is a question for me because the time in
>> Linux environment should give me half of the time taking on
>> laptop)
>> I also make same setting  of postgresql.conf in the linux setup,
>> which is available same in the win7  setup, because win7 setup
>> gives better performance of the query.
>> What do u suggest me, where I need to make performance tuning?
>> which configuration setting must need to modify in the linux?
>> * laptop RAM 4 GB and Linux machine 32 GB
>
> Given a little time, I could probably list 100 plausible reasons
> that could be.  For my part, load balancing a production system
> between PostgreSQL on Windows and on Linux hitting identical
> databases on identical hardware, I saw 30% better performance on
> Linux.

One sneaky way that windows tends to beat linux is that windows has a
low precision high performance timer that linux does not have.   This
affects both java and postgres and particularly tends to show up when
benchmarking with times.

merlin