Hi Harry, Your nfacctd config looks OK; i tried to reproduce in lab (although i have availability of PostgreSQL 9.1 instead of 9.4 i don't think it's making an actual difference) without success. Any chance i can debug this on your box? If yes, we can follow-up privately for the details. In principle this smells like a bug.
Cheers, Paolo On Tue, Dec 22, 2015 at 06:24:13PM +0000, Harry Foster wrote: > > 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 _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
