Summarizing: issue fixed, commit log here: https://github.com/pmacct/pmacct/commit/00ac4a1008edf4fd7f026c656e0488418a174740
Cheers, Paolo On Wed, Dec 23, 2015 at 07:41:14PM +0000, Paolo Lucente wrote: > 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 _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
