If max_allowed_packet is maxed out at 1G and you are still having issues, there is really only two places to look:
- innodb_log_buffer_size : The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.
- innodb_log_file_size : The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.
Step 01 : Add these to /etc/my.cnf
[mysqld]
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M
Step 02 : Run this in mysql
mysql> SET GLOBAL innodb_fast_shutdown = 0;
Step 03 : Shutdown mysql
systemctl stop mysqld
Step 04 : Move the old logs aside
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
Step 05 : Start mysql
systemctl start mysqld
That’s it.