ID fields in cacti database

bulek · Post by **bulek** » Fri Oct 18, 2002 5:17 am

I propose to use larger numbers for ID fileds in cacti database. All of them are defined as "smallint". In case of auto-increment columns one can overflow such field because it is growing fast when you make a lot of changes to the table.

I encountered such problem with rrd_graph_item table. Cacti stucked at the value 32767 and I was not able to add more graphs. I had to change some fields to "int".

Maybe it is not so important for smaller environments but we want to use cacti in larger ones as well... right?

BTW, I have at the moment (still growing): 13300 DS-es, 2800 graphs (with almost 30000 graph items). I am polling routers SNMP stats only.

- bulek

sayasif · Post by **sayasif** » Fri Oct 18, 2002 6:49 am

hey bulek,

that is a huge setup ! how much time does it take for you to gather data for so many datasources? are you using parallel setup of cacti?

thanx for the info

ACE

bulek · Post by **bulek** » Fri Oct 18, 2002 8:53 am

I have large network to monitor and my goal is to cover at least 200 of them with cacti (maximum 1500). Every router can have 20 - 200 interfaces. The parameters I am looking for are cpu, memory, temperature, in/out traffic, errors, QoS statistics per class, latency per link and some others.

I started with "normal" cacti and quite fast I saturated the server. The bottleneck was of course data gathering via SNMP. I decided to write small pollers in PHP that I can put outside of main cacti server. The poller makes only two things: gathers SNMP data from assigned routers (snmpbulkwalk) and puts the data with into additional table on cacti server. From the other side cacti has Data Input defined that can read the stats from local SQL table. This is much faster than fetching the data via SNMP from routers. This way I have distributed environment with pollers in various parts of my network and central cacti server.

All of this required then some (or huge) modification of cacti GUI - I can't imagine displaying all of my DS-es on one page.

Still have some performance problems (this time with SQL server - it has about 140 queries/sec and eats 30% of my 4-way server) but I try to fight them one by one. I can't tell how much time does it take but I am still below 5 min (probably 2 - 3 minutes).

- bulek

Post by **raX** » Fri Oct 18, 2002 9:40 am

Understandable. Every time you think something is big enough for the long term, it always runs out/overflows a lot sooner (ie. IPv4

). Anyhow, I started using mediumint(8)'s in 0.8, but I am starting to wonder if int(12) would not be better.

To tell you the truth I am surprised you got to 32767 so quick, but don't doubt it for one moment.

-Ian

yid · Post by **yid** » Fri Oct 18, 2002 10:01 am

