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