Oracle jdbc perf on linux and windows

[Jean-Charles Godiien]

Hi,

I'am a newbie in "java technolgie" and I have serious performance issues
with an application running on a linux server. Many, many hours of tests
latters, problems seems to be related to jdbc on linux.

We are deploying a webobjects application, but I don't think it's a webobject
issue (?). In short for a reasonable SQL query :
- 25s width jdk1.3 and classes12.zip on linux server (2Ghz, 2G,5 Go RAM linux
Redhat 8.0),
- 2s width jdk1.3 and classes12.zip on a PIII 450 380Mb with windows XP.
Horrific !!! I have two identical servers for backup and the second one MUST be
linux.

On another try a xeon 3Ghz, 1 Go RAM fedora-2), jdk 1.4.2 and ojdbc14.jar
application perform better : 6/7 seconds but it's, at least, 3 time my old
supermarket PC and his old jdk !
No matter of firewall, I made my try on several subnets including the database subnet,
command option for the jvm are strictly the sames and application perform great on
all linux servers... until the first SQl query. Application is also report to work on
mac OS X. No errors, timeout or warning in application or oracle logs. So what ?

I can't explain this. Can some could ? And give me the trick to stop the world to be
upside down. I don't want to die stupid ; nor hosting a 2003 server

Thanks by advance


- Jean-Charles -

 

[Steve Horsley]

Hi,

I'am a newbie in "java technolgie" and I have serious performance issues
with an application running on a linux server. Many, many hours of tests
latters, problems seems to be related to jdbc on linux.

We are deploying a webobjects application, but I don't think it's a
webobject
issue (?). In short for a reasonable SQL query :
- 25s width jdk1.3 and classes12.zip on linux server (2Ghz, 2G,5 Go RAM
linux
Redhat 8.0),
- 2s width jdk1.3 and classes12.zip on a PIII 450 380Mb with windows XP.
Horrific !!! I have two identical servers for backup and the second one
MUST be
linux.

I am probably wrong, but it's worth a try...

Make sure that the Oracle server is in the /etc/hosts file so that its
name/address can be resolved. It just moght be that the Linux box is
trying to do a DNS lookup for a long time.

HTH
Steve

 

[Jean-Charles Godiien]

Steve Horsley a écrit:

Make sure that the Oracle server is in the /etc/hosts file so that its
name/address can be resolved. It just moght be that the Linux box is
trying to do a DNS lookup for a long time.

An idea, but it's not a name resolving issue, I use IP adresse, not db
server name (and as I spend a loooong time on this problem : tcpdump see
nothing).

I can't immagine it's a general problem with java application running on
linux/unix and using oracle db since a lot of application around the world
are running this configuration (and google is dumb). But I wish to be
sure...

It's possibly unrelated, but another of our team have similar problems (I
don't konow details) width a tomcat app running realy bad with 5 (!) users
on a huge Tru64 UNIX. It's completly different database on another host but
oracle too. This app seem running blazing fast on Windows 2003 !

Is this group the good posting choice or is there a more dedicated java/db
newsgroup ?

Thanks

- Jean-Charles -

 

[Sam]

Steve Horsley a écrit:


An idea, but it's not a name resolving issue, I use IP adresse, not db
server name (and as I spend a loooong time on this problem : tcpdump see
nothing).

I can't immagine it's a general problem with java application running on
linux/unix and using oracle db since a lot of application around the world
are running this configuration (and google is dumb). But I wish to be
sure...

It's possibly unrelated, but another of our team have similar problems (I
don't konow details) width a tomcat app running realy bad with 5 (!) users
on a huge Tru64 UNIX. It's completly different database on another host but
oracle too. This app seem running blazing fast on Windows 2003 !

Is this group the good posting choice or is there a more dedicated java/db
newsgroup ?

Thanks

- Jean-Charles -

In a timing problem (or any other kind, come to think of it), I try to
narrow down the problem area as much as possible. Make sure you know
the exact statement where the delay is incurred.

Since use of SQL introduces problem, I would first try to eliminate
Oracle as the culprit. What happens if you run the SQL statement where
the problem occurs as a statement in Oracle's command line prompt? If
it runs fine, then your problem lays in your app, otherwise you've got
an Oracle configuration problem.

If it's in your app, double check your SQL. Some SQL syntax if
improperly done can be tremendously slow. A common one is the "NOT IN"
(or is it not exists?) phrase, which requires a scan of the entire
file for each record examined.

If in fact it is the communication overhead with Oracle, I don't thing
TCPDUMP will help if your app is running on the same system because
TCPDUMP only reveals traffic on that comes over the NIC card (there
may be a localhost setting). There might be an oracle log or something
you could check.

Good Luck,
Sam90

 

[Juha Laiho]

