Yes, I am normally using java -Xmx100MB and -Xms50MB, do you suspect that is too much?
I don't have time right now to make a simple test case, but maybe later today. I will try to post code and another trace. Also, I'll open up a new post if I find anything wrong, since I've kinda gotten two issues (one connection or 36 connections) jumbled up in one post. Thanks for the help, Thomas. Julian On Nov 3, 9:40 am, "Thomas Mueller" <[EMAIL PROTECTED]> wrote: > 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 > filehttp://h2-database.googlegroups.com/web/java.hprof.1DBoutofmemory.txt... > (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(); > > ... > > 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 -~----------~----~----~----~------~----~------~--~---
