[infinispan-issues] [JBoss JIRA] Commented: (ISPN-877) analyse, document and suggest indexing the JDBC cache store

[Mircea Markus (JIRA)]

    [ https://issues.jboss.org/browse/ISPN-877?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603224#comment-12603224 ] 

Mircea Markus commented on ISPN-877:
------------------------------------

The following program was used with and without indexes:
      EmbeddedCacheManager ecm = new DefaultCacheManager("/Users/mmarkus/github/infinispan-xa-recovery-sample/src/main/resources/config.xml");
      Cache<Object,Object> c = ecm.getCache();

      int KEY_COUNT = 10000;
      long start = System.currentTimeMillis();
      for (int i = 0; i < KEY_COUNT; i++) {
         String s = key(i);
         c.put(s, s);
      }
      long duration  = System.currentTimeMillis() - start;

      System.out.println("duration(millis) for " + KEY_COUNT + " writes = " + duration);

      c.getAdvancedCache().getDataContainer().clear();

      Set<String> result = new HashSet<String>();
      start = System.currentTimeMillis();
      for (int i = 0; i < KEY_COUNT; i++) {
         result.add((String) c.get(key(i)));
      }
      duration  = System.currentTimeMillis() - start;

      System.out.println("duration(millis) for " + KEY_COUNT + " reads = " + duration);

      if (result.size() != KEY_COUNT) throw new RuntimeException("Huh?!!");

      start = System.currentTimeMillis();
      for (int i = 0; i < KEY_COUNT; i++) {
         c.remove(key(i));
      }
      duration  = System.currentTimeMillis() - start;

      System.out.println("duration(millis) for " + KEY_COUNT + " removes = " + duration);

The indexed column was "idColumnName" - which is passed as param in all key-based ops (get, remove).

The underlaying database was postgresql 8.4. Same results were obtained with and without indexes. Looking at the generated table the "idColumnName" is marked as primary key, and gets automatically indexed when created. I expect most vendors to index the PK out of the box, so no point in doing any recommandation around indexing.
 

> analyse, document and suggest indexing the JDBC cache store
> -----------------------------------------------------------
>
>                 Key: ISPN-877
>                 URL: https://issues.jboss.org/browse/ISPN-877
>             Project: Infinispan
>          Issue Type: Feature Request
>          Components: Loaders and Stores
>    Affects Versions: 4.2.0.Final
>            Reporter: Mircea Markus
>            Assignee: Mircea Markus
>             Fix For: 5.0.0.CR3, 5.0.0.FINAL
>
>
> Adding indexes to the JDBC cache store's backend table can drastically increase performance especially for the JdbcStringBasedCacheStore and partially for the JdbcMixedCacheStore.
> Investigate weather indexes can be defined through Jdbc in a portable manner (same way the tables are created on startup) or at least update documentation to describe how indexes should be defined and the advantages of having them defined.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira