BLOB performance test FYI - Mailing list pgsql-jdbc

Hi,

I'm adding a page on using binary data to a primer doc I'm writing, and have been performing some tests with PostgreSQL
7.2and the latest JDBC code from CVS (14/4). 

The following output shows some results of the test on a FreeBSD 4.5-S machine, P3 1GHz CPU, 1G RAM, PostgreSQL 7.2,
JDK1.3.1p6(OpenJIT); here the test was storing and retrieving increasingly large amounts of random binary data. The
byteatype seems to cause real memory problems around the 4MB mark, presumably from trying to build an insert
statement/StringBufferbig enough for the escaped data. 

I was trying to see what data size would be best for which type. Around even 500k there seems to be a big overhead for
storingbytea, again I guess converting the mostly unprintable binary data into the PGbytea string, though I'm not sure
ifit s a fair comparison test. I don't suppose there's any easy way of streaming the bytea values like the LO manager?
Icould be missing something fundamental about the bytea type, but given its limits, should the JDBC driver act by
defaulton bytea? 

Cheers,
Joe

---
www:~user# java -Xmx128m -classpath .:postgresql.jar BLOBTest postgres username password
[bytea] storing    0k of data -> 24ms
[bytea] retrieving 0k of data -> 7ms
[LO/oid] storing    0k of data -> 19ms
[LO/oid] retrieving 0k of data -> 4ms
[bytea] storing    1k of data -> 8ms
[bytea] retrieving 1k of data -> 1ms
[LO/oid] storing    1k of data -> 3ms
[LO/oid] retrieving 1k of data -> 3ms
[bytea] storing    500k of data -> 4943ms
[bytea] retrieving 500k of data -> 136ms
[LO/oid] storing    500k of data -> 142ms
[LO/oid] retrieving 500k of data -> 28ms
[bytea] storing    1000k of data -> 12423ms
[bytea] retrieving 1000k of data -> 270ms
[LO/oid] storing    1000k of data -> 285ms
[LO/oid] retrieving 1000k of data -> 55ms
Exception in thread "main" java.lang.OutOfMemoryError
        at java.lang.StringBuffer.expandCapacity(StringBuffer.java, Compiled Code)
        at java.lang.StringBuffer.append(StringBuffer.java, Compiled Code)
        at org.postgresql.jdbc2.PreparedStatement.setString(Unknown Source)
        at org.postgresql.jdbc2.PreparedStatement.setBytes(Unknown Source)
        at org.postgresql.jdbc2.PreparedStatement.setBinaryStream(Unknown Source)
        at BLOBTest.storeBlobAsBytea(BLOBTest.java, Compiled Code)
        at BLOBTest.testData(BLOBTest.java, Compiled Code)
        at BLOBTest.main(BLOBTest.java, Compiled Code)

The LO/oid output is using the LargeObject manager, and performance-wise seems to scale well until about 65M in my case
(theresults below can be avoided if I bump -Xmx up to 256M). This output is from commenting out the bytea tests: 

---
www:~user# java -Xmx128m -classpath .:postgresql.jar BLOBTest postgres username password
[LO/oid] storing    0k of data -> 38ms
[LO/oid] retrieving 0k of data -> 5ms
[LO/oid] storing    1k of data -> 3ms
[LO/oid] retrieving 1k of data -> 3ms
[LO/oid] storing    500k of data -> 139ms
[LO/oid] retrieving 500k of data -> 30ms
[LO/oid] storing    1000k of data -> 268ms
[LO/oid] retrieving 1000k of data -> 55ms
[LO/oid] storing    4000k of data -> 1162ms
[LO/oid] retrieving 4000k of data -> 227ms
[LO/oid] storing    16000k of data -> 4817ms
[LO/oid] retrieving 16000k of data -> 868ms
[LO/oid] storing    32000k of data -> 9711ms
[LO/oid] retrieving 32000k of data -> 1704ms
[LO/oid] storing    63999k of data -> 19748ms
Unknown Response Type
Unknown Response Type
        at org.postgresql.core.QueryExecutor.execute(Unknown Source)
        at org.postgresql.Connection.ExecSQL(Unknown Source)
        at org.postgresql.Connection.ExecSQL(Unknown Source)
        at org.postgresql.Connection.setAutoCommit(Unknown Source)
        at BLOBTest.retrieveBlobAsLO(BLOBTest.java, Compiled Code)
        at BLOBTest.testData(BLOBTest.java, Compiled Code)
        at BLOBTest.main(BLOBTest.java, Compiled Code)

--- BLOBTest.java

import java.io.*;
import java.sql.*;
import org.postgresql.largeobject.*;

public class BLOBTest {

    public BLOBTest()
        throws Exception {

        Class.forName("org.postgresql.Driver");

        createDummyDataFile("bin0",256);
        createDummyDataFile("bin1",1024);
        createDummyDataFile("bin512",512000);
        createDummyDataFile("bin1024",1024000);
        createDummyDataFile("bin4096",4096000);
        createDummyDataFile("bin16384",16384000);
        createDummyDataFile("bin32768",32768000);
        createDummyDataFile("bin65535",65535000);
    }