You might try having your remote pollers write directly to the RRDs, instead of through the MySql DB. Just keep the SQL DB for configuration, and as a graphing frontend/configuration tool. That would get rid of your SQL bottleneck.
A colleage and I have recently finished a perl-base remote rrd tool (creates, updates, and checks to make sure its writing to an RRD), called RRRDD. The client is a perl module (though it wouldn't be hard to create a php client). You could also mod the perl SNMP collector to write using RRRDD instead of RRDs; that's what I plan on doing. RRRDD includes support for encryption of updates, and client side (i.e. your pollers) queueing of data between updates.
Here's the RRRDD page:
http://www.birch.net/~trance/ <-- sorry fixed the url

Aaron

kmpanilla · Post by **kmpanilla** » Fri Oct 18, 2002 10:47 am

I'm interesting in looking at your RRRDD, but the URL produces a 404.

yid · Post by **yid** » Sat Oct 19, 2002 7:26 pm

For those non-cacti standard SNMP OIDs, particularly errors, and QoS, are you adding those individually as SNMP Data, or have you modded cacti to support additional SNMP tables.

I ask because, I'm planning on supporting a lot of enterprise OIDs for vendor specific info (for firewalls, routers, DSLAMs, UPSs, Sun boxes, etc.) and was about to add an SNMP_Table table, and link those walkable OIDs to SNMP_Hosts. I was wondering if you've done anything like this yet, or have any suggestions before I get started.

Aaron

bulek · Post by **bulek** » Sun Oct 20, 2002 6:24 am

Aaron,

Thanks for posting information about RRRDD. I will definetly look into this. I think my SQL based solution would work for 200 routers at max (about 90000 DS-es). Maybe with your tool I will be able to jump above this limit.

Actually RRRDD makes me thing of something else. Right now what I have is central reporting (cacti) with remote pollers. With your remote RRD update I can implement distributed reporting as well (means for example: 1 central cacti for configuration, 4 reporting servers, 100 pollers). Just an idea.

One problem I have is I can go to your URL however I can't download the tool - seems to be not placed on the server.

Regarding your question - I just map chosen SNMP tables to SQL tables (with some conversions). Some examples:

Code: Select all

mysql> describe stats_device;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| HostID      | smallint(6) |      | PRI | 0       |       |
| CPU_5s      | smallint(6) | YES  |     | NULL    |       |
| CPU_1m      | smallint(6) | YES  |     | NULL    |       |
| CPU_5m      | smallint(6) | YES  |     | NULL    |       |
| MemUsed     | int(11)     | YES  |     | NULL    |       |
| MemFree     | int(11)     | YES  |     | NULL    |       |
| TempInlet   | smallint(6) | YES  |     | 0       |       |
| TempOutlet1 | smallint(6) | YES  |     | 0       |       |
| TempOutlet2 | smallint(6) | YES  |     | 0       |       |
| TempOutlet3 | smallint(6) | YES  |     | 0       |       |
| Volt_3      | smallint(6) | YES  |     | 0       |       |
| Volt_5      | smallint(6) | YES  |     | 0       |       |
| Volt_11     | smallint(6) | YES  |     | 0       |       |
| Volt_12     | smallint(6) | YES  |     | 0       |       |
| Timestamp   | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
15 rows in set (0.23 sec)

mysql> describe stats_ifc;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| IfcID                | smallint(6)      |      | PRI | 0       |       |
| OperStatus           | smallint(6)      | YES  |     | NULL    |       |
| InOctets             | int(10) unsigned | YES  |     | NULL    |       |
| OutOctets            | int(10) unsigned | YES  |     | NULL    |       |
| InUcastPkts          | int(10) unsigned | YES  |     | NULL    |       |
| OutUcastPkts         | int(10) unsigned | YES  |     | NULL    |       |
| InNUcastPkts         | int(10) unsigned | YES  |     | NULL    |       |
| OutNUcastPkts        | int(10) unsigned | YES  |     | NULL    |       |
| InErrors             | int(10) unsigned | YES  |     | NULL    |       |
| OutErrors            | int(10) unsigned | YES  |     | NULL    |       |
| InDiscards           | int(10) unsigned | YES  |     | NULL    |       |
| OutDiscards          | int(10) unsigned | YES  |     | NULL    |       |
| IfSlowOutPkts        | int(10) unsigned | YES  |     | NULL    |       |
| IfFastOutPkts        | int(10) unsigned | YES  |     | NULL    |       |
| IfCollisions         | int(10) unsigned | YES  |     | NULL    |       |
| CircuitDEins         | int(10) unsigned | YES  |     | NULL    |       |
| CircuitReceivedFECNs | int(10) unsigned | YES  |     | NULL    |       |
| CircuitReceivedBECNs | int(10) unsigned | YES  |     | NULL    |       |
| Timestamp            | datetime         | YES  |     | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
19 rows in set (0.23 sec)

mysql> describe rtt_ping;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| InterfaceID | smallint(6) |      | PRI | 0       |       |
| RTTMin      | smallint(6) | YES  |     | 0       |       |
| RTTAvg      | smallint(6) | YES  |     | 0       |       |
| RTTMax      | smallint(6) | YES  |     | 0       |       |
| Loss        | float       | YES  |     | 100     |       |
| Timestamp   | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.24 sec)

