Friday, April 11, 2008

MySQL, eat your food! Come on... eat it!

OK - a word to those as wise as I was previously to encountering this issue:

Do you have text blobs bigger than a few MBs that you'd like to log to the database? Guess who did? Here's a great thing to know about that:

max_allowed_packet - Maximum allowed packet size. In other words - if your query is bigger than this number, MySQL will clench it's little lips up and spit it's food back in your face (or is that my 1-year-old daughter?). This variable defaults to 1MB. You can set it up to 1GB.

Now for the fun part - since I didn't know about this value (nor that the data I was logging was going to get so big) mysql wouldn't take my 20MB+ insert query. Then, the ExceptionNotifier plugin (the helpful plugin that tells you when you have errors, and tells you what the errors were about) wanted to be kind enough to tell me about it. Not only did it try and email the failed query, but it multiplied it a few times, and spit out all kinds of other information. This ultimately sent our server into a tailspin and ate up all the cache. We had to call Rack-Space and ask them to get us back up and running again.

Moral of the story? Well, if you're gonna feed MySQL a 20MB chunk of data, make sure you set the max_allowed_packet variable higher. And, watch out for ExceptionNotifier. I've got a giant TODO on my list of things to do to go and patch ExceptionNotifier to prevent that from happening again.

Until then, may you be wiser than I was.

1 comment:

Roger Pack said...

definitely submit that patch :)