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

----- 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. "