Well, I've figured part of this out: You'll notice that my config had a number of fields indexed - even fields that weren't even aggregated to the database. Once I cleaned up my primary keys (removed unnecessary keys) MySQL started operating in a much more sane manner (not eating both CPUs).
My logs still regularly mentions "expecting flow '2828584623' but received '2828584683'" messages, but I've definitely made progress here. I've also noticed that I tend to see messages like "Table 'XXXX.acct_2007051417' doesn't exist". Anyone have advice? I'm having some excellent results with pmacct so far. If anyone has additional configuration tips, they're welcome. Many thanks, Fleet. [EMAIL PROTECTED] wrote: > The device I'm exporting from is a Cisco 7204. My collector is a dual > CPU PIII (running at ~1.264Mhz each) with 1G RAM. > I'm using InnoDB Mysql tables, with sql_lock_style set to row (See > config below) > I'm thinking that most likely my issue lies in the config. Many thanks. > > > [root@ ~]# ps auxw | grep nfacctd > root 3134 0.0 0.3 9768 3656 ? Ss 10:42 0:00 nfacctd: > Core Process [default] > root 3135 0.1 0.7 14368 8244 ? S 10:42 0:00 nfacctd: > MySQL Plugin [default] > root 3141 0.2 0.5 13924 5780 ? S 10:43 0:00 nfacctd: > MySQL Plugin -- DB Writer [default] > root 3149 0.1 0.5 14184 5988 ? S 10:44 0:00 nfacctd: > MySQL Plugin -- DB Writer [default] > root 3152 0.0 0.5 14316 6180 ? S 10:45 0:00 nfacctd: > MySQL Plugin -- DB Writer [default] > root 3315 0.0 0.6 14448 6292 ? S 10:46 0:00 nfacctd: > MySQL Plugin -- DB Writer [default] > root 3318 0.0 0.6 14448 6296 ? S 10:47 0:00 nfacctd: > MySQL Plugin -- DB Writer [default] > root 3321 0.0 0.6 14452 6316 ? S 10:48 0:00 nfacctd: > MySQL Plugin -- DB Writer [default] > > *From logs: > May 10 10:42:34 nfacctd[3134]: INFO ( default/core ): Start logging ... > May 10 10:42:34 nfacctd[3134]: INFO ( default/core ): waiting for data > on UDP port '2106' > May 10 10:44:07 nfacctd[3134]: WARN: expecting flow '282433353' but > received '282433413' collector=x.x.x.x:2106 agent=x.x.x.x:0 > May 10 10:44:07 nfacctd[3134]: WARN: expecting flow '282433623' but > received '282433713' collector=x.x.x.x:2106 agent=x.x.x.x:0 > May 10 10:47:02 nfacctd[3134]: WARN: expecting flow '282497793' but > received '282497913' collector=x.x.x.x:2106 agent=x.x.x.x:0 > > *Packet captures DO include the missing netflow data. > > *Approximately 1,258 inserts occur per minute. > > *Configuration file: > > debug:false > > syslog:local1 > daemonize:true > aggregate: src_host,dst_host,src_port,dst_port,proto,tcpflags > > nfacctd_ip: x.x.x.x > nfacctd_port: 2106 > nfacctd_time_new: true > > interface: lo > > sql_host:x.x.x.x > sql_user:xxxxx > sql_passwd:xxxxxx > > sql_db:xx_db > sql_table:acct_%Y%m%d%H > sql_optimize_clauses: true > sql_history:5m > sql_table_schema:/etc/pmacct/xxxxx.sql > sql_locking_style:row > > pidfile:/var/run/xxx.flow.pid > > plugins: mysql > > *Mysql Schema: > > create table acct_%Y%m%d%H ( > agent_id INT(2) UNSIGNED NOT NULL, > class_id CHAR(16) NOT NULL, > mac_src CHAR(17) NOT NULL, > mac_dst CHAR(17) NOT NULL, > vlan INT(2) UNSIGNED NOT NULL, > as_src INT(2) UNSIGNED NOT NULL, > as_dst INT(2) UNSIGNED NOT NULL, > ip_src CHAR(15) NOT NULL, > ip_dst CHAR(15) NOT NULL, > src_port INT(2) UNSIGNED NOT NULL, > dst_port INT(2) UNSIGNED NOT NULL, > tcp_flags INT(4) UNSIGNED NOT NULL, > ip_proto CHAR(6) NOT NULL, > tos INT(4) UNSIGNED NOT NULL, > packets INT UNSIGNED NOT NULL, > bytes BIGINT UNSIGNED NOT NULL, > flows INT UNSIGNED NOT NULL, > stamp_inserted DATETIME NOT NULL, > stamp_updated DATETIME, > PRIMARY KEY (agent_id, class_id, mac_src, mac_dst, vlan, as_src, > as_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, tos, stamp_inserted) > ) ENGINE=innodb; > > > > <http://www.mail-archive.com/[email protected]/msg00784.html> > _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
