Re: [h2] Strange long INSER : One INSERT out of 18889 is very long
can you try running the built in profiler during that insert, and lets see what it's doing: http://h2database.com/html/performance.html#built_in_profiler -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: File corruption with nioMemLZF
I found one more scenario where compressed database is 30 times slower -
see attachment
But now it's not related to synchronization because the test is single
threaded
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class Bug_H2_6 {
static String compressedDbUrl = "jdbc:h2:nioMemLZF:db1";
static String uncompressedDbUrl = "jdbc:h2:nioMemFS:db2";
static String dbParams = ";DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=1;WRITE_DELAY=500";
static String user = "sa", pwd = "";
public static final int ITERS = 100_000;
public static void main(String args[]) {
try {
Class.forName("org.h2.Driver");
run(uncompressedDbUrl + dbParams);
run(compressedDbUrl + dbParams);
} catch (ClassNotFoundException | SQLException | InterruptedException ex) {
Logger.getLogger(Bug_H2_6.class.getName()).log(Level.SEVERE, null, ex);
}
}
private static void run(String dbUrl) throws SQLException, InterruptedException {
System.out.println("\nURL: " + dbUrl);
Connection dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
dbConnect.setAutoCommit(false);
DbPreparator.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");
long start = System.currentTimeMillis();
DbPreparator.populate(dbConnect);
System.out.println("DB populated in " + (System.currentTimeMillis() - start) + "ms");
PreparedStatement readAcctStmt = dbConnect.prepareStatement("select * from account where contractId = ?");
start = System.currentTimeMillis();
for (int i = 0; i < ITERS; i++) {
readAcctStmt.setLong(1, (int) (Math.random() * DbPreparator.OBJ_CNT));
try (ResultSet rs = readAcctStmt.executeQuery()) {
rs.next();
}
}
System.out.println("Test duration " + (System.currentTimeMillis() - start) + "ms\n");
}
static class DbPreparator {
public static final int OBJ_CNT = 5;
private static final String[] SQLs = new String[]{
"CREATE TABLE IF NOT EXISTS "
+ "ACCOUNT (\n"
+ " ID NUMBER(18,0) not null PRIMARY KEY,\n"
+ " CONTRACTID NUMBER(18,0) null,\n"
+ " BALANCE NUMBER null,\n"
+ " NOTES VARCHAR2(4000 char) null)",
"create index IF NOT EXISTS IDX_ACCOUNT_CONTRACT on ACCOUNT (CONTRACTID asc)",};
public static void prepareScheme(Connection db) throws SQLException {
for (String sql : SQLs) {
db.createStatement().execute(sql);
db.commit();
}
}
public static void populate(Connection db) throws SQLException {
PreparedStatement mergeAcctStmt = db.prepareStatement("MERGE INTO Account(id, balance, contractId) key (id) VALUES (?, ?, ?)");
for (int i = 0; i < OBJ_CNT; i++) {
mergeAcctStmt.setLong(1, i);
mergeAcctStmt.setBigDecimal(2, BigDecimal.ZERO);
mergeAcctStmt.setLong(3, i);
mergeAcctStmt.addBatch();
}
mergeAcctStmt.executeBatch();
db.commit();
}
}
}
[h2] Strange long INSER : One INSERT out of 18889 is very long
Here is what's happening :
I'm using a table like this :
CREATE TABLE IF NOT EXISTS TMPACKETS(id int primary key auto_increment,
type varchar(64), reception_time TIMESTAMP, onboard_time TIMESTAMP, data
varbinary(50))
I'm inserting with prepared statement like this :
this.preparedStatement = this.conn.prepareStatement("INSERT INTO TMPACKETS
(TYPE, RECEPTION_TIME, ONBOARD_TIME, DATA) VALUES (?,?,?,?)");
My DB size is now 2GO. I'm doing a lots a of inserts in a row. I've noticed
that when I do precisely 18689 inserts in a row then the 18688 firsts are
as fast as usual, but the number18689, the last one, is really slow (around
3 sec for 1 insert). I can repeat the same operation every time and for
this precise insert number 18689 it takes 3 seconds and blocks the h2
database (impossible to perform a read at the same moment).
I've noticed that is was not coming from the speed I'm doing the inserts,
it blocks every time at the insert number 18689, no matter at what speed I
do it.
What it even more strange it is that if I just do 18688 inserts in a row
and close my app (close the java vm) and re-run 18688 inserts in a row it
goes well every time and I can do it as much as I want. But if I do one
more insert then I have to wait 3 seconds more on the last insert, which is
quite a long time.
Did anyone already faced this issue? Is 2GByte to much to handle for H2?
Thanks for your help,
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
[h2] I'm no longer able to offer H2 commercial support; can you?
Hi all, For the last few years, I've offered H2 commercial support, as per the link here: http://www.h2database.com/html/links.html#commercial_support I'm now focusing on building a new product, so I don't have the time for this anymore. I've been turning down enquiries lately. This is an opportunity for someone else who knows H2 well. I typically receive a few serious enquiries per year. They involve trying to fix corrupted databases, advising on how best to use H2, or offering an ongoing SLA. Is anyone interested in taking over? Regards, Steve McLeod -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
