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

[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

[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

[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 <a.ka...@gmail.com > 
> 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 (?, ?, ?)&

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.

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

Re: [h2] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

I created separate topic for this bug 
https://groups.google.com/forum/#!topic/h2-database/rhSoRhzeZzk

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

[Anatolii K]

Continuation 
of https://groups.google.com/forum/#!topic/h2-database/oHpaE_LMLz4

I'm getting following error with nioMemLZF (url= jdbc:h2:nioMemLZF:db):

org.h2.jdbc.JdbcSQLException: General error: 
"java.lang.IllegalStateException: File corrupted in chunk 28, expected page 
length 4..32, got -1621686845 [1.4.193/6]"; SQL statement:
update account set balance = ? where id = ? [5-193]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:295)
at org.h2.command.Command.executeUpdate(Command.java:266)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
at Bug_H2_4$Arte.call(Bug_H2_4.java:99)
at Bug_H2_4$Arte.call(Bug_H2_4.java:82)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalStateException: File corrupted in chunk 28, 
expected page length 4..32, got -1621686845 [1.4.193/6]
at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:766)
at org.h2.mvstore.Page.read(Page.java:649)
at org.h2.mvstore.Page.read(Page.java:195)
at org.h2.mvstore.MVStore.readPage(MVStore.java:1952)
at org.h2.mvstore.MVMap.readPage(MVMap.java:738)
at org.h2.mvstore.Page.getChildPage(Page.java:217)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:470)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:471)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:471)
at org.h2.mvstore.MVMap.get(MVMap.java:452)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.getValue(TransactionStore.java:1203)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.get(TransactionStore.java:1180)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.get(TransactionStore.java:1148)
at org.h2.mvstore.db.MVPrimaryIndex.getRow(MVPrimaryIndex.java:215)
at org.h2.mvstore.db.MVTable.getRow(MVTable.java:463)
at 
org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.get(MVSecondaryIndex.java:489)
at org.h2.index.IndexCursor.get(IndexCursor.java:288)
at org.h2.table.TableFilter.get(TableFilter.java:594)
at org.h2.command.dml.Update.update(Update.java:109)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)


Please try attached test. It fails after 10-60 seconds run

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


public class Bug_H2_4 {

static String dbUrl = "jdbc:h2:nioMemLZF:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=1;WRITE_DELAY=500";
static String user = "sa", pwd = "";
static int threadCount = 200;

public static void main(String args[]) throws InterruptedException, SQLException {
Arte[] artes = new Arte[threadCount];
Connection dbConnect;
try {
Class.forName("org.h2.Driver");
dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
DbPreparator.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");

DbPreparator.populate(dbConnect);
System.out.println("DB populated");

for (int i = 0; i < threadCount; i++) {
artes[i] = new Arte(DriverManager.getConnection(dbUrl, user, pwd));
}
System.out.println("ARTEs created");
} catch (ClassNotFoundException | SQLException e) {
System.out.println("DB Connection Failed: " + dbUrl);
e.printStackTrace();
return;
}

ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
for (int i = 0; i < threadCount; i++) {
threadPool.submit(artes[i]);
}

while (true) {
Thread.sleep(100);
}
}

static class DbPreparator {

public static final int OBJ_CNT = 1;
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 

[h2] Re: table partitioning

[Anatolii K]

+1 vote for partitioning

I hope partitioning will improve insert/update scalability. In current 
implementation I see huge contention on table's MVMap

-- 
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] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

Please try attached test. It fails after 10-60 seconds run

org.h2.jdbc.JdbcSQLException: General error: 
"java.lang.IllegalStateException: File corrupted in chunk 28, expected page 
length 4..32, got -1621686845 [1.4.193/6]"; SQL statement:
update account set balance = ? where id = ? [5-193]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:295)
at org.h2.command.Command.executeUpdate(Command.java:266)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
at Bug_H2_4$Arte.call(Bug_H2_4.java:99)
at Bug_H2_4$Arte.call(Bug_H2_4.java:82)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalStateException: File corrupted in chunk 28, 
expected page length 4..32, got -1621686845 [1.4.193/6]
at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:766)
at org.h2.mvstore.Page.read(Page.java:649)
at org.h2.mvstore.Page.read(Page.java:195)
at org.h2.mvstore.MVStore.readPage(MVStore.java:1952)
at org.h2.mvstore.MVMap.readPage(MVMap.java:738)
at org.h2.mvstore.Page.getChildPage(Page.java:217)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:470)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:471)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:471)
at org.h2.mvstore.MVMap.get(MVMap.java:452)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.getValue(TransactionStore.java:1203)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.get(TransactionStore.java:1180)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.get(TransactionStore.java:1148)
at org.h2.mvstore.db.MVPrimaryIndex.getRow(MVPrimaryIndex.java:215)
at org.h2.mvstore.db.MVTable.getRow(MVTable.java:463)
at 
org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.get(MVSecondaryIndex.java:489)
at org.h2.index.IndexCursor.get(IndexCursor.java:288)
at org.h2.table.TableFilter.get(TableFilter.java:594)
at org.h2.command.dml.Update.update(Update.java:109)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)



