I think I'm experiencing a similiar scaling problem. Once my db
shards grow beyond 500,000 records - the insert time drops like a
stone. Inserting 5 million records over 64 clean db shards happens in
3 minutes. After those shards get over 500,000 records - the whole
insert takes 29 minutes.
On Oct 26, 4: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, timeStamp) VALUES (?, ?, ?)");
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 48764);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881248E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 51851);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881264E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 38297);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881264E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 60055);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881264E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 36196);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881279E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 65488);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881279E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 5725);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881279E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setInt(1, 39291);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setByte(2, 0);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.setDouble(3, 1.224880881279E12d);
> > 10-24 13:41:21 jdbc[2]:
> > /**/prep36.addBatch();
> > 10-24 13:41:21
>
> ...
>
> 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
-~----------~----~----~----~------~----~------~--~---
