MYSQL 8.0.27 商业版中 sort_buffer_size 的意外行为

发布于 2025-01-09 04:08:32 字数 1144 浏览 1 评论 0原文

我有两个不同的mysql服务器，一个是8.0.16社区版本，另一个是8.0.27商业版本。两者都有在 Linux 上运行的 mysql 默认配置。

我有一个名为 jsonstore 的表，其中包含 34 列，其中两列是 json，表中的总行数为 9。我已在两台服务器中复制了相同的表。

Select json1 from jsonstore where reference = 7 order by generated_date desc limit 1;

其中引用是 int(11) 索引列， generated_date 是日期时间列，json1 是 json 列，jsonstore 是 innodb 表

上面的查询返回 1 条 json 记录，json 的大约大小是 1.5MB

我在 MySQL 的两台服务器中执行了相同的查询（8.0） .16) 执行服务器查询并返回数据。在 MySQL(8.0.27) 服务器中，它失败并返回内存不足，请考虑增加服务器排序缓冲区大小。

两台服务器的 sort_buffer_size 均为 1048576 字节（1MB），sort_merge_passes 小于 20。

select json1 from jsonstore order by generated_on desc limit 1;

上述查询在 8.0.16 中也可以正常工作，但在 8.0.27 中失败。

所以，我厌倦了在 mysql (8.0.27) 中将 sort_buffer_size 增加到 2MB，然后它对第一个查询有效，但对第二个查询失败。我再次将其更新为 5MB，然后两个查询均成功执行。

但在其他 mysql 服务器 (8.0.16) 中，相同的查询在 sort_buffer_size 为 256Kb 的情况下工作正常。

我担心的是，如果随着表的增长出现这种情况，我需要继续增加 sort_buffer_size 。不确定这是 mysql 新版本中的错误还是 sort_buffer_size 机制发生了变化。

请帮助我了解 sort_buffer_size 在内部如何工作。

注意：-上述问题不仅限于这两个查询，还有一些使用 order by 和 group by 的其他查询，其 json 列也会引发相同的错误，但在 8.0.16 版本中工作正常。

原文

I have two different mysql servers one is 8.0.16 community version and the other is 8.0.27 commercial version. Both has mysql default configuration running on Linux.

I have table called jsonstore which contain 34 column and two of which are jsons and total count of rows in table is 9. I have copied the same table in both servers.

Select json1 from jsonstore where reference = 7 order by generated_date desc limit 1;

Where reference is an int(11) indexed column ,generated_date is datetime column and json1 is json column and jsonstore is innodb table

Above query return 1 json record approx size of json is 1.5MB

Same query i have executed in both servers in MySQL( 8.0.16 ) server query is executed and returned data. Where as in MySQL(8.0.27) server it failed and returns out of sort memory please consider increasing server sort buffer size.

Both servers has sort_buffer_size of 1048576 bytes(1MB) and sort_merge_passes less than 20.

select json1 from jsonstore order by generated_on desc limit 1;

Above query also working fine in 8.0.16 but failed in 8.0.27.

So, i tired with increasing sort_buffer_size to 2MB in mysql (8.0.27) then it worked for 1st query and failed for 2nd query. Again i have updated it to 5MB then both query's executed successfully.

But same queries in other mysql server (8.0.16) are working fine with sort_buffer_size of 256Kb.

My fear is that if this is the case as table grows i need to keep on increase the sort_buffer_size. not sure weather it is an bug in mysql new verison or there is a change in mechanism of sort_buffer_size.

Please help me with understanding of how sort_buffer_size will work internally.

Note:- above problem is not limited to those two queries there are some other queries using order by and group by has json columns also throwing same error but working fine in 8.0.16 version.

分享到QQ

分享到微博