On Thursday, December 22, 2016 at 2:36:35 PM UTC+5, Thomas Mueller Graf 
wrote:
>
> Hi,
>
> I didn't see a failure either.
>
> Regards,
> Thomas
>
>
> On Thu, Dec 22, 2016 at 10:13 AM, Noel Grandin <noelg...@gmail.com 
> > wrote:
>
>>
>>
>> On 2016/12/21 7:24 PM, Anatolii K wrote:
>>
>>> Test attached
>>>
>>>
>> Are you definitely testing with the latest HEAD? Because I ran your test 
>> for 10 minutes without problems.
>>
>> On 64-bit JDK 1.8.0_111
>>
>>
>> -- 
>> 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.SQLException;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


public class Bug_H2_4 {

static String dbUrl = "jdbc:h2:nioMemLZF:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=1;WRITE_DELAY=500";
static String user = "sa", pwd = "";
static int threadCount = 200;

public static void main(String args[]) throws InterruptedException, SQLException {
Arte[] artes = new Arte[threadCount];
Connection dbConnect;
try {
Class.forName("org.h2.Driver");
dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
DbPreparator.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");

DbPreparator.populate(dbConnect);
System.out.println("DB populated");

for (int i = 0; i < threadCount; i++) {
  

Re: [h2] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

Test attached

On Wednesday, December 21, 2016 at 8:41:44 PM UTC+5, Thomas Mueller Graf 
wrote:
>
> Hi,
>
> I guess that bug would be on my plate... I recently made changes there 
> (trying to improve concurrency). I'm sorry about that. Having a test case 
> would be great!
>
> Regards,
> Thomas
>
>
>
> On Wed, Dec 21, 2016 at 3:56 PM, Noel Grandin  > wrote:
>
>> Can we get a full stack trace for that failure.​
>>
>> -- 
>> 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.SQLException;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Bug_H2_4 {

static String dbUrl = "jdbc:h2:nioMemLZF:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1 ";
static String user = "sa", pwd = "";
static int threadCount = 100;

public static void main(String args[]) throws InterruptedException, SQLException {
Arte[] artes = new Arte[threadCount];
Connection dbConnect;
try {
Class.forName("org.h2.Driver");
dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
DbPreparator.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");

DbPreparator.populate(dbConnect);
System.out.println("DB populated");

for (int i = 0; i < threadCount; i++) {
artes[i] = new Arte(DriverManager.getConnection(dbUrl, user, pwd));
}
System.out.println("ARTEs created");
} catch (ClassNotFoundException | SQLException e) {
System.out.println("DB Connection Failed: " + dbUrl);
e.printStackTrace();
return;
}

ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
for (int i = 0; i < threadCount; i++) {
threadPool.submit(artes[i]);
}

while (true) {
Thread.sleep(100);
}
}

static class DbPreparator {

public static final int OBJ_CNT = 1;
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"
};

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) key (id) VALUES (?, ?)");
for (int i = 0; i < OBJ_CNT; i++) {
mergeAcctStmt.setLong(1, i);
mergeAcctStmt.setBigDecimal(2, BigDecimal.ZERO);
mergeAcctStmt.addBatch();
}
mergeAcctStmt.executeBatch();
db.commit();
}
}

static class Arte implements Callable {

Connection db;
PreparedStatement updateAcctStmt;

public Arte(Connection db_) throws SQLException {
db = db_;
db.setAutoCommit(false);
updateAcctStmt = db.prepareStatement("UPDATE account set balance = ? where id = ?");
}

@Override
public Integer call() {
try {
while (true) {
updateAcctStmt.setDouble(1, Math.random());
updateAcctStmt.setLong(2, (int) (Math.random() * DbPreparator.OBJ_CNT));
updateAcctStmt.execute();
db.commit();
}
} catch (SQLException e) {
System.out.println("DB write error: ");
e.printStackTrace();
System.exit(0);
}
return null;
}
}

}

Re: [h2] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

org.h2.jdbc.JdbcSQLException: General error: 
"java.nio.BufferUnderflowException"; SQL statement:
UPDATE account set balance = ? where id = ? [5-193]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:295)
at org.h2.command.Command.executeUpdate(Command.java:266)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
at org.radixware.imdb_test.Arte.updateAcct(Arte.java:137)
at org.radixware.imdb_test.Arte.call(Arte.java:68)
at org.radixware.imdb_test.Arte.call(Arte.java:11)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.nio.BufferUnderflowException
at java.nio.Buffer.nextGetIndex(Buffer.java:500)
at java.nio.DirectByteBuffer.get(DirectByteBuffer.java:249)
at org.h2.compress.CompressLZF.expand(CompressLZF.java:427)
at org.h2.store.fs.FileNioMemData.expand(FilePathNioMem.java:555)
at org.h2.store.fs.FileNioMemData.readWrite(FilePathNioMem.java:659)
at org.h2.store.fs.FileNioMem.read(FilePathNioMem.java:332)
at org.h2.mvstore.DataUtils.readFully(DataUtils.java:421)
at org.h2.mvstore.FileStore.readFully(FileStore.java:98)
at org.h2.mvstore.Page.read(Page.java:190)
at org.h2.mvstore.MVStore.readPage(MVStore.java:1959)
at org.h2.mvstore.MVMap.readPage(MVMap.java:800)
at org.h2.mvstore.Page.getChildPage(Page.java:217)
at org.h2.mvstore.Cursor.min(Cursor.java:129)
at org.h2.mvstore.Cursor.hasNext(Cursor.java:36)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap$1.fetchNext(TransactionStore.java:1221)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap$1.(TransactionStore.java:1217)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.keyIterator(TransactionStore.java:1212)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.keyIterator(TransactionStore.java:1200)
at org.h2.mvstore.db.MVSecondaryIndex.find(MVSecondaryIndex.java:309)
at org.h2.mvstore.db.MVSecondaryIndex.find(MVSecondaryIndex.java:254)
at org.h2.index.BaseIndex.find(BaseIndex.java:128)
at org.h2.index.IndexCursor.find(IndexCursor.java:169)
at org.h2.table.TableFilter.next(TableFilter.java:467)
at org.h2.command.dml.Update.update(Update.java:102)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)
... 8 more


