On Wed, 4 Apr 2012 10:59:20 -0500, "Jay A. Kreibich" <[email protected]> wrote:
> Not to mention avg(). > > You might want to have a look at how RRDtool deals with condensing > data. It is common pratice to plot average, min, and max to preserve > outliers, while still showing trends. > > http://oss.oetiker.ch/rrdtool/ > > > -j I second this, just dump all your data into a well configured rrdtool round robin database. ################################ ## CONSTANTS ################################ # sample interval in seconds, 5 minutes INTERVAL=300 # max interval to miss (heartbeat) HEARTBEAT=900 ## Aggregate parameters # aggregate1 is 1 samples = 1 * 300 = 300 sec = 5 min @ 2016 rows = 168 hrs = 7 days = 1 week # aggregate2 is 12 samples = 12 * 300 = 3600 sec = 1 hr @ 1008 rows = 1008 hrs = 42 days = 6 weeks (5 + 1 ^^ ) # aggregate3 is 72 samples = 72 * 300 = 21600 sec = 6 hr @ 896 rows = 5376 hrs = 224 days = 32 weeks (26+5+1 ^^ ^^ ) # aggregate3 is 288 samples = 288 * 300 = 86400 sec = 24 hr @ 616 rows = 14784 hrs = 616 days = 88 weeks (56+26+5+1 round up^^ ^^ ^^) STEPS1=1 STEPS2=12 STEPS3=72 STEPS4=288 # nr of aggregared generations 42 days ROWS1=2016 ROWS2=1008 ROWS3=896 ROWS4=616 ################################ ### create and populate the round robin database "once" ################################ rrcreate () { test -f "${RRFN}" && rm "${RRFN}" rrdtool create "${RRFN}" \ --start $(printf ".separator :\nSELECT epstamp - 300 FROM rrdfrom;\n" | sqlite3 ${dbfn}) \ --step ${INTERVAL} \ DS:UpstreamBitRate:GAUGE:${HEARTBEAT}:0:4096000 \ DS:DnstreamBitRate:GAUGE:${HEARTBEAT}:0:32768000 \ DS:BytesSent:COUNTER:${HEARTBEAT}:0:500000000000 \ DS:BytesRecv:COUNTER:${HEARTBEAT}:0:1000000000000 \ DS:Connected:GAUGE:${HEARTBEAT}:0:3 \ RRA:AVERAGE:0.1:${STEPS1}:${ROWS1} \ RRA:MIN:0.1:${STEPS1}:${ROWS1} \ RRA:MAX:0.1:${STEPS1}:${ROWS1} \ RRA:AVERAGE:0.1:${STEPS2}:${ROWS2} \ RRA:MIN:0.1:${STEPS2}:${ROWS2} \ RRA:MAX:0.1:${STEPS2}:${ROWS2} \ RRA:AVERAGE:0.1:${STEPS3}:${ROWS3} \ RRA:MIN:0.1:${STEPS3}:${ROWS3} \ RRA:MAX:0.1:${STEPS3}:${ROWS3} \ RRA:AVERAGE:0.1:${STEPS4}:${ROWS4} \ RRA:MIN:0.1:${STEPS4}:${ROWS4} \ RRA:MAX:0.1:${STEPS4}:${ROWS4} ### POPULATE RRD, printf ".separator :\nSELECT * FROM rrdupdate;\n" \ | sqlite3 "${dbfn}" \ | gawk -v rr="${RRFN}" 'BEGIN{ # group 24 measurements into one update line for speed buf="" } function prbuf(){ printf "update %s%s\n",rr,buf buf="" } ( 0 == (NR % 24)){ prbuf() } { buf = buf " " $0 } END{ if (buf != "") prbuf() }' | rrdtool - } where rrdfrom is a view to define the start timestamp of the RRD: CREATE VIEW rrdfrom AS SELECT strftime('%s',MIN(dtstamp)) AS epstamp FROM T_Statistics AS ST ; rrdupdate is a view providing the data: CREATE VIEW rrdupdate AS SELECT strftime('%s',dtstamp) AS epstamp , Layer1UpstreamMaxBitRate , Layer1DownstreamMaxBitRate , TotalBytesSent , TotalBytesReceived , CASE CS.cs_val WHEN 'Connected' THEN 1 ELSE 0 END AS ConnectionStatus FROM T_Statistics AS ST INNER JOIN ConnectionStatuss AS CS ON ST.cs_id == CS.cs_id ORDER BY dtstamp; T_Statistics is a table: CREATE TABLE T_Statistics ( -- ST st_id INTEGER PRIMARY KEY NOT NULL , dtstamp REAL NOT NULL -- julianday(), UTC , rc INTEGER NOT NULL , cs_id INTEGER NOT NULL REFERENCES ConnectionStatuss (cs_id) ON UPDATE CASCADE ON DELETE CASCADE , le_id INTEGER NOT NULL REFERENCES LastConnectionErrors (le_id) ON UPDATE CASCADE ON DELETE CASCADE , uptime INTEGER NOT NULL -- seconds , wa_id INTEGER NOT NULL REFERENCES WANAccessTypes (wa_id) ON UPDATE CASCADE ON DELETE CASCADE , Layer1UpstreamMaxBitRate INTEGER NOT NULL , Layer1DownstreamMaxBitRate INTEGER NOT NULL , pl_id INTEGER NOT NULL REFERENCES PhysicalLinkStatuss (pl_id) ON UPDATE CASCADE ON DELETE CASCADE , ByteSendRate INTEGER NOT NULL , ByteReceiveRate INTEGER NOT NULL , PacketSendRate INTEGER NOT NULL , PacketReceiveRate INTEGER NOT NULL , TotalBytesSent INTEGER NOT NULL , TotalBytesReceived INTEGER NOT NULL , AutoDisconnectTime INTEGER NOT NULL , IdleDisconnectTime INTEGER NOT NULL , dn_id1 INTEGER NOT NULL REFERENCES DNSServers (dn_id) ON UPDATE CASCADE ON DELETE CASCADE , dn_id2 INTEGER NOT NULL REFERENCES DNSServers (dn_id) ON UPDATE CASCADE ON DELETE CASCADE , vd_id1 INTEGER NOT NULL REFERENCES VoipDNSServers (vd_id) ON UPDATE CASCADE ON DELETE CASCADE , vd_id2 INTEGER NOT NULL REFERENCES VoipDNSServers (vd_id) ON UPDATE CASCADE ON DELETE CASCADE , UpnpControlEnabled INTEGER NOT NULL , RoutedBridgedModeBoth INTEGER NOT NULL ); ConnectionStatuss is a related table: CREATE TABLE ConnectionStatuss ( -- CS cs_id INTEGER PRIMARY KEY NOT NULL , cs_val TEXT UNIQUE NOT NULL ); [[tested, yet not guaranteed :D ]] -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
