Re: [h2] Re: File corruption with nioMemLZF

[Andrey Belyaev]

I faced the same issue today.

On Monday, January 23, 2017 at 4:16:32 PM UTC+5, Anatolii K wrote:
>
> One more problem :( :
>
> It happens rarely and unpredictable 
>
> Caused by: java.lang.ClassCastException: org.h2.value.ValueNull cannot be 
> cast to org.h2.value.ValueArray
> at 
> org.h2.mvstore.db.MVPrimaryIndex$MVStoreCursor.get(MVPrimaryIndex.java:402)
> at 
> org.h2.mvstore.db.MVPrimaryIndex$MVStoreCursor.getSearchRow(MVPrimaryIndex.java:412)
> at org.h2.index.IndexCursor.getSearchRow(IndexCursor.java:293)
> at org.h2.table.TableFilter.next(TableFilter.java:499)
> at org.h2.command.dml.Select.queryGroup(Select.java:336)
> at org.h2.command.dml.Select.queryWithoutCache(Select.java:650)
> at org.h2.command.dml.Query.query(Query.java:341)
> at org.h2.command.dml.Query.query(Query.java:309)
> at org.h2.command.dml.Query.query(Query.java:36)
> at org.h2.command.ddl.Analyze.analyzeTable(Analyze.java:115)
> at org.h2.mvstore.db.MVTable.analyzeIfRequired(MVTable.java:738)
> at org.h2.mvstore.db.MVTable.addRow(MVTable.java:725)
> at org.h2.table.Table.updateRows(Table.java:483)
> at org.h2.command.dml.Update.update(Update.java:145)
> at org.h2.command.CommandContainer.update(CommandContainer.java:98)
> at org.h2.command.Command.executeUpdate(Command.java:258)
>
>
>
> On Monday, January 23, 2017 at 9:55:38 AM UTC, Noel Grandin wrote:
>>
>> I have committed a cache-sizing feature for this filesystem 
>>
>

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

[Anatolii K]

One more problem :( :

It happens rarely and unpredictable 

Caused by: java.lang.ClassCastException: org.h2.value.ValueNull cannot be 
cast to org.h2.value.ValueArray
at 
org.h2.mvstore.db.MVPrimaryIndex$MVStoreCursor.get(MVPrimaryIndex.java:402)
at 
org.h2.mvstore.db.MVPrimaryIndex$MVStoreCursor.getSearchRow(MVPrimaryIndex.java:412)
at org.h2.index.IndexCursor.getSearchRow(IndexCursor.java:293)
at org.h2.table.TableFilter.next(TableFilter.java:499)
at org.h2.command.dml.Select.queryGroup(Select.java:336)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:650)
at org.h2.command.dml.Query.query(Query.java:341)
at org.h2.command.dml.Query.query(Query.java:309)
at org.h2.command.dml.Query.query(Query.java:36)
at org.h2.command.ddl.Analyze.analyzeTable(Analyze.java:115)
at org.h2.mvstore.db.MVTable.analyzeIfRequired(MVTable.java:738)
at org.h2.mvstore.db.MVTable.addRow(MVTable.java:725)
at org.h2.table.Table.updateRows(Table.java:483)
at org.h2.command.dml.Update.update(Update.java:145)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)



On Monday, January 23, 2017 at 9:55:38 AM UTC, Noel Grandin wrote:
>
> I have committed a cache-sizing feature for this filesystem 
>

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

[Noel Grandin]

I have committed a cache-sizing feature for this filesystem

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

[Noel Grandin]

On 2017/01/23 9:42 AM, Anatolii K wrote:

Thank you Noel for the explanation

I think it would be very useful to be able to control the cach size. And I 
would like to be able to do it dynamically,
without database restart




Yeah, sorry, the "dynamic without restart" part is not going to happen, not unless you supply a patch yourself. My time 
budget for this particular problem is pretty much exhausted.

And even then I don't know how we'd do that, we don't have dynamic control for 
anything else down in that part of the code.


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

[Noel Grandin]

Firstly note that this test generates fairly widely varying results, so you
need to run it a couple of times and average the output.

That said, this performance here is completely dependant on the CACHE_SIZE
constant in FileNioMemData.
A value of anything > approx 80 produces decent performance.

However, the point is that now we are approaching trade-off zone. If we
want more performance, we need to let enough data remain uncompressed to
fit the working set of your queries. However, that will be different from
program to program, and even different depending on what the user is doing
with the program.

But a larger cache means less compressed data, which means the DB will use
more RAM.

So right now I am leaning towards sizing the cache as a % of the size of
the DB, and adding a setting to allow users to override that %.
​

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

[Anatolii K]

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();
}
}

}

