Re: BLOB performance test FYI - Mailing list pgsql-jdbc

Joe,

Yes, Barry has commented on the reasons for this recently. The short
version is that when you are using bytea, the backend parses the input.

Dave
On Tue, 2002-04-16 at 23:17, Joe Shevland wrote:
> Hi,
>
> I'm adding a page on using binary data to a primer doc I'm writing, and have been performing some tests with
PostgreSQL7.2 and 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
forstoring bytea, again I guess converting the mostly unprintable binary data into the PGbytea string, though I'm not
sureif it s a fair comparison test. I don't suppose there's any easy way of streaming the bytea values like the LO
manager?I could be missing something fundamental about the bytea type, but given its limits, should the JDBC driver act
bydefault on 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(the results 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>