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
vlan and local vlan, and between local vlan and Internet (or external
network).
Thanks,
Andras
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
--
Edward van Kuik
Nitric Software Laboratory
78 Strand Street
Tel: 021 300 1073
Cell: 083 645 6443
http://nitric.co.za/
--
-- Triggers `ipaddress`
--
DROP TRIGGER IF EXISTS `afteripinsert`;
DELIMITER //
CREATE TRIGGER `afteripinsert` BEFORE INSERT ON `ipaddress`
FOR EACH ROW BEGIN
SET new.ipaddress_uid = INET_ATON(new.ipaddress_name);
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `afteripupdate`;
DELIMITER //
CREATE TRIGGER `afteripupdate` BEFORE UPDATE ON `ipaddress`
FOR EACH ROW BEGIN
SET new.ipaddress_uid = INET_ATON(new.ipaddress_name);
END
//
DELIMITER ;
-- --------------------------------------------------------
--
-- Triggers `probe1`
--
DROP TRIGGER IF EXISTS `afterinsertprobe1`;
DELIMITER //
CREATE TRIGGER `afterinsertprobe1` AFTER INSERT ON `probe1`
FOR EACH ROW BEGIN
SET @p = 0;
CASE
WHEN new.dst_port in (SELECT port_uid FROM port) THEN
SET @p = new.dst_port;
WHEN new.src_port in (SELECT port_uid FROM port) THEN
SET @p = new.src_port;
ELSE
SET @p = 0;
END CASE;
SET @src = INET_ATON(new.ip_src);
SET @dst = INET_ATON(new.ip_dst);
SET @srclocal = @src IN (SELECT ipaddress_uid FROM ipaddress);
SET @dstlocal = @dst IN (SELECT ipaddress_uid FROM ipaddress);
CASE
WHEN @srclocal AND !@dstlocal THEN
INSERT DELAYED INTO probex2 (ip_local, ip_remote, upload, stamp, probe_uid, port_uid) VALUES (@src,@dst,new.bytes,new.stamp_inserted,1,@p)
ON DUPLICATE KEY UPDATE upload = upload + new.bytes;
INSERT DELAYED INTO summary3 (ip_local, upload, stamp, probe_uid) VALUES (@src,new.bytes,new.stamp_inserted,1)
ON DUPLICATE KEY UPDATE upload = upload + new.bytes;
WHEN !@srclocal AND @dstlocal THEN
INSERT DELAYED INTO probex2 (ip_local, ip_remote, download, stamp, probe_uid, port_uid) VALUES (@dst,@src,new.bytes,new.stamp_inserted,1,@p)
ON DUPLICATE KEY UPDATE download = download + new.bytes, count = count + 1;
INSERT DELAYED INTO summary3 (ip_local, download, stamp, probe_uid) VALUES (@dst,new.bytes,new.stamp_inserted,1)
ON DUPLICATE KEY UPDATE download = download + new.bytes;
WHEN @srclocal AND @dstlocal THEN
INSERT DELAYED INTO probe_internal2 VALUES (new.ip_src, new.ip_dst, new.src_port, new.dst_port, new.packets, new.bytes, new.stamp_inserted, new.stamp_updated);
ELSE
INSERT DELAYED INTO probe_unknown2 VALUES (new.ip_src, new.ip_dst, new.src_port, new.dst_port, new.packets, new.bytes, new.stamp_inserted, new.stamp_updated);
END CASE;
END
//
DELIMITER ;
-- --------------------------------------------------------
--
-- Triggers `probe2`
--
DROP TRIGGER IF EXISTS `afterinsertprobe2`;
DELIMITER //
CREATE TRIGGER `afterinsertprobe2` AFTER INSERT ON `probe2`
FOR EACH ROW BEGIN
SET @p = 0;
CASE
WHEN new.dst_port in (SELECT port_uid FROM port) THEN
SET @p = new.dst_port;
WHEN new.src_port in (SELECT port_uid FROM port) THEN
SET @p = new.src_port;
ELSE
SET @p = 0;
END CASE;
SET @src = INET_ATON(new.ip_src);
SET @dst = INET_ATON(new.ip_dst);
SET @srclocal = @src IN (SELECT ipaddress_uid FROM ipaddress);
SET @dstlocal = @dst IN (SELECT ipaddress_uid FROM ipaddress);
CASE
WHEN @srclocal AND !@dstlocal THEN
INSERT DELAYED INTO probex2 (ip_local, ip_remote, upload, stamp, probe_uid, port_uid) VALUES (@src,@dst,new.bytes,new.stamp_inserted,2,@p)
ON DUPLICATE KEY UPDATE upload = upload + new.bytes;
INSERT DELAYED INTO summary3 (ip_local, upload, stamp, probe_uid) VALUES (@src,new.bytes,new.stamp_inserted,2)
ON DUPLICATE KEY UPDATE upload = upload + new.bytes;
WHEN !@srclocal AND @dstlocal THEN
INSERT DELAYED INTO probex2 (ip_local, ip_remote, download, stamp, probe_uid, port_uid) VALUES (@dst,@src,new.bytes,new.stamp_inserted,2,@p)
ON DUPLICATE KEY UPDATE download = download + new.bytes;
INSERT DELAYED INTO summary3 (ip_local, download, stamp, probe_uid) VALUES (@dst,new.bytes,new.stamp_inserted,2)
ON DUPLICATE KEY UPDATE download = download + new.bytes;
WHEN @srclocal AND @dstlocal THEN
INSERT DELAYED INTO probe_internal2 VALUES (new.ip_src, new.ip_dst, new.src_port, new.dst_port, new.packets, new.bytes, new.stamp_inserted, new.stamp_updated);
ELSE
INSERT DELAYED INTO probe_unknown2 VALUES (new.ip_src, new.ip_dst, new.src_port, new.dst_port, new.packets, new.bytes, new.stamp_inserted, new.stamp_updated);
END CASE;
END
//
DELIMITER ;
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
