Edward, I did not start to work with mysql triggers but your last comment did ring a bell for me. I downloaded the latest pmacct and I started to use mongodb. There is no performance issues anymore and I got what I wanted. Actually I use a mix of backends: mysql and mongodb together.
Here is my nfacctd.conf: !MongoDB aggregate: src_host,dst_host,src_port,dst_port,proto mongo_history: 5m mongo_time_roundoff: m mongo_refresh_time:300 mongo_table: acctv5.netflowsender_2013_07_03 !MySQL sql_history: 1h sql_history_roundoff: mhd sql_table_version: 5 aggregate[in]: dst_host aggregate[out]: src_host sql_table[in]: netflowsender_in_2013_07_03 sql_table[out]: netflowsender_out_2013_07_03 sql_host: 127.0.0.1 sql_db: pm sql_user: pm sql_passwd:secret sql_refresh_time: 300 sql_dont_try_update: true sql_optimize_clauses: true So in mongodb I have the historical data of connection and in mysql I have the data what I can use for accounting (how many bytes a host receives and sends). I would like to use one database backands but I did not find info how to do this with mongodb only. Another thing is that I restart the pmacct everyday at midnight and create a separate table (naming with the actual date) in every database. So for every day I have the data in a separate table. The load of the machine is: 13:26:11 up 16 days, 3:13, 1 user, load average: 0.08, 0.08, 0.13 yesterday the entries in the mongodb database: 5927799 Please feel free to comment, Andras On Tue, Jun 18, 2013 at 6:16 PM, Andras Horvai <[email protected]>wrote: > Thanks Edward, > > I will check triggers.sql. > > Andras > > > On Tue, Jun 18, 2013 at 5:04 PM, Edward van Kuik <[email protected]>wrote: > >> Hi >> >> What I do is I use triggers which create some summary tables for me as it >> inserts. >> I've attached my trigger sql in case you find it helpful. >> >> I insert it into tables with partitioning by range. But the system is >> generally still quite slow. (I currently have 1.3 billion rows) >> I am thinking of inserting the data and summary data into another type of >> database, like mongo. >> >> Edward >> >> >> >> >> On 18/06/2013 16:34, Andras Horvai wrote: >> >> Hello, >> >> >> This is what I would like to achieve: >> I would like to have a history of ip connections of my network, let's >> say >> back to 90 days. So to achieve this I understand that I have to aggregate >> in >> nfacctd. >> Here is the aggregate what I tried: >> >> aggregate: src_host,dst_host,src_port,dst_port,proto >> >> The problem is when I do this type of aggregate my system slows >> down very much. (I cannot run a simple query in mysql so I cannot >> get the information what I need... so the collection is useless). >> >> I tried to distinguish the in and out traffic with plugins, >> but did not help me regarding the performance. >> The only way what does not slows down my system if I aggregate only >> src or dst addresses (and separate these into different tables). >> But with this only aggregate, I lost the information about the src_port, >> dst_port and protocol (per ip pairs) >> >> I would like to monitor the traffic of 400 hosts. >> Is it possible with a server with >> 4 cores (Intel(R) Xeon(R) CPU E5603 @ 1.60GHz) >> and with 4 GB of rams. >> >> Flow exporter is a L3 cisco siwtch in the core of the network. It has >> vlans defined to them. I am interested in the traffic between local vlanand >> local >> vlan, and between local vlan and Internet (or external network). >> >> Thanks, >> >> Andras >> >> >> _______________________________________________ >> pmacct-discussion mailing listhttp://www.pmacct.net/#mailinglists >> >> >> >> -- >> Edward van Kuik >> Nitric Software Laboratory >> 78 Strand Street >> Tel: 021 300 1073 >> Cell: 083 645 6443http://nitric.co.za/ >> >> >> _______________________________________________ >> pmacct-discussion mailing list >> http://www.pmacct.net/#mailinglists >> > >
_______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
