MySQL type MAService
From GEANT2-JRA1 Wiki
PerfSONAR MA using MySQL instead of RRD (adaptation guide)
Contents |
Preface
This adaptation guide refers to the Python version of the perfSONAR MA. With this article you should download our mySQL message handler in an external file (srce_mysql_ma_message_handler.py) . You can use this file as a template for your own message handler implementation.
Introduction
PerfSONAR MA is using RRD as a back-end tool for storing and retrieving interface traffic data. This is generally a good idea but many NRENs already monitor and store traffic on their own devices using MySQL or any other database server. Implementing and installing the prefSONAR MA is fairly simple but adjusting and modifying all the monitoring software and tools involving interface traffic can be pretty time consuming, expensive and sometimes just not an option. There are few solutions to this problem. One is to produce the RRD archives for perfSONAR MA use only, based on already collected and stored data. This way we'll have redundant data and an additional work trying to force our tools and software to write both in our own and in the RRD database. The other option is to force MA to fetch data from our database on the fly. This is by far the easiest and the simplest solution and as such probably the best. What we need is a way to extend the perfSONAR MA so that it can use SQL database server. This upgrade must be done with as little code mangling as possible and in such a manner that it will not interfere with the future version upgrades.
This article will provide you with a set of guidelines on how to implement such a solution using our working MA as an example.
The theory
When you generate an xml request using your perfSONAR client and use it to get the data from MA this is what generally happens: MA receives your request and hands the request to the message handler, the message handler interprets the request finds the interface that you need the data for in the RRD configuration file and then use the rrdtool to fetch the actual data from the RRD archive. The data is then formated and nicely put in a xml response that you receive with your perfSONAR client.
In theory all we need to do is to create our own message handler, capable of getting data form MySQL database instead of RRD.
The practical implementation
First we have to copy srce_mysql_ma_message_handler.py to /perfSONAR-MA/service/SONARpyMA/message/service/ma/ folder, then we have to modify pipeline.py so that it can use our message handler. Second you have to modify our message handler so that it can suit your database specification. And in the end we have to configure or better yet create our MA database configuration file. Our configuration file will be the same as standard RRD except for the file part, where we put the name of the database table instead of file name of RRD database.
Making a new message handler
In order to build your own MA message handler you need to extend the RRDMAMessageHandler class and override the setupDataRequest method. Furhter more you need to extend RRDAccess class and override the fetch method to. The main reason why we need to override the setupDataRequest method is because in this method we actually create the RRDAccess object and invoke the fetch method in order to get data from RRD archive.
Key points in the code: perfSONAR-MA/service/SONARpyMA/message/services/ma/srce_mysql_ma_message_handler.py
class MySQLMAMessageHandler(rrd_ma_message_handler.RRDMAMessageHandler):
...
def setupDataRequest(self, inputObject):
...
# access data via MySQL (this is the only change)
rrdAccess = RRDAccessViaMysql()
...
...
class RRDAccessViaMysql(rrd_ma_message_handler.RRDAccess):
...
def fetch(self, file, ds, cf, startTime, endTime, resolution):
THIS IS WHERE THE FUN STARTS
...
The fetch method is the place where we actually handle the request for the RRD, MySQL or any other database data. This is the point where every NREN has to make it's own modifications based on their database design and database table schema. This is what we've done.
Part of our database schema:
| database table: Traffic_7_2006 | |
| Field Name | Explanation |
| br_interface | interface number |
| In_kbps | kb/ps input |
| Out_kbps | kb/ps output |
| dan_u_god | day of the year |
...
def fetch(self, file, ds, cf, startTime, endTime, resolution):
...
#we collect data every 300sec, so the minimal resolution is 300sec (5min)
min_resolution = 300
if int(resolution) < min_resolution:
resolution = min_resolution
_start_time = gmtime(int(startTime))
_end_time = gmtime(int(endTime))
# SETTING UP SQL QUERY
#We need to use and calculate "LIMIT" for our sql query (in order to save space,
# the day of the year is the only time value being recoreded in our traffic database table).
limit = ""
limit_start = (_start_time[3] * 12) + ((_start_time[4])/5)
limit_tmp = (int(endTime)-int(startTime)) / 300
limit = "Limit %s, %s" % (limit_start, limit_tmp)
#This is the exact replica of rrdtool.fetch response, only empty
response = ((int(startTime), int(endTime), int(resolution)),('bytes','pkts','flows'),[])
The file parameter here has the same value as the file prarameter in MA RRD configuration file(see the MA RRD configuration file chapter). Based on our MA configuration file our file parameter value will look something like this /Traffic_7_/1/in so what we need to do is to transform this value into this Traffic_7_1_2006.
#Formatting traffic table name
noc_params = string.split(file, "/")
interface_number = noc_params[2]
direction = noc_params[3]
year = _start_time[0]
traffic_table = noc_params[1] + repr(year)
#In our database the first day of the year begins with 0 so we need to set the differences strait.
dan_u_god1 = _start_time[7] - 1
dan_u_god2 = _end_time[7] - 1
db = MySQLdb.connect(host="localhost", user="******", passwd="*******", db="******")
cursor = db.cursor()
#One query for each direction
if direction == "in":
cursor.execute("""SELECT In_kbps, PIn_ps FROM %s
WHERE (br_interface=%s) AND (dan_u_god >= %s) AND (dan_u_god <= %s)
ORDER BY dan_u_god, ID %s""" %
(traffic_table, interface_number, dan_u_god1, dan_u_god2, limit))
else:
cursor.execute("""SELECT Out_kbps, POut_ps FROM %s
WHERE (br_interface=%s) AND (dan_u_god >= %s) AND (dan_u_god <= %s)
ORDER BY dan_u_god, ID %s""" %
(traffic_table, interface_number, dan_u_god1, dan_u_god2, limit))
result = cursor.fetchall()
#Formatting response to look exactly the same as if it was produced by rrdtool.fetch method
step = int(resolution)//min_resolution
cntrl = 1
bytes, pkts = [], []
for res in result:
_bytes = float(res[0]) * 125 # Our data is in “kilobit” and we need it in “byte” (a decimal kilobit is equal to 125 bytes)
_pkts = float(res[1])
if step == 1:
response[2].append((_bytes, _pkts, 0))
else:
bytes.append(_bytes)
pkts.append(_pkts)
if cntrl < step:
cntrl = cntrl + 1
elif cntrl == step :
#This is where we handle consolidation functions
if cf == "AVERAGE":
response[2].append((sum(bytes)/step, sum(pkts)/step, 0))
elif cf == "MIN":
response[2].append((min(bytes), min(pkts), 0))
elif cf == "MAX":
response[2].append((max(bytes), max(pkts), 0))
elif cf == "LAST":
response[2].append((pop(bytes), pop(pkts), 0))
else:
response[2].append((_bytes, _pkts, 0))
cntrl = 1
bytes, pkts = [], []
log.debug("srce_mysql_ma_message_handler.RRDAccessViaMysql.takeAction:: method is
beeing executed")
dataSource = ds
#data = rrdtool.fetch(file, cf, "-s %s" % startTime, "-e %s" % endTime, "-r %s" % resolution)
data = response
Modify pipeline.py
We've got to modify pipeline.py located in "perfSONAR-MA/service/" folder. The whole deal revolves around placing our own message handler in the data flow pipeline.
Source code changes: perfSONAR-MA/service/pipeline.py
| Before | After |
| from SONARpyMA.message.services.ma import rrd_ma_message_handler | from SONARpyMA.message.services.ma import srce_mysql_ma_message_handler |
| __inputters1 = [ "", rrd_ma_message_handler.RRD_MA_MESSAGE_HANDLER_NAME ] | __inputters1 = [ "", srce_mysql_ma_message_handler.MYSQL_MA_MESSAGE_HANDLER_NAME ] |
| rrdMAMessageHandler = rrd_ma_message_handler.RRDMAMessageHandler(__inputters2) | srceMAMessageHandler = srce_mysql_ma_message_handler.MySQLMAMessageHandler(__inputters2) |
| pipeline = [ (message_convertor.STRING_TO_NMWG, messageConvertor),
(ma_message_handler.ACCEPT_MESSAGE, rrdMAMessageHandler), (message_convertor.NMWG_TO_STRING, messageConvertor) ] | pipeline = [ (message_convertor.STRING_TO_NMWG, messageConvertor),
(ma_message_handler.ACCEPT_MESSAGE, srceMAMessageHandler), (message_convertor.NMWG_TO_STRING, messageConvertor) ] |
Now the MA service will use our own message handler when handling requests instead of rrd_ma_message_handler.
MA RRD configuration file
Finally, we've got to setup the MA RRD configuration file. This is a standard procedure except for the file parameter part. Based on our database design every interface has it's own traffic database table. So instead of the file name of the RRD database we put the name of the interface traffic database table. Now we can call this the MA MySQL configuration file.
Part of the MA configuration file: perfSONAR-MA/service/conf/mysql-database_SRCE.xml:
<nmwg:metadata id="meta5">
<perfsonar:subject id="subj5">
<nmwgt:interface>
<nmwgt:hostName>the.name.of.the.host</nmwgt:hostName>
<nmwgt:ifAddress type="ipv4">193.000.000.000</nmwgt:ifAddress>
<nmwgt:ifName>POS4/0</nmwgt:ifName>
<nmwgt:ifDescription>cn-decription.CARNet.hr</nmwgt:ifDescription>
<nmwgt:direction>in</nmwgt:direction>
<nmwgt:capacity>10000000000</nmwgt:capacity>
<nmwgt:authRealm>srce.hr</nmwgt:authRealm>
</nmwgt:interface>
</perfsonar:subject>
<nmwg:eventType>utilization</nmwg:eventType>
</nmwg:metadata>
<nmwg:data id="data5" metadataIdRef="meta5">
<nmwg:key>
<nmwg:parameters>
<nmwg:parameter name="file">/Traffic_7_/1/in</nmwg:parameter>
<nmwg:parameter name="dataSource">bytes</nmwg:parameter>
</nmwg:parameters>
</nmwg:key>
</nmwg:data>
Download
Source code (python): 
Conclusion
This is neither perfect nor the best possible solution but for the time being it can satisfy our needs. It is quick, reasonably clean, goes along with version upgrades and is fairly easy to maintain. If you have any questions, suggestions or remarks feel free to contact me.
Authors: Danijel Matek, Mario Klobucar
Contact e-mail: dmatek@srce.hr
