The cached table is not cached but in memory when using hsqldb

I am trying the cached table feature of hsqldb version 2.2.5 .

Just using JDBC batch insert into one table, the odd thing is if I run the my test alone, although I used the create cached table, but it is still using the memory mode, because after 100,0000 records, OOM.

But if I run some other test before this test case or use jdbc:hsqldb:mem:HSQLDB;hsqldb.default_table_type=cached , it will use cached table.

Anyway, I don't want to use cached for default.

The cached table 开发者_Python百科performance looks like this after 2600000 records, the performance decreased a lot. after 3600000 records, it becomes very slow

Perhaps some indexes occupies the memory fully makes the JVM becomes slow. Not because of db itself

Two sub questions

1)Any other config to force the cached table is using cached mode?

2)Any db config will help to increase the performace of cached table?

(With JDK1.6 -Xmx1024m)

---

The URL that you report is for a mem: (everything in memory - no persistence) database. In this setup, any reference or setting for CACHED tables is simply ignored and the data is stored in memory.

The CACHED table feature is available is file: (persist to disk) databases. These databases also support persistent MEMORY tables (all data is stored in memory). The data for both CACHED and MEMORY tables is stored on disk in file: databases.

Regarding performnce of CACHED, recently used rows are cached in memory and changes are saved to disk when the cache gets full.

The database engine uses java.nio methods to access databases that are above 16MB on disk, and below a certain limit. To improve performance, you can increase the limit with the hsqldb.nio_max_size property or the equivalent SQL statement.

In general, disks are slow in random access mode (maximum 100 access per second). Use of cache, nio, and other strategies can reduce effective access time, but only to some extent.

Also note you can turn data logging off during bulk inserts, and turn it on again for normal database operations.

These issues and settings are covered in this chapter of the Guide.

http://hsqldb.org/doc/2.0/guide/deployment-chapt.html