Обсуждение: slow jdbc postgres connection
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
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
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
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
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
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
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.