We have struggled with the same issue and took to restarting the data_engine six days a week to prevent the maintenance from running more than once a week.
The maintenance routines normally do the following:
- Compress data from the raw (RN) data tables to the historic (HN) data tables
- Purge old data from the raw data tables
- Purge old data from the historic data tables
The thresholds that determine which data is compressed and purged are set in the data_engine GUI.
What I find especially interesting about the historic data tables is that they are never used. The report_engine uses only the raw data, so there is currently no reason to allow compression to happen (as far as I know). There was little that could be done about this in the past, but with data_engine 7.02 you should be able to disable compression by setting compress_raw_samples to 0.
If you disable compression, the purging of historic data should go much faster as well. If you have data in the historic tables now, you can probably delete it unless someone else knows of a reason to keep it around.
Nimsoft has put quite a bit of work into the data_engine recently to address these issues. In addition to the option of disabling compression and purges in 7.02, data_engine 7.31 is supposed to have the ability to split up the maintenance into multiple jobs that run on different nights. I am not completely sure how this works yet; I have not had an opportunity to test it. I think those changes should help a lot, but they are also very big and will need to be tested thoroughly.
These database issues can be challenging, but make sure you carefully choose settings that are right for your environment. I hope some of this information is helpful; let me know if you have questions. Keep us posted on what you do to address this issue and how it works for you.
Good luck,
Keith