Hi Daniel, in scenarios in which UPDATEs are affordable, the advice is to keep hourly and monthly stats in different tables. With proper indexing, such method is lighter compared to making sums.
If UPDATEs are not affordable then, yes, there is no other choice but make sums basing on the stamp_inserted field. Scenarios in which pre-building monthly stats in a separate table is clearly the best choice are the non-deterministic ones: the DB is available for queries straight off a web page where customers can click and enquire. But in the past it has been evidenced that even in scenarios where monthly data were summarized once a day/ week/month by a cron script, the pre-built approach was still winning. Cheers, Paolo On Tue, Dec 11, 2007 at 10:52:33PM +0100, Daniel Netzer wrote: > Hi Paolo, > > thank you very much for your help and a great software! pmacct is > exactly what I was looking for. I used a snmp based solution for > accounting my traffic from switches. "rtg" did the job but I needed more > detailed statistics and IP based accounting, upgrading to *Flow enabled > hardware was no option so pmacct is a great improvement and works > perfectly. > > To roundup my solution I need to calculate monthly totals per IP but > after digging through the config keys, faq, and this lists archive I am > still not quite sure how to implement it correctly. > > my config (excerpt): > > aggregate: sum_host > networks_file: /etc/pmacct/networks.lst > sql_table: acct_v4 > sql_refresh_time: 60 > sql_optimize_clauses: true > sql_history: 1h > sql_history_roundoff: mhd > > fills my table at one row per IP per hour (stamp_inserted). Ok. Using > "sum(bytes)" I get my totals but how to get the correct time period > (month)? Of course I could extend the query and include stamp_inserted > >= 1st of month. Is this the intended method / best practice? I have > read about filling two tables one for the detailed information and one > for a monthly total (one row per ip per month). > > Thanks for any help! > regards > daniel _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
