The origin of the alliterative phrase “proper preparation prevents poor performance” is unknown to me, but I know where I first heard it. My fifth grade teacher in Catholic grade school ground that mantra into our heads. Combine it with the Boy Scouts’ “Be Prepared” motto, and you should have a recipe for success.
Except that there are times when even proper preparation misses a step. It’s all part of the learning process. So it is that I humble myself to the great MySQL prep gods and bow in deference to those who came before me writing documentation so that I would not repeat the same mistake.
Alas, I skipped that page and had the pleasure of fighting with the default (nuisance) 4GB limit on MySQL tables. Thanks to my negligence, I ended up with a spastic database complaining that the table was full.
Indeed it was. Thankfully, back in 2003, a kind man by the name of Jeremy Zawodny posted an article addressing this very issue.
> (After having explained this for the 35th time, I decided it’s time to simply put something on-line.)
> When a MyISAM table grows large enough, you’ll encounter the dreaded “The table is full” error. Now I could simply point at that page and leave this subject alone, but there’s more to this story.
> When this happens, the first reaction I hear is “You never told me that MySQL has a 4GB limit! What am I going to do?” Amusingly, I usually do describe the limit when I discuss the possibility of using MySQL with various groups–they often forget or underestimate the impact it will have. Putting that aside, the problem is easily fixed, as that page explains. You simply need to run an ALTER TABLE command.
> And you’ll need to wait. That ALTER TABLE is going to take some time. Really.
Reading that last line put the fear into me. I had visions of hours upon days of downtime. This was a large database. Not humongous, but possibly corrupt at this point. Lucky for me, it only took just under and hour total for a repair on the table and an increase to the MAX_ROWS.
Jeremy sums up the theme of this post.
> To protect yourself in the future, use the MAX_ROWS and AVG_ROW_LENGTH options at CREATE TABLE time if the table is likely to get big.
Ah, yes.
Preparation
What a concept.