mysql> describe qos;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| IfID             | smallint(6)      |      | PRI | 0       |       |
| Queue            | varchar(200)     |      | PRI |         |       |
| PrePolicyPkts    | int(10) unsigned | YES  |     | 0       |       |
| PrePolicyBytes   | int(10) unsigned | YES  |     | 0       |       |
| PostPolicyBytes  | int(10) unsigned | YES  |     | 0       |       |
| DropPkts         | int(10) unsigned | YES  |     | 0       |       |
| DropBytes        | int(10) unsigned | YES  |     | 0       |       |
| QueueDepth       | int(10) unsigned | YES  |     | 0       |       |
| QueueDiscardPkts | int(10) unsigned | YES  |     | 0       |       |
| Timestamp        | datetime         | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+
10 rows in set (0.23 sec)

A poller reads SNMP tables (or runs ping processes on router in case of RTT poller) and then writes gathered stats to SQL table. Then I have four Data Input scripts that read the data from mentioned fout SQL tables and return a row of all read values. Result are four types of multi-output DS-es. Data Input input parameters are based on hostname, interface description and classmap name (in case of QoS poller). This allows for being not dependend on SNMP index changes.

- bulek

yid · Post by **yid** » Sun Oct 20, 2002 10:49 am

You should be able to download that tar.gz now. Permissions...

If the RRD updates become a bottleneck, you could even distribute that out, and just access the RRDs on the cacti server via NFS. I guess you could even just keep the RRDs on each server if you're going to do that, but then the poller needs its own diskspace. As far as reporting, are you exporting graphs? If so, do you use the cmd.php for that?

Your config was exactly what I was looking at. I think I'll add a table to link SNMP_hosts to their SNMP_field, and add a column to the SNMP_hosts to their pollers, that way the pollers know which hosts are theirs automatically.

