技術分享 | 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

##匯入報錯
[[email protected] 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大小的資料
[[email protected] 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/秒
[[email protected] 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,讀取成功
[[email protected] ~]# mysql -h127.0.0.1 -P13306 -uroot -proot --max-allowed-packet=23068672 sbtest -e "select * from t1;" > /tmp/t1.txt

[[email protected] ~]# 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)

[[email protected] ~]# 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....

相關文章