Mysql性能調優之max_allowed_packet使用及說明

Posted on by

Mysql max_allowed_packet使用

max_allowed_packet是什麼？

指mysql服務器端和客戶端在一次傳送數據包的過程當中最大允許的數據包大小。

什麼情況下遇到？

有時候大的插入和更新會被max_allowed_packet 參數限制掉，導致失敗。

  • 場景一：將本地數據庫遷移到遠程數據庫時運行sql錯誤。錯誤信息是max_allowed_packet
  • 場景二：插入數據時某個字段數據過於龐大(使用Elmentui編輯器自帶的圖片加密，圖片過多，地址超級長，最好用的時候改成自定義的)，會報

Packet for query is too large (20682943>1048576). You can change this value on the server by setting the max_allowed_packet’ variable.

解決辦法？

調整mysql的配置文件

mysql 56中該參數修改好像無效，所以需要升級數據庫到mysql57

window下修改配置文件my.ini 在mysqld段下添加

 max_allowed_packet = 64M

後面的數字根據實際情況調優

linux下修改etc/my.cnf ,同樣在mysqld段下添加

 max_allowed_packet = 64M

註意改完參數後需要重啟mysql服務

查看目前配置

show VARIABLES like '%max_allowed_packet%';

寫入Mysql報錯超出max_allowed_packet

Mysql會根據配置文件會限制server接受的數據包的大小。

如果寫入大數據時，因為默認的配置太小，插入和更新操作會因為 max_allowed_packet 參數限制，而導致失敗。

查看當前配置

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)

也可以用select查看

mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|              4194304 |
+----------------------+
1 row in set (0.00 sec)

mysql> 

max_allowed_packet 如果不設置，默認值在不同的 MySQL 版本表現不同，有的版本默認1M，有的版本默認4M。

修改方法1(配置文件持久化修改)

vim /etc/my.cnf
[mysqld]
max_allowed_packet = 100M

註意：修改配置文件以後，需要重啟mysql服務才能生效。

mysql> show variables like '%max_allowed_pack%';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 104857600 |
+--------------------+-----------+
1 row in set (0.00 sec)

修改方法2(命令行臨時修改)

mysql> set global max_allowed_packet = 100 * 1024 * 1024;
mysql> exit
[root@localhost opt]# 
[root@localhost opt]# mysql -uroot
mysql> 
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|            104857600 |
+----------------------+
1 row in set (0.00 sec)

mysql> 

註意：

1.命令行修改時，不能用M、G，隻能這算成字節數設置。配置文件修改才允許設置M、G單位。

2.命令行修改之後，需要退出當前回話(關閉當前mysql server鏈接)，然後重新登錄才能查看修改後的值。通過命令行修改隻能臨時生效，下次數據庫重啟後又復原瞭。

3.max_allowed_packet 最大值是1G(1073741824)，如果設置超過1G，查看最終生效結果也隻有1G。

[mysqld]
max_allowed_packet = 1G

mysql> show variables like '%max_allowed_pack%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：