Also, I think having an RRD per datasource excessive on diskspace overhead. I'm think I'll create each SNMP_host as a parent DS to the child DSs (this'll make it easier to have different RRAs for different hosts...). The problem is adding datasources when you "refresh interfaces". However one of the other frontends already has a script that dumps the RRD to XML, adds the DS, and reimports. I can add the ability to remove DS's also, and call that from cacti in those instances.

let me know what you think.

I hope I have time to get started on all of this

, its starting to get fun!

Aaron

bulek · Post by **bulek** » Tue Oct 22, 2002 4:32 pm

If the RRD updates become a bottleneck, you could even distribute that out, and just access the RRDs on the cacti server via NFS. I guess you could even just keep the RRDs on each server if you're going to do that, but then the poller needs its own diskspace.

After some investigation I decided to stay with my SQL based solution for now. It turned out that the server had some orphan processes saturating mysqld server. After restart I have 3% of CPU in average with over 17000 DS-es

. This means I can start adding another pollers.

As far as reporting, are you exporting graphs? If so, do you use the cmd.php for that?

No I do not export graphs - too much overhead I guess. Talking about distributed reporting I thought about central cacti server with all configuration inside and some cacti slaves. In this case cacti would need additional field near every DS and graph informing on which remote server it is updated by slave cact. Just an idea.

Your config was exactly what I was looking at. I think I'll add a table to link SNMP_hosts to their SNMP_field, and add a column to the SNMP_hosts to their pollers, that way the pollers know which hosts are theirs automatically.

This is very important feature. I can switch given router to a different poller within a few seconds in case of overload. You can even write a simple script checking if a poller is operational - if not you can hand over the devices monitored by broken poller to a good one. Kind of redundancy right?

Also, I think having an RRD per datasource excessive on diskspace overhead. I'm think I'll create each SNMP_host as a parent DS to the child DSs (this'll make it easier to have different RRAs for different hosts...).

I agree with you regarding minimizing number of rrd files. I use one file per interface, host, latency link and QoS element with all needed parameters inside.

The problem is adding datasources when you "refresh interfaces".

I don't have problems with refreshing interfaces. All my Data Inputs use hostname and interface descriptions. After refreshing the interfaces I don't care if SNMP index has been changed. In case new interfaces in configuration I just add DS and graphs for them. Actually I automatically can create all DS-es, graphs and even put them into right hierarchy with one mouse click. This saves a lot of work. Still there is a lot of manual work in case of deleting or updating.

- bulek

yid · Post by **yid** » Tue Oct 22, 2002 7:28 pm

This is very important feature. I can switch given router to a different poller within a few seconds in case of overload. You can even write a simple script checking if a poller is operational - if not you can hand over the devices monitored by broken poller to a good one. Kind of redundancy right?

You could even setup an event handler in Netsaint to do this automagically

I don't have problems with refreshing interfaces. All my Data Inputs use hostname and interface descriptions.

The issue would be if you wanted to keep 1 RRD per Host. Adding / Removing DS's will mean dumping to XML adding the DS, and switching back.

The idea I'm looking at now, is how to make new SNMP sources easy to add. Standard MIB-II interfaces are pretty straight forward, but I'd like to make it easy to add any OID table.

yid · Post by **yid** » Sun Oct 27, 2002 9:45 am

I know this discussion has gone far off the original topic, but whatever...

I was just excited, because I've figured out a great way to add new OIDs to cacti's SNMP collector. Here's my idea.

I create a table for OIDs:

Code: Select all

+-------------+-------------+------+-----+---------+-------+ 
| Field       | Type        | Null | Key | Default | Extra | 
+-------------+-------------+------+-----+---------+-------+ 
| ID          | smallint(5) | NO   | PRI | 0       |autoinc|
| OID         | varchar(50) | NO   |     |         |       | 
| Name        | varchar(50) | NO   |     |         |       | 
| Type        | enum(single,| NO   |     |         |       | 
|             | table, alt) |      |     |         |       |
| ParentOID   | smallint(5) | YES  |     |  NULL   |       | 
+-------------+-------------+------+-----+---------+-------+

Singles are for OIDs that are 1 per host. Tables are stuff that are multiple per host. Alt is for OIDs that identify other interfaces (IP addr, MAC, AdminStatus, etc.) These will link to the SNMP hosts

then I make the snmp_host_interface table more generic:

Code: Select all

+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| ID              | smallint(5) |      | PRI | NULL    | auto_increment |
| Name            | char(16)    | YES  |     | NULL    |                |
| InterfaceNumber | bigint(12)  |      |     | 0       |                |
| Data            | int(10)     | YES  |     | NULL    |                |
| MaxValue        | int(10)     | YES  |     | NULL    |                |
| Alt 1           | varchar(50) | YES  |     | NULL    |                |
| Alt 2           | varchar(50) | YES  |     | NULL    |                |
| Alt 3           | varchar(50) | YES  |     | NULL    |                |
| Alt 4           | varchar(50) | YES  |     | NULL    |                |
| Alt 5           | varchar(50) | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+

This will make it possible for someone with just basic knowlege about the MIB, or OIDs involved to begin monitoring quickly. With up to 5 identifiers.

The "refresh interfaces" will just snmp_get (to make sure its available) the "single" OIDs (that're linked to the host), and insert it in the MySQL DB. For table OIDs it'll walk the "table" OIDs to get interfaces, store the interfacenumber and name (name will be the <oidname>_<interfacenumber>) in the MySQL DB, then for each interface in the DB, get the "alt" OIDs, and store in the appropriate row.

Actual collection will just do a get for each OID in snmp_host_interface, store the resulting data in the RRD under using the snmp_host_interface.name as the datasource. 1 RRD per host.

What do you think,
Aaron

bulek · Post by **bulek** » Fri Nov 01, 2002 4:29 pm

I made similar extensions to cacti predefined tables. They are not so flexible as yours... I just added some fields to snmp_hosts_interfaces (NextHop, ifAlias, etc.).

Your solution is nice idea. I can see only one drawback - it is not snmpwalk (or better snmpbulkwalk) friendly. In larger environments single snmpgets may become a bottleneck. I use snmpbulkwalk to fetch interface related stats and I don't care that I read more stats that I actually require. The speed gain is the most important benefit here.

- bulek

ID fields in cacti database

ID fields in cacti database

Who is online