Database performance slows

[zeiglerm]

I have a web application developed using ModeShape 4.4.0 and Infinispan 7.2.3.  My application loads documents into the ModeShape repository and stores them in a MySQL database. I am running ModeShape within a TomEE application server.

I am running a test to see how many documents I can load into the repository and how much performance slows and it gets more and more data loaded into the index. I am loading documents in a single thread and started out loaded 12,000 per hour.  After a couple of hours, things slowed down drastically.  I am currently at around 70,000 documents loaded and only getting approximately 200 per hour. I have noticed that when my database server seems to have used all the memory available to it on the server that performance really takes the hit. I am currently running on a database server with 32GB of memory.

I am looking for help determining why I am seeing such a slow down.  Is there something I have configured incorrectly?

[hchiorean]

If you're planning to use ModeShape 4, please try to use the latest 4 version - 4.6.0.Final (also note that the latest community version is 5.1.0 as we've stopped using Infinispan). As far as performance, the first thing to do is profile is investigate where the problem lies (70k documents it not much, I would not expect performance degradation with those numbers).

I am looking for help determining why I am seeing such a slow down.  Is there something I have configured incorrectly?

You haven't provided your configuration, so I can say if there's anything off or not.

[zeiglerm]

We are currently in a production environment.  If we were starting out I would definitely start with the latest version.

I attached the configuration I am using to the original post.  Please let me know if there is anything that stands out that would slow my database performance down.  In the mean time I will begin profiling my application.

[zeiglerm]

What is troubling me is that performance seems to be fine until the database starts to run out of available memory.  It keeps claiming memory on the server as long as it is available.  Once it has exhausted or claimed most of the free memory on the machine (32GB), then database then seems to slow down drastically and start to have problems.  I see statements in the app server logs that infinispan timed out waiting for a database lock.  I expected the database to evict rows out of its in memory cache and just replace it with new data as it needs to.  When I get to this point is when the slowdown really seems to begin.

[hchiorean]

What is troubling me is that performance seems to be fine until the database starts to run out of available memory.

it sounds like the problem is with your DB machine then, not ModeShape/ISPN.

From the configuration files, nothing seems to me misconfigured performance wise. One problem that does stand out however, is that your ISPN cache is configured to use OPTIMISTIC locking. While this will prevent ModeShape from working, it can very well cause data corruption when doing concurrent updates. You should use PESSIMISTIC locking instead, as described in the ModeShape documentation: Configuration - ModeShape 4 - Project Documentation Editor