On Wednesday, December 21, 2016 at 7:57:41 PM UTC+5, Noel Grandin wrote:
>
> Can we get a full stack trace for that failure.​
>

-- 
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] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

The bug is reproduced only in multi-thread application

On Wednesday, December 21, 2016 at 8:41:44 PM UTC+5, Thomas Mueller Graf 
wrote:
>
> Hi,
>
> I guess that bug would be on my plate... I recently made changes there 
> (trying to improve concurrency). I'm sorry about that. Having a test case 
> would be great!
>
> Regards,
> Thomas
>
>
>
> On Wed, Dec 21, 2016 at 3:56 PM, Noel Grandin  > wrote:
>
>> Can we get a full stack trace for that failure.​
>>
>> -- 
>> 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.

Re: [h2] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

Without split it also failed (url  jdbc:h2:nioMemLZF:db):

Caused by: java.nio.BufferUnderflowException
at java.nio.Buffer.nextGetIndex(Buffer.java:500)

On Wednesday, December 21, 2016 at 7:50:31 PM UTC+5, Noel Grandin wrote:
>
> then let's fix that problem, but I'm not really keen on trying to fix a 
> combination crash like 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] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

I have very big in-memory database ~ 1TB. Without split H2 crashes 


On Wednesday, December 21, 2016 at 7:43:13 PM UTC+5, Noel Grandin wrote:
>
> I'm sorry, but that's just a silly combination to be using. 
>
> You should only using be "split:" for FAT filesystems, and you should 
> certainly not be using it in combination with 
> "nioMemLZF:" 
>
>