Re: [h2] Re: File corruption with nioMemLZF

[Noel Grandin]

Ah that was silly, forgot to hit the sync button.Done now.​

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

[Anatolii K]

Have you aready commited at github? I don't see it

On Friday, January 20, 2017 at 2:26:20 PM UTC, Noel Grandin wrote:
>
> I have pushed a fix for this. 
>
> On 2017/01/20 2:23 PM, Anatolii K wrote: 
> > Please see attached. 
> > 
> > At my notebook the test takes 0.9s without compression and 50s with 
> compression 
> > 
> > 
>

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

[Noel Grandin]

I have pushed a fix for this.

On 2017/01/20 2:23 PM, Anatolii K wrote:

Please see attached.

At my notebook the test takes 0.9s without compression and 50s with compression




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

[Anatolii K]

Please see attached.

At my notebook the test takes 0.9s without compression and 50s with 
compression

 

On Thursday, January 19, 2017 at 5:51:36 PM UTC, Noel Grandin wrote:
>
> Can you post your performance test code?
> On Thu, 19 Jan 2017 at 18:36, Anatolii K  
> wrote:
>
>> Sorry to say so, but performance dropped dramatically. 
>> Previously compressed storage was just a bit slower than uncompressed.
>> But now it become almost 100 times slower!
>>
>>
>>
>> On Thursday, January 19, 2017 at 1:32:49 PM UTC, Noel Grandin wrote:
>>>
>>> I have pushed a fix for this, it now survives your test. 
>>>
>>> I had to simplify the current code and just synchronize the methods, 
>>> which means it may lose some performance. If this 
>>> is a problem for you, I'm sorry, but you'll have to come up with some 
>>> patches yourself. 
>>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to h2-da...@googlegroups.com 
>> .
>> Visit this group at https://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
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.ArrayList;
import java.util.Collection;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Bug_H2_5 {

static String compressedDbUrl = "jdbc:h2:nioMemLZF:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=1;WRITE_DELAY=500";
static String uncompressedDbUrl = "jdbc:h2:nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=1;WRITE_DELAY=500";
static String user = "sa", pwd = "";
static int threadCount = 300;

public static void main(String args[]) throws InterruptedException, SQLException, ClassNotFoundException {
Class.forName("org.h2.Driver");
run(uncompressedDbUrl);
run(compressedDbUrl);

}

private static void run(String dbUrl) throws SQLException, InterruptedException {
System.out.println("\nURL: " + dbUrl);
Connection dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
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");

Collection artes = new ArrayList<>();
for (int i = 0; i < threadCount; i++) {
artes.add(new Arte(DriverManager.getConnection(dbUrl, user, pwd), i));
}
ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);

start = System.currentTimeMillis();
threadPool.invokeAll(artes);

System.out.println("Test duration " + (System.currentTimeMillis() - start) + "ms\n");
threadPool.shutdown();
}

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"
+ "	BALANCE NUMBER null,\n"
+ "	CONTRACTID NUMBER(18,0) 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();
}
}

static class Arte implements Callable {

Connection db;

Re: [h2] Re: File corruption with nioMemLZF

[Noel Grandin]

Can you post your performance test code?
On Thu, 19 Jan 2017 at 18:36, Anatolii K  wrote:

> Sorry to say so, but performance dropped dramatically.
> Previously compressed storage was just a bit slower than uncompressed.
> But now it become almost 100 times slower!
>
>
>
> On Thursday, January 19, 2017 at 1:32:49 PM UTC, Noel Grandin wrote:
>
> I have pushed a fix for this, it now survives your test.
>
> I had to simplify the current code and just synchronize the methods, which
> means it may lose some performance. If this
> is a problem for you, I'm sorry, but you'll have to come up with some
> patches yourself.
>
> --
> 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.
>

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

[Anatolii K]

Sorry to say so, but performance dropped dramatically. 
Previously compressed storage was just a bit slower than uncompressed.
But now it become almost 100 times slower!



On Thursday, January 19, 2017 at 1:32:49 PM UTC, Noel Grandin wrote:
>
> I have pushed a fix for this, it now survives your test. 
>
> I had to simplify the current code and just synchronize the methods, which 
> means it may lose some performance. If this 
> is a problem for you, I'm sorry, but you'll have to come up with some 
> patches yourself. 
>

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

[Noel Grandin]

I have pushed a fix for this, it now survives your test.

I had to simplify the current code and just synchronize the methods, which means it may lose some performance. If this 
is a problem for you, I'm sorry, but you'll have to come up with some patches yourself.


--
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] Re: File corruption with nioMemLZF

[Anatolii K]

Sorry for bothering you, but could you please look at it 

-- 
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.