MySQL报错：Out of sort memory, consider increasing server sort buffer size - 简书.pdf

四宝

804

2页

0次

2022-07-12

10墨值下载

MySQL报错：Out of sort memory, consider increasing

server sort buffer size

今天一个错误反馈到我这边，我还是第一次遇到这种错误，然后就分析了一下，因为以前曾经做过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设置的32K大小报错了。

并且整个计算过程还没有真正的进行排序，因此即便是空表也会进行计算，和数据量无关。就是MySQL认为

sort buffer连一行排序数据都装不下。

三、如何避免

显然这个问题和表的设计有关，如果有一个数字类型的主键，问题自然解决。当然加大sort_buffer_size也可以

治标。

of 2

10墨值下载

mysql

关注

评论