-- 
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] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

After applying this patch I'm getting following error with nioMemLZF 
(url= jdbc:h2:split:30:nioMemLZF:db):

org.h2.jdbc.JdbcSQLException: General error: 
"java.nio.BufferUnderflowException"; SQL statement:
UPDATE account set balance = ? where id = ? [5-193]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:295)
at org.h2.command.Command.executeUpdate(Command.java:266)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
at org.radixware.imdb_test.Arte.updateAcct(Arte.java:137)
at org.radixware.imdb_test.Arte.call(Arte.java:68)
at org.radixware.imdb_test.Arte.call(Arte.java:11)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.nio.BufferUnderflowException
at java.nio.Buffer.nextGetIndex(Buffer.java:500)
at java.nio.DirectByteBuffer.get(DirectByteBuffer.java:249)
at org.h2.compress.CompressLZF.expand(CompressLZF.java:427)
at org.h2.store.fs.FileNioMemData.expand(FilePathNioMem.java:555)
at org.h2.store.fs.FileNioMemData.readWrite(FilePathNioMem.java:659)
at org.h2.store.fs.FileNioMem.read(FilePathNioMem.java:332)
at org.h2.store.fs.FileSplit.read(FilePathSplit.java:296)
at org.h2.mvstore.DataUtils.readFully(DataUtils.java:421)
at org.h2.mvstore.FileStore.readFully(FileStore.java:98)
at org.h2.mvstore.Page.read(Page.java:190)
at org.h2.mvstore.MVStore.readPage(MVStore.java:1952)
at org.h2.mvstore.MVMap.readPage(MVMap.java:738)
at org.h2.mvstore.Page.getChildPage(Page.java:217)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:470)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:471)
at org.h2.mvstore.MVMap.get(MVMap.java:452)


On Wednesday, December 21, 2016 at 4:01:40 PM UTC+5, Anatolii K wrote:
>
> Thank you for quick help!
>
> On Wednesday, December 21, 2016 at 3:55:32 PM UTC+5, Noel Grandin wrote:
>>
>> It appears I was wrong about this, we already implement reading/writing 
>> without synchronisation for all of our other 
>> file implementations, I just missed that you are using the "split:" 
>> stuff. 
>>
>> I have pushed a fix for this. 
>>
>

-- 
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] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

Thank you for quick help!

On Wednesday, December 21, 2016 at 3:55:32 PM UTC+5, Noel Grandin wrote:
>
> It appears I was wrong about this, we already implement reading/writing 
> without synchronisation for all of our other 
> file implementations, I just missed that you are using the "split:" stuff. 
>
> I have pushed a fix for this. 
>

-- 
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] Concurrency at org.h2.store.fs.FileBase.read()

[Anatolii K]

Hi

I see a lot of thread blocked at 

java.lang.Thread.State: BLOCKED (on object monitor)
at org.h2.store.fs.FileBase.read(FileBase.java:39)
- waiting to lock <0x0006f1dfcdd0> (a org.h2.store.fs.FileSplit)
at org.h2.mvstore.DataUtils.readFully(DataUtils.java:421)
at org.h2.mvstore.FileStore.readFully(FileStore.java:98)
at org.h2.mvstore.Page.read(Page.java:190)
at org.h2.mvstore.MVStore.readPage(MVStore.java:1952)
at org.h2.mvstore.MVMap.readPage(MVMap.java:800)
at org.h2.mvstore.Page.getChildPage(Page.java:217)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:489)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:490)
at org.h2.mvstore.MVMap.binarySearch(MVMap.java:490)
at org.h2.mvstore.MVMap.get(MVMap.java:471)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.getValue(TransactionStore.java:1036)
- locked <0x0006f1dd0608> (a org.h2.mvstore.MVMap)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.get(TransactionStore.java:1013)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.get(TransactionStore.java:981)
at org.h2.mvstore.db.MVPrimaryIndex.getRow(MVPrimaryIndex.java:215)
at org.h2.mvstore.db.MVTable.getRow(MVTable.java:463)
at 
org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.get(MVSecondaryIndex.java:489)
at org.h2.index.IndexCursor.get(IndexCursor.java:288)
at org.h2.table.TableFilter.get(TableFilter.java:594)
at org.h2.command.dml.Update.update(Update.java:109)

Why the read() method is synchronized?
May be it would be better to use java.util.concurrent.locks.ReadWriteLock? 


-- 
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] SELECT FOR UPDATE does not work with MERGE

[Anatolii K]

Really? It seems to work with UPDATE. I tested it with H2 console and using 
attached test with UPDATE instead of MERGE

If SELECT FOR UPDATE doesn't work what 


On Tuesday, December 20, 2016 at 2:30:45 PM UTC+5, Noel Grandin wrote:
>
> SELECT FOR UPDATE doesn't actually do anything useful in MVCC mode, I'm 
> afraid. 
>

-- 
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] SELECT FOR UPDATE does not work with MERGE

[Anatolii K]

Hi

Please see attached file: I added method testConcurrentMergeDeadlock() to 
existing test.
This test failed with error
...
Caused by: java.lang.IllegalStateException: Entry is locked [1.4.193/101]
at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:766)


The same test with UPDATE instead of MERGE works fine.
 
Looks like MERGE removes lock previously acquired by SELECT FOR UPDATE


-- 
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.
/*
 * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0,
 * and the EPL 1.0 (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.test.mvcc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.concurrent.CountDownLatch;

import org.h2.api.ErrorCode;
import org.h2.test.TestBase;
import org.h2.util.Task;

/**
 * Multi-threaded MVCC (multi version concurrency) test cases.
 */
