Hi, Could you verify you have enough physical memory? If you allocate too much memory for a Java application (using java -Xmx...) then it can get very slow because the virtual memory is swapped to disk.
In the file http://h2-database.googlegroups.com/web/java.hprof.1DBoutofmemory.txt?gda=Pgty5k8AAADMx8EVBOZjpKEj2B_BjWXWkOGLnyt4ePrL-MCURujTMoBhoEAfjLvaPd785Lcc5VczJQpUcvdUY2Z-14fz_0UYnHMhSp_qzSgvndaTPyHVdA (is this the correct one)? it looks like 2/3 of the time is spent in allocating memory. If this is the problem, I would like to solve that. However I first need to have a simple test case where I can reproduce this problem. Is it the application you have already posted? I would like to solve the problem using just one database / one connection. Regards, Thomas On Thu, Oct 30, 2008 at 8:41 PM, julianbui <[EMAIL PROTECTED]> wrote: > > IGNORE previous message. I accidentally uploaded the file for a > SINGLE database that ran out of memory. I will be uploading the > profile for the 36 database program that ran out of memory. > > Sorry to anyone subscribed to the group who keeps seeing me delete and > add messages. > > On Oct 30, 11:00 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > wrote: >> I just uploaded the java.hprof.txt. >> >> Be aware that I used "C:\Temp\test;CACHE_SIZE=51200" and I started >> with a database that was very a few GB in size. I make 1000 inserts >> before committing my transactions. When I get the resultset (I'm >> using JDBC) from the queries, I don't do anything with them, so they >> ought to be GCd (or so I think). >> >> -Julian >> >> On Oct 29, 1:50 pm, "Thomas Mueller" <[EMAIL PROTECTED]> >> wrote: >> >> > Hi, >> >> > > but as the tables get beyond 500,000 rows ... >> > > the performance gets brutally slow. >> >> > I can't reproduce this so far. Could it be a cache / memory problem? >> >> > If you are running benchmarks, could you profile them please? To do >> > that use java -Xrunhprof:cpu=samples,depth=8 ... - and then >> > upload/post the file java.hprof.txt? >> >> > Regards, >> > Thomas >> >> > On Mon, Oct 27, 2008 at 8:09 PM, [EMAIL PROTECTED] >> >> > <[EMAIL PROTECTED]> wrote: >> >> > > I open 36 databases because 1 database with that much data slows down >> > > way too much. I figured if inserts were fast on smaller databases, I >> > > could partition the one database into many smaller databases so that >> > > the inserts would be faster. It wouldn't solve the scalability issue, >> > > but it would alleviate the problem. >> >> > > Like Brian's problem, once a db grows to a certain point (a few >> > > million records) the insert performance really degrades. >> >> > > -Julian >> >> > > On Oct 26, 1:28 am, "Thomas Mueller" <[EMAIL PROTECTED]> >> > > wrote: >> > >> Hi, >> >> > >> In your test, you open 37 separate databases. Why not just one >> > >> database? Of course 37 databases use more memory than one. And why 37 >> > >> connections instead of just one? >> >> > >> Regards, >> > >> Thomas >> >> > >> On Fri, Oct 24, 2008 at 11:10 PM, [EMAIL PROTECTED] >> >> > >> <[EMAIL PROTECTED]> wrote: >> >> > >> > Hi Thomas, >> >> > >> > I created a simple test case (though for a simple case the file isn't >> > >> > as small as you'd probably like). I've attached the code and one of >> > >> > the trace files. If you want more I can send them to you since there >> > >> > are 36. Note there are no threads directly in my app. >> >> > >> > Julian >> >> > >> > <code - takes 3 params 1) number of records to write 2) writes before >> > >> > commit 3) path - e.g., 1000000 10 "C:\Temp\test"> >> > >> > /* >> > >> > * To change this template, choose Tools | Templates >> > >> > * and open the template in the editor. >> > >> > */ >> > >> > package h2partitionedtest; >> >> > >> > import java.sql.Connection; >> > >> > import java.sql.DriverManager; >> > >> > import java.sql.PreparedStatement; >> > >> > import java.sql.ResultSet; >> > >> > import java.sql.SQLException; >> > >> > import java.sql.Statement; >> > >> > import java.util.logging.Level; >> > >> > import java.util.logging.Logger; >> >> > >> > //memory size >> > >> > //clean up >> > >> > /** >> > >> > * >> > >> > * @author julian.bui >> > >> > */ >> > >> > public class Main { >> >> > >> > private final int NUM_CONNECTIONS = 36; >> > >> > private Connection[] conn = new Connection[NUM_CONNECTIONS]; >> > >> > private int[] newRowAddedCount = new int[NUM_CONNECTIONS]; >> > >> > private PreparedStatement[] dataInsertPs = new >> > >> > PreparedStatement[NUM_CONNECTIONS]; >> > >> > private PreparedStatement[] ps = new >> > >> > PreparedStatement[NUM_CONNECTIONS]; >> >> > >> > private String driverClass = "org.h2.Driver"; >> > >> > private String databaseURL; >> > >> > private int LIMIT_BEFORE_COMMIT; // = 1000; >> > >> > private int INDEX_ON; >> > >> > private static int NUM_TO_WRITE; // = 10000000; >> >> > >> > public Main() { >> > >> > } >> >> > >> > private void loadDriver(String driverPath) { >> > >> > try { >> > >> > Class.forName(driverPath); >> > >> > } catch (ClassNotFoundException ex) { >> > >> > Logger.getLogger(Main.class.getName()).log(Level.SEVERE, >> > >> > "Could not load driver '" + driverPath + "'", ex); >> > >> > System.exit(1); >> > >> > } >> > >> > } >> >> > >> > private boolean createTables() { >> > >> > try { >> > >> > for (int i = 0; i < NUM_CONNECTIONS; i++) { >> > >> > String createTableQueryString = "CREATE table IF NOT >> > >> > EXISTS messages" + i + " (aaa BIGINT, bbb SMALLINT, timeStamp >> > >> > DOUBLE);"; >> > >> > ps[i] = >> > >> > conn[i].prepareStatement(createTableQueryString); >> > >> > ps[i].execute(); >> > >> > } >> >> > >> > if (INDEX_ON == 1) { >> > >> > for (int i = 0; i < NUM_CONNECTIONS; i++) { >> > >> > String createIndexQueryString = "CREATE INDEX IF >> > >> > NOT EXISTS IDX_TIME" + i + " on messages" + i + " (timeStamp)"; >> > >> > ps[i] = >> > >> > conn[i].prepareStatement(createIndexQueryString); >> > >> > ps[i].execute(); >> > >> > } >> > >> > } >> >> > >> > for (int i = 0; i < NUM_CONNECTIONS; i++) { >> > >> > conn[i].commit(); >> > >> > ps[i].close(); >> > >> > } >> > >> > } catch (Exception ex) { >> > >> > Logger.getLogger(Main.class.getName()).log(Level.SEVERE, >> > >> > "Could not create table", ex); >> > >> > return false; >> > >> > } >> >> > >> > return true; >> > >> > } >> >> > >> > private void makeConnections(String path) { >> > >> > try { >> > >> > for (int i = 0; i < NUM_CONNECTIONS; i++) { >> > >> > conn[i] = DriverManager.getConnection("jdbc:h2:file:" >> > >> > + path + i + ";TRACE_LEVEL_FILE=3"); >> > >> > conn[i].setAutoCommit(false); >> > >> > } >> > >> > } catch (SQLException ex) { >> > >> > Logger.getLogger(Main.class.getName()).log(Level.SEVERE, >> > >> > "Could not make connection to url", ex); >> > >> > } >> > >> > } >> >> > >> > private void createPreparedStatements() { >> > >> > try { >> > >> > for (int i = 0; i < NUM_CONNECTIONS; i++) { >> > >> > dataInsertPs[i] = conn[i].prepareStatement("INSERT >> > >> > INTO messages" + i + " (aaa, bbb, timeStamp) VALUES (?, ?, ?)"); >> > >> > } >> > >> > } catch (SQLException ex) { >> > >> > Logger.getLogger(Main.class.getName()).log(Level.SEVERE, >> > >> > "Could not create prepared statement", ex); >> > >> > } >> > >> > } >> >> > >> > private void makeWrite() { >> > >> > try { >> > >> > // an attribute has 256 values, figure out which of the >> > >> > NUM_CONNECTION database it should go into >> > >> > int bucket = ((int) (Math.random() * 256)) % >> > >> > NUM_CONNECTIONS; >> >> > >> > if (conn[bucket] == null) { >> >> > >> > Logger.getLogger(Main.class.getName()).log(Level.SEVERE, "makeWrites: >> > >> > Could not make connection to url '" + databaseURL + "'"); >> > >> > } >> >> > >> > dataInsertPs[bucket].setInt(1, (int) (Math.random() * >> > >> > 65536)); >> > >> > dataInsertPs[bucket].setByte(2, (byte) (Math.random() * >> > >> > 1)); >> > >> > dataInsertPs[bucket].setDouble(3, (double) >> > >> > System.currentTimeMillis()); >> >> > >> > dataInsertPs[bucket].addBatch(); >> >> > >> > newRowAddedCount[bucket]++; >> >> > >> > if (newRowAddedCount[bucket] >= LIMIT_BEFORE_COMMIT) { >> > >> > int[] results = dataInsertPs[bucket].executeBatch(); >> > >> > conn[bucket].commit(); >> > >> > newRowAddedCount[bucket] = 0; //reset the counter for >> > >> > the next commit buffer >> > >> > } >> >> > >> > } catch (Exception ex) { >> > >> > ex.printStackTrace(); >> > >> > } >> > >> > } >> >> > >> > private void closeDBConnections() { >> > >> > try { >> > >> > for (int i = 0; i < NUM_CONNECTIONS; i++) { >> > >> > conn[i].close(); >> > >> > } >> > >> > } catch (SQLException ex) { >> > >> > Logger.getLogger(Main.class.getName()).log(Level.SEVERE, >> > >> > "Could not close database '" + databaseURL + "'", ex); >> > >> > } >> > >> > } >> >> > >> > public static void main(String[] args) { >> > >> > Main myMain = new Main(); >> >> > >> > myMain.NUM_TO_WRITE = Integer.valueOf(args[0]); >> > >> > myMain.LIMIT_BEFORE_COMMIT = Integer.valueOf(args[1]); >> >> > >> > myMain.loadDriver(myMain.driverClass); >> > >> > myMain.makeConnections(args[2]); >> > >> > myMain.createTables(); >> > >> > myMain.createPreparedStatements(); >> >> > >> > System.err.println("Writing..."); >> > >> > long start = System.currentTimeMillis(); >> > >> > for (int i = 0; i < NUM_TO_WRITE; i++) { >> > >> > myMain.makeWrite(); >> > >> > } >> > >> > System.err.println("Done"); >> >> > >> > myMain.closeDBConnections(); >> > >> > } >> > >> > } >> >> > >> > </end code> >> >> > >> > <trace> >> > >> > 10-24 13:41:19 database: opening C:\Temp\test0 (build 100) >> > >> > 10-24 13:41:19 fileLock: save {method=file, >> > >> > id=11d3096f82620198e42ea93f601be71b8a43310115c} >> > >> > 10-24 13:41:19 fileLock: load {method=file, >> > >> > id=11d3096f82620198e42ea93f601be71b8a43310115c} >> > >> > 10-24 13:41:19 index: open existing SYS rows: 0 >> > >> > 10-24 13:41:19 lock: 1 exclusive write lock added for SYS >> > >> > 10-24 13:41:19 lock: 1 exclusive write lock unlock SYS >> > >> > 10-24 13:41:19 database: opened C:\Temp\test0 >> > >> > 10-24 13:41:19 lock: 1 exclusive write lock added for SYS >> > >> > 10-24 13:41:19 lock: 1 >> >> ... >> >> read more ยป > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
