The Strange Story of the Bandwidth Overage Charge - part 1

A client’s hosting company gives 2TB of bandwidth per server, but they got hit with a 1.3TB overage last month. That seems strange seeing how the sites hosted are low traffic sites - definitely not 3.3TB worth of usage.

Even stranger is the host with the overage is an older MySQL/FTP only server, no Apache or anything else. Strangest of all is that he RRDTool bandwidth graph provided by the hosting company shows 3.1TB data in, 200MB out! What?!

After ruling out any exploits or incoming DOS, I installed vnstat, ntop, and darkstat to let them log for a while.

Meanwhile, tcpdump showed a constant stream of MySQL traffic between the database server and the main web server. A quick look at vnstat (since it could provide the quickest answers) showed the exact opposite of the hosting co’s RRD graph - 10 times the data going out, not in - over the next hour.

The next day, all tools reported different results. darkstat showed that incoming MySQL traffic to be around 1/100th of the outgoing traffic. This makes sense since the db server is basically a massive SELECT box. darkstat’s hosts section though reported a near equal amount of traffic in and out between the web and db server.

ntop returned similar results.

This is quite disturbing because after a few days of logging, the bandwidth graphs do not correlate to the usage generated by RRD (which is what they bill by). So, what to do? RRD bandwidth graphs for the web server show a strongly correlation between amount of traffic going out and what the db server says is incoming. Hosting co says they monitor and bill traffic at the switch. I couldn’t get the numbers to jive, but had enough of a clue to make a hypothesis/action plan before they get another overage bill:

1. TCP/IP overhead between the web/db server is causing the most traffic, or at least enough to push it over.
2. The poorly written, closed-source CMS the web server runs is making inefficient use of the database.
3. Due to some proprietary Windows software, the web server must stay Windows, so the database for the CMS needs to be moved to the web server.

(They offer a private network connection between 2 hosts that would normally be the Right Thing, but several factors prevent it from being used for this client. Not an option in this case.)

With a hypothesis in hand and a plan in mind, I set about to do the unbearable - migrate a super-stable Debian based database server to a 4 year old Windows server.

[Part 2 will cover getting MySQL running on the Windows server, dumping the database from the existing db server, and reconfiguring the CMS/web server to use the new local db.]