MySQL案例 | consider increasing server sort buffer

今天一個錯誤回報到我這邊，我還是第一次遇到這種錯誤，然後就分析了一下，因為以前曾經做過filesort流程分析，新書《深入了解MySQL主從原理》中也有一節專門介紹這部分。這裡簡單做了一下debug後分析出原因。

問題版本5.7.27，我的測試版本5.7.22。

一、問題模拟

mysql> show create table testsorterr3 \G
*************************** 1. row ***************************
       Table: testsorterr3
Create Table: CREATE TABLE `testsorterr3` (
  `id` int(11) DEFAULT NULL,
  `name1` varchar(510) NOT NULL,
  `name2` varchar(510) NOT NULL,
  UNIQUE KEY `name1` (`name1`,`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select count(*) from testsorterr3 ;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> show variables like 'sort_buffer_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sort_buffer_size | 32768 |
+------------------+-------+
1 row in set (0.02 sec)


mysql>  select id from testsorterr3 order by id;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size      

當然問題解決很簡單，加大sort_buffer_size 設定即可。

但是這裡實際上為32K，我隻是按照一個int類型的4位元組類型進行排序而已，并且表中一條資料都沒有報錯顯然有點讓人摸不到頭腦說sort_buffer_size設定小了。

再說sort_buffer_size不夠不是可以使用臨時檔案做歸并排序嘛？

二、問題分析

注意這裡隻談 original filesort algorithm（回表排序）。

先來看看抛錯點

if (memory_available < min_sort_memory)
    {
      my_error(ER_OUT_OF_SORTMEMORY,MYF(ME_ERRORLOG + ME_FATALERROR));
      goto err;
    }      

這裡memory_available 就是我們sort_buffer_size 的設定大小，這裡就是32K。min_sort_memory則是通過計算得到的如下：

const ulong min_sort_memory=
      max<ulong>(MIN_SORT_MEMORY,
                 ALIGN_SIZE(MERGEBUFF2 * (param.rec_length + sizeof(uchar*))));      

其中MIN_SORT_MEMORY為32K，MERGEBUFF2 為15。

那麼剩下的變量實際上就隻有param.rec_length一個了，這實際上是計算出來的排序字段的長度。

而對于original filesort algorithm（回表排序）而言這個值實際上包含的是：

我們來簡單debug一下：

(gdb) p ref_length
$28 = 3064
(gdb) p sort_length
$29 = 5
(gdb)       

可以看到這裡3064位元組，大概就是510*2*3的長度，因為我們這裡非空唯一鍵為

(`name1`,`name2`)

，Innodb表正是按照它進行組織的，而sort_length為5是int類型(4位元組)再加上可以為null(1位元組)也就是5位元組。

好了，我們大概計算一下，那麼

忽略一個指針的大小，大概就是

比sort_buffer_size設定的32K還要大，是以報錯了。

并且整個計算過程還沒有真正的進行排序，是以即便是空表也會進行計算，和資料量無關。就是本案中MySQL認為sort_buffer_size連一行排序資料都裝不下産生報錯的原因。

三、如何避免

顯然這個問題和表的設計有關，如果遵循開發設計規範，采用自增INT做主鍵，問題自然解決，這是治本。

當然簡單地加大sort_buffer_size，這是治标。

全文完。

Enjoy MySQL :)