Long-running Infinispan DELETE query

[Jim Coble]

We're running Fedora 4.5.1 with MySQL (MariaDB) for the Infinispan database.  Following the migration of 324,000 objects from our Fedora 3 repository to this Fedora 4 repository, we are seeing the following frequent (every minute or two?), long-running DELETE query run against the Infinispan database:

DELETE FROM `ispn_entry_FedoraRepository` WHERE version< 1467297632684 AND version> 0

This DELETE query currently takes something over a minute to complete and acquires a lock that prevents other INSERT (e.g.) queries from running.  We have had to increase our innodb_lock_wait_timeout in order to prevent these subsequent INSERT's from timing out but that is not a satisfactory solution.

Does anyone know where this DELETE query is coming from?  The 'version' column is not indexed on the ispn_entry_FedoraRepository table so I assume the DELETE query has to do a full table scan each time it runs.  We have considered indexing the 'version' column but are not sure what other effects that might have.

Any help or advice would be greatly appreciated!  Thanks.

--Jim Coble

Duke University Libraries

[David Chandek-Stark]

For lack of any other better option at this point, we are experimenting with adding an index on the version column of the database.  In our test environment we have not yet seen any side effects from this change; OTOH we probably won't really know if this makes a difference until we push it to production because in any case it's not likely to be a performance issue with a smallish test repository.

We'll report back to this thread when we have more information.

--D

[David Chandek-Stark]

I'm curious BTW if anyone thinks that this query ever actually deletes anything in Fedora's case.  We've occasionally queried the db seeing only -1 values in the column.  And since the column is not indexed the query has to scan the table (7-8 million rows and >20Gb of data).  Even a simple select takes over a minute:

MariaDB [ispn]> select * from ispn_entry_FedoraRepository where version = 1;

Empty set (1 min 13.98 sec)

-D

On Thursday, June 30, 2016 at 10:52:58 AM UTC-4, Jim Coble wrote:

[David Chandek-Stark]

Following some research and chat on #infinispan, I was led to https://issues.jboss.org/browse/ISPN-6710.  Our testing of adding an index to version column of the ispn table has not surfaced any issues, so we are planning now to move it to production.  Hopefully, this will alleviate the lengthy lock wait times that were becoming a real blocker to further ingest.

--D

On Thursday, June 30, 2016 at 10:52:58 AM UTC-4, Jim Coble wrote:

[Andrew Woods]

Thanks for the investigation and solution, David.

It would be a great help to the community if you would be willing to update the following wiki page with notes on your MySQL (MariaDB) tuning configuration:

https://wiki.duraspace.org/display/FEDORA4x/Configuring+JDBC+Object+Store

Regards,

Andrew

--
You received this message because you are subscribed to the Google Groups "Fedora Tech" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fedora-tech...@googlegroups.com.
To post to this group, send email to fedor...@googlegroups.com.
Visit this group at https://groups.google.com/group/fedora-tech.
For more options, visit https://groups.google.com/d/optout.

[David Chandek-Stark]

Andrew,

I added a brief note to that page.  I'm still unclear whether the purge operation that executes the DELETE query is actually necessary in Fedora's case.  It would be helpful if someone could confirm that and explain why it's necessary.  My sense is that it's default behavior for the infinispan cache-container, but I'm not at all certain.

https://github.com/infinispan/infinispan/blob/7.2.4.Final/core/src/main/java/org/infinispan/expiration/impl/ExpirationManagerImpl.java

https://github.com/infinispan/infinispan/blob/7.2.4.Final/persistence/jdbc/src/main/java/org/infinispan/persistence/jdbc/stringbased/JdbcStringBasedStore.java#L268-L304

--D