public class TestMvccMultiThreaded extends TestBase {

/**
 * Run just this test.
 *
 * @param a ignored
 */
public static void main(String... a) throws Exception {
TestBase.createCaller().init().test();
}

@Override
public void test() throws Exception {
testMergeWithUniqueKeyViolation();
// not supported currently
if (!config.multiThreaded) {
testConcurrentMerge();
testConcurrentUpdate();
testConcurrentMergeDeadlock();
}
}

private void testMergeWithUniqueKeyViolation() throws Exception {
deleteDb(getTestName());
Connection conn = getConnection(getTestName());
Statement stat = conn.createStatement();
stat.execute("create table test(x int primary key, y int unique)");
stat.execute("insert into test values(1, 1)");
assertThrows(ErrorCode.DUPLICATE_KEY_1, stat).
execute("merge into test values(2, 1)");
stat.execute("merge into test values(1, 2)");
conn.close();

}

private void testConcurrentMerge() throws Exception {
deleteDb(getTestName());
int len = 3;
final Connection[] connList = new Connection[len];
for (int i = 0; i < len; i++) {
Connection conn = getConnection(
getTestName() + ";MVCC=TRUE;LOCK_TIMEOUT=500");
connList[i] = conn;
}
Connection conn = connList[0];
conn.createStatement().execute(
"create table test(id int primary key, name varchar)");
Task[] tasks = new Task[len];
final boolean[] stop = { false };
for (int i = 0; i < len; i++) {
final Connection c = connList[i];
c.setAutoCommit(false);
tasks[i] = new Task() {
@Override
public void call() throws Exception {
while (!stop) {
c.createStatement().execute(
"merge into test values(1, 'x')");
c.commit();
Thread.sleep(1);
}
}
};
tasks[i].execute();
}
Thread.sleep(1000);
stop[0] = true;
for (int i = 0; i < len; i++) {
tasks[i].get();
}
for (int i = 0; i < len; i++) {
connList[i].close();
}
deleteDb(getTestName());
}

private void testConcurrentUpdate() throws Exception {
deleteDb(getTestName());
int len = 2;
final Connection[] connList = new Connection[len];
for (int i = 0; i < len; i++) {
connList[i] = getConnection(
getTestName() + ";MVCC=TRUE");
}
Connection conn = connList[0];
conn.createStatement().execute(
"create table test(id int primary key, value int)");
conn.createStatement().execute(
"insert into test values(0, 0)");
final int count = 1000;
Task[] tasks = new Task[len];

final CountDownLatch latch = new CountDownLatch(len);

for (int i = 0; i < len; i++) {
final int x = i;
tasks[i] = new Task() {
@Override
public void call() throws Exception {
for (int a = 0; a < count; a++) {
connList[x].createStatement().execute(
"update test set value=value+1");
latch.countDown();
latch.await();
}
}
};

Re: [h2] Re: Multithread insert performance issue

[Anatolii K]

In fact this test shows 3 problems:
1. Bad scalabity even with embedded server:  H2 can't use more then 10% of 
PU due to locking 
2. Slow TCP exchange even with localhost
3. Degradation of TCP server performance, probably due to a leak

I would very appreciate your input for each of the problems

My final goal - 2000 tps with TCP server. Each transaction includes approx. 
10 inserts, 10 updates by primary key, 50 short selects. Database size 
>1TB. CPU cores are not limited, we can have 30-100 cores
Do you think it is achivable?  Do you have such experience?
  

On Sunday, December 11, 2016 at 7:41:34 PM UTC, Noel Grandin wrote:
>
> thanks.
>
> looks like we are bottlenecking on the locking on the undoLog object in 
> TransactionStore, particularly in the commit() method.
>
> I see a comment there
>// TODO could synchronize on blocks (100 at a time or so)
> which means perhaps Thomas had some ideas how this could be improved.
>
> Bit late here now, will thank about this more tomorrow.
>
> Still don't understand why the TCP server is so much slower than direct, 
> but perhaps it's a combined latenct thingof the locking and the socket 
> connection limiting the throughput?
>
> ​
>

-- 
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: Multithread insert performance issue

[Anatolii K]

Test attached

-- 
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.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Bug_H2_3 {

static String dbUrl = "jdbc:h2:tcp://localhost:9002/nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1"; //"jdbc:h2:nioMemFS:db;MULTI_THREADED=1";
static String user = "sa", pwd = "";
static int threadCount = 100;

public static void main(String args[]) throws InterruptedException, SQLException {
if (args.length > 0) {
dbUrl = args[0];
}
Arte3[] artes = new Arte3[threadCount];
Connection dbConnect;
try {
Class.forName("org.h2.Driver");
dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
DbPreparator3.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");

for (int i = 0; i < threadCount; i++) {
artes[i] = new Arte3(DriverManager.getConnection(dbUrl, user, pwd), i);
}
System.out.println("ARTEs created");
} catch (ClassNotFoundException | SQLException e) {
System.out.println("DB Connection Failed: " + dbUrl);
e.printStackTrace();
return;
}

ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
for (int i = 0; i < threadCount; i++) {
threadPool.submit(artes[i]);
}

long t = System.currentTimeMillis();
long lastTranCnt = 0;
while (true) {
Thread.sleep(100);
long insertCnt = 0;
for (int i = 0; i < threadCount; i++) {
insertCnt += artes[i].insertCnt;
}
long t1 = System.currentTimeMillis();
if (t1 - t > 1000) {
System.out.println("TPS: " + ((int) ((insertCnt - lastTranCnt) * 1000.0 / (t1 - t)) + "; Inserts: " + insertCnt));
lastTranCnt = insertCnt;
t = t1;
}
}
}
}

class DbPreparator3 {

public static final int OBJ_CNT = 1;
private static final String[] SQLs = new String[]{
"CREATE TABLE IF NOT EXISTS DOER ("
+ " ID NUMBER(18,0) not null, \n"
+ " SEQ NUMBER(9,0) not null, \n"
+ " PRIMARY KEY(ID, SEQ) \n"
+ ")"
};

public static void prepareScheme(Connection db) throws SQLException {
for (String sql : SQLs) {
db.createStatement().execute(sql);
db.commit();
}
}

}

class Arte3 implements Callable {

Connection db;
PreparedStatement insertDoerStmt;
long tranId;
int insertCnt;

public Arte3(Connection db_, int number) throws SQLException {
db = db_;
db.setAutoCommit(false);
insertDoerStmt = db.prepareStatement("INSERT INTO doer (id, seq) values(?, ?)");
tranId = number * 1_000_000_000_000L; //to guarantee uniques
}

@Override
public Integer call() {
try {
while (true) {
for (int i = 0; i < 10; i++) {
insertDoerStmt.setLong(1, tranId);
insertDoerStmt.setLong(2, i);
insertDoerStmt.addBatch();
insertCnt++;
}
insertDoerStmt.executeBatch();
db.commit();
tranId++;
}
} catch (SQLException e) {
System.out.println("Insert error at " + tranId + ": ");
e.printStackTrace();
System.exit(0);
}
return null;
}
}

[h2] Multithread insert performance issue

[Anatolii K]

Hi

I run attached test at AWS r3.8xlarge instance (32 core, 256 GB RAM). The 
test has 100 threads which perform inserts into one simple table 

*Test #1 - TCP server*
dbUrl = 
"jdbc:h2:tcp://localhost:9002/nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1"
At the very beginning I get 25K inserts/sec, then performance slowly 
decreased and come to 10K inserts/sec after 1M inserts

*Test #2 - embedded*
dbUrl = "jdbc:h2:nioMemFS:db;MULTI_THREADED=1"

I get stable troughput 70K inserts/sec
__
In both cases CPU was used less then 10%


Questions:
1. It there anything I can do to increase troughput of TCP server? I need 
at least 50K insert/second  
2. Why server performance goes down? And why it's only at TCP server


-- 
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] NullPointerException at concurrent updates (MULTI_THREADED)

[Anatolii K]

Thank you sir!

-- 
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] NullPointerException at concurrent updates (MULTI_THREADED)

[Anatolii K]

Hi

I've attached test which generates the exception if MULTI_THREADED=1: 

Caused by: java.lang.NullPointerException
at org.h2.mvstore.db.MVPrimaryIndex.getRow(MVPrimaryIndex.java:217)
at org.h2.mvstore.db.MVTable.getRow(MVTable.java:463)
at 
org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.get(MVSecondaryIndex.java:489)
at org.h2.index.IndexCursor.get(IndexCursor.java:288)
at org.h2.table.TableFilter.get(TableFilter.java:594)
at org.h2.command.dml.Update.update(Update.java:109)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)
... 3 more

