mysql Writing to net & max_allowed_packet

mysql> show processlist;......| 196875 | root | 192.168.60.150:38098 | shanghai_test            | Query   |     0 | NULL           | INSERT INTO gcjl_hphm_info (hphm, hpzl, hpys, clpp, clzpp, clnk, cllx, hash_key) values ('沪LE9578'  || 218956 | root | localhost            | trans_tmp                | Query   |     0 | NULL           | show processlist                                                                                     || 220087 | root | 192.168.60.150:34678 | shanghai_test            | Sleep   |  8583 |                | NULL                                                                                                 || 221206 | root | 192.168.60.150:43724 | trans_tmp                | Query   |  5051 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `gcjl_hphm_info`                                              |+--------+------+----------------------+--------------------------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+

可以看到有Writing to net，这个是查询数据太快返回网络回写不赢。

上面的情况是由脚本中下面的一条语句引起的

mysqldump -h${db_ip[1]} -P${db_port[1]} -u${db_user[1]} --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --no-tablespaces  --complete-insert  --no-create-db=TRUE --no-create-info=TRUE --skip-quote-names --max-allowed-packet=4096 --net_buffer_length=4096 ${tmp_dbname} gcjl_hphm_info|grep -v ^/|mysql -h${opaq_id} -P${opaq_port}

所以Writing to net，在这个例子中的根本原因是后面的消费者消费的慢，导致生产者的写网络的时候网络写不赢。

max_allowed_packet

 可以 通过增加包的大小的改善下，默认max_allowed_packet只有1M

官方对max_allowed_packet的解释

max_allowed_packetCommand-Line Format--max_allowed_packet=#System VariableNamemax_allowed_packetVariable ScopeGlobalDynamic VariableYesPermitted ValuesTypeintegerDefault1048576Min Value1024Max Value1073741824The maximum size of one packet or any generated/intermediate string.The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.The session value of this variable is read only.