There is yet another bug related to LOB handling, present in both 1.4.196
and master/HEAD. Namely, with the MVStore, just reading LOBs from the DB
causes it to grow. This behavior virtually rules out any long-term usage of
H2/MVStore with LOBs as the DB grows absurdly.
I'm attaching a standalone testcase. Just by reading LOBs the testcase is
able to increase the initial 367 MB to 1435 MB. That is almost fourfold.
What worse, it seems that there no limit to that growth. Replacing BLOB
datatype with VARCHAR makes the issue disappear.
The speed advantage that MVStore offers over PageStore is nice but the
PageStore DBs do not grow by being read using a single connection. I have
more that 10 million LOBs in my production database and dozen thousands of
them are read every day so this is not a hypothetical scenario. And
unfortunately the PageStore suffers from another LOB issue - it grows when
transaction is rolled back
(https://groups.google.com/forum/#!topic/h2-database/vACgYzuSu-o).
--
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.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Random;
import org.h2.jdbcx.JdbcDataSource;
import org.h2.store.fs.FileUtils;
public class H2IncreasingSizeTest
{
private static final String OBJ_TYPE = "BLOB"; // possible values are "BLOB" or "VARCHAR"
private static final String PAGESTORE_LOB_COMPRESSION_ALGO = "LZF"; // possible values are "LZF" or "DEFLATE"
private static final String DB_PATH = System.getProperty("java.io.tmpdir");
private static final String DB_NAME = "H2SizeTest";
private static Connection dbConn;
static void createFreshDb(boolean mvStore, boolean compress) throws Exception
{
// remove previous DB, if any
File testDbDir = new File(DB_PATH, DB_NAME);
FileUtils.deleteRecursive(testDbDir.getCanonicalPath(), false);
testDbDir.mkdirs();
// create a new DB
JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL("jdbc:h2:" + testDbDir.getCanonicalPath() + File.separator + DB_NAME
+ ";MV_STORE=" + mvStore
+ (mvStore ? ";MAX_LOG_SIZE=1" : "")
+ (mvStore ? ";COMPRESS=" + compress : (";COMPRESS_LOB=" + (compress ? PAGESTORE_LOB_COMPRESSION_ALGO : "NO")))
);
dbConn = dataSource.getConnection();
Statement st = dbConn.createStatement();
st.executeUpdate("CREATE SEQUENCE IF NOT EXISTS dataStampSequence CACHE 1024");
st.executeUpdate("CREATE TABLE IF NOT EXISTS dataTable("
+ "dataStamp BIGINT NOT NULL DEFAULT NEXT VALUE FOR dataStampSequence PRIMARY KEY, "
+ "data1 " + OBJ_TYPE + ", "
+ "data2 " + OBJ_TYPE + ")");
st.close();
}
static void openExistingDb(boolean mvStore, boolean compress) throws Exception
{
File testDbDir = new File(DB_PATH, DB_NAME);
String testDbFileName = testDbDir.getCanonicalPath() + File.separator + DB_NAME + (mvStore ? ".mv.db" : ".h2.db");
if (!FileUtils.exists(testDbFileName)) {
throw new AssertionError();
}
JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL("jdbc:h2:" + testDbDir.getCanonicalPath() + File.separator + DB_NAME
+ ";MV_STORE=" + mvStore
+ (mvStore ? ";MAX_LOG_SIZE=1" : "")
+ (mvStore ? ";COMPRESS=" + compress : (";COMPRESS_LOB=" + (compress ? PAGESTORE_LOB_COMPRESSION_ALGO : "NO")))
);
dbConn = dataSource.getConnection();
}
static void pushData(int numRows, boolean commit) throws Exception
{
if (dbConn == null) {
throw new AssertionError();
}
// push data
Random rnd = new Random(0xBADDB);
PreparedStatement dataInsertSt = dbConn.prepareStatement("INSERT INTO dataTable VALUES("
+ "DEFAULT, "
+ "?, "
+ "?)");
dbConn.setAutoCommit(false);
for (int i = 0; i < numRows; ++i) {
if (dbConn.getAutoCommit() == true) {
throw new AssertionError();
}
// compressible data
byte[] data1 = new byte[rnd.nextInt((int)(2.5 * 4096)) + 1];
for (int j = 0; j < data1.length; ++j) {
data1[j] = (byte)j;
}
dataInsertSt.setBytes(1, data1);
// incompressible data
byte[] data2 = new byte[rnd.nextInt((int)(0.75 * 4096)) + 1];
rnd.nextBytes(data2);
dataInsertSt.setBytes(2, data2);
dataInsertSt.executeUpdate();
dataInsertSt.clearParameters();
if (commit) {
dbConn.commit();
} else {
dbConn.rollback();
}
}
dataInsertSt.close();
if (!commit) { // make sure that the only table has no rows
Statement st = dbConn.createStatement();
ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM dataTable");
rs.first();
int numRowsInserted = rs.getInt(1);
if (numRowsInserted != 0) {
throw new AssertionError();
}
st.close();
}
}
static void readData(int numRows) throws Exception
{
if (dbConn == null) {
throw new AssertionError();
}
PreparedStatement dataRetrSt = dbConn.prepareStatement("SELECT data1, data2 FROM dataTable WHERE dataStamp = ?");
for (int i = 1; i <= numRows; ++i) {
dataRetrSt.setLong(1, i);
ResultSet rs = dataRetrSt.executeQuery();
if (rs.first()) {
byte[] data1 = rs.getBytes("data1");
byte[] data2 = rs.getBytes("data2");
}
rs.close();
dataRetrSt.clearParameters();
}
dataRetrSt.close();
}
static void printDbSize() throws Exception
{
File testDbDir = new File(DB_PATH, DB_NAME);
long testDbSize = 0;
for (File file : testDbDir.listFiles()) {
testDbSize += file.length();
}
System.out.println("Size of the test DB is " + testDbSize / (1024 * 1024) + " MB");
}
static void closeDb(boolean compact) throws Exception
{
if (compact) {
Statement st = dbConn.createStatement();
st.execute("SHUTDOWN COMPACT");
st.close();
}
dbConn.close();
}
public static void main(final String[] args)
{
boolean mvStore = true;
boolean commit = true;
boolean compress = true;
int numRows = 100000;
boolean compactOnClose = false;
try {
createFreshDb(mvStore, compress);
pushData(numRows, commit);
closeDb(compactOnClose);
printDbSize();
for (int i = 0; i < 100; ++i) {
openExistingDb(mvStore, compress);
readData(numRows);
closeDb(compactOnClose);
printDbSize();
}
} catch (final Exception e) {
e.printStackTrace();
}
}
}