We are deploying a webobjects application, but I don't think it's a webobject
issue (?). In short for a reasonable SQL query :
- 25s width jdk1.3 and classes12.zip on linux server (2Ghz, 2G,5 Go RAM linux
Redhat 8.0),
- 2s width jdk1.3 and classes12.zip on a PIII 450 380Mb with windows XP.
Horrific !!! I have two identical servers for backup and the second one MUST be
linux.

On the Linux machine, what happens during that 25s? Is the process using
100% (or whatever is remaining) of the CPU power all the time, or is the
process waiting? (f.ex. 'top' command can be used for finding out this)

At least if the process is waiting, try to find out what it is waiting
for. This can be done f.ex. by running the application under 'strace'
with timestamping enabled, and looking at what was the system call that
causes the program to pause.

If the program did use all that 25s by running on the CPU, the strace may
or may not help: strace only shows calls to the OS, but nothing about what
the program does in user mode; also, even if the program did make any
OS calls during the 25s, it can be expected that the resulting strace
trace file is huge.

 

[Jean-Charles Godiien]

Sam a écrit:

In a timing problem (or any other kind, come to think of it), I try to
narrow down the problem area as much as possible. Make sure you know
the exact statement where the delay is incurred.

Since use of SQL introduces problem, I would first try to eliminate
Oracle as the culprit. What happens if you run the SQL statement where
the problem occurs as a statement in Oracle's command line prompt? If
it runs fine, then your problem lays in your app, otherwise you've got
an Oracle configuration problem.

You're absolutelly right but my app work at reasonable speed in windows
environment (and I is know to work with macos X too). Oracle is inocent.

If in fact it is the communication overhead with Oracle, I don't thing
TCPDUMP will help if your app is running on the same system because
TCPDUMP only reveals traffic on that comes over the NIC card (there
may be a localhost setting). There might be an oracle log or something
you could check.

It was a last chance attempt to discover an highly unproblable network
filtering problem with something like DNS, auth daemon or whatever else
locking my application. It was before I transport it in the same subnet
than my supermarket PC.

Good Luck,

Thank for spending some time on my problem.


- Jean-Charles -

 

[Steve Horsley]

An idea, but it's not a name resolving issue, I use IP adresse, not db
server name (and as I spend a loooong time on this problem : tcpdump see
nothing).

Are you saying that because you tried my suggestion, or because you think
it cannot be so?

I once got called to look at a "network fault" where a telnet connection
to a server took exactly 60 seconds to respond with a login prompt. That
turned out to be the server trying to ask a non-existent DNS server what
the name of the calling IP address was. Loke you, they just knew that it
couldn't be a problem with their configuration - it was obviously a
network fault (that just happened to go away when they changed their DNS
config).

Steve

 

[Sam]

Sam a écrit:

You're absolutelly right but my app work at reasonable speed in windows
environment (and I is know to work with macos X too). Oracle is inocent.

Clearly there's some variation in the environment that affects the
speed of the application. Can you guarantee that Oracle runs your
particular SQL statement as well on Linux as it does on Windows/MacOS?
I would test the SQL from the command line...just to be sure. (You can
display it in debugger and paste it to the command line - trust me
it's worth it).

If that runs ok, and you're sure that it's the execute jdbc statement
where the problem, then it's possible there's an issue with the
driver. What driver are you using? It's possible that it's some kind
of generic ODBC driver pre-packaged with Java Linux, which could lead
to brutal performance. Make sure you're using Oracle's recommended
driver for Java 1.3 on Linux.

Thank for spending some time on my problem.

Je vous en prie. Bon chance.

Sam90

 

[Jean-Charles Godiien]

Juha Laiho a écrit:

On the Linux machine, what happens during that 25s? Is the process using
100% (or whatever is remaining) of the CPU power all the time, or is the

process waiting? (f.ex. 'top' command can be used for finding out this)
At least if the process is waiting, try to find out what it is waiting
for. This can be done f.ex. by running the application under 'strace'
with timestamping enabled, and looking at what was the system call that
causes the program to pause.

Interesting questions and good advice...
The process is mostly idle : between 2 and 5% cpu.
strace give me some information : slow reading on network. java -Xperf
confirm : 86% idle and most activity in SocketRead.

Hopefully in searching some information on this slow function I found a
guy with similar problem on AIX and someone with a precise idea of what
happend : http://dbforums.com/t405329.html

If you are using oracle server < 8.1.7.3 and jdbc thin driver : read this
post ! And then this paper : http://miracleas.dk/tools/Miracle_4_nagle.pdf

Oracle use by default a stupid (?) option called TCP.NODELAY witch can be
desactivated on the server or at client request.
Thin driver on windows seem to disable this option by defaut (or oracle
disable it if the client is windows ?), not on linux/unix.
Moreover you must modifify your code (and I can't) and get a recent driver
to disable it.
Other solution is to convince dba to modify his parameters (I try)
You can also use oci driver (with oracle client > 9 ?) : I did and it works
2s, 15% idle, just a little bit slower than windows.

Thanks everyone (and many thanks to Dave Barton...)