Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

In the field of Digital forensic , a packet size of 1GB is very common and I am getting trouble.

Using in-house python script , we extract text out from archives , some files are as big as 4 GB and text inside it can easily reach 1GB . and then we use Sphinxsearch to make it searchable .

So now i am facing packet Packet too large Errors when inserting those text into database.

Machine is 64 core with 64 GB bit of ram , can easily handel Terrabytes.

So what should i do?

Another question is , Does increasing max_allow_packet also increases memory usage, during < 1GB packets?

OS : Linux 2.6.39 Mysql : 5.1 and 5.5

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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 !!!

share|improve this answer

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.