Re: raw data broken in 7.2 driver? - Mailing list pgsql-jdbc

Thanks for sharing the inforamtion.

I am also interested in the performance issue, especial binary data for image files.

Marin's comparsion test concludes that binary data insertion (write ?) operation in pg is not as good as in Oracle. How
is 
the read operation?


4/10/2002 11:58:42 PM, "Marin Dimitrov" <marin.dimitrov@sirma.bg> wrote:

>
>----- Original Message -----
>From: "David Bernhill"
>
>>
>> Great! Thanks for your answer. Are there any documentation/discussion
>> concerning the performance of the jdbc driver for postgresql (or some
>> tuning parameters that can be of interest)? Are there any figures
>> related to "blob" performance? I takes me about 11 seconds to insert 1mb
>> raw data in postgresql, compared to 1.5 second in oracle. I tried the
>> bytea type as well, but the result was more or less the same.
>>
>
>I've created a sample java test that inserts 16K/128K/512K/1024K binary data
>in Oracle9i and Postgres7.2 databases residing on the same server.
>
>Data is stored in Oracle as BLOB, in Postgres as BYTEA, only the thin
>drivers were used (one usually gets at least 20% better performance with the
>Oracle OCI driver). The test reports the avg time of 3 consecutive inserts
>of data chunks of the same size.
>
>the differences in performance are comparable with the ones you report but
>they vary substantially depending on the size of the binary data. The
>difference in *times* (Oracle vs Postgres, with Oracle considered as base )
>is as follows
>
>16K          - x 1.5
>128K        - x 7.4
>512K        - x 16.9
>1024K      - x  22.4
>
>Note that the Oracle instance is fairly well tuned, while the Postgres one
>is a default installation with the buffer cache set to 3000 (x 8K) so these
>numbers are unlikely to be very representative.
>
>The facts of concern are that:
>
>    -  the difference (in times) in performance grows with the size of
>binary data
>
>    - the CPU load on the client machine during the test was ~80% when
>connected to Oracle, while when connected to Postgres it was constantly 100%
>
>    - the memory consumption for Oracle is almost constant (<15MB) while in
>the case of the postgres driver the memory used grows as the binary data
>size grows (up to 120MB!) which is strange IMHO - although bytea data cannot
>be read in chunks but only as a whole, this should not affect data writes,
>since the source buffer is already in memory
>
>
>the code looks almost like in the Programmer's manual, so it's unlikely that
>there is something wrong with it (I don't quite care about the proper
>cleanup for this test):
>
>  public void testPostgresLOB(int size, int count) throws Exception {
>
>    byte[] buffer = new byte[size];
>    String url =
>"jdbc:postgresql://192.168.128.208:5432/gate09?user=gateuser&password=gate";
>
>    try {
>      Connection conn = DBHelper.connect(url);
>      conn.setAutoCommit(false);
>      PreparedStatement pstmt = conn.prepareStatement("insert into lob_test
>values(?)");
>
>      for (int i =0; i< count; i++) {
>        pstmt.setBytes(1,buffer);
>        pstmt.executeUpdate();
>        conn.commit();
>      }
>    }
>    catch(Exception e) {
>      e.printStackTrace();
>    }
>  }
>
>
>is there anything specific in the Postgres configuration that could improve
>binary data handling?
>
>thanx,
>
>    Marin
>
>----
>"...what you brought from your past, is of no use in your present. When
>you must choose a new path, do not bring old experiences with you.
>Those who strike out afresh, but who attempt to retain a little of the
>old life, end up torn apart by their own memories. "
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>