at org.h2.engine.SessionRemote.done(SessionRemote.java:624)
at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:191)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
at Arte2.call(Bug_H2_1.java:95)
at Arte2.call(Bug_H2_1.java:78)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)


PS. Sorry if it's duplicate topic. I already publish this yesterday but 
can't find 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.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Bug_H2_1 {

static String dbUrl = "jdbc:h2:tcp://localhost:9002/nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1";
static String user = "sa", pwd = "";
static int threadCount = 100;

public static void main(String args[]) throws InterruptedException, SQLException {
Arte2[] artes = new Arte2[threadCount];
Connection dbConnect;
try {
Class.forName("org.h2.Driver");
dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
DbPreparator2.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");

DbPreparator2.populate(dbConnect);
System.out.println("DB populated");

for (int i = 0; i < threadCount; i++) {
artes[i] = new Arte2(DriverManager.getConnection(dbUrl, user, pwd));
}
System.out.println("ARTEs created");
} catch (ClassNotFoundException | SQLException e) {
System.out.println("DB Connection Failed: " + dbUrl);
e.printStackTrace();
return;
}

ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
for (int i = 0; i < threadCount; i++) {
threadPool.submit(artes[i]);
}

while (true) {
Thread.sleep(100);
}
}
}

class DbPreparator2 {

public static final int OBJ_CNT = 1;
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"
};

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) key (id) VALUES (?, ?)");
for (int i = 0; i < OBJ_CNT; i++) {
mergeAcctStmt.setLong(1, i);
mergeAcctStmt.setBigDecimal(2, BigDecimal.ZERO);
mergeAcctStmt.addBatch();
}
mergeAcctStmt.executeBatch();
db.commit();
}
}