    public static void main( String args[] ) {
        if ( args.length != 3 ) {
            System.err.println("usage: java BLOBTest <database_spec> <username> <password>");
            System.exit(1);
        }

        try {

            BLOBTest test = new BLOBTest();
            long time = 0;

            Connection conn = DriverManager.getConnection(
                "jdbc:postgresql:"+args[0],args[1],args[2]);

            test.testData(conn,"bytea","bin0",256);
            test.testData(conn,"LO/oid","bin0",256);

            test.testData(conn,"bytea","bin1",1024);
            test.testData(conn,"LO/oid","bin1",1024);

            test.testData(conn,"bytea","bin512",512000);
            test.testData(conn,"LO/oid","bin512",512000);

            test.testData(conn,"bytea","bin1024",1024000);
            test.testData(conn,"LO/oid","bin1024",1024000);

            test.testData(conn,"bytea","bin4096",4096000);
            test.testData(conn,"LO/oid","bin4096",4096000);

            test.testData(conn,"bytea","bin16384",16384000);
            test.testData(conn,"LO/oid","bin16384",16384000);

            test.testData(conn,"bytea","bin32768",32768000);
            test.testData(conn,"LO/oid","bin32768",32768000);

            test.testData(conn,"bytea","bin65535",65535000);
            test.testData(conn,"LO/oid","bin65535",65535000);

        } catch ( Exception e ) {
            System.err.println(e);
            e.printStackTrace();
        }
    }

    private void createDummyDataFile( String fileName, int size )
        throws Exception {

        byte data[] = new byte[size];
        FileOutputStream fos = new FileOutputStream(fileName);
        fos.write(data);
        fos.close();
    }

    private void testData( Connection conn, String method, String fileName, int size )
        throws Exception {

        long time;

        // Garbage collect to clean up any garbage objects
        System.gc();

        if ( method.equals("bytea") ) {
            time = storeBlobAsBytea(conn, fileName);
            System.err.println("["+method+"] storing    "+(size/1024)+"k of data -> "+time+"ms");
            time = retrieveBlobAsBytea(conn, fileName);
            System.err.println("["+method+"] retrieving "+(size/1024)+"k of data -> "+time+"ms");
        } else {
            time = storeBlobAsLO(conn, fileName);
            System.err.println("["+method+"] storing    "+(size/1024)+"k of data -> "+time+"ms");
            time = retrieveBlobAsLO(conn, fileName);
            System.err.println("["+method+"] retrieving "+(size/1024)+"k of data -> "+time+"ms");
        }
    }

    private long storeBlobAsBytea( Connection conn, String fileName )
        throws Exception {

        long t1, t2;

        t1 = System.currentTimeMillis();
        File file = new File(fileName);
        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
        PreparedStatement ps = conn.prepareStatement("INSERT INTO blob_bytea VALUES (?, ?)");
        ps.setString(1, file.getName());
        ps.setBinaryStream(2, bis, (int)file.length());
        ps.executeUpdate();
        ps.close();
        bis.close();
        t2 = System.currentTimeMillis();
        return (t2 - t1);
    }

    private long retrieveBlobAsBytea( Connection conn, String fileName )
        throws Exception {

        long t1, t2;

        t1 = System.currentTimeMillis();
        PreparedStatement ps = conn.prepareStatement(
            "SELECT blob_data FROM blob_bytea WHERE blob_name=?");
        ps.setString(1, fileName);
        ResultSet rs = ps.executeQuery();
        if ( rs.next() ) {
            byte[] imgBytes = rs.getBytes(1);
            // use the stream in some way here
        }
        rs.close();
        ps.close();
        t2 = System.currentTimeMillis();
        return (t2 - t1);
    }

    private long storeBlobAsLO( Connection conn, String fileName )
        throws Exception {

        long t1, t2;

        boolean oldState = conn.getAutoCommit();
        try {

            t1 = System.currentTimeMillis();

            // All LargeObject API calls must be within a transaction
            conn.setAutoCommit(false);

            // Get the Large Object Manager to perform operations with
            LargeObjectManager lobj = ((org.postgresql.Connection)conn).getLargeObjectAPI();

            // create a new large object
            int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);

            // open the large object for write
            LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

            // Now open the file
            File file = new File(fileName);
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));

            // copy the data from the file to the large object
            byte buf[] = new byte[2048];
            int s, tl = 0;
            while ((s = bis.read(buf, 0, 2048)) > 0) {
                obj.write(buf, 0, s);
                tl += s;
            }
            // Close the large object
            obj.close();
            // Now insert the row
            PreparedStatement ps = conn.prepareStatement("INSERT INTO blob_lo VALUES (?, ?)");
            ps.setString(1, file.getName());
            ps.setInt(2, oid);
            ps.executeUpdate();
            ps.close();
            bis.close();

            t2 = System.currentTimeMillis();
            return (t2 - t1);

        } finally {
            conn.setAutoCommit(oldState);
        }
    }

    private long retrieveBlobAsLO( Connection conn, String fileName )
        throws Exception {

        long t1, t2;

        boolean oldState = conn.getAutoCommit();

        try {

            t1 = System.currentTimeMillis();

            // All LargeObject API calls must be within a transaction
            conn.setAutoCommit(false);

            // Get the Large Object Manager to perform operations with
            LargeObjectManager lobj = ((org.postgresql.Connection)conn).getLargeObjectAPI();
            PreparedStatement ps = conn.prepareStatement("SELECT blob_data FROM blob_lo WHERE blob_name=?");
            ps.setString(1, fileName);
            ResultSet rs = ps.executeQuery();

            while(rs.next()) {
                // open the large object for reading
                int oid = rs.getInt(1);
                LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
                // read the data
                byte buf[] = new byte[obj.size()];
                obj.read(buf, 0, obj.size());
                // do something with the data read here
                // Close the object
                obj.close();
            }
            rs.close();
            ps.close();

            t2 = System.currentTimeMillis();
            return (t2 - t1);
        } finally {
            conn.setAutoCommit(oldState);
        }
    }
}

---
Finally, to build or run:

  javac -classpath .:postgresql.jar BLOBTest.java

  java -Xmx128m -classpath .:postgresql.jar BLOBTest //somehost/db user password