MySQL : max_allowed_packet Greater than 1 GB is it possible?

Before altering the max_allowed_packet, first let us define it.

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

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.

In light of this book excerpt and the MySQL Documentation on max_allowed_packet, there is basically nothing you can do with surpassing 1G for max_allowed_packet. However, there is still one more aspect to explore concerning BLOB and Text Data tuning.

Another question in Server Fault made the following assertion with regard to InnoDB and BLOBs: 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. If you don't (and you shouldn't [1,2]), there is really no need to bother a lot with it. Check MySQL Performance Blog for a detailed report on how to calculate.

There is still another aspect to think about: The option net_buffer_length (default 16K) is used as the initialize size of the MySQL Packet. The packet can dynamically expand to max_allowed_packet. It can inevitably shrink back to the size specified by net_buffer_length. The max values of net_buffer_length is 1M. You may want to set the value of this to 1M. May not help much if you are cranking through 1G, but it would not hurt either.

If you really want a MySQL Packet bigger than 1G, the book excerpt I quoted tells you what source code to use that defines MySQL Packet internals. You are free to try to raise the limit. However, the source code may have internal limits of its own if the code assumes 1G aside from explicit numbers being set.

I hope this info helps !!!