技術分享 | MySQL：max_allowed_packet 影響了什麼？

愛可生雲資料庫發表於2022-07-18
MySql

作者：胡呈清

愛可生 DBA 團隊成員，擅長故障分析、效能優化，個人部落格：https://www.jianshu.com/u/a95...，歡迎討論。

本文來源：原創投稿

*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

max_allowed_packet 表示 MySQL Server 或者客戶端接收的 packet 的最大大小，packet 即資料包，MySQL Server 和客戶端上都有這個限制。

資料包

每個資料包，都由包頭、包體兩部分組成，包頭由 3 位元組的包體長度、1 位元組的包編號組成。3 位元組最多能夠表示 2 ^ 24 = 16777216 位元組（16 M），也就是說，一個資料包的包體長度必須小於等於 16M 。

如果要傳送超過 16M 的資料怎麼辦？

當要傳送大於 16M 的資料時，會把資料拆分成多個 16M 的資料包，除最後一個資料包之外，其它資料包大小都是 16M。而 MySQL Server 收到這樣的包後，如果發現包體長度等於 16M ，它就知道本次接收的資料由多個資料包組成，會先把當前資料包的內容寫入緩衝區，然後接著讀取下一個資料包，並把下一個資料包的內容追加到緩衝區，直到讀到結束資料包，就接收到客戶端傳送的完整資料了。

那怎樣算一個資料包？

  • 一個 SQL 是一個資料包
  • 返回查詢結果時，一行資料算一個資料包
  • 解析的 binlog ，如果用 mysql 客戶端匯入，一個 SQL 算一個資料包
  • 在複製中，一個 event 算一個資料包

下面我們通過測試來討論 max_allowed_packet 的實際影響。

匯入 SQL 檔案受 max_allowed_packet 限制嗎？

如果 SQL 檔案中有單個 SQL 大小超過 max_allowed_packet ，會報錯：

##匯出時設定 mysqldump --net-buffer-length=16M，這樣保證匯出的sql檔案中單個 multiple-row INSERT 大小為 16M
mysqldump -h127.0.0.1 -P13306 -uroot -proot --net-buffer-length=16M \
--set-gtid-purged=off sbtest sbtest1 > /data/backup/sbtest1.sql

##設定max_allowed_packet=1M

##匯入報錯
[root@localhost data]# mysql -h127.0.0.1 -P13306 -uroot -proot db3 < /data/backup/sbtest1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1153 (08S01) at line 41: Got a packet bigger than 'max_allowed_packet' bytes

匯入解析後的 binlog 受 max_allowed_packet 限制嗎？

row 格式的 binlog，單個SQL修改的資料產生的 binlog 如果超過 max_allowed_packet，也會報錯。

在恢復資料到指定時間點的場景，解析後的binlog單個事務大小超過1G，並且這個事務只包含一個SQL，此時一定會觸發 max_allowed_packet 的報錯。但是恢復資料的任務又很重要，怎麼辦呢？可以將 binlog 改名成 relay log，用 sql 執行緒回放來繞過這個限制。

查詢結果受 max_allowed_packet 限制嗎？

查詢結果中，只要單行資料不超過客戶端設定的 max_allowed_packet 即可：

##插入2行20M大小的資料
[root@localhost tmp]# dd if=/dev/zero of=20m.img bs=1 count=0 seek=20M
記錄了0+0 的讀入
記錄了0+0 的寫出
0位元組(0 B)已複製，0.000219914 秒，0.0 kB/秒
[root@localhost tmp]# ll -h 20m.img
-rw-r--r-- 1 root root 20M 6月   6 15:15 20m.img

mysql> create table t1(id int auto_increment primary key,a longblob);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(NULL,load_file('/tmp/20m.img'));
Query OK, 1 row affected (0.65 sec)

mysql> insert into t1 values(NULL,load_file('/tmp/20m.img'));
Query OK, 1 row affected (0.65 sec)

##mysql客戶端預設 --max-allowed-packet=16M,讀取失敗
mysql> select * from t1;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

##設定 mysql 客戶端 --max-allowed-packet=22M，讀取成功
[root@localhost ~]# mysql -h127.0.0.1 -P13306 -uroot -proot --max-allowed-packet=23068672 sbtest -e "select * from t1;" > /tmp/t1.txt

[root@localhost ~]# ll  -h /tmp/t1.txt
-rw-r--r-- 1 root root 81M 6月   6 15:30 /tmp/t1.txt

load data 檔案大小受 max_allowed_packet 限制嗎？

load data 檔案大小、單行大小都不受 max_allowed_packet 影響：

##將上一個測試中的資料匯出，2行資料一共81M
mysql> select * into outfile '/tmp/t1.csv' from t1;
Query OK, 2 rows affected (0.57 sec)

[root@localhost ~]# ll -h /tmp/t1.csv
-rw-r----- 1 mysql mysql 81M 6月   6 15:32 /tmp/t1.csv

##MySQL Server max_allowed_packet=16M
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|             16777216 |
+----------------------+
1 row in set (0.00 sec)

##load data 成功,不受 max_allowed_packet 限制
mysql> load data infile '/tmp/t1.csv' into table t1;
Query OK, 2 rows affected (1.10 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

binlog 中超過 1G 的 SQL ，是如何突破 max_allowed_packet 複製到從庫的？

從庫 slave io 執行緒、slave sql 執行緒可以處理的最大資料包大小由引數 slave_max_allowed_packet 控制。這是限制 binlog event 大小，而不是單個 SQL 修改資料的大小。

主庫 dump 執行緒會自動設定 max_allowed_packet為1G，不會依賴全域性變數 max_allowed_packet。用來控制主庫 DUMP 執行緒每次讀取 event 的最大大小。

具體可以參考：
https://mp.weixin.qq.com/s/Ef...

另外超過 4G 的大事務，從庫心跳會報錯：
https://opensource.actionsky....

相關文章