best design to store database records

[kullmann]

Hi,

I want to use infinispan to cache database records ( extend the sga :-) ). Now I'm looking for some best practise pattern. I'm using the primary key as key in the cache and the value is the record as byte[]. But how handle lookups for secondary indexes ( unique and not unique ) ? My idea is to store the key criteria as key in the cache and the value contains a list of the primary keys. But this means there are many roundtripps just to fetch the entries of an one unique index and 2 roundtrip to select an unique index entry ( one to fetch the primary key value by the unique key and the fetch the value with the primary key ).

Is there an better solution for the problem ?

Regards,

André

[sannegrinovero]

Hi André,

above the problems you mentioned, you should also consider when it's safe to update the cache, to keep them properly in sync you'll want to update the Infinispan grid only - and reliably - during a succesfull commit on the database.

All these issues where solved already in the design of the Hibernate cache integration (second level cache using Hibernate terminology).

Did you consider using Hibernate for this purpose?

[kullmann]

Hi Sanne,

the project I want to speed up with infinispan uses an in house or mapper, so hibernate isn't an option.

I will take a look how the hibernate integration resolve the problems. Thanks for the hint !!

Currently a database roundtrip took 5ms. I'm not sure if infinispan can top this.

[sannegrinovero]

the project I want to speed up with infinispan uses an in house or mapper, so hibernate isn't an option.

I will take a look how the hibernate integration resolve the problems. Thanks for the hint !!

Good idea. Also consider that to be able to apply the changes in a Transaction Sync / post transaction operation, you'll have to track state changes performed in a "session", so hope your in house mapper is able to do that. State change tracking is quite sofisticated in Hibernate.

While keeping two resources in sync can be dounting, did you consider moving some data exclusively to Infinispan?

Currently a database roundtrip took 5ms. I'm not sure if infinispan can top this.

I'd hope so, still the low roundtrip should not be your only good reason to use Infinispan, the main reason is the extreme scalability: a single request will always involve some networking (unless with Infinispan it's cached locally), but by design you can scale the number of requests linearly while this is impossible with a database. Also - depending on your network infrastructure - Infinispan should do way better than 5ms.

[kullmann]

As first step we just want to cache read only data in infinispan. The data will be imported in our database via an batch job. The idea is the job will refresh, or clear the infinispan in the future.

[sannegrinovero]

ok, it should be fine then.

Assuming the stored data is significantly larger than the keys, you could store the secondary indexes in the cache itself under "query keys", if you do so and use a differently configured cache you could store this index in a replicated cache instead of ditributed, and so get zero roundtrips for the secondary lookups (as local lookups are extremely fast).