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
-~----------~----~----~----~------~----~------~--~---
