mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解决方法

今天在检查mysql服务器的时候提示Sort aborted: Out of sort memory, consider increasing server sort buffer size,安装字面意思就是 sort内存溢出,考虑增加服务器的排序缓冲区(sort_buffer_size)大小

sort_buffer_size=3M
join_buffer_size = 3M

下面是针对16G 内存设置的参数:

sort_buffer_size = 2M
# Sort_Buffer_Size 是一个connection级参数,在每个connection(session)第一次需要使用这个buffer的时候,一次性分配设置的内存。
#Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗 500*sort_buffer_size(8M)=4G内存
#Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
#技术导读 http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/
#dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
#explain select*from table where order limit;出现filesort
#属重点优化参数

不过我看到有人将sort_buffer_size设置为10M,我就是因为设置了10M,后来我设置为2-3M,就没有问题了

Written by cenalulu @ 2010-10-26

基础知识:

1、 Sort_Buffer_Size 是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。
2、 Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。
3、 文档说“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation”

本文主要针对第三点做测试:
据说Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。

环境:

为了更大的体现性能差距,使用 1GB内存的Fedora 虚拟机进行测试

测试表结构:

1w 行的表, 表结构

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

测试语句:

分别设置Sort_Buffer_Size 为 250K ,512K, 3M ,然后运行以下语句,查看运行时间。
1. sql_no_cache 防止query cache起效。
2. limit 1 为了减少排序占执行时间的比重,更多的体现内存分配带来的影响
3. 语句explain的结果是 filesort , 以确保使用sort_buffer

代码如下:

mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10

测试结果:

执行时间

250K : 1.318s
512K : 1.944s
3M     : 2.412s

250 K
[root@localhost tmp]# mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10
Benchmark
        Average number of seconds to run all queries: 1.318 seconds
        Minimum number of seconds to run all queries: 1.285 seconds
        Maximum number of seconds to run all queries: 1.378 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

512 K

[root@localhost tmp]# mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10
Benchmark
        Average number of seconds to run all queries: 1.944 seconds
        Minimum number of seconds to run all queries: 1.362 seconds
        Maximum number of seconds to run all queries: 4.406 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

3M
[root@localhost tmp]# mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10
Benchmark
        Average number of seconds to run all queries: 2.412 seconds
        Minimum number of seconds to run all queries: 2.376 seconds
        Maximum number of seconds to run all queries: 2.527 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

结论:
确实如文档所说, 使用mmap 分配内存时,会带来性能上的损耗,影响大约在 30% 左右。
由于是limit 1,因此减弱了buffer size对排序速度的影响。
其实buffer size 越大,所需要进行的sort-merge的次数越少,执行时间越短。视具体的语句而定。
我只是想说明下,并不是越大越好。 也并不存在一个绝对的最佳值。
还是要看具体的业务环境决定值的大小。 对于高并发的应用,还是别设置太大的比较好。

(0)