class Arte2 implements Callable {

Connection db;
PreparedStatement updateAcctStmt;

public Arte2(Connection db_) throws SQLException {
db = db_;
db.setAutoCommit(false);
updateAcctStmt = db.prepareStatement("UPDATE account set balance = ? where id = ?");
}

@Override
public Integer call() {
try {
while (true) {
updateAcctStmt.setDouble(1, Math.random());
updateAcctStmt.setLong(2, (int) (Math.random() * DbPreparator2.OBJ_CNT));
updateAcctStmt.execute();
db.commit();
}
} catch (SQLException e) {
System.out.println("DB write error: ");
e.printStackTrace();
System.exit(0);
}
return null;
}
}

Re: [h2] "Unique index or primary key violation" at concurrent insers (MULTI_THREADED)

[Anatolii K]

 Great, thanks!

>
>  

>

-- 
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] "Unique index or primary key violation" at concurrent insers (MULTI_THREADED)

[Anatolii K]

Hi

I've attached test which generates the exception if MULTI_THREADED=1: 

org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: 
"PRIMARY KEY ON PUBLIC.TRAN"; SQL statement:
INSERT INTO tran (id) values(?) [23505-193]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:138)
at org.h2.mvstore.db.MVTable.addRow(MVTable.java:704)
at org.h2.command.dml.Insert.insertRows(Insert.java:156)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:344)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:158)
at java.lang.Thread.run(Unknown Source)

at org.h2.engine.SessionRemote.done(SessionRemote.java:624)
at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:191)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
at Arte.call(Bug_H2_2.java:82)
at Arte.call(Bug_H2_2.java:64)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

In case MULTI_THREADED=0 this test doesn't fail


-- 
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.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Bug_H2_2 {

static String dbUrl = "jdbc:h2:tcp://localhost:9002/nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1";
static String user = "sa", pwd = "";
static int threadCount = 100;

public static void main(String args[]) throws InterruptedException, SQLException {
Arte[] artes = new Arte[threadCount];
Connection dbConnect;
try {
Class.forName("org.h2.Driver");
dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
DbPreparator.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");

for (int i = 0; i < threadCount; i++) {
artes[i] = new Arte(DriverManager.getConnection(dbUrl, user, pwd), i);
}
System.out.println("ARTEs created");
} catch (ClassNotFoundException | SQLException e) {
System.out.println("DB Connection Failed: " + dbUrl);
e.printStackTrace();
return;
}

ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
for (int i = 0; i < threadCount; i++) {
threadPool.submit(artes[i]);
}

while (true) {
Thread.sleep(100);
}
}
}

class DbPreparator {

public static final int OBJ_CNT = 1;
private static final String[] SQLs = new String[]{
"CREATE TABLE IF NOT EXISTS TRAN ("
+ " ID NUMBER(18,0) not null PRIMARY KEY\n"
+ ")"
};

public static void prepareScheme(Connection db) throws SQLException {
for (String sql : SQLs) {
db.createStatement().execute(sql);
db.commit();
}
}

}

class Arte implements Callable {

Connection db;
PreparedStatement insertTranStmt;
long tranId;

public Arte(Connection db_, int number) throws SQLException {
db = db_;
db.setAutoCommit(false);
insertTranStmt = db.prepareStatement("INSERT INTO tran (id) values(?)");
tranId = number * 1_000_000_000 + System.currentTimeMillis(); //to garantee uniques
}

@Override
public Integer call() {
try {
while (true) {
insertTranStmt.setLong(1, tranId++);
insertTranStmt.execute();
db.commit();
}
} catch (SQLException e) {
System.out.println("DB write error: ");
e.printStackTrace();
System.exit(0);
}
return null;
}
}