The Strange Story of the Bandwidth Overage Charge - part 2

To combat the 1TB of overage caused by MySQL, I was going to move a subset of the databases over to the webserver,

Should have been pretty uneventful. An earlier version of MySQL 5 has been installed around 2 years ago, so I upgraded it and set about to import the data.

The dump came from mysqldump and ended up being 173MB in total. It’s format did not work with mysqlimport though, so I used the GUI admin browser. It took 30 minutes to import 800K! Yeah right. The command line client took 4 seconds. Sweet.

Moving on, the CMS (thankfully) only kept it’s database config in one file. Updated that, set up a backup task for the database, and turned it on. Load the CMS site and…

Error:Incorrect integer value: ” for column ‘id’ at row 1

Okay….

After much googling on this one, we can blame the poorly written, closed source CMS for this. It appears that MySQL 5 (sanely) will not let you set an integer column to a blank string. MySQL 4 will autoconvert, 5 won’t. Since the original db server ran MySQL 4 I had to enable a different SQL mode on the new host.

Removing STRICT_TRANS_TABLES from my.ini did the trick, and the CMS site loaded….like a glacier. Ouch. So, I enabled query_cache, tweaked some variables, and restarted. Much better.

With that yak shaved I could focus on the bandwidth issues. I’ll let ntop speak here:

Before:

After:

I know it’s a small sample time, but wow - from a peak of 28Mbps to 20Kbps. Intense!

To wrap this up some more supporting applications needed their database moved to the new host, and their configs updated, but that wasn’t a big deal. I am concerned about the Windows host becoming overloaded now, but short of getting an entire new server provisioned for it (ughhhh…) it will have to do.

So, what did we learn?

1. Put your database server on an unmetered subnet, or use something like Amazon EC2 (they don’t bill inter-cloud bandwidth)
2. Monitor bandwidth to avoid a surprise.
3. Query cache is good as long as your SQL supports it. Our CMS puts some weird timestamp limits in some queries, but for the most post it’s a SELECT monster which cache’s nicely.
4. The CMS was more than just a subset of the traffic - it was the traffic.