Hi,
I've tested performance of H2 on the insertion and deleting of ~1M
records. (java source code is in the end of the message).
Test stend configuration:
Windows 7, NTFS
Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
Java HotSpot(TM) Client VM (build 11.3-b02, mixed mode, sharing)
Custom H2 options:
MAX_MEMORY_UNDO=120000
MAX_LOG_SIZE=256
MAX_OPERATION_MEMORY=4194304
WRITE_DELAY=0
H2 speed [build-131]:
INSERT: 115706 ms
DELETE: 103225 ms
For comparison the speed of DERBY using the same data:
INSERT: 135150 ms
DELETE: 53156 ms
Profiler: top 3 stack trace(s) of 220109 ms [build-131]
1512/4258
at java.io.WinNTFileSystem.getLastModifiedTime(Native Method)
at java.io.File.lastModified(File.java:826)
at org.h2.store.fs.FileSystemDisk.getLastModified(FileSystemDisk.java:
278)
at org.h2.util.IOUtils.getLastModified(IOUtils.java:678)
at org.h2.util.TempFileDeleter.updateAutoDelete(TempFileDeleter.java:
79)
at org.h2.store.FileStore.autoDelete(FileStore.java:466)
at org.h2.engine.UndoLog.add(UndoLog.java:133)
at org.h2.engine.Session.log(Session.java:603)
at org.h2.engine.Session.log(Session.java:588)
at org.h2.command.dml.Insert.insertRows(Insert.java:121)
at org.h2.command.dml.Insert.update(Insert.java:82)
at org.h2.command.CommandContainer.update(CommandContainer.java:70)
at org.h2.command.Command.executeUpdate(Command.java:198)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
141)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:
127)
at TestH2.testSql(TestH2.java:52)
1451/4258
at java.io.WinNTFileSystem.getLastModifiedTime(Native Method)
at java.io.File.lastModified(File.java:826)
at org.h2.store.fs.FileSystemDisk.getLastModified(FileSystemDisk.java:
278)
at org.h2.util.IOUtils.getLastModified(IOUtils.java:678)
at org.h2.util.TempFileDeleter.updateAutoDelete(TempFileDeleter.java:
79)
at org.h2.store.FileStore.autoDelete(FileStore.java:466)
at org.h2.engine.UndoLog.add(UndoLog.java:133)
at org.h2.engine.Session.log(Session.java:603)
at org.h2.engine.Session.log(Session.java:588)
at org.h2.command.dml.Delete.update(Delete.java:74)
at org.h2.command.CommandContainer.update(CommandContainer.java:70)
at org.h2.command.Command.executeUpdate(Command.java:198)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
141)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:
127)
at TestH2.testSql(TestH2.java:60)
at TestH2.testH2(TestH2.java:35)
158/4258
at java.io.RandomAccessFile.writeBytes(Native Method)
at java.io.RandomAccessFile.write(RandomAccessFile.java:466)
at org.h2.store.FileStore.write(FileStore.java:333)
at org.h2.engine.UndoLogRecord.save(UndoLogRecord.java:160)
at org.h2.engine.UndoLog.saveIfPossible(UndoLog.java:139)
at org.h2.engine.UndoLog.add(UndoLog.java:131)
at org.h2.engine.Session.log(Session.java:603)
at org.h2.engine.Session.log(Session.java:588)
at org.h2.command.dml.Delete.update(Delete.java:74)
at org.h2.command.CommandContainer.update(CommandContainer.java:70)
at org.h2.command.Command.executeUpdate(Command.java:198)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
141)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:
127)
at TestH2.testSql(TestH2.java:60)
at TestH2.testH2(TestH2.java:35)
at TestH2.main(TestH2.java:18)
.
Similar situation is observed in Windows 2003 and Windows XP.
I've made a build with quick&dirty hack. I commented out the call of
lastModified method.
And got this rates:
INSERT: 32437 ms
DELETE: 23276 ms
The speed of operations was increased ~4-5 times!
I propose to get rid of checking the temporary files with
lastModified.
Instead, use the guaranteed unique names.
Now file names are generated like this:
‘databaseName’ + [random] + ‘.temp.db’
I suggest doing it like this:
‘databaseName.’ + [counter] + ’.’ + [random] + ‘.temp.db’
[counter] can look like this:
private static final Object CNT_LOCK = new Object();
private static int CNT = 0;
private static int nextTempCounter() {
synchronized(CNT_LOCK) {
return ++CNT;
}
}
Or for Java 5 and above:
private static final AtomicInteger CNT = new AtomicInteger();
private static int nextTempCounter() {
return CNT.incrementAndGet();
}
It can be put either in IOUtils or FileSystem.
It remains to remove possible conflicts between different instances of
engines in
two cases:
1) When working with database in FileLock.LOCK_SERIALIZED mode. We can
add uniqueId from FileLock to name of a temporary file.
2) When working with the same database in zip-file. May be it would be
possible to create a separate unique subfolder tmp for each db engine?
Or we should leave lastModified mechanism only for files created in
tmp.
--- TestH2.java
import org.h2.util.Profiler;
import java.sql.*;
public class TestH2 {
private static final int _1M = 1000000;
private static final String CREATE = "CREATE TABLE TEST " +
"(id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY," +
"a VARCHAR(256), b VARCHAR(256), c VARCHAR(256))";
private static final String INSERT = "INSERT INTO TEST (a,b,c)
VALUES ('aaa', 'bbbb', 'ccccc')";
private static final String DELETE = "DELETE FROM TEST";
public static void main(String args[]) throws Exception {
if(args.length!=2) {
System.out.println("usage: TestH2 [-h2|-derby]
databaseName");
System.exit(-1);
}
String db = args[0];
String dbanme = args[1];
if ("-h2".equals(db)) {
testH2(dbanme);
} else if ("-derby".equals(db)) {
testDerby(dbanme);
}
}
private static void testDerby(String dbname) throws
ClassNotFoundException, SQLException {
System.out.println("=== DERBY");
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
testSql("jdbc:derby:" + dbname + ";create=true");
}
private static void testH2(String dbname) throws
ClassNotFoundException, SQLException {
System.out.println("=== H2");
Class.forName("org.h2.Driver");
Profiler profiler = new Profiler();
profiler.startCollecting();
testSql("jdbc:h2:" + dbname);
profiler.stopCollecting();
System.out.println(profiler.getTop(3));
}
private static void testSql(String url) throws SQLException {
Connection conn = DriverManager.getConnection(url);
Statement st = conn.createStatement();
st.executeUpdate(CREATE);
st.close();
conn.setAutoCommit(false);
long t = System.currentTimeMillis();
PreparedStatement ps = conn.prepareStatement(INSERT);
for (int i = 0; i < _1M; i++) {
ps.executeUpdate();
}
conn.commit();
ps.close();
System.out.println("INSERT: " + (System.currentTimeMillis() -
t) + " ms");
t = System.currentTimeMillis();
ps = conn.prepareStatement(DELETE);
ps.executeUpdate();
ps.close();
conn.commit();
System.out.println("DELETE: " + (System.currentTimeMillis() -
t) + " ms");
conn.close();
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
