About the traffic accounting issues

[kilburn]

Ok, I've noticed something very wrong with traffic counting today and decided to take a look to the whole counting arch, with some surprising findings:

engine/traffic/ispcp-srv-traf
This is the responsible of counting server traffic stastistics using iptables information (so it doesn't know which clients to account it for).
Noticed problem: On the "sub srv_traff_start_up" function it launches a query equivalent to "SELECT * FROM domain", which results are obviously never used because it doesn't know a thing about domains!

engine/traffic/ispcp-vrl-traf
This is the responsible of counting domain traffic stastistics, extracting this info from the logfiles of the corresponding services (mail.log for smtp/pop3/imap, apache's traffic logs for web, etc.).
Noticed problem: as each traffic type uses separate logfiles, traffic is counted in defferent functions. The problem is that each of this functions pushes the results to mysql, effectively creating 1 insert query and 3 updates for each domain! On a highly loaded table like the domain_traffic one this has a big impact on performance. It shouldn't be difficult to aggregate the data first and then execute a single INSERT

engine/traffic/ispcp-vrl-traf-correction
After reading and re-reading the code, I've concluded that this is trying to make domain_traffic and server_traffic match by evenly distributting the difference (server_traffic - SUM(all domains_traffic)) among all domains.
Noticed "problem": From my point of view this is a nonsense. Obviously, there's traffic that doesn't get caught in logfiles, and the domain and server traffic will never exactly match if you don't perform this artificial "gap removal", but blindly accounting things to the domains is a perfect recipe for annoyed customers, like in:

client Wrote:Hey! I've my mail service outsourced anywhere else, but I'm consistently accounted for some mail traffic each day! I think you're also miscounting my web traffic, you unskilled morons!

Moreover, up to now if a host got iptables counting wrong because it used some firewall or whatever, the only problem was that server traffic was wrong. Only the admin noticed it and may decide to just ignore it. With this correction thing, server_traffic - domain_traffic would yield negative results and some reall funny corrections...

Unsolved things
On earlier ispcp days, I myself proposed to aggregate all the domain_traffic counts per day (we stored record per domain and half an hour), which is the finest scale at which the panel let's you see them. Otherwise, we generate a record for each half hour, up to (24*2*number_of_domains) per day, making the table grow huge on heavily loaded servers and having a noticeable impact on performance. Until today, I thought that the vrl-traf-correction was about this, but now I've realized that... it hasn't been implemented! (ispcomm posted some code that did the trick, but I've been unable to find it right now).

I don't want to change such things until hearing the opinion of other devs, so that's why I've decided to post here instead of opening tickets and all this

[BeNe]

Quote:Ok, I've noticed something very wrong with traffic counting

You are not alone! I started also a Thread long time ago with this Problem.
But looks like you have here many more knowledge than i
I´m happy that you talk about this Problem!
It does still exist and there are more than one change needed.

Here are some Threads i found:
--> http://www.isp-control.net/forum/thread-3165.html
--> http://www.isp-control.net/forum/thread-3511.html
--> http://www.isp-control.net/ispcp/ticket/1232

Greez BeNe

[sci2tech]

(04-28-2009 05:53 PM)kilburn Wrote:  engine/traffic/ispcp-srv-traf
Noticed problem: On the "sub srv_traff_start_up" function it launches a query equivalent to "SELECT * FROM domain", which results are obviously never used because it doesn't know a thing about domains!

This has the the purpose of instantiate sql connection. But it miss a LIMIT 1, so entire table is selected.

(04-28-2009 05:53 PM)kilburn Wrote:  engine/traffic/ispcp-vrl-traf
Noticed problem: as each traffic type uses separate logfiles, traffic is counted in defferent functions. The problem is that each of this functions pushes the results to mysql, effectively creating 1 insert query and 3 updates for each domain! On a highly loaded table like the domain_traffic one this has a big impact on performance. It shouldn't be difficult to aggregate the data first and then execute a single INSERT

True if in last hour a domain has traffic for http/ftp/mail. There will be execute 2 query(1 Select + 1Update / 1 Insert) for each type of traffic. Multiply with domain number and assume worse case and indeed is a stress for mysql. I`ll try to modify it after I finish what I started already.

(04-28-2009 05:53 PM)kilburn Wrote:  engine/traffic/ispcp-vrl-traf-correction
After reading and re-reading the code, I've concluded that this is trying to make domain_traffic and server_traffic match by evenly distributting the difference (server_traffic - SUM(all domains_traffic)) among all domains.
Noticed "problem": From my point of view this is a nonsense. Obviously, there's traffic that doesn't get caught in logfiles, and the domain and server traffic will never exactly match if you don't perform this artificial "gap removal", but blindly accounting things to the domains is a perfect recipe for annoyed customers, ...:

True, traffic generated by gui is added to costumers traffic, witch is not correct. But imagine following situation -> 1 user offer for download one file of 1GB. Another user hit that file but do not download (cancel the download). In traffic log 1Gb will be added. Here come correction to reflect real traffic. User will not be charged with 1Gb traffic.

(04-28-2009 05:53 PM)kilburn Wrote:  Unsolved things
On earlier ispcp days, I myself proposed to aggregate all the domain_traffic counts per day (we stored record per domain and half an hour), which is the finest scale at which the panel let's you see them. Otherwise, we generate a record for each half hour, up to (24*2*number_of_domains) per day, making the table grow huge on heavily loaded servers and having a noticeable impact on performance. Until today, I thought that the vrl-traf-correction was about this, but now I've realized that...

It`s not true. Traffic is aggregated per day. See line 442 to 458 in ispcp-vrl-traff.
There are other traffic issue like aliases subdomain traffic not been counted.

[kilburn]

(04-29-2009 02:44 AM)sci2tech Wrote:  This has the the purpose of instantiate sql connection. But it miss a LIMIT 1, so entire table is selected.

Ok, but just for curiosity: why do you need to instantiate sql connection?

(04-29-2009 02:44 AM)sci2tech Wrote:  True if in last hour a domain has traffic for http/ftp/mail. There will be execute 2 query(1 Select + 1Update / 1 Insert) for each type of traffic. Multiply with domain number and assume worse case and indeed is a stress for mysql. I`ll try to modify it after I finish what I started already.

Great

(04-29-2009 02:44 AM)sci2tech Wrote:  True, traffic generated by gui is added to costumers traffic, witch is not correct. But imagine following situation -> 1 user offer for download one file of 1GB. Another user hit that file but do not download (cancel the download). In traffic log 1Gb will be added. Here come correction to reflect real traffic. User will not be charged with 1Gb traffic.

You're completely right. That said, now imagine all the traffic that isn't counted when the user downloaded his big attachments from the webmail, or when he downloads something from a php script, or whatever.
My point is that IMHO those very edge cases should be completely ignored: you putted a 1Gb file in your website, someone hitted it, it got counted. I'm sorry man but it goes this way. Otherwise we're going to get crazy trying to match every bit of traffic...

(04-29-2009 02:44 AM)sci2tech Wrote:  It`s not true. Traffic is aggregated per day. See line 442 to 458 in ispcp-vrl-traff.

Ooops! I was looking at the file form my old RC5 version ...

[sci2tech]

(04-29-2009 04:44 AM)kilburn Wrote:  Ok, but just for curiosity: why do you need to instantiate sql connection?

I did question myself about this and I can only guess why the original author did it: For debugging purpose, if it fail to connect to mysql, this will happend in startup section. It can be safely removed, so I`ll remove it.

(04-29-2009 04:44 AM)kilburn Wrote:  ...now imagine all the traffic that isn't counted when the user downloaded his big attachments from the webmail, or when he downloads something from a php script, or whatever.

Well all this traffic will be assigned, but not all the time to correct user. This is really something to be discussed, maybe we can find a solution for this. Being possible to use webftp and webmail from gui (master domain) allow users to bypass some traffic count.

[must]

sorry if i write this without reading all the thread, but i'm not a programmer (so couldn't help anyway) and i'm not even particularly interested in traffic counting.

but still, i see a big problem, related to this topic, as kilburn wrote on first post: the domain_traffic table becomes HUGE. i have over 3M records in that table, and i think there's a relation to its size (not even useful in my situation) and the awful performance i have on my server. it often gets stuck, with incredibly high load, and according to mytop, there's always something on this table when my server has problems.

cheers

[kilburn]

You should read the thread even if you aren't a programmer: this had been corrected some time ago. Which version are you using?

[must]

some time ago? though this thread has been started less than 10 days ago?

anyway:

BuildDate = 20090225

Version = 1.0.0 OMEGA

(but came from an upgrade from VHCS - and from a different server: yes, kinda undocumented migration)