Обсуждение: Re: measure of JDBC performances
Hello,
On which server platform are you using postgres? Are you using the
JDBC driver of postgres or the JDBC-ODBC driver?
Frederic
> -----Original Message-----
> From: Auri Mason [mailto:amason@syntrex.com]
> Sent: Wednesday, March 13, 2002 9:18 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] measure of JDBC performances
>
>
> Hi to all,
>
> This is my first msg in this list and I hope it isn't OT...
>
> I've a java server application that accecc a database using a JDBC
> driver.
> Due to compare the postgres performances I made two tests
> with the same
> java server using MS Sql 2000 and Postgres 7.2 as database.
> The test reports a time 3 or 4 time longer when using postgres.
> How can I investigate if the bottleneck is the driver or the database?
>
> My first idea is a measure of query execution time with psql
> and via JDBC;
> how can I do that?
>
> TIA, Auri
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Hi Frederic, I'm using postgres on Linux Red Hat 7.2 and the latest (Feb 9) JDBC drivers. Auri On Wed, 13 Mar 2002, Rouyre Frédéric wrote: > Hello, > > On which server platform are you using postgres? Are you using the > JDBC driver of postgres or the JDBC-ODBC driver? > > Frederic
Given that PostgreSQL is running on Linux, I presume that the MS SQL Server is running on another machine. Could you describe the two machines (processor power, amount of main memory)? Where is your client? On a third machine? Peter > -----Original Message----- > From: Auri Mason [mailto:amason@syntrex.com] > Sent: Wednesday, March 13, 2002 9:55 AM > To: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] measure of JDBC performances > > > Hi Frederic, > > I'm using postgres on Linux Red Hat 7.2 and the latest (Feb 9) JDBC > drivers. > > Auri > > On Wed, 13 Mar 2002, Rouyre Frédéric wrote: > > > Hello, > > > > On which server platform are you using postgres? Are > you using the > > JDBC driver of postgres or the JDBC-ODBC driver? > > > > Frederic > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Hi Peter, The Java server tier is running on P4 1900MHz 512MB and access via JDBC to a DB that could be: - PostgreSQL 7.2 - RH 7.2 (running on Celeron 733, 256MB) - MS SQL 2000 SP2 - Win NT4 SP6 (running on a clone machine as Postgres, same proc, same memory, same monitor! ;P ) The client is running on another machine, always the same for both MS SQL and PostgreSQL tests (another clone of DBs machines). Was the idea to measure the execution time with psql and via JDBC wrong? Auri On Wed, 13 Mar 2002, Kovács Péter wrote: > Given that PostgreSQL is running on Linux, I presume that the MS SQL Server > is running on another machine. Could you describe the two machines > (processor power, amount of main memory)? Where is your client? On a third > machine? > > Peter
> Was the idea to measure the execution time with psql and via > JDBC wrong? It is not a wrong idea at all, but I do not think the difference between the performance of psql and JDBC should be significant. What I am confused about is the order of magnitude by which MS SQL outperformes PostgreSQL. I tested PostgreSQL against Oracle, Interbase and Versant (an OODBMS) on Windows 2000 and I found that PostgreSQL was the second best just a bit behind Interbase and scaled much better than Interbase -- and all this with the Cygwin version of PostgreSQL which I expect to be inherently slower than a real "native" version (say on Linux). Performance very much depends on the kind of tasks you execute (ie. the kind of testcases you apply), but for my purposes Interbase and PostgreSQL were significantly better than either Oracle or Versant. So my guess would be, that the observed huge performance gap between MS SQL and PostgreSQL can be explained either (1) by the specific way your Java server tier uses the JDBC connection or (2) some indexing problem (e.g. you forgot to put an index on a column in one database which is indexed in the other). Or you may have a peculiar test case, for which MS SQL is tuned (perhaps by default) much better than PostgreSQL. Peter > -----Original Message----- > From: Auri Mason [mailto:amason@syntrex.com] > Sent: Wednesday, March 13, 2002 10:58 AM > To: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] measure of JDBC performances > > > Hi Peter, > > The Java server tier is running on P4 1900MHz 512MB and > access via JDBC to > a DB that could be: > - PostgreSQL 7.2 - RH 7.2 (running on Celeron 733, 256MB) > - MS SQL 2000 SP2 - Win NT4 SP6 (running on a clone machine > as Postgres, > same proc, same memory, same monitor! ;P ) > > The client is running on another machine, always the same for > both MS SQL > and PostgreSQL tests (another clone of DBs machines). > > Was the idea to measure the execution time with psql and via > JDBC wrong? > > Auri > > On Wed, 13 Mar 2002, Kovács Péter wrote:
Hi again Peter, Thx for you feedback/opinion, > It is not a wrong idea at all, but I do not think the difference between the > performance of psql and JDBC should be significant. My first idea was that the JDBC were the cause of low performances... > and I found that PostgreSQL was the second best just a bit behind Interbase Yeah, good news! This was the result as I'm aspecting! > So my guess would be, that the observed huge performance gap between MS SQL > and PostgreSQL can be explained either >(1) by the specific way your Java server tier uses the JDBC connection Could you explain me exactly what you mean? >(2) some indexing problem I'll check this ASAP! THX, Auri
Hi Auri, >>(1) by the specific way your Java server tier uses the JDBC connection >Could you explain me exactly what you mean? Though the jdbc API itself is fairly unabiguous, the implementations differ slightly from vendor to vendor. For example, the clear intent of the API designer was that result sets should use cursors (see ResultSet.getCursorName). However, last time I checked many of the vendors have not used cursors for selects, because their cursors can only be moved forward and so the methods of the ResultSet class implying backward movement could not have been implemented. Even PGSQL's JDBC driver uses client side caching of the full result set instead of cursors, although the PGSQL cursor supports backward movements. So implementations of different vendors may differ not only between themselves, but also from what behaviour/implementation you would instinctively expect based on the API. If your application relies on a relatively large amount of persistency logic (such as container managed persistency for EJBs) implemented in the "java tier" you use (application server or equivalent), your application may indirectly use generic code (driving the JDBC driver), which is not necessarily aware of the subtle differences between JDBC driver implementations. In this case, the java tier contains built-in assumptions about the behaviour of the JDBC driver, which may hold for most of the JDBC driver implementations, but may not hold for certain implementations resulting in substantial performance degradation. But this is pure speculation, because I do not know (and probably never will :-)) how exactly you use the JDBC driver. Peter -----Original Message----- From: Auri Mason [mailto:amason@syntrex.com] Sent: Wednesday, March 13, 2002 12:49 PM To: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] measure of JDBC performances Hi again Peter, Thx for you feedback/opinion, > It is not a wrong idea at all, but I do not think the difference between the > performance of psql and JDBC should be significant. My first idea was that the JDBC were the cause of low performances... > and I found that PostgreSQL was the second best just a bit behind Interbase Yeah, good news! This was the result as I'm aspecting! > So my guess would be, that the observed huge performance gap between MS SQL > and PostgreSQL can be explained either >(1) by the specific way your Java server tier uses the JDBC connection Could you explain me exactly what you mean? >(2) some indexing problem I'll check this ASAP! THX, Auri ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org