MySQL案例 | consider increasing server sort buffer

比眉伴天荒 2022-11-13 12:26 70阅读 0赞

作者：八怪（高鹏） 中亦科技数据库专家

今天一个错误反馈到我这边，我还是第一次遇到这种错误，然后就分析了一下，因为以前曾经做过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（回表排序）而言这个值实际上包含的是：

> 主键（或者非空唯一建或者rowid） + 排序字段长度

我们来简单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字节。

好了，我们大概计算一下，那么

> MERGEBUFF2 \* (param.rec\_length + sizeof(uchar\*)))

忽略一个指针的大小，大概就是

> 15 \*（ 3064+5），约为45k左右

比sort\_buffer\_size设置的32K还要大，所以报错了。