I have an ERP-CRM Java software that I developed a few years ago. Currently, approximately 60 of my clients are using it, with an average of 100 employees each using the system themselves. I had each of them buy a decent server in the past.
Initially, there were no issues; there was little data and not much processing was being done. However, now each one has around 10-15 GB of MySQL data on average.
My problem is that some employees frequently need to generate reports, and whenever they do, the system locks up. MySQL CPU usage spikes to 100%, and this continues until the report generation is completed.During this time, other employees become unable to perform any tasks. The system becomes unresponsive.
I am using Java and Hibernate 3 versions. MySQL configurations and indexing have been done on all servers, but I still can’t solve the problem.
How can I overcome this issue? Can someone knowledgeable in performance help me?

ImageHere

I’ve tried configuring MySQL and Hibernate, but I couldn’t find a solution.