單條記錄大小增長倍數和ibd檔案大小的增長倍數不成正比

GreatSQL發表於2024-08-16

單條記錄大小增長倍數和ibd檔案大小的增長倍數不成正比

環境資訊

資料庫版本: GreatSQL 8.0.25

字符集：utf8mb4

innodb_default_row_format: dynamic

innodb_page_size: 16384

問題描述

表資料為新insert資料，無delete、無update

GreatSQL 一個資料量為1萬的A表，有100個varchar欄位，每個欄位存10位元組，ibd大小為21M

GreatSQL 一個資料量為1萬的B表，有100個varchar欄位，每個欄位存100位元組，ibd大小為4.7G

問題：相同資料量，相同資料量，B表的每行比A表大10倍，磁碟使用大小不是10倍，而是200多倍？

greatsql> show create table t_user_100_1000_100  \G;
*************************** 1. row ***************************
       Table: t_user_100_1000_100
Create Table: CREATE TABLE `t_user_100_1000_100` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c_name1` varchar(10) NOT NULL DEFAULT '',
。。。
  `c_name100` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

greatsql> show create table t_user_100_10000_100  \G;
*************************** 1. row ***************************
       Table: t_user_100_10000_100
Create Table: CREATE TABLE `t_user_100_10000_100` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c_name1` varchar(100) NOT NULL DEFAULT '',
。。。
  `c_name100` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

greatsql> select count() from t_user_100_10000_100 ;
+----------+
| count() |
+----------+
| 10000 |
+----------+
1 row in set (0.06 sec)

greatsql> select count() from t_user_100_1000_100 ;
+----------+
| count() |
+----------+
| 10000 |
+----------+
1 row in set (0.18 sec)

#os ibd 檔案大小
ll
total 4313096
-rw-r----- 1 mysql mysql 5016387584 Apr 9 18:52 t_user_100_10000_100.ibd
-rw-r----- 1 mysql mysql 20971520 Apr 9 18:40 t_user_100_1000_100.ibd

greatsql> select 5016387584/20971520 from dual;
+---------------------+
| 5016387584/20971520 |
+---------------------+
| 239.2000 |
+---------------------+
1 row in set (0.00 sec)

問題分析

多出來的24倍難道是碎片導致的？

使用optimize table重整表後，幾乎沒有任何最佳化，檢視系統檢視，也沒有多少空洞。

file

使用官方工具innochecksum檢視錶空間檔案PAGE型別分佈，可以看到，B表相對A表多了大量的Other type of page。看來主要的空間消耗是在這個“Other”上。

file

  • INNODB的行溢位

INNODB預設下每個PAGE的大小為16K。B表每行10K，每個PAGE只能存放1行記錄，餘下的6K就浪費了。但即使按照這個演算法，也只浪費了37%的空間。

實際上，INNODB在這裡有個處理，當記錄過大，會將最大的列使用一個指標替代，指標指向一個新的PAGE，在該PAGE上存放實際資料。

由函式page_zip_rec_needs_ext()判斷是否需要溢位。判斷方式是該記錄長度是否大於空PAGE的可用空間。

file

GDB執行一下，可以看到一個16k的PAGE實際可用的空間為16252位元組（頁頭等佔用了小量位元組）。一半粗略算作是8k。

file

如果行長度大於8K，會將最長的列存放到新的PAGE，原位置使用20位元組的指標代替。如果處理後，行長度依然大於8K，則選擇當前最長的列進一步處理，不斷迴圈。如果列長度無法進一步縮少，仍然大於8K，則丟擲DB_TOO_BIG_RECORD錯誤，即“row size too large”。dtuple_convert_big_rec()函式上可以看到更多的執行細節。

file

  • 小量資料溢位的情況

以下堆疊展示把溢位資料寫入"Off Page"。主要函式為lob::insert()。

file

log::insert()會申請一個新的16K大小的PAGE，並將資料寫入新的page。

dberr_t insert(){
  ......
  //分配一個新的16KB的PAGE
  first_page_t first(mtr, index);
  buf_block_t *first_block = first.alloc(mtr, ctx->is_bulk());
  ......
  //將100位元組寫入寫入
  ulint to_write = first.write(trxid, ptr, len);
  ......
 }

以下是B表插入資料，往innodb"Off page"寫入資料時候的斷點，可以看到只寫了B表單列100位元組資料。16KB的容量只寫100位元組的資料，剩餘99%的空間用不上，實在太浪費了。

file

B表有100個varchar列，每個列100位元組。如果需要滿足列長少於8K，需要25個列以上進行溢位（一個列還有隱藏列和其它資料，實際需要溢位的列略多於25）。使用innochecksum查詢到“OTHER” page 有29萬，B表有一萬行，平均每行29個“OTHER” page。看來這個“OTHER” page基本都是這種“Off page”了。

問題總結

GreatSQL 白白浪費了95%的磁碟空間，是因為大量的列溢位了小量的資料。INNODB存放每個溢位列的資料的最小分配單元大小是16KB。原本10KB的行長度，需要多佔N倍的儲存空間。

最佳化建議

表設計時，要注意控制行長度小於8k,避免小量列資料溢位，導致磁碟容量和效能問題。

延伸閱讀

  • https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html
  • https://mp.weixin.qq.com/s/AjGrQONFPJgVAqy_qtoiYA
  • https://mp.weixin.qq.com/s/QMZ7O0gfs81zXo69F1bHdQ

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章