"real time" statistics....

[kilburn]

I'm thinking about trying to integrate real-time traffic statistics into ispcp and would like to share my thoughts and get oppinions on how/why or why not, etc. My main motivations are:

1. - The "batched" traffic accounting has proven fragile and annoying with ispcp (there are a bunch of posts about getting 100% usage when the domain traffic "manager" runs).
2. - Logging traffic usage to a mysql database degrades over time, so when you have 1 year of traffic logs in a 300 domains server the querys become painfully slow (more cpu usage, blah blah...).
3. - This one is childish but... real time statistics rock!
   

Well, after some research here and there I've outlined a plan:

1. 1. Use rrdtool or something similar to store the traffic data in the highly efficient rdd format (this would leave us with 50-200Kb of traffic data per domain&service&year wich is WAY less than the current usage with mysql).
2. 2. Process logs as they're generated:
   
   1. - Apache: Create a piped logging program that (1) accounts traffic usage by vhost and stores them in .rrd databases, (2) splits the single server-wide log stream, storing a single log on per-domain folders (ending with a lot less open file descriptors than now).
   2. - Postfix: Modify mailgraph to split the accounting on a per-host basis and add pop3/imap counters too.
   3. - Others: Use an approach similar to the apache one if their log output can be piped to a parser, or similar to the postfix one otherwise.
      
3. 3. Obviously, modify the gui to display the gathered traffic data (and probably graphs!)
   

Ok so... what do you think?

[ephigenie]

Quote:- Logging traffic usage to a mysql database degrades over time, so when you have 1 year of traffic logs in a 300 domains server the querys become painfully slow (more cpu usage, blah blah...).

I'd suggest using mysql logging - and just keep the last 4 weeks in a mysql db.
I 've only good things to tell about mod_log_sql.
It has a failback where it writes its log information into a file (in sql format) ... so it can be replayed once the mysql server is up again.
This solved the "too many open files" problem for me, too on a lot of customer servers i support.

Export into awstats can be done via pipe.
Some years ago i payed jan knescke via amazon to support generating of stats out of modlogan directly - i think its worth the try to implement this in awstats as well (reading of log information out of the db ).
I had one dedicated mysql running for ~20 Servers. It worked very well. So performance is not the issue . All i had to do is to make a good cleanup script
(which deleted old data and removed tables from not used domains ) .

Traffic calculation can then be calculated on top of the gathered data in the db.
Perhaps we can look into gplhost how they solved this problem as well.
Another approach would be to use mod_log_spread ... but this goes more into clustered environments.