Thread: slow jdbc postgres connection

slow jdbc postgres connection

From
Stefan Zweig
Date:
hi i have the following problem:

when i execute the following query via pgAdminIII i get a result within 31ms

Select version()
result: PostgreSQL 8.1

if try to execute the same query using the jdbc-interface from a java-application the same query needs horrible 4756ms.
my jdbc driver is  8.1-409 JDBC 3
my postgres version is 8.1.8
my JRE-Version: 1.5.0_07

Connection pgconnection = null;

// Load the pgSQL JDBC driver
String pgdriverName = "org.postgresql.Driver";
Class.forName(pgdriverName);

// Create a connection to the "test" database
String pgserverName = "10.100.6.245";
String pgdatabase = "test";
String url = "jdbc:postgresql://" + pgserverName +  "/" + pgdatabase + "?characterEncoding=UTF8&useUnicode=true";
String username = "user";
String password = "password";

pgconnection = DriverManager.getConnection(url, username, password);

ResultSet pgresultSet;
Statement pgstatement;
pgstatement = pgconnection.createStatement();

String pgquery;
pgquery="SELECT version()";


long start=System.currentTimeMillis();//start time measurement

pgresultSet = pgstatement.executeQuery(pgquery);

long time=System.currentTimeMillis()-start;//end time measurement

//time =4756ms

pgconnection.close();


if i do the similar query on our mysql database, which is on the same local server, i get quick results, so the reason
probablycannot be a slow intranet-connection 


any help would be appriciated.


regards. stefan
_______________________________________________________________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192


Re: slow jdbc postgres connection

From
Stefan Zweig
Date:
hi,

just to be sure that it cannot be the intranet-connection i tried to run my java test database application directly on
theserver. mysql-jdbc answered within 20ms pgsql-jdbc needed 5494ms and i have no idea why? 

any advices would be appreciated.

regards. stefan
> hi i have the following problem:
>
> when i execute the following query via pgAdminIII i get a result within 31ms
>
> Select version()
> result: PostgreSQL 8.1
>
> if try to execute the same query using the jdbc-interface from a java-application the same query needs horrible
4756ms.
> my jdbc driver is  8.1-409 JDBC 3
> my postgres version is 8.1.8
> my JRE-Version: 1.5.0_07
>
> Connection pgconnection = null;
>
> // Load the pgSQL JDBC driver
> String pgdriverName = "org.postgresql.Driver";
> Class.forName(pgdriverName);
>
> // Create a connection to the "test" database
> String pgserverName = "10.100.6.245";
> String pgdatabase = "test";
> String url = "jdbc:postgresql://" + pgserverName +  "/" + pgdatabase + "?characterEncoding=UTF8&useUnicode=true";
> String username = "user";
> String password = "password";
>
> pgconnection = DriverManager.getConnection(url, username, password);
>
> ResultSet pgresultSet;
> Statement pgstatement;
> pgstatement = pgconnection.createStatement();
>
> String pgquery;
> pgquery="SELECT version()";
>
>
> long start=System.currentTimeMillis();//start time measurement
>
> pgresultSet = pgstatement.executeQuery(pgquery);
>
> long time=System.currentTimeMillis()-start;//end time measurement
>
> //time =4756ms
>
> pgconnection.close();
>
>
> if i do the similar query on our mysql database, which is on the same local server, i get quick results, so the
reasonprobably cannot be a slow intranet-connection 
>
>
> any help would be appriciated.
>
>
> regards. stefan
> _______________________________________________________________
> SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
> kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


_______________________________________________________________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192


Re: slow jdbc postgres connection

From
Heikki Linnakangas
Date:
Stefan Zweig wrote:
> when i execute the following query via pgAdminIII i get a result within 31ms
>
> Select version()
> result: PostgreSQL 8.1
>
> if try to execute the same query using the jdbc-interface from a java-application the same query needs horrible
4756ms.
> my jdbc driver is  8.1-409 JDBC 3
> my postgres version is 8.1.8
> my JRE-Version: 1.5.0_07

Strange. Are you sure the time is spent on executing the query, and not
on acquiring the connection? What kind of an application is it? There's
no application server or framework involved? Would you please add
loglevel=1 to the URL, and post the output? Maybe that will give a clue.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: slow jdbc postgres connection

From
Heikki Linnakangas
Date:
I wrote:
> Strange. Are you sure the time is spent on executing the query, and not
> on acquiring the connection? What kind of an application is it? There's
> no application server or framework involved? Would you please add
> loglevel=1 to the URL, and post the output? Maybe that will give a clue.

Actually loglevel=2, that'll give more details..

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: slow jdbc postgres connection

From
Oliver Jowett
Date:
Stefan Zweig wrote:

> long start=System.currentTimeMillis();//start time measurement
>
> pgresultSet = pgstatement.executeQuery(pgquery);
>
> long time=System.currentTimeMillis()-start;//end time measurement
>
> //time =4756ms

And if you run the query a second time on the same connection (or even
on a completely new connection in the same JVM), what's the time for
that? I'd guess you're measuring mostly classloading costs.

-O


Re: slow jdbc postgres connection

From
Oliver Jowett
Date:
Stefan Zweig wrote:

> String url = "jdbc:postgresql://" + pgserverName +  "/" + pgdatabase + "?characterEncoding=UTF8&useUnicode=true";

One other note, the PG driver will not understand (and silently ignores)
the "characterEncoding" and "useUnicode" URL parameters.. not sure quite
what you're trying to do there.

-O

Re: slow jdbc postgres connection

From
Craig Servin
Date:
Without seeing more of the logging information it is hard to tell what the
problem may be, but as a sanity check I can tell you that I have never seen a
5000ms connection time.



On Thursday 26 April 2007 04:43:59 am Stefan Zweig wrote:
> hi,
>
> just to be sure that it cannot be the intranet-connection i tried to run my
> java test database application directly on the server. mysql-jdbc answered
> within 20ms pgsql-jdbc needed 5494ms and i have no idea why?
>
> any advices would be appreciated.