Mysql – Got timeout reading communication packets vs Got error reading communication packets

MySQLtimeout

I felt "Got error reading communication packets" is related to max_allowed_packet variable and "Got timeout reading communication packets" is related 'net_read_timout' parameter or network issues.

Let me know whether my understanding is correct or not.

Thanks in advance.

Best Answer

For my situation, the master met with the error timeout, but the slave run the same sql successfully. The sql is about load data, so the master actually finished reading the whole packet?

Related Solutions

Mysql – Aborted connection (Got an error reading communication packets) MySQL on Amazon RDS

Since you tried the above steps, your sticking point seems to be the one you can't alter.

I wrote a post back in August 2012 (Local database vs Amazon RDS), mentioning how the number of DB connections and the Buffer Size are fixed and immutable for each in RDS server model. I also mentioned that the Transactions Logs are fixed at 128M, no matter what RDS server model you use.

SUGGESTION: Be kind to yourself. Migrate the DB from RDS to EC2. Then, you have the necessary flexibility to change the InnoDB Infrastructure. Consequently, you will have to write your own backups and setup replication by hand.

I know you said going to EC2 is your last resort. Since you cannot tune InnoDB effectively in an RDS environment, EC2 is the next logical step.

Mysql : Aborted connection …/… (Got an error reading communication packets)

You are running MySQL 5.7 with PHP on the one server. This shows me that your server is not dedicated to MySQL. PHP and MySQL are evidently competing for RAM.

I also do not see any attempt to set the InnoDB buffer pool size. The default value for innodb_buffer_pool_size is 128M (134217728).

Please keep in mind something about max_allowed_packet. As the variable name suggests, it is the maximum size allowed for a MySQL Packet. According the MySQL Documentation, the MySQL Packet does not start out as 1G. Its initial size is based on the value of net_buffer_length. The MySQL Packet will then dynamically expand to max_allowed_packet as needed.

Your attempts to tune around the MySQL Packet will be nullified by a lack of RAM.

SUGGESTION #1

Install PHP on another server that can be a dedicated web server.
Increase the amount of RAM on the DB Server

SUGGESTION #2

If you cannot afford another server, then just increase RAM and the Buffer Pool size.

SUGGESTION #3 (OPTIONAL)

Since you are using MySQL 5.7, you can expand the Buffer Pool dynamically.

Do the following to set it to 2GB:

mysql> SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 2;