> Couldn't you instead configure pmacctd on the routers to use nfprobe to > export netflow to the database server and then on the database server run > nfacctd to collect the netflows from the routers and store to a local > mysql db.
That sounds needlessly complicated. I'll look into it. :-) I just want a couple of records in my pre-existing MySQL database from each server every minute, so I can (a) know the bandwidth RIGHT NOW, and (b) have a few days of history. The software has a MySQL connector that is supposed to do the job, that's why I chose it. The only reason I would not use the built-in connector is if it doesn't work. In which case there's no reason to use pmacct at all. Which, frankly, is looking to be the situation. I'm now discovering that there's often a huge time lag before the data goes into the database, ranging from a few minutes to up to an hour. I've got debug going, 20 seconds between SQL refreshes, and I get several: ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** In sequence with no records going to the database. And then a bunch of DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+70, bytes=bytes+3192, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254708120) = stamp_inserted AND ip_src='65.19.179.0' AND ip_dst='0.0.0.0' which I would expect to see every refresh, not once every ten minutes. And even when the update happens, the data is minutes old. I think it may have something to do with my buffer sizes, which are usually set relatively high because otherwise I get: ERROR ( default/mysql ): We are missing data. If you see this message once in a while, discard it. Otherwise some solutions follow: - increase shared memory size, 'plugin_pipe_size'; now: '10240'. - increase buffer size, 'plugin_buffer_size'; now: '1024'. - increase system maximum socket size. However, it seems that when traffic drops back to lower rates, it takes forever for these buffers to fill, so minutes go by without a database update. If I restart pmacctd because I get impatient or think it's crashed, I loose ten minutes worth of data. Not cool. I'm just guessing, but I seems like I have a choice of setting them low, getting regular data, but loosing some when traffic is high (which corrupts the counters), or setting it high and getting the data very late when traffic is low. Both options suck. I don't understand why it would be this way. Oh, and I've just got a new kind of error I've never seen before: WARN ( default/core ): eth0 has become unavailable; throttling ... ERROR ( default/mysql ): PRIMARY 'mysql' backend trouble. ERROR ( default/mysql ): The SQL server says: Lost connection to MySQL server at 'reading authorization packet', system error: 0 WARN ( default/core ): eth0 has become unavailable; throttling ... Here's config for the router I've been discussing: ======================================================= ! debug: true ! logfile: /var/log/pmacctd.log !pidfile: /var/run/pmacct/pmacctd.pid ! interface: eth0 daemonize: false !promisc: false plugin_pipe_size: 1024000 plugin_buffer_size: 10240 ! plugins: mysql aggregate: src_net,dst_net ! sql_host: XXXX.com sql_db: XXXX sql_table: pmacct_XXXX sql_optimize_clauses: true sql_user: XXXXXX sql_passwd: XXXXXXX sql_multi_values: 64000 !sql_dont_try_update: true sql_history: 1m sql_refresh_time: 20 sql_history_roundoff: m ! networks_file: /config/networks.pmacct ! ports_file: /config/ports.pmacct ! ======================================================= Here's a chunk of the log output: ======================================================= DEBUG ( /config/networks.pmacct ): IPv4 Networks Cache successfully created: 99991 entries. DEBUG ( default/core ): PCAP buffer: obtained 2048000 / 1024000 bytes. OK ( default/core ): link type is: 1 ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** DEBUG ( default/mysql ): 10 VALUES statements sent to the MySQL server. ( default/mysql ) *** Purging cache - END (QN: 11, ET: 1) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 11, ET: 1) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** ( default/mysql ) *** Purging cache - END (QN: 0, ET: 0) *** ( default/mysql ) *** Purging cache - START *** DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+360, bytes=bytes+74974, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='0.0.0.0' AND ip_dst='3.99.121.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+4770, bytes=bytes+544268, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='0.0.0.0' AND ip_dst='0.0.0.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+7695, bytes=bytes+3433999, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='20.223.98.0' AND ip_dst='0.0.0.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+362, bytes=bytes+74890, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='3.99.121.0' AND ip_dst='0.0.0.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+6186, bytes=bytes+3377595, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='0.0.0.0' AND ip_dst='20.223.98.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+75, bytes=bytes+6552, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='0.0.0.0' AND ip_dst='6.19.19.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+76, bytes=bytes+5837, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='6.19.19.0' AND ip_dst='0.0.0.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+128, bytes=bytes+6680, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='0.0.0.0' AND ip_dst='20.152.22.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+17, bytes=bytes+1258, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='0.0.0.0' AND ip_dst='6.19.18.0' DEBUG ( default/mysql ): UPDATE `pmacct_james2` SET packets=packets+17, bytes=bytes+1234, stamp_updated=NOW() WHERE FROM_UNIXTIME(1254710520) = stamp_inserted AND ip_src='6.19.18.0' AND ip_dst='0.0.0.0' ( default/mysql ) *** Purging cache - END (QN: 10, ET: 1) *** ======================================================= The networks file has about a dozen entries. The whole setup is supposed to be tiny and simple. I don't understand why the database is at least five minutes behind every daemon. Or why there are gaps in the data. (There is never zero traffic in a minute) I'm getting really frustrated. I thought this was working. -- Jeremy Lee BCompSci (Hons) The Unorthodox Engineers www.unorthodox.com.au _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
