12

I need to know some ball park number for this variable for my drupal 7 site. I recently got a "drupal "PDOException: SQLSTATE[08S01]: Communication link failure: 1153" error. I am a newbie to mysql and am using shared hosting. I probably need to ask the administrator to do it for me.

Thanks in advance.

  • 1
    My answer shows my experience with this, but you may get better responses at dba.stackexchange.com from those who maintain MySQL for a living. – mpdonadio May 22 '12 at 19:47
12

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 Book

TheBookImage

Here is what Page 99 Paragraphs 1-3 says about 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 MySQL Packet, even if they are obnoxiously large.

Regarding your situation, you should find out what is the biggest BLOB in your database, multiple that number by 11 and set your max_allowed_packet to that number. You should be able to set it for the server without a mysql restart (Personally, I would set it 256M because it would address other problems regarding migration and replication, which is beyond the scope of this forum). To set it to 256M for your database for all incoming connections, please run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;

Afterwards, add this setting to my.cnf under the [mysqld] section:

[mysqld]
max_allowed_packet = 256M
  • Wouldn't max allowed packet also affect the size of insert statements? – Buttle Butkus Mar 15 '14 at 1:42
  • @ButtleButkus Of course. This is why bigger MySQL Packets do well for INSERTs. Please note that 1G is the maximum size for max_allowed_packet. Also note that setting max_allowed_packet to 256M does not allocate 256M upfront. According to dev.mysql.com/doc/refman/5.6/en/…, mysqld allocates whatever net_buffer_length is as the initial MySQL Packet size. The packet gradually grows to the size set by max_allowed_packet. Therefore, it's OK to set a large max_allowed_packet. INSERTs will love you for it. – RolandoMySQLDBA Mar 15 '14 at 2:29
  • I've never set it anything close to that size, even though we have a couple machines with 32G of RAM. An application choked on it, though, and I increased it to 32M from the default (1M?) and that worked. Perhaps I'll increase it further, if it won't hurt. But, should applications check the max_allowed_packet and break up inserts into chunks to avoid hitting that limit? What's the downside to that approach? Perhaps I should post that question on the dba stack site. – Buttle Butkus Mar 17 '14 at 8:27
  • Could you elaborate a bit more on why we should multiply by 11? The linked post is about innodb buffers - how does is that related to packet size? – SystemParadox Jan 11 '18 at 14:35
4

Taken from Drupal's Database Server Requirements page:

It may be necessary to set the system variable max_allowed_packet to at least 16M. Some inexpensive hosting plans set this value too low (the MySQL default is only 1M). In that case, you may need to choose a better hosting plan. A value of 1M may be sufficient for Drupal 5.

Should be fine for a site on shared hosting.

  • 1
    Wow!! Drupal server requirements officially say it's 16M!! I was never aware of that. Will keep it in mind next time. Thanks a lot for sharing this info. I wish I could mark it the best answer!! – shivams Oct 17 '14 at 5:14
2

It can be a little tricky to set max_allowed_packet. I have yet to come across a method to calculate out that value. Typically, I run into it when trying to restore a database snapshot that includes rows from the {cache} tables.

When I have this problem, I will pick a value between 4M and 8M. I keep bumping up the value until it stops. However, I don't know if a shared host will change this value for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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