1 Reply Latest reply on Aug 28, 2014 3:44 PM by rhauch

What is the recommended infinispan.jdbc.data_column.type value when using MySQL 5

ma6rl Aug 28, 2014 2:59 PM

I am currently doing some testing with ModeShape 4.0.0.Beta1 to see how it performs with indexes.

As part of this testing I have been creating number of child nodes under a parent node and running some queries with and without indexes.

I quickly ran into a problem when creating approx. > 700 child nodes under the same parent node due to the size of the data being stored in the infinispan.jdbc.data_column exceeding the maximum size of the column.

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'datum' at row 1

The nodes I am creating have 6 properties (4 String, 2 Dates) and no child nodes.

Below is my Infinispan Cache configuration:

           <cache-container name="modeshape" default-cache="modeshape-repo" module="org.modeshape">
                <transport lock-timeout="60000"/>
                <replicated-cache name="modeshape-repo" mode="SYNC">
                    <transaction mode="NON_XA"/>
                    <string-keyed-jdbc-store shared="true" preload="false" passivation="false" purge="false" datasource="java:jboss/datasources/TestDS">
                        <string-keyed-table prefix="modeshape">
                            <id-column name="id" type="VARCHAR(200)"/>
                            <data-column name="datum" type="BLOB"/>
                            <timestamp-column name="version" type="BIGINT"/>
                        </string-keyed-table>
                    </string-keyed-jdbc-store>
                </replicated-cache>
            </cache-container>

What is the recommended column type for the data-column when using MySql? I looked at the Modeshape code base and the mysql maven profile uses 'BLOB' which is the same as above.

I tried changing the value to 'LONGBLOB' and was able to create 10000 child nodes, I have yet to find the limit with 'LONGBLOB'.

Should I be able to store more than 700 child nodes under a parent node if I use BLOB or do I need to use a MEDIUM or LONG BLOB if I am expecting more than 700 number of chid nodes?

1. Re: What is the recommended infinispan.jdbc.data_column.type value when using MySQL 5

rhauch Aug 28, 2014 3:44 PM (in response to ma6rl)

What is the recommended column type for the data-column when using MySql? I looked at the Modeshape code base and the mysql maven profile uses 'BLOB' which is the same as above.

I tried changing the value to 'LONGBLOB' and was able to create 10000 child nodes, I have yet to find the limit with 'LONGBLOB'.

It's difficult to translate the size of a node (e.g., number of children and number of properties) into number of characters used in the persisted representation. It certainly won't hurt to use LONGBLOB (and it costs only 3 extra bytes per value compared to BLOB), but if you're trying to really minimize storage space you might try MEDIUMBLOB and see if you run into a limit.

You need to be careful about creating so many children under a single parent. ModeShape is hierarchical, and the more you get away from hierarchical (by creating tons of children in parent nodes), the farther away from ModeShape's sweet spot you're going to get. It will work, but be cognizant of what you're doing and how it affects performance.
Actions