i can suggest to drop all the indexes and create only the ones that are interesting for you.
Atm in similar situ i've gained 10fold improvements in response and cpu usage On 18/05/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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 > -- С уважение, Владимир Витков http://www.netsecad.com http://www.supportbg.com _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
