Tuning max_allowed_packet variable

Basic Details

The max_allowed_packet is the maximum size of a MySQL network packet that can be read or created by the server. The parameter comes with a default, minimum, and maximum values that you can change according to your requirements.

Releem automatically tunes 44 variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.

max_allowed_packet – Usage

There are several ways of changing the max_allowed_packet value. It is primarily set to the default value, which is 16MB.

To change it, follow this:
$> mysql --max_allowed_packet=32M

This will set the client's value to 32 MB. You can change the value according to your requirements.

Changing the server value is also important, especially when you are dealing with large queries. Usually, the server's default value is 16 MB.
Let's double the server's value, as we did to the client's program:
$> mysqld --max_allowed_packet=32M

Lastly, you can also set the value using an configuration file. Here's how it is done:

This will change the server size to 32 MB, thus enabling it to transfer larger queries without any error.

When is max_allowed_packet Changed?

So, what's the need for changing max_allowed_packet? When a client server receives larger packet bytes, an error occurs. The connection faces ER_NET_PACKET_TOO_LARGE error and closes instantly.

Another error can be Lost Connection to Server During Query Error. It has the same reason, i.e., a large communication packet.

This happens when the set value for max_allowed_packet is less than the size of uploading files. To perform the transaction successfully, you would have to change the value in both client and server.
Releem automatically detects MySQL performance degradation and optimizes MySQL configuration files