Database tables - Traps vs TrapVarbinds

Question

In our Orion database, the TrapVarbinds table is very large (in the order of 10x the number of rows than that contained in the Traps table).

I would have thought that the two tables should have contained approx the same number of rows - given the TrapVarbinds table is referenced via the TrapID field in the Traps table.

Is it possible that we had an issue with the database maintenance at some stage and the TrapVarbinds table is not being cleaned up correctly?

Can anyone else confirm the row counts are similar in your implementation - or is what I see just normal?

Dave.

savell · Answer

Thanks for that John!

There appears to no problem in our tables (i.e. no orphaned TrapID's). However I did notice that we have some very old entries in the Traps (dating back 12 months). Given our retention is set to 5 days it would appear that the database maintenance isn't cleaning up the tables. I have manually removed the expired entries and will keep an eye on it.

Dave.

Edit: It was a useful exercise looking at these tables. Using some basic counts I determined that over half our trap activity was coming from just three devices (and each trap writing a large number of OID data into the Trapvarbinds table. These traps have now been suppressed at the source as they were unnecessary.

jtimes · Answer

I haven't seen this condition since going directly to 9.0 SP2 from 8.1...  My current TrapVarBinds table is only 4Mbytes, and doesn't change in size.

Count query I used before 9.0 SP2:

SELECT COUNT(*)
FROM TrapVarBinds
WHERE NOT EXISTS (SELECT TrapID FROM Traps WHERE TrapVarBinds.TrapID=Traps.TrapID)

Orphan TrapVarBinds cleanup query:

delete from TrapVarbinds
FROM TrapVarBinds
WHERE NOT EXISTS (SELECT TrapID FROM Traps WHERE TrapVarBinds.TrapID=Traps.TrapID)

(*** Insert standard disclaimer regarding DB maint while the polling engines are up...)

savell · Answer

We are running the latest 9.1 release.

I might run a query to tomorrow just to check that there are no TrapID's in the TrapVarbinds table that don't also exist in the Traps table (this should this be the case given the way you described the tables above).

If this is a problem, it existed prior to verion 9 I think. I only started looking at the tables when I noticed the database growing above what I thought we had stabilised at some time ago. The TrapVarbinds table seemed like something to look at given we now have over 10 million rows...

Thanks for the response Tim.

Dave.