How to determine the right size of table entries for CacheJdbcBlobStore?

Question

I am trying to set up a persistent cache store for my Apache Ignite application. The cache store I try to use is CacheJdbcBlobStore. Here is the list of software I am using in my prototype:

• JDK 1.8.0_66
• Apache Tomcat 7.0.68
• Apache Ignite 1.6.0
• HyperSQL 2.3.4

My data source is set up as below in Spring:

<bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
    <property name="url" value="jdbc:hsqldb:http://localhost"/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
</bean>

And the Ignite configuration:

<bean id="ignite.cfg" class="org.apache.ignite.configuration.IgniteConfiguration">
    <property name="cacheConfiguration">
        <list>
            <bean class="org.apache.ignite.configuration.CacheConfiguration">
                <property name="cacheMode" value="REPLICATED"/>
                <property name="name" value="session-cache"/>
                <property name="cacheStoreFactory">
                    <bean class="org.apache.ignite.cache.store.jdbc.CacheJdbcBlobStoreFactory">
                        <property name="dataSourceBean" value="myDataSource" />
                    </bean>
                </property>
                <property name="readThrough" value="true" />
                <property name="writeThrough" value="true" />                               
            </bean>
        </list>
    </property>
</bean>

However, when I ran the application, I got the following exception:

ERROR - root                       - Failed to update web session: null
class org.apache.ignite.IgniteException: Failed to save session: C56AF4E1DA01A439E43E512950D32D45
Caused by: javax.cache.integration.CacheWriterException: class org.apache.ignite.internal.processors.cache.CachePartialUpdateCheckedException: Failed to update keys (retry update if possible).: [C56AF4E1DA
01A439E43E512950D32D45]
Caused by: class org.apache.ignite.internal.processors.cache.CachePartialUpdateCheckedException: Failed to update keys (retry update if possible).: [C56AF4E1DA01A439E43E512950D32D45]
                Suppressed: class org.apache.ignite.IgniteCheckedException: Failed to put object [key=C56AF4E1DA01A439E43E512950D32D45, val=WebSessionEntity [id=C56AF4E1DA01A439E43E512950D32D45, createTime=1464182374328, accessTime=1464182374330, maxInactiveInterval=1800, attributes=[]]]
                        at org.apache.ignite.internal.processors.cache.store.GridCacheStoreManagerAdapter.put(GridCacheStoreManagerAdapter.java:583)
                        at org.apache.ignite.internal.processors.cache.GridCacheMapEntry.innerUpdate(GridCacheMapEntry.java:2358)
                        at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.updateSingle(GridDhtAtomicCache.java:2246)
                        ... 37 more
                Caused by: javax.cache.integration.CacheWriterException: Failed to put object [key=C56AF4E1DA01A439E43E512950D32D45, val=WebSessionEntity [id=C56AF4E1DA01A439E43E512950D32D45, createTime=1464182374328, accessTime=1464182374330, maxInactiveInterval=1800, attributes=[]]]
                        at org.apache.ignite.cache.store.jdbc.CacheJdbcBlobStore.write(CacheJdbcBlobStore.java:281)
                        at org.apache.ignite.internal.processors.cache.store.GridCacheStoreManagerAdapter.put(GridCacheStoreManagerAdapter.java:575)
                        ... 39 more
                Caused by: java.sql.SQLDataException: data exception: string data, right truncation;  table: ENTRIES column: AKEY
                        at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
                        at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
                        at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
                        at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
                        at org.apache.ignite.cache.store.jdbc.CacheJdbcBlobStore.write(CacheJdbcBlobStore.java:277)
                        ... 40 more

I've figured out that you need to add the createTableQuery property to jdbc.CacheJdbcBlobStoreFactory:

<bean class="org.apache.ignite.cache.store.jdbc.CacheJdbcBlobStoreFactory">
    ...
    <property name="createTableQuery" value="create table if not exists ENTRIES (akey VARBINARY(100) primary key, val BLOB(10k))" />
</bean>

So as to override the default one:

create table if not exists ENTRIES (akey binary primary key, val binary)

My question is: how would you determine the size of the table entry (especially the val column)? Currently I put down 10k. If I set the size too high, it will be wasteful, but If I put the size too low, I'll get the exception mentioned above sooner or later. Thank you.

Answer 1

Perhaps you don't have to worry about the columns sizes of table ENTRIES. For example, in HyperSQL database if you create the table the following way:

create table if not exists ENTRIES (akey VARBINARY(100) primary key, val BLOB(10k))

10k is the maximum size of column val rather than the actual space that would be taken. Therefore it will not be wasteful if the actual length is lower than the maximum limit.

In Oracle, you can create the table as below:

create table ENTRIES (akey VARCHAR2(100) primary key, val BLOB);

And again, you don't have to worry about the size of column val. The size of column with type BLOB can be regarded as unlimited (Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)) in Oracle. The actual space taken depends on the actual size of the cache. No space will be wasted.