> I have a few questions that fit nicely in this thread:
> - What are the database schemas you are using? Are they derived from
> polled MIB tables or are they more generic?
Bearing in mind this is my first real attempt at an SQL database!!
The stored data goes like:
create table INTVAL (
intf_ref smallint not null,
oid_ref smallint not null,
start datetime not null,
count bigint not null,
primary key (intf_ref, start, oid_ref)
);
and exactly the same format for tables DAILY, WEEKLY, MONTHLY and YEARLY
(except I use date rather than datetime). INTVAL is for storing data polled
every 15 minutes.
count is the accumulated count for that interval, be it a year or 15 mins (I
lied about that previously, I must have been thinking of a similar but much
smaller program that I was working on more recently). One experimental
optimisation is to not store entries with a count of 0, though I haven't
thought about how to distinguish these from totally missed polls.
start represents the time at the start of the collection interval. This is a
potential weakness in that it may prove difficult to later allow some hosts to
be polled at different rates. OTOH I force the start time of all data
collected in the same run to be the same, so if it starts at 12:00 then all
data has 12:00 even if later ones actually occured at eg 12:01:45. This
allows me to easily compare different interfaces at a given time.
There is a table in the DB that dictates how far back entries should be kept
for (currently indexed by OID but more could be added) so I can say that the
15 min polls of InOctets are kept for up to 7 days, monthly ones for the last
13 months etc.
intf_ref is an index into:
create table interfaces (
intf_ref smallint not null,
host char(50) not null,
name char(50) not null,
primary key (intf_ref)
);
oid_ref is an index into:
create table oids (
oid_ref smallint not null,
name char(50) not null,
primary key (oid_ref)
);
oid.name is just ifInOctets etc. anything that scotty recognises
interfaces.host is just a dns name
interfaces.name is an interface name like Serial0 etc. so it does a walk on
the MIB to map interface names to indexes at the start of each run.
There is an (incomplete) GUI for configuring these.
There my be a potential problem with tying the host and interface name
together, I'm not quite sure if sometimes I will have difficulty extracting
the data in the right order (this is an implementation detail to do with the
fact that I can only sort on both fields if I join the interfaces table with
the relevant query which in some cases could be too expensive) but it does
seem 'natural' to represent an interface as a single unit.
There is a seperate table that holds the previous count for each measurement
point for each collection interval (daily, ...), this is for efficiency.
> - How to you represent the polling time and the "context" (e.g. SNMP
> agent address) in your database?
Most of this is answered above. I don't yet have fields for passwords
etc. but I assume I can add that if needed.
> - How compact is the data actually stored on disk (e.g. 100000 polls
> of variables xyz take m MByte of disk space)?
Compact it isn't, here's the current sizes of all DB files
[root@noodle poller]# ls -l /var/lib/mysql/traffic/
total 101616
-rw-rw---- 1 mysql mysql 25 Feb 18 12:16 CURR_TM.ISD
-rw-rw---- 1 mysql mysql 1024 Feb 18 12:16 CURR_TM.ISM
-rw-rw---- 1 mysql mysql 8698 Dec 1 19:38 CURR_TM.frm
-rw-rw---- 1 mysql mysql 2367552 Feb 18 00:03 DAILY.ISD
-rw-rw---- 1 mysql mysql 1905664 Feb 18 00:03 DAILY.ISM
-rw-rw---- 1 mysql mysql 8644 Dec 1 19:38 DAILY.frm
-rw-rw---- 1 mysql mysql 61320 Jan 5 18:09 DAILY_graph_info.ISD
-rw-rw---- 1 mysql mysql 88064 Jan 5 18:09 DAILY_graph_info.ISM
-rw-rw---- 1 mysql mysql 8616 Jan 5 11:40 DAILY_graph_info.frm
-rw-rw---- 1 mysql mysql 0 Dec 1 19:38 HOURLY.ISD
-rw-rw---- 1 mysql mysql 1024 Dec 1 19:38 HOURLY.ISM
-rw-rw---- 1 mysql mysql 8644 Dec 1 19:38 HOURLY.frm
-rw-rw---- 1 mysql mysql 50159172 Feb 18 12:16 INTVAL.ISD
-rw-rw---- 1 mysql mysql 44802048 Feb 18 12:16 INTVAL.ISM
-rw-rw---- 1 mysql mysql 8644 Dec 1 19:38 INTVAL.frm
-rw-rw---- 1 mysql mysql 61320 Jan 5 18:09 INTVAL_graph_info.ISD
-rw-rw---- 1 mysql mysql 86016 Jan 5 18:09 INTVAL_graph_info.ISM
-rw-rw---- 1 mysql mysql 8616 Jan 5 11:40 INTVAL_graph_info.frm
-rw-rw---- 1 mysql mysql 276 Dec 1 19:38 MAXES.ISD
-rw-rw---- 1 mysql mysql 1024 Dec 1 19:38 MAXES.ISM
-rw-rw---- 1 mysql mysql 8728 Dec 1 19:38 MAXES.frm
-rw-rw---- 1 mysql mysql 322496 Feb 18 00:03 MONTHLY.ISD
-rw-rw---- 1 mysql mysql 277504 Feb 18 00:03 MONTHLY.ISM
-rw-rw---- 1 mysql mysql 8644 Dec 1 19:38 MONTHLY.frm
-rw-rw---- 1 mysql mysql 61320 Jan 5 18:09 MONTHLY_graph_info.ISD
-rw-rw---- 1 mysql mysql 88064 Jan 5 18:09 MONTHLY_graph_info.ISM
-rw-rw---- 1 mysql mysql 8616 Jan 5 11:40 MONTHLY_graph_info.frm
-rw-rw---- 1 mysql mysql 547048 Feb 18 12:16 POLL_INFO.ISD
-rw-rw---- 1 mysql mysql 110592 Feb 18 12:16 POLL_INFO.ISM
-rw-rw---- 1 mysql mysql 8792 Dec 1 19:38 POLL_INFO.frm
-rw-rw---- 1 mysql mysql 0 Dec 1 19:38 POLL_TIME_INFO.ISD
-rw-rw---- 1 mysql mysql 1024 Dec 1 19:38 POLL_TIME_INFO.ISM
-rw-rw---- 1 mysql mysql 8562 Dec 1 19:38 POLL_TIME_INFO.frm
-rw-rw---- 1 mysql mysql 25380 Jan 31 14:40 SINGLE_graph_info.ISD
-rw-rw---- 1 mysql mysql 24576 Jan 31 14:40 SINGLE_graph_info.ISM
-rw-rw---- 1 mysql mysql 8642 Jan 20 17:42 SINGLE_graph_info.frm
-rw-rw---- 1 mysql mysql 712016 Feb 18 00:03 WEEKLY.ISD
-rw-rw---- 1 mysql mysql 576512 Feb 18 00:03 WEEKLY.ISM
-rw-rw---- 1 mysql mysql 8644 Dec 1 19:38 WEEKLY.frm
-rw-rw---- 1 mysql mysql 61320 Jan 5 18:09 WEEKLY_graph_info.ISD
-rw-rw---- 1 mysql mysql 87040 Jan 5 18:09 WEEKLY_graph_info.ISM
-rw-rw---- 1 mysql mysql 8616 Jan 5 11:40 WEEKLY_graph_info.frm
-rw-rw---- 1 mysql mysql 286976 Feb 18 00:03 YEARLY.ISD
-rw-rw---- 1 mysql mysql 258048 Feb 18 00:03 YEARLY.ISM
-rw-rw---- 1 mysql mysql 8644 Dec 1 19:38 YEARLY.frm
-rw-rw---- 1 mysql mysql 61320 Jan 5 18:09 YEARLY_graph_info.ISD
-rw-rw---- 1 mysql mysql 88064 Jan 5 18:09 YEARLY_graph_info.ISM
-rw-rw---- 1 mysql mysql 8616 Jan 5 11:40 YEARLY_graph_info.frm
-rw-rw---- 1 mysql mysql 0 Jan 6 15:10 _graph_info.ISD
-rw-rw---- 1 mysql mysql 1024 Jan 6 15:10 _graph_info.ISM
-rw-rw---- 1 mysql mysql 8616 Jan 6 15:10 _graph_info.frm
-rw-rw---- 1 mysql mysql 63 Jan 26 17:50 graph_info.ISD
-rw-rw---- 1 mysql mysql 1024 Jan 26 17:50 graph_info.ISM
-rw-rw---- 1 mysql mysql 8554 Jan 6 15:06 graph_info.frm
-rw-rw---- 1 mysql mysql 205 Jan 6 15:06 graph_type_info.ISD
-rw-rw---- 1 mysql mysql 1024 Jan 6 15:06 graph_type_info.ISM
-rw-rw---- 1 mysql mysql 8584 Jan 6 15:05 graph_type_info.frm
-rw-rw---- 1 mysql mysql 126845 Feb 1 12:24 html_info.ISD
-rw-rw---- 1 mysql mysql 1024 Feb 1 12:24 html_info.ISM
-rw-rw---- 1 mysql mysql 8700 Feb 1 12:19 html_info.frm
-rw-rw---- 1 mysql mysql 230926 Dec 1 19:38 interfaces.ISD
-rw-rw---- 1 mysql mysql 21504 Dec 1 19:38 interfaces.ISM
-rw-rw---- 1 mysql mysql 8610 Dec 1 19:38 interfaces.frm
-rw-rw---- 1 mysql mysql 212 Dec 1 19:38 oids.ISD
-rw-rw---- 1 mysql mysql 2048 Dec 1 19:38 oids.ISM
-rw-rw---- 1 mysql mysql 8584 Dec 1 19:38 oids.frm
Approx 100M
This is keeping 15 minute polls for the last 7 days. I didn't really want
that level of resolution, 1 hour would be adeqaute but doing more than one
type of poll less than a day is awkward with the current setup.
I just checked and this is 2242 interfaces, each with 4 OIDs collected thus
8968 measurement points (long term goal is to consider about 10 OIDs).
> - How do you make the polling engine reliable, e.g. do you fire up a
> new poller every once in a while or do you rely on the poller being
> reliable and able to actually handle all error situations?
I use cron to start the poller. There's little in the way of robustness
checking yet, that all comes at the end!
Any comments are welcome of course, no matter how severe. If anything is
unclear don't hesitate to ask, I know my communication skills are not the best
(but better than they used to be)
I've made a half hearted attempt to start documenting it, I will try and step
this up if I can find the energy but what I really want to get going next is
the graph generation to web page so that my manager can see I'm actually doing
something :). Again this turned out to be a lot more work than just piping
data through gnuplot. There are many special cases to consider etc. It may
seem over the top to use gnuplot to create such simple graphs, it is, but
practically all the work is in dealing with the date and time, perhaps later I
can improve this if I find some decent generic time handling code.
There are issues I haven't put much thought to yet such as storing things
other than counters, storing averages, rates, peaks etc as well as counts.
I know such things could be added but what the penalty might be...
That's enough for now.
Eddie
-- !! 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 <tkined-request@ibr.cs.tu-bs.de>. !! See http://wwwsnmp.cs.utwente.nl/~schoenw/scotty/ for more information.
This archive was generated by hypermail 2b29 : Mon Jan 08 2001 - 15:27:36 MET