28 November, 2006

Database Relief

So, I've been battling with a database problem for a while now. I have this table that records all the traffic to every website we host at work. Needless to say, this grows. Quickly. At a rate averaging about.. ehhh... 35k hits/day.

Now, thanks to the wonderfully forward thinking engineers in this position before me, we have run into a built-in limitation of MySQL. By default, when you create a table, it's built with the ability to grow up to a size of 4gb. Well with as much traffic as we get here, that 4gb point has quite obviously come and gone. In just over a year worth of traffic. Brilliant.

I have finally found a solution to get us by for now though, as opposed to deleting old traffic records for inactive accounts. (I hate deleting archived data. Always bad.) One little query has made my world all better:
ALTER TABLE traffic MAX_ROWS=1000000000;

Yes, that is 1,000,000,000. One billion. We're at around 12.4 million records in that table right now. I figure if we get to a billion, I'll be retired on a tropical island, sipping on drinks with little umbrellas in them. Now, in the process of rebuilding this table with the greatly increased row limit, MySQL went ahead and recalculated the maximum amount of disk space the table could take up. The new number it came up with is 281474976710655 bytes. That comes out to exactly 256tb. Two hundred and fifty six TERABYTES. Yeah. Drinks with little umbrellas. Definitely.

1 comment:

Anonymous said...

ooooh i love little umbrellas!