Re: [tkined] SNMP to SQL (was: High volume SNMP poller)

From: Eddie Corns (
Date: Thu Feb 17 2000 - 18:31:36 MET

   Date: Thu, 17 Feb 2000 06:05:51 -0800
   From: " " <>
   Precedence: bulk

   Are you using Oratcl or some other means to provide your SQL interface? I
   am working on a project that sounds exactly like yours. What I ended up
   doing was taking the results of the mib walk tcl command, using puts to
   write it out to a flat file, then SQL*Loader it into the database. From
   that point, I use a PL/SQL procedure to compute deltas (most of my
   performance measures come in as COUNTER32s), time intervals (based on delta
   time the PDU was returned during successive polling intervals), then
   compute an average (delta/interval).

I'm using MySQL with a TCL interface but I ended up doing pretty much the same
technique. I don't use snmp walk because I have an explicit list of what to
poll but essentially I write out all the collected data to a file and use the
import feature of MySQL. I actually just store all the counters as is and do
all the work of extracting deltas, 32 bit wrap etc. using TCL, on retrieval.
This is actually quite slow too so I don't try and update the graphs (that
will eventually be part of it) more than once an hour. I also have to do the
daily, weekly, ... yearly collated data and graphs at midnight.

   I would be interested to see what approaches you've taken to scale this up
   to a large number of network elements and a large number of MIB variables.

I'd never really used SQL before starting this project and I certainly
learned a few things! Part of the point of the project was to assess the
viability of creating a real tool by glueing together well made components
(something I've often advocated but never actually done). The major limit
in this instance turned out to be the unexpected (to me) serial update problem
for SQL (as I believe it's technically known). The whole reason for starting
my own project rather than use something like MRTG was that I felt that you
shouldn't skimp on using a proper database (and also using the interface name
rather than a volatile MIB offset as an index). So I'm rather stuck on that
aspect. It did occur to me when I had the whole thing more or less up and
running to post a query to comp.databases or somesuch and get some unbiased,
professional opinions :) on the subject. (What would fit the bill would be
attaching SQL read-only (ie select) functionality to a simpler database).

Anyway, since I know it's practical to poll about 2,500 MIB counters as I
currently do, I intend to get it finished and in serious production before
addressing how it scales (ie how good my design was) (I envisage we might want
at least 10,000 counters if it's practical). Other than SQL performance, I
suppose one area would be to investigate the efficiency of the actual polling.
My first attempt was to have only one outstanding SNMP request per node and
firing the next one out as soon as the previous response was processed. Thus
doing all nodes in parallel, this seems to work quite well (early tests seemed
to indicate this was faster than snmp walks).

I'm moderately pleased with what I have so far for the control GUI. I used to
get so annoyed with HPOV requiring me to keep popping up seperate windows for
every single variable to be changed. So I tried to create an interface where
I could control the values of all related items in one go. Whether it will
turn out to be practical ...

   I've posted this to the list but if anyone feels it's off topic for scotty
   we can do it via private e-mail.

As I mentioned, the project is on hold for a short time anyway but other than
trying to draw others in ..., I guess private e-mail for the time being.

   Stuart Austin


!! This message is brought to you via the `tkined & scotty' mailing list.
!! Please do not reply to this message to unsubscribe. To subscribe or
!! unsubscribe, send a mail message to <>.
!! See for more information.

This archive was generated by hypermail 2b29 : Mon Jan 08 2001 - 15:27:36 MET