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 exclusive write lock unlock SYS
> >> > 10-24 13:41:19 session: connecting #2 to C:\Temp\test0
> >> > 10-24 13:41:19 jdbc[2]:
> >> > /*SQL */SET TRACE_LEVEL_FILE 3;
> >> > 10-24 13:41:19 session: connected #2
> >> > 10-24 13:41:19 jdbc[2]:
> >> > /**/Connection conn0 = DriverManager.getConnection("jdbc:h2:file:C:\
> >> > \Temp\\test0;TRACE_LEVEL_FILE=3", "", "")
> >> > 10-24 13:41:19 jdbc[2]:
> >> > /**/conn0.setAutoCommit(false);
> >> > 10-24 13:41:19 jdbc[2]:
> >> > /*SQL */SET AUTOCOMMIT FALSE;
> >> > 10-24 13:41:21 jdbc[2]:
> >> > /**/PreparedStatement prep0 = conn0.prepareStatement("CREATE table IF
> >> > NOT EXISTS messages0 (aaa BIGINT, bbb SMALLINT, timeStamp DOUBLE);");
> >> > 10-24 13:41:21 jdbc[2]:
> >> > /**/prep0.execute();
> >> > 10-24 13:41:21 index: open existing MESSAGES0 rows: 0
> >> > 10-24 13:41:21 lock: 2 exclusive write lock added for SYS
> >> > 10-24 13:41:21 jdbc[2]:
> >> > /*SQL */CREATE table IF NOT EXISTS messages0 (aaa BIGINT, bbb
> >> > SMALLINT, timeStamp DOUBLE);
> >> > 10-24 13:41:21 lock: 2 exclusive write lock unlock SYS
> >> > 10-24 13:41:21 jdbc[2]:
> >> > /**/conn0.commit();
> >> > 10-24 13:41:21 jdbc[2]:
> >> > /*SQL */COMMIT;
> >> > 10-24 13:41:21 jdbc[2]:
> >> > /**/prep0.close();
> >> > 10-24 13:41:21 jdbc[2]:
> >> > /**/PreparedStatement prep36 = conn0.prepareStatement("INSERT INTO
> >> > messages0 (aaa, bbb,
>
> ...
>
> 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
-~----------~----~----~----~------~----~------~--~---
