Hi Thomas, UPDATEs are resource intensive and hence take more time for the database to process them; moreover, a locked access to the table is performed and what you see is the queue of pmacct processes waiting to obtain access to the table to dump their data. That's why you really want to avoid UPDATE queries in production and keep the sql_dont_try_update to false.
The other option you can play with is the sql_cache_entries; bring it up from 256K to 512K, 1M or 2M. See if you spot any changes in the behaviour or you manage to "survive" the full 10 minutes. Yet another option is to bring the sql_refresh_time and sql_history down from 10 mins to, say, 5mins or 1 min and from there get an idea how many aggregates you might end getting in the 10mins timespan. Cheers, Paolo On Sun, Sep 12, 2010 at 06:31:03PM +0000, Thomas Raabo - ZitCom A/S wrote: > Btw... when running with updates.. > > The mysql processlist keep growing and growing with updates.... > > How would i bring down the amount of updates? > > 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
