Hello list, I'm trying to cook up an improved datamodel to store our bandwidth statics.
Requirements/wishes: * Long-term (at least a year) storage of detailed (5 minute) samples * "Fast"/efficient storage/lookups of the data * As small as possible disk footprint (disks are cheap, but I don't want to waste any more than needed) I know these 3 don't mix up very well. But I'm looking for a decent compromise. Currently I use one database with one table for each /24 per month. So the tables are named subnet_octet1_octet2_octet3__date_year_month (eg: subnet_217_148_171__date_10_03). Each table than has the following fields: timestamp, direction, ip_0, ip_1, ip_2, ..., ip_255. timestamp: when was sample collected direction: in or out ip_X: number of kbytes sent or recieved This works fairly well for our current handful of /24s. However, I need to "expand" the datamodel to support IPv6 as well. Using a similar setup for IPv6's /64s doesn't work very well obviously. The most obvious (in my eyes, atleast) next step would be using the same standard for for table names (one per month for each IPv4 /24 or IPv6 /64) with a different layout: timestamp, node, bytes_in, bytes_out, packets_in, packets_out timestamp: when was sample collected node: remaining 8 or 64 bits of the ip address (as integer) bytes/packets_in/out: speak for themselves Even taking in account the extra information being stored, it has a much larger disk footprint for both the data and indices. But I guess that's a sacrifice I'll have to make. However, considering I'm far from a DBA, any hints/tips/tricks are most welcome. -- Regards, Ruben Laban Senior Systems and Network Administrator ISM eCompany _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
