Hi all I've been working on a traffic aggregator, flow forensics, and a bit of DDoS mitigation using pmacct. To overcome the overwhelming amount of information on about 5Gbps of flow data I've implemented the sql_preprocess directive to help cut down on the amount of information (minb, minp, etc) written to disk. Eventually, I'll look into a bit more optimisation. Netflow v5 is being pumped into a PostgreSQL database via nfacct, which then feeds the info into a web frontend for the network guys.
All in all, a fantastic piece of software I must say! However, it seems as soon as I enable the sql_preprocess directive we correctly get the reduced writes to the database as shown in the logs as the QN isn't 0/x, but no rows actually end up in the database. Rows being flushed: Dec 22 17:30:01 nfacctd[3280]: INFO ( dst_port/pgsql ): *** Purging cache - END (PID: 3280, QN: 249/5498, ET: 0) Dec 22 17:30:01 nfacctd[3279]: INFO ( src_port/pgsql ): *** Purging cache - END (PID: 3279, QN: 249/5498, ET: 0) Dec 22 17:30:01 nfacctd[3281]: INFO ( src_addr/pgsql ): *** Purging cache - END (PID: 3281, QN: 249/5498, ET: 0) Dec 22 17:30:01 nfacctd[3282]: INFO ( dst_addr/pgsql ): *** Purging cache - END (PID: 3282, QN: 249/5498, ET: 0) In the PostgreSQL log I notice the following error after the COPY statements: LOG: incomplete message from client CONTEXT: COPY agg_ipsrc, line 264 STATEMENT: COPY agg_ipsrc (stamp_updated, stamp_inserted, ip_src, packets, bytes) FROM STDIN DELIMITER ',' ERROR: unexpected EOF on client connection with an open transaction CONTEXT: COPY agg_ipsrc, line 264 STATEMENT: COPY agg_ipsrc (stamp_updated, stamp_inserted, ip_src, packets, bytes) FROM STDIN DELIMITER ',' FATAL: terminating connection because protocol sync was lost It's similar with INSERTS: LOG: statement: INSERT INTO agg_ipdst (stamp_updated, stamp_inserted, ip_dst, packets, bytes) VALUES (ABSTIME(1450805401)::Timestamp, ABSTIME(1450805100)::Timestamp, '255.255.0.246', 988, 988) LOG: unexpected EOF on client connection with an open transaction However, if you remove the sql_preprocess directive completely then the errors about unexpected EOFs disappear and rows are written to the database. It also seems to be OK if the preprocess statement doesn't match anything but then it's not much use! PostgreSQL - Version 9.4.5 - Bound to localhost. pmacct - Version 1.5.2 - (Configured with --enable-ipv6 --enable-pgsql, gcc version 5.3.1) The relevant config is below: aggregate[src_port]: src_port, proto aggregate[dst_port]: dst_port, proto aggregate[src_addr]: src_host aggregate[dst_addr]: dst_host ! plugins: pgsql[src_port], pgsql[dst_port], pgsql[src_addr], pgsql[dst_addr] ! sql_host: 127.0.0.1 sql_table[src_port]: agg_portsrc sql_table[dst_port]: agg_portdst sql_table[src_addr]: agg_ipsrc sql_table[dst_addr]: agg_ipdst sql_db: pmacct sql_refresh_time: 300 sql_optimize_clauses: true sql_history: 5m sql_history_roundoff: m sql_preprocess: minb=1000 So, at this point I'm a bit stumped. I'm not sure if I've missed something out in regards to the database schema or configuration, whether it's a bug with the cache flushing code or some esoteric nfacct configuration issue I've misunderstood. If anyone wants anymore info, I'll be happy to provide it. Regards Harry Foster _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
