Cannot remove records, cache size too small

[fharms]

Hi,

We are starting to see this error "database: Cannot remove records,
cache size too small?" after we upgrade from 1.1.110 -> 1.1.119 and
inserting a large number of records into one table (240.000).

/**/prep17249.executeUpdate();
12-02 18:07:14 lock: 2 exclusive write lock requesting for
CUSTOM_PROPERTY
12-02 18:07:14 database: Cannot remove records, cache size too small?
12-02 18:07:14 jdbc[2]:
/*SQL l:69 #:1 t:8*/insert into custom_property (value, ownerUuid,
name) values (?, ?, ?) {1: '', 2: '69f32aca-
af8d-4294-9a87-2d98e09cfe2f', 3: 'Serial No'};
12-02 18:07:14 jdbc[2]:
/**/prep17249.close();

our connection url look like this.

jdbc:h2:/database/
127.0.0.1;CACHE_SIZE=32720;DB_CLOSE_DELAY=0;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;CACHE_TYPE=TQ"

I could try to increase the cache_size, but I would like to understand
the error message and why we are starting to see in 1.1.119

Thanks in advance

br
Flemming

[Thomas Mueller]

Hi,

This is a warning message only, but it's strange that it shows up. Do
you use a very large transaction? Could you create a simple
reproducible test case? If not, no problem, there are other ways to
find out what's going on, but that would be the easiest way to
analyze.

By the way CACHE_TYPE=TQ doesn't have an effect since version 1.1.119.
I found some problems with this cache algorithm, and according to my
test it's slower in most cases. That's why I have disabled it. In any
case it will use the CacheLRU implementation - see also the changelog.

Regards,
Thomas

> --
>
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>
>

[fharms]

Thanks for your replay.

Yes it is a very large transaction. I solve it for now by increase the
cache size.
But this is only at temporary solution, because depend on the size I
might ran into this problem again.

I think it will be fairly easy to set-up a test case that reproduce
the error. I will get back when it's ready.

Do you have any idea why I didn't see this in 1.1.110?

br,
Flemming Harms


On Dec 3, 9:11 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> This is a warning message only, but it's strange that it shows up. Do
> you use a very large transaction? Could you create a simple
> reproducible test case? If not, no problem, there are other ways to
> find out what's going on, but that would be the easiest way to
> analyze.
>
> By the way CACHE_TYPE=TQ doesn't have an effect since version 1.1.119.
> I found some problems with this cache algorithm, and according to my
> test it's slower in most cases. That's why I have disabled it. In any
> case it will use the CacheLRU implementation - see also the changelog.
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

> I think it will be fairly easy to set-up a test case that reproduce
> the error. I will get back when it's ready.

A test case would be great!

> Do you have any idea why I didn't see this in 1.1.110?

No.

Regards,
Thomas

[fharms]

Hi,

Okay I managed to reproduce it through a simple test, but I didn't
find away of catching the warning messaged and then failing the unit
test.

So the only way I know it failed is because I log warning messaged
from H2.

Thanks

br
Flemming

package com.performancetest.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Arrays;

import junit.framework.TestCase;

public class TestH2CachePerformance extends TestCase {
private static final int NO_OF_RECORDS = 100000;

static String URL = "jdbc:h2:/tmp/
testcache;CACHE_TYPE=SOFT_LRU;DB_CLOSE_DELAY=0;CACHE_SIZE=2500;TRACE_LEVEL_FILE=4"; //
$NON-NLS-1$
private Connection connection;

protected void setUp() throws Exception {
connection = DriverManager.getConnection(URL, "sa", ""); //
$NON-NLS-1$ //$NON-NLS-2$
connection.setAutoCommit(false);

Statement createStatement = connection.createStatement();
try {
createStatement.execute("drop table
TestH2CachePerformance;"); //$NON-NLS-1$
} catch (Exception e) {
// Expect it in case of the table does not exits
}
createStatement
.execute("create table TestH2CachePerformance (\"id\" INT,
\"FirstName\" CHAR(255),\"LastName\" CHAR(255));"); //$NON-NLS-1$
createStatement.close();
connection.commit();
}

public void testCache() throws Exception {
Statement insertStatement = connection.createStatement();
char[] firstCol = new char[255];
char[] secondCol = new char[255];
Arrays.fill(firstCol, 'A');
Arrays.fill(secondCol, 'B');

for (int i = 0; i < NO_OF_RECORDS; i++) {
insertStatement.addBatch("INSERT INTO
TestH2CachePerformance VALUES ("+i+", '"+new String(firstCol)
+"','"+new String(secondCol)+"');"); //$NON-NLS-1$ //$NON-NLS-2$ //
$NON-NLS-3$ //$NON-NLS-4$
}

int[] executeBatch = insertStatement.executeBatch();
insertStatement.close();
connection.commit();
}

protected void tearDown() throws Exception {
connection.close();
}

On Dec 12, 9:33 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

Thanks for the test case! I could reproduce the problem. It occurs
when the cache size is smaller than the transaction, which is the case
in the test case you sent. H2 doesn't support very large transactions
that well currently, one of the top feature requests on the roadmap is
"Support large inserts and updates". I'm not sure however when I will
have time to implement it (it will probably be only available when
using the the page store, version 1.2.x).

As a workaround, I suggest to split large transactions into smaller
ones, or use a larger cache size.

I create a new issue
http://code.google.com/p/h2database/issues/detail?id=157 with a
simpler test case.

Regards,
Thomas