Hi Thomas,
What matters is really how many 'src_host, dst_host' aggregates
are generated out of the traffic. Predicting for me is difficult
but you can turn sql_dont_try_update to false (so that it doesn't
complain about duplicates anymore for the sake of this test) and
see whether during the sql_refresh_time time-span you are pushing
more than 256K aggregates to the database. Verification is to run
a query like the following against the database:
SELECT COUNT(*) AS aggregates, stamp_inserted
FROM acct
GROUP BY stamp_inserted;
Cheers,
Paolo
On Sun, Sep 12, 2010 at 05:59:20PM +0000, Thomas Raabo - ZitCom A/S wrote:
> r...@mgmt5 examples]# top
> top - 19:31:01 up 4 days, 9:24, 5 users, load average: 0.12, 0.08, 0.07
> Tasks: 202 total, 1 running, 201 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 24675552k total, 3611064k used, 21064488k free, 312036k buffers
> Swap: 3124632k total, 260k used, 3124372k free, 1490888k cached
>
> We are running about 400mbit of traffik average with netflow exporting 6000
> flow/sec
>
> What sql_cache_entries do you recommend?
>
>
> Thomas
>
>
>
> -----Oprindelig meddelelse-----
> Fra: pmacct-discussion-boun...@pmacct.net
> [mailto:pmacct-discussion-boun...@pmacct.net] P? vegne af Paolo Lucente
> Sendt: 12. september 2010 01:32
> Til: pmacct-discussion@pmacct.net
> Emne: Re: [pmacct-discussion] duplicates...
>
> Hi Thomas,
>
> This very likely answers why you are not seeing all you expect in the
> database. Reason you get duplicates is pmacct is trying to insert into the
> database more than once per 600 seconds (as per your configuration) and
> sql_dont_try_update is set to true.
>
> So quickest work-around is to set sql_dont_try_update to false; but i guess
> it's good to spend some efforts to understand why pmacct is behaving like
> that. The cause "safe action" gives a pointer and it's due to one of the two
> following reasons:
>
> * There are too many 'src_host, dst_host' aggregates for the
> configured sql_cache_entries value. This should be excluded
> as only 500-600 entries are to be inserted while the config
> would allow for sql_cache_entries entries (256K). But, it
> could be it stops on the first of a series of INSERTs, so
> there is more data to dump. Knowing your environment, you
> can ultimately comment whether the 256K value is good or
> not.
>
> * The system is short of memory (or the OS limits the amount
> of memory the pmacct process(es) can allocate); so at some
> stage pmacct asks for a new chunk of memory to insert a new
> entry in the SQL cache and it's not able to get it; this
> results in pmacct purging the cache content in an effort
> to make room to new data. Please check whether this can be
> the case.
>
> Let me know.
>
> Cheers,
> Paolo
>
>
> On Sat, Sep 11, 2010 at 05:49:17PM +0000, Thomas Raabo - ZitCom A/S wrote:
> > Once every 10 min iam getting some errors
> >
> > DEBUG ( default/mysql ): purging process (CAUSE: safe action) (
> > default/mysql ) *** Purging cache - START *** DEBUG ( default/mysql ):
> > 568 VALUES statements sent to the MySQL server.
> > ERROR ( default/mysql ): Duplicate entry
> > '0-unknown-0:0:0:0:0:0-0:0:0:0:0:0-0-0-0-188.176.68.71-194.182.12' for key
> > 'PRIMARY'
> >
> > DEBUG ( default/mysql ): purging process (CAUSE: safe action) (
> > default/mysql ) *** Purging cache - START *** DEBUG ( default/mysql ):
> > 569 VALUES statements sent to the MySQL server.
> > ERROR ( default/mysql ): Duplicate entry
> > '0-unknown-0:0:0:0:0:0-0:0:0:0:0:0-0-0-0-91.100.100.103-94.231.97' for key
> > 'PRIMARY'
> >
> > DEBUG ( default/mysql ): purging process (CAUSE: safe action) (
> > default/mysql ) *** Purging cache - START *** DEBUG ( default/mysql ):
> > 565 VALUES statements sent to the MySQL server.
> > ERROR ( default/mysql ): Duplicate entry
> > '0-unknown-0:0:0:0:0:0-0:0:0:0:0:0-0-0-0-94.231.102.73-95.166.47.' for key
> > 'PRIMARY'
> >
> > My config
> >
> > ! nfacctd configuration example
> > !
> > ! Did you know CONFIG-KEYS contains the detailed list of all
> > configuration keys ! supported by 'nfacctd' and 'pmacctd' ?
> > !
> > debug: true
> > !daemonize: true
> >
> > aggregate: tag, src_host, dst_host
> > plugin_pipe_size: 40960000
> > plugin_buffer_size: 40960
> > nfacctd_port: 9000
> >
> > ! nfacctd_time_secs: true
> >
> > nfacctd_time_new: true
> >
> > plugins: mysql
> >
> > sql_db: pmacct
> > sql_table: acct
> > sql_table_version: 2
> > sql_passwd: *****
> > sql_user: root
> > sql_refresh_time: 600
> > sql_multi_values: 100000
> > sql_dont_try_update: true
> > sql_cache_entries: 256000
> > ! sql_optimize_clauses: true
> > sql_history: 10m
> > sql_history_roundoff: mh
> >
> > Thomas
>
> > _______________________________________________
> > 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
