Proper Preparation Prevents Poor Performance

The ori­gin of the allit­er­a­tive phrase “proper prepa­ra­tion pre­vents poor per­for­mance” 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 prepa­ra­tion misses a step. It’s all part of the learn­ing process. So it is that I hum­ble myself to the great MySQL prep gods and bow in def­er­ence to those who came before me writ­ing doc­u­men­ta­tion so that I would not repeat the same mistake.

Alas, I skipped that page and had the plea­sure of fight­ing with the default (nui­sance) 4GB limit on MySQL tables. Thanks to my neg­li­gence, I ended up with a spas­tic data­base com­plain­ing that the table was full.

Indeed it was. Thankfully, back in 2003, a kind man by the name of Jeremy Zawodny posted an arti­cle address­ing 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 down­time. This was a large data­base. Not humon­gous, but pos­si­bly cor­rupt 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.


What a concept.