★ Proper Preparation Prevents Poor Performance

The origin 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 suc­cess.
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 mis­take.

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 hav­ing explained this for the 35th time, I decided it’s time to sim­ply put some­thing on-line.)

> When a MyISAM table grows large enough, you’ll encoun­ter the dreaded “The table is full” error. Now I could sim­ply point at that page and leave this sub­ject alone, but there’s more to this story.

> When this hap­pens, the first reac­tion I hear is “You never told me that MySQL has a 4GB limit! What am I going to do?” Amusingly, I usu­ally do describe the limit when I dis­cuss the pos­si­bil­ity of using MySQL with var­i­ous groups–they often for­get or under­es­ti­mate the impact it will have. Putting that aside, the prob­lem is eas­ily fixed, as that page explains. You sim­ply need to run an ALTER TABLE com­mand.

> 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 pro­tect your­self 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 con­cept.