Hello,
I have similar H2 1.4 MVCC performance problems with parallel selects.
In attached test case a test table is created with 10k records.
Then 4 threads are started, each executing 1000 consecutive selects, n-th
select returns n records.
If a test is started as is, with MULTI_THREADED=TRUE (MVCC true by
default), it takes approx 2s on my machine.
If ';MV_STORE=FALSE;MVCC=FALSE' is appended to dbUrl, test takes 0,5s.
Taking in account 4 parallel threads, the MVCC version is practically
non-parallel.
Thread dump during parallel read shows that one of the 4 threads locks on
'undoLog', others are waiting for that same lock.
Tested on 1.4.194 and 1.4.195.
In a real case with 50 threads this results in performance similar as in 3
threads; 15-20 times slower with MVCC. And I need MVCC.
Otherwise you just have to love this H2, its simply great. :)
David.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.*;
/**
* @author David Vidrih
*/
public class H214MvccTest
{
public static void main(String... args) throws Exception
{
String url = "jdbc:h2:mem:H214MvccTest;MULTI_THREADED=TRUE"; // MVCC version
//String url = "jdbc:h2:mem:H214MvccTest;MULTI_THREADED=TRUE;MV_STORE=FALSE;MVCC=FALSE"; // PageStore version
Class.forName("org.h2.Driver");
Connection con = DriverManager.getConnection(url, "sa", "");
con.setAutoCommit(false);
// create table, insert test data
executeUpdate(con, "create table test(id number(19), str varchar2(4000), primary key (id))");
for (int i = 0; i < 10000; i++)
executeUpdate(con, "insert into test (id, str) values (" + i + ", '" + i + "')");
con.commit();
// read in parallel
int threadCount = 4;
List<TestReader> testReaders = new ArrayList<>();
for (int threadNo = 0; threadNo < threadCount; threadNo++)
testReaders.add(new TestReader(threadNo));
ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
long startMillis = System.currentTimeMillis();
threadPool.invokeAll(testReaders);
long duration = System.currentTimeMillis() - startMillis;
threadPool.shutdown();
System.out.println("duration:" + duration);
con.close();
//System.exit(0);
}
private static void executeUpdate(Connection con, String sql) throws SQLException
{
Statement stmt = con.createStatement();
stmt.executeUpdate(sql);
stmt.close();
}
public static class TestReader implements Callable<Object>
{
private int id;
public TestReader(int id)
{
this.id = id;
}
@Override
public Object call() throws Exception
{
try
{
Connection con = DriverManager.getConnection("jdbc:h2:mem:H214MvccTest", "sa", "");
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement("select id, str from test where id <= ?");
for (int readNo = 0; readNo < 1000; readNo++)
{
ps.clearParameters();
ps.setLong(1, readNo);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
long id = rs.getLong(1);
String str = rs.getString(2);
}
rs.close();
}
ps.close();
con.commit();
con.close();
}
catch (SQLException e)
{
e.printStackTrace();
throw e;
}
System.out.println("TestReader " + id + " finished.");
return null;
}
}
}
