Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps of the database, then restoring them periodically to either a staging server we have or to my MacBookPro.

We recently had some trouble restoring with errors related to the packet size. Late last week, I changed the max_packet_size setting within automysqlbackup to 1MB (it was unspecified). After that change, the database backups stop at the table with the blob column. Actually, it stopped with the first record in that table. Do I have to set the max_packet_size setting to be at LEAST as large as the largest blob stored in that column?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

I saw an interesting answer to a question about the biggest blob you may have. Here is the statement I saw in ServerFault : innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones.

Based on that ServerFault post from Nils-Anders Nøttseter, you should query the table and find out which BLOB is the biggest, multiply that number by 11 or more, and use that answer as the max_allowed_packet going forward.

It's funny that I addressed another question where I suggested sizing the max_allowed packet to hopefully solve the issue.

CAVEAT

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Knowing this about MySQL Packets allows a DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large.

In the past, I have had issues like this with MySQL Replication where the Master had a smaller value for the max_allowed packet than the Slave. It required me to bump up max_allowed_packet to 256M and restart mysql on the Master. In 99% of issues I have had, this would solve it. A few times I had to set to 512M.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.