Over the years, we have had many issues with Spectrum Report Manager (SRM). Some of the most frustrating issues have been around stability due to the massive amount of data that we process. What we have found is that shortly after we wipe the SRM systems out, they run well for a while, but then start experiencing issues again. After looking at our SRM issues, we decided to try to setup an automated process that would attempt to clean the MySQL tables that are the biggest problems that we have with stability. Especially the Events table. In the process, we created this simple little script. We have now been running this script for several months, and our SRM issues have disappeared. What we do is to schedule the script to run once a week.
Please note, there are several things to be aware of.
1 - While this has been working well in our environment, it may not in yours. Be careful. Maybe try it on a development system first. No guarantees are made. This script is provided to help others, but we will not maintain it.
2 - The disk space that is required to optimize the table is 1.5 times the table size for each table that is optimized. This is extremely important. Make sure you have enough space, especially for the event table.
I hope you find this helpful.