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

2022-11-25 14:01:30

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)

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。