Hi, I'm interested in other people's experiences with regularly purging excess data in the SAMStore.
We currently keep 90 days worth of data and each day look to purge one day's worth - we do this with the following soitoolbox commands, run locally on the database server with SOI still running:
soitoolbox.exe -x -v -b 300 -t 86400 --purgeClearedAlerts 90soitoolbox.exe -x -v -b 300 -t 86400 --cleanHistoryData 90soitoolbox.exe -x -v -b 300 -t 1200 --rebuildIndexes
However it is taking approximately 6 hours to purge 20,000 alerts - these are timings taken from my development environment and in Production we get roughly 10,000 alerts per day, so I am assuming it would take around 3 hours.
The server isn't CPU or Memory bound during execution and I was wondering whether other people were experiencing these sorts of times.
We have a similar set of maintenance scripts running, but with slightly different figures:
Besides the above we also have
In our situation purgeClearedAlerts takes around 220 secs to clear approx. 50,000 alerts every two days
cleanHistoryData takes 5 to 15 secs every week
rebuildIndexes takes max. 130 secs every
purgeDBInconsistencies takes 500 to 1500 secs every week
We seem to have the same version of SOI running. Planning to upgrade on very short notice.
Hope this helps.
Thanks very much for your response Vincent and these figures show your purges to be considerably quicker than ours! - I have one further question, if you wouldn't mind; Are your purges performed whilst SOI is still running? As I know they will be a lot quicker if SOI is stopped. I am running my soitoolbox purges locally on the database server, so that it doesn't have to go over the network.
I have reached out to our DBAs for advice and they have suggested the following changes:
1. Decrease the max degree of parallelism from 3 to 2, that means every DB process will use 2 CPUs instead of 3 (current configuration), to try to get a better performance on CPU utilization.
2. Create 3 indexes on the below tables with recommended indexes, as these have been identified as impacting the performance:
I will supply updates on my findings.
The above changes made no difference to performance whatsoever :-(
I've opened a Support case with CA Broadcom to see if they can help.
All of the above soitoolbox commands run whilst SOI is still running, without any issues.
CA Support have been working with us and have produced a revised soitoolbox command that targets specific database tables and also allows a "batchsize" to be defined which will speed up the online processing - this has dramatically improved our timings and I'm told will be included in future releases.