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