NOW i uploaded it, sorry I was busy.
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
-~----------~----~----~----~------~----~------~--~---
