September 2, 2014

New variable slave_max_allowed_packet for slave servers

One month ago I wrote about how a big read_buffer_size could break the replication. The bug is not solved but now there is an official workaround to ease this problem using a new configuration variable:

slave_max_allowed_packet

This new variable will be available in 5.1.64, 5.5.26, and 5.6.6 and can establish a different limit on the max_allowed_packet for the slave servers (IO Thread). Now on a slave server the maximum size of a packet is checked against this variable and not max_allowed_packet.

The default value is 1GB and that means if we don’t tune the variable our I/O thread can read up to that amount of data. This is important if we use the binary logs for PITR. This variable solves only one part of the problem, the slave doesn’t stop working but binary log events can still be bigger than max_allowed_packet. During the recovery process the slave_max_allowed_packet is not going to help us and our recovery process could fail.

Conclusion

Now we have two workarounds for the same problem. First, use a small value for your read_buffer_size (less than max_allowed_packet) and the second one, upgrade to a version that has slave_max_allowed_packet.

Training

In September I’m going to deliver MySQL Training (Sep 3-6) in Madrid. This is the first time we are going to deliver the training in Spanish :) If you’re interested just click on the previous link to get more information.

En Septiembre voy a impartir Formación de MySQL en Madrid (Sep 3-6). Está será la primera vez que se dará la formación en Español :) Si estás interesado haz click en el enlace anterior para tener más información.

About Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow. Miguel's roles inside Percona are Senior Support Engineer and Manager of EMEA Support Team.

Comments

  1. Antoine LUCAS says:

    I just got the update today, and I was surprise to see my replication broken with this error :

    Got a packet bigger than ‘slave_max_allowed_packet’ bytes

    And my slave_max_allowed_packet is already set to the maximum (1G) . I have no idea how to override this setting.

  2. Seems to be a bug, if you have binlog_format=STATEMENT (and probably MIXED as well) MySQL still looking to max_allowed_packet but it report on slave_max_allowed_packet http://bugs.mysql.com/bug.php?id=69104

Speak Your Mind

*