相关推荐

  • 通过案例分析MySQL中令人头疼的Aborted告警

    本文主要给大家介绍的是关于MySQL中Aborted告警的相关内容,分享出来供大家参考学习,下面来一起看看详细的介绍: 实战 Part1:写在最前 在MySQL的error log中,我们会经常性看到一些各类的Aborted connection错误,本文中会针对这类错误进行一个初步分析,并了解一个问题产生后的基本排查思路和方法.掌握这种方法是至关重要的,而不是出现问题了,去猜,去试.数据库出现问题的时候需要DBA在短时间内快速解决问题,因此一个好与坏的DBA,区别也在于此. Part2:种类

  • mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解决方法

    今天在检查mysql服务器的时候提示Sort aborted: Out of sort memory, consider increasing server sort buffer size,安装字面意思就是 sort内存溢出,考虑增加服务器的排序缓冲区(sort_buffer_size)大小 sort_buffer_size=3M join_buffer_size = 3M 下面是针对16G 内存设置的参数: sort_buffer_size = 2M # Sort_Buffer_Size 是

  • MySQL 5.7解压版安装、卸载及乱码问题的图文解决方法

    1. 解压版的安装 (1). 下载压缩包并解压到一个磁盘位置 压缩包下载链接:https://dev.mysql.com/downloads/mysql/ 压缩包内容: (2). 写配置文件 复制my-default.ini重命名为my.ini,然后修改或添加配置信息: [mysqld] #mysql解压目录 basedir = <解压目录> #data文件夹位置,用来存放库和表 datadir = <data文件夹目录> #mysql默认端口号 port = 3306 #数据库服

  • ThinkPHP提示错误Fatal error: Allowed memory size的解决方法

    本文实例讲述了ThinkPHP提示错误Fatal error: Allowed memory size的解决方法.分享给大家供大家参考.具体分析如下: 如果你的ThinkPHP提示你:致命错误(Fatal error: Allowed memory size),根据网上说的提高服务器可使用内存,我觉得都不是好的解决办法.麻烦也没必要.因为这是ThinkPHP本身存在BUG. 错误提示:Fatal error: Allowed memory size of 1073741824 bytes exh

  • gearman中worker常驻后台,导致MySQL server has gone away的解决方法

    本文实例讲述了gearman中worker常驻后台,导致MySQL server has gone away的解决方法.分享给大家供大家参考,具体如下: 产生这个原因主要有如下几点: 1.mysql服务宕机了 2.长时间没有操作,超过了wait_timeout的设置,mysql自动断开 3.mysql请求链接被主动kill 4.发送的请求或返回结果过大,可设置max_allowed_packet的值 5.程序中你都是通过单例来操作数据库,如果两个操作时间超过wait_timeout. 为了演示的

  • Linux连接mysql报错:Access denied for user ‘root’@‘localhost’(using password: YES)的解决方法

    前言 最近在linux连接mysql /usr/local/mysql/bin/mysql -uroot -p 输入密码出现Access denied for user 'root'@'localhost'(using password: YES)错误.下面话不多说了,来一起看看详细的解决方法吧 解决办法: 1.先停止mysql 服务 service mysqld stop 2.进入mysql安装目录bin/ 使用safe模式,进行重启: ./mysqld_safe --skip-grant-t

  • Mysql单文件存储删除数据文件容量不会减少的bug与解决方法

    MySQL的Bug之一:InnoDB ibdata1 never shrinks after data is removed 问题描述 当innodb引擎使用单个文件进行存储的时候,当对数据库中数据进行删除的时候,文件的大小并不会发生变化,意味着文件将会越来越大并且即使删除也没办法 让其释放磁盘的空间. 验证过程 创建一个测试使用的表,如下创建好t这样的一个表. 创建简单的存储过程来批量的插入数据. 查看开始前的文件的大小 当批量插入接近十万的数据后 然后进行删除操作 占用磁盘空间并未减少 解决

  • mysql中You can’t specify target table for update in FROM clause错误解决方法

    mysql中You can't specify target table for update in FROM clause错误的意思是说,不能先select出同一表中的某些值,再update这个表(在同一语句中). 例如下面这个sql: 复制代码 代码如下: delete from tbl where id in (         select max(id) from tbl a where EXISTS         (             select 1 from tbl b

  • MySQL删除外键时报错Error Code:1091. Can‘t DROP ‘XXX‘的解决方法

    解决MySQL删除外键时报错Error Code: 1091. Can't DROP 'XXX'; check that column/key exists 长期不写基础的 MySQL 代码,笔者也开始犯一些低级错误.最近,笔者在尝试将表中某列的外键约束删除时,MySQL 却一直报该列本就不存在的错误. 笔者报错时的运行环境: MySQL 8.0.27 Windows 10 教育版 Error Code: 1091. Can't DROP 'XXX'; check that column/key

  • PHP读MYSQL中文乱码的快速解决方法

    打算切换某个网站的主机,没想到遇到Php和Mysql中文乱码的问题. 以前的国外主机用的Mysql是4.x系列的,感觉还比较好,都无论GBK和UTF-8都没有乱码,没想到新的主机的Mysql是5.0版本的,导入数据后,用Php读出来全是问号,乱码一片,记得我以前也曾经有过一次切换出现乱码的经验,原因肯定是Mysql版本之间的差异问题. 只好查资料,发现了一个解决方法,就是在mysql_connect后面加一句SET NAMES UTF8,即可使得UTF8的数据库消除乱码,对于GBK的数据库则使用

  • PHP+MYSQL 出现乱码的解决方法

    肯定都已经解决了JSP里的乱码问题,不过还是有些人的MYSQL乱码问题没有得到解决,包括我自己,所以又找了一些资料,希望这次能完全解决MYSQL数据库的乱码问题.  第一种方法,很精辟的总结:  经常更换虚拟主机,而各个服务商的MYSQL版本不同,当导入数据后,总会出现乱码等无法正常显示的问题,查了好多资料,总结出自己的一点技巧: WINDOWS 下导入应该这样  使用MYSQL的命令  在DOS命令下进入mysql的bin目录下,输入mysql -uroot -p密码 数据库名称<要恢复的数据

随机推荐