|
| | | Forum Newbie
Group: Forum Members
Last Login: 2/20/2007 2:42:00 PM
Posts: 7, Visits: 1 |
| | That worked perfectly. I hope this addresses the slowness we've been experiencing with the application. |
| |
| | | Forum Guru
Group: Forum Members
Last Login: 7/27/2006 9:37:00 AM
Posts: 51, Visits: 1 |
| I figured that "ActiveMonitorSampleData" contained the "Raw Data" that was getting purged every 1 day. But what is this about the "ActiveMonitorActivityLog" table? What goes in there?
I just noticed that my "ActiveMonitorActivityLog" is HUGE, and I'm not clear exactly what you guys have done to clean it up. Can someone simplify the explanation for me? 
Thanks!
Scott Moseman
|
| |
| | | Forum Newbie
Group: Forum Members
Last Login: 2/20/2007 2:42:00 PM
Posts: 7, Visits: 1 |
| Read NathanA's responses. He lists out step by step what you have to do to clean it out. After I cleaned out both areas, the system worked 100% better. Too bad this cleanup process can't be automated within the application. Later, Kevin |
| |
| | | Supreme Being
Group: Forum Members
Last Login: 9/28/2005 11:33:00 AM
Posts: 128, Visits: 1 |
| Anybody know how WUP uses the ActiveMonitorActivityLog? Is there a recommended minimum time that it needs to be around? The work that I have done on this points to the delete statement. It looks like the delete statement is taking forever to run. I am actually working with one of my DBAs to see if indexes could be added to speed things up, but his time has been taken up with monthly processing etc...so I haven't gotten too far with this. He thinks adding indexes would dramatically speed up the delete query. I played around with the indexing on that table and was able to speed things up a bit
For Example, here is the WUP query that cleans out the table:
DELETE from ActiveMonitorActivityLog
FROM (SELECT TOP 300
FROM ActiveMonitorActivityLog WHERE dPollTime < '2005-03-02 23:52:30' ) AS A WHERE A.nActiveMonitorActivityLogID = ActiveMonitorActivityLog.nActiveMonitorActivityLogID
This query used to take 7 1/2 minutes to run prior to adding the indexing. The same query now takes 1 /2 minutes. Still waaaay too long.
Running this query will give me the last log entry prior to 2005-03-02 23:52:30
SELECT max(nActiveMonitorActivityLogID)
FROM ActiveMonitorActivityLog WHERE dPollTime < '2005-03-02 23:52:30'
If I pass this value to a delete statement
DELETE FROM ActiveMonitorActivityLog
WHERE nActiveMonitorActivityLogID < 6261433
(where 6261433 is the record ID from the previous query)
This query takes seconds to run.
This leads me to believe that there is an indexing problem on the table. What do you all think?
If we update the indexing the problem should take care of itself and we won't have to bother with manually clearing out the table on a schedule.
|
| |
| | | Supreme Being
Group: Forum Members
Last Login: 9/28/2005 11:33:00 AM
Posts: 128, Visits: 1 |
| Looks like this table is for the performance charts.
http://www.ipswitch.com/forums/shwmessage.aspx?ForumID=14&MessageID=6000
Yup, this did it. The deletions are taking about 200ms on average. It needed a little help to get started using the queries I put above, but it looks like it is keeping up now. |
| |
| | | Forum Guru
Group: Forum Members
Last Login: 7/27/2006 9:37:00 AM
Posts: 51, Visits: 1 |
| Can entries in "ActiveMonitorActivityLog" be safely deleted? Is there any other tables that reference this one that might be affected? I'm trying to get IPSwitch to answer me via a technical support request to verify before I go about destroying anything in the database.
Thanks,
Scott Moseman
|
| |
| | | Supreme Being
Group: Forum Members
Last Login: 9/28/2005 11:33:00 AM
Posts: 128, Visits: 1 |
| There are some constraints with other tables, but it looks like the other tables coresponding records are removed quicker than this table, so you should be cool as long as you don't go buck wild deleting everything. I ended up turing off statistics gathering since I am mainly concerned with uptime. I misunderstood what it was doing since I thought the stats gathering would be the only thing that would give me reports at the end of the month. They are actually used for performance monitoring. Things will dramatically speed up as the table shrinks in size.
IPSwitch should really come up with better indexing on that table... |
| |
| | | Forum Guru
Group: Forum Members
Last Login: 7/27/2006 9:37:00 AM
Posts: 51, Visits: 1 |
| Something interesting about my data:
ActiveMonitorSampleData = 4M rows, IDs 941 to 11,200,000
ActiveMonitorActivityLog = 11M rows, IDs 1 to 11,200,000
(By the IDs, I mean nActiveMonitorActivityLogID entries.)
So obviously my SampleData table does not have ALL of the data that is found in the ActivityLog, but from the first/last entries, it appears that it has random correlated entries.
I have not heard from IPSwitch with my ticket on this, I'm going to have to call before this problem gets way out of hand. I wish we would have known about this problem before we cutover from WUG to WUP. 
Edit: I should probably not call it a "problem". I think I'm just not understanding the architecture/database scheme. I believe someone from IPSwitch is going to be calling me and hopefully explaining it. 
Thanks,
Scott Moseman
|
| |
|
|