Re: PreparedStatement parameters and mutable objects

Oliver,

OK, now how do we test your estimates? If it truly shows this kind of
improvement you would sway me!

Dave
On Mon, 2004-01-12 at 21:02, Oliver Jowett wrote:
> Paul Thomas wrote:
> > On 12/01/2004 00:39 Oliver Jowett wrote:
> >> The problem is that it's very specific to the application workload;
> >> what case do we measure?
> >
> > I think you would need something which closely models you app's work load.
> >
> >> The reason I'm interested in doing this for is not the direct CPU
> >> overhead of object creation (none of the JDBC code is on our main
> >> execution path), but the effect that object creation has on GC
> >> interval and pause. We're running a low-latency app where an extra
> >> 50ms pause due to GC has a large impact on our latency figures .. so
> >> the less garbage we generate in the first place, the better. We could
> >> farm the JDBC work out to a separate VM, but that gets complex quite
> >> fast.
> >
> >
> > Reducing the amount of garbage created is never a bad thing. Do you have
> > an estimate of the % reduction you think you could achieve?
>
> See the attached testcase, it does batched inserts of byte arrays. I've
> run this as:
>
> java -Xrunhprof:heap=all,file=insertgarbage.txt,depth=10,cutoff=0 \
> -verbose:gc -cp .:/net/java/java_libs/postgresql.jar \
> TestInsertGarbage \
> 'jdbc:postgresql:rhino?user=oliver&password=oliver' \
> 100000 100 100
>
> which does 100k inserts each with a 100 byte array, in batches of 100.
> This is roughly the same as our application workload when it is doing a
> DB resync of a reasonably-sized provisioning database.
>
> In the resulting heap dump, the allocation hotspots are:
>
> QueryExecutor.sendQueryV2 -> Encoding.encode -> String.getBytes
> (100000 byte[], 143738400 bytes)
> (100000 byte[], 37144800 bytes)
> (100000 byte[], 20000000 bytes) // This is encoding the query text
> Statement.setBytes -> setString -> escapeString -> StringBuffer.append
> (63000 char[], 81608000 bytes)
> (19200 char[], 25018400 bytes)
> (17800 char[], 23091200 bytes)
> Statement.setBytes -> setString -> StringBuffer.ensureCapacity
> (100000 char[], 65445600 bytes)
> Statement.setBytes -> PGbytea.toPGString -> StringBuffer.<init>
> (100000 char[], 41600000 bytes)
> Statement.setBytes -> PGbytea.toPGString -> StringBuffer.append
> (21800 char[], 17788800 bytes)
> (21400 char[], 17462400 bytes)
> (21300 char[], 17380800 bytes)
> (20800 char[], 16972800 bytes)
> (14400 char[], 11750400 bytes)
> QueryExecutor.executeV2 -> Jdbc3PreparedStatement.createResultSet
> (100000 Jdbc3ResultSet, 13600000 bytes)
>
> All other allocation points allocate <10MB. There's quite a bit of other
> object allocation that could be cleaned up, e.g. repeated allocation of
> QueryExecutor objects.
>
> Total allocations were around 631800000 bytes. I estimate that streaming
> the parameter data would generate about 499000000 bytes less garbage
> (basically all of the above goes away except for encoding the query text
> and creating the resultsets), which is a 79% reduction (this is actually
> a lot more than I expected!).
>
> There are improvements we can make here without streaming, though. Not
> creating resultset objects for updates, and doing all necessary escaping
> in a single pass rather than two, both seem like low-hanging fruit.
>
> -O
>
> ______________________________________________________________________
>
> import java.sql.*;
>
> public class TestInsertGarbage {
> public static void main(String args[]) throws Exception {
> if (args.length < 4) {
> System.err.println("usage: java TestInsertGarbage <jdbc: URL> <# of inserts> <array size> <batch size>");
> return;
> }
>
> String driverURL = args[0];
> int numInserts = Integer.parseInt(args[1]);
> int arraySize = Integer.parseInt(args[2]);
> int batchSize = Integer.parseInt(args[3]);
>
> // We don't want to count the data to be inserted itself, since that's
> // mostly static and isn't garbage. So just create a single array
> // to represent all the data. We'll reuse that on each insert.
>
> // we also give it a random distribution of values since that affects
> // the exact length needed to escape the value.
> byte[] dummyArray = new byte[arraySize];
>
> Class.forName("org.postgresql.Driver");
> Connection conn = DriverManager.getConnection(driverURL);
>
> // Schema setup.
> Statement stmt = conn.createStatement();
> try {
> stmt.executeUpdate("DROP TABLE test_insert_garbage");
> } catch (SQLException e) {}
> stmt.executeUpdate("CREATE TABLE test_insert_garbage(data bytea)");
>
> // Testcase itself.
> conn.setAutoCommit(false);
> PreparedStatement inserter =
> conn.prepareStatement("INSERT INTO test_insert_garbage(data) VALUES (?)");
>
> // Main loop.
> java.util.Random randomizer = new java.util.Random();
> for (int i = 0; i < numInserts; ) {
> randomizer.nextBytes(dummyArray); // Hee hee.
> for (int j = 0; j < batchSize && i < numInserts; ++i, ++j) {
> inserter.setBytes(1, dummyArray);
> inserter.addBatch();
> }
> inserter.executeBatch();
> System.err.println("Inserted " + i + " rows");
> }
>
> // Done.
> conn.commit();
> System.err.println("Committed transaction.");
> }
> }
>
> ______________________________________________________________________
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer
519 939 0336
ICQ # 1467551