[rrd-users] Mail site_id and msg_type for dating sites? Can RRD do this

[Zack]

We run 10 dating sites, each site has 10 different email type messages that
our systems send.

Some are notices that another member has sent a message, others are warnings
that something might  happen. etc...

Since Sept,9 of last year I have been tracking all sent message in a Mysql
database with the following structure. Currently 20million rows

Field               Type                    Null        Default
q_id                bigint(20)             No
q_timestamp    timestamp(14)     Yes         NULL
q_siteid           tinyint(4)               No 0
q_mailtype      tinyint(4)               No 0

I now want to convert this into a rrd type database for graphing reasons.

I tried building an rrd database using the following

../bin/rrdtool create rrd_mailstats.rrd --start 1063409581 --step 300
DS:siteid_1_msgtype_1:GAUGE:600:0:U:U
DS:siteid_2_msgtype_1:GAUGE:600:0:U:U
DS:siteid_3_msgtype_1:GAUGE:600:0:U:U
DS:siteid_4_msgtype_1:GAUGE:600:0:U:U
DS:siteid_5_msgtype_1:GAUGE:600:0:U:U
DS:siteid_6_msgtype_1:GAUGE:600:0:U:U
DS:siteid_7_msgtype_1:GAUGE:600:0:U:U
DS:siteid_8_msgtype_1:GAUGE:600:0:U:U
DS:siteid_9_msgtype_1:GAUGE:600:0:U:U
DS:siteid_10_msgtype_1:GAUGE:600:0:U:U
DS:siteid_1_msgtype_2:GAUGE:600:0:U:U
DS:siteid_2_msgtype_2:GAUGE:600:0:U:U
DS:siteid_3_msgtype_2:GAUGE:600:0:U:U
DS:siteid_4_msgtype_2:GAUGE:600:0:U:U
DS:siteid_5_msgtype_2:GAUGE:600:0:U:U
DS:siteid_6_msgtype_2:GAUGE:600:0:U:U
DS:siteid_7_msgtype_2:GAUGE:600:0:U:U
DS:siteid_8_msgtype_2:GAUGE:600:0:U:U
DS:siteid_9_msgtype_2:GAUGE:600:0:U:U
DS:siteid_10_msgtype_2:GAUGE:600:0:U:U
DS:siteid_1_msgtype_3:GAUGE:600:0:U:U
DS:siteid_2_msgtype_3:GAUGE:600:0:U:U
DS:siteid_3_msgtype_3:GAUGE:600:0:U:U
DS:siteid_4_msgtype_3:GAUGE:600:0:U:U
DS:siteid_5_msgtype_3:GAUGE:600:0:U:U
DS:siteid_6_msgtype_3:GAUGE:600:0:U:U
DS:siteid_7_msgtype_3:GAUGE:600:0:U:U
DS:siteid_8_msgtype_3:GAUGE:600:0:U:U
DS:siteid_9_msgtype_3:GAUGE:600:0:U:U
DS:siteid_10_msgtype_3:GAUGE:600:0:U:U
DS:siteid_1_msgtype_4:GAUGE:600:0:U:U
DS:siteid_2_msgtype_4:GAUGE:600:0:U:U
DS:siteid_3_msgtype_4:GAUGE:600:0:U:U
DS:siteid_4_msgtype_4:GAUGE:600:0:U:U
DS:siteid_5_msgtype_4:GAUGE:600:0:U:U
DS:siteid_6_msgtype_4:GAUGE:600:0:U:U
DS:siteid_7_msgtype_4:GAUGE:600:0:U:U
DS:siteid_8_msgtype_4:GAUGE:600:0:U:U
DS:siteid_9_msgtype_4:GAUGE:600:0:U:U
DS:siteid_10_msgtype_4:GAUGE:600:0:U:U
DS:siteid_1_msgtype_5:GAUGE:600:0:U:U
DS:siteid_2_msgtype_5:GAUGE:600:0:U:U
DS:siteid_3_msgtype_5:GAUGE:600:0:U:U
DS:siteid_4_msgtype_5:GAUGE:600:0:U:U
DS:siteid_5_msgtype_5:GAUGE:600:0:U:U
DS:siteid_6_msgtype_5:GAUGE:600:0:U:U
DS:siteid_7_msgtype_5:GAUGE:600:0:U:U
DS:siteid_8_msgtype_5:GAUGE:600:0:U:U
DS:siteid_9_msgtype_5:GAUGE:600:0:U:U
DS:siteid_10_msgtype_5:GAUGE:600:0:U:U
RRA:LAST:0.5:1:1440
RRA:LAST:0.5:7:1440
RRA:LAST:0.5:35:1440
RRA:LAST:0.5:42:1440

I chopped the other 5 msgtypes out of the above for size reasons.

My question is how can I update this sort of rrd database using the above
Mysql data?

I ran awk to compile a text file that looks like this

../bin/rrdtool update rrd_mailstats.rrd 20031203160032:27:5
../bin/rrdtool update rrd_mailstats.rrd 20031203160032:29:5
../bin/rrdtool update rrd_mailstats.rrd 20031203181359:22:3
../bin/rrdtool update rrd_mailstats.rrd 20031203181358:24:1
../bin/rrdtool update rrd_mailstats.rrd 20031203181356:24:4
../bin/rrdtool update rrd_mailstats.rrd 20031203181355:24:4

Is there a better way of doing this?

I was hoping to have one graph with 10 lines for each site, for the 10
different email types we send.

Thank
zack



--
Unsubscribe mailto:rrd-users-request at list.ee.ethz.ch?subject=unsubscribe
Help        mailto:rrd-users-request at list.ee.ethz.ch?subject=help
Archive     http://www.ee.ethz.ch/~slist/rrd-users
WebAdmin    http://www.ee.ethz.ch/~slist/lsg2.cgi