4 Replies Latest reply on Oct 21, 2008 7:55 PM by savell

    Database tables - Traps vs TrapVarbinds

    savell

      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.

        • Re: Database tables - Traps vs TrapVarbinds
          tdanner

          It is normal for TrapVarbinds to have several times more rows than Traps. For each SNMP trap we receive, the Traps table gets one row and TrapVarbinds gets one row for each OID in the trap. A 10:1 ratio sounds a bit high, but it's not outside the possible range.

          What version of Orion are you running?

            • Re: Database tables - Traps vs TrapVarbinds
              savell

              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.

                • Re: Database tables - Traps vs TrapVarbinds
                  jtimes

                   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...)

                    • Re: Database tables - Traps vs TrapVarbinds
                      savell

                      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.