【MySQL】Mysql提示:Out of sort memory, consider increasing server sort buffer size
Java接口忽然报错,错误信息是Out of sort memory, consider increasing server sort buffer size。
字面意思就是 sort内存溢出,考虑增加服务器的排序缓冲区(sort_buffer_size)大小。
[root@localhost ~]# docker exec -it mysql8 mysql -uroot -p******** mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 69597 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 262144 | +-------------------------+---------+ 3 rows in set (0.01 sec) mysql> SET GLOBAL sort_buffer_size = 1024*1024; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@localhost ~]# docker exec -it mysql8 mysql -uroot -p****** mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 69601 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 1048576 | +-------------------------+---------+ 3 rows in set (0.01 sec) mysql> exit Bye
设置大了,可以提升性能,但占用更多的内存资源。设置小了,可能会降低性能。