Mysql – ‘packet too large’ error with packet smaller than max_allowed_packet

The first I thought about was what max_allowed_packet actually controls. Here is what I found:

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.

Given that definition of max_allowed_packet, I then discovered something else from 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

Keeping these two things in mind, I would increase innodb_log_file_size in /etc/my.cnf to the max size allowed for it, 2047M. This of course requires the following

service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

This will accommodate any big blobs you may have in your data.

You need to tune your InnoDB settings in the following areas:

• Make InnoDB access all your cores
• Increase innodb_buffer_pool_size to 12G
• Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
• Increase Log File Size (innodb_log_file_size) to 2047M
• support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)
• support either high performance or high durability (ACID Compliance)
  • High Performance : innodb_flush_log_at_trx_commit set to 0 or 2
  • High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)
  • Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
  • Your current setting for innodb_flush_log_at_trx_commit is good
  • Your current setting for innodb_flush_method is good
• Increase innodb_read_io_threads to 64
• Increase innodb_write_io_threads to 64
• Increase innodb_io_capactity to 10000

Here are my past posts on tuning the InnoDB storage engine

• Is the CPU performance relevant for a database server? (Apr 26, 2012)
• Why does InnoDB store all databases in one file? (Mar 25, 2012)
• Multi cores and MySQL Performance (Sep 20, 2011)
• Possible to make MySQL use more than one core? (Sep 12, 2011)
• About single threaded versus multithreaded databases performance (May 26, 2011)
• How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)
• How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)
• Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)