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_packet
Command-Line Format	--max_allowed_packet=#
System Variable	Name	max_allowed_packet
Variable Scope	Global
Dynamic Variable	Yes
Permitted Values	Type	integer
Default	1048576
Min Value	1024
Max Value	1073741824

The 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.