温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

怎么解决mysql中的ERROR 1135 (HY000)报错问题

发布时间:2021-11-18 15:48:37 来源:亿速云 阅读:482 作者:iii 栏目:MySQL数据库

这篇文章主要讲解了“怎么解决mysql中的ERROR 1135 (HY000)报错问题”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么解决mysql中的ERROR 1135 (HY000)报错问题”吧!

收到报错:

[root@i-iivphroy ~]# mysql -uroot -p*********  -h292.168.0.254

ERROR 1135 (HY000): Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

马上google一番,有人说可能说磁盘空间满了,经查看发现果然是磁盘满了,(这个盘一直空间很紧张(95%),我高度警惕着,天天检查,可是昨天我执行了个大事务,产生了大量的binlog,给一下子撑爆了)

马上删除了几天前的binlog和一些别的不需要的数据,空间释放到了80%,再次登录mysql

[root@i-iivphroy ~]# mysql -uroot -p******* -h292.168.0.254

依旧报错:

ERROR 1135 (HY000): Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

再次google一番,查到下面这个文档:

This error was the bane of my life for a while, and it was very hard to get a definitive answer as to what was causing it, I hope this saves you some trouble.

My website occasionally got large traffic spikes, and at the top of these peaks, I would start to see errors like these:

MySQL error #1135: Can’t create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug.

I looked in the my.cnf file on the db server and looked at the open files limit, because a process is counted as an open file, but it seemed fine:

[mysqld_safe]

open-files-limit=10240

I also checked that maximum connections was high enough, it was at 2048.

What the open-files-limit in my.cnf files does is it tells the init script to use ulimit to whatever number you put in there.

After a lot of digging around various places, and much frustration, I discovered that by default linux has a hard limit of 1024 open files for all non super-users, so even though I had set a high open-files-limit, it was capped at 1024 by the OS. I also discovered how to raise it;

/etc/security/limits.conf

This file is used by PAM to set things like maximum processes, max open files, memory usage etc and these limits can be set on a per-user basis, so I added these lines:

mysql soft nofile 4096

mysql hard nofile 4096

大体的意思是说,这个报错的原因:由于:mysql的配置文件/etc/my.cnf的参数open-files-limit设置的比linux的max user processes的数值大,需要通过修改linux的配置文件 /etc/security/limits.d/90-nproc.conf来扩大linux系统的限制,也就是这个错是由于linux的max user processes阈值太小了。

马上查看我的相关配置:

mysql的open-files-limit,如下所示:

[root@i-iivphroy ~]# cat /etc/my.cnf

[mysqld_safe]

open-files-limit=85216

linux的max user processes,如下所示红色部分:

[root@i-iivphroy ~]# ulimit -a

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 62842

max locked memory       (kbytes, -l) 64

max memory size         (kbytes, -m) unlimited

open files                      (-n) 1024

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) 10240

cpu time               (seconds, -t) unlimited

max user processes              (-u) 62842

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

将查看果然是前面文档描述的情况,马上修改max user processes

方法一:

[root@i-iivphroy ~]# cat  /etc/security/limits.conf

*  soft nofile 65535

*  hard nofile 65535

* soft nproc 65535

* hard nproc 65535

其中nofile对应open_files

nproc对应max_user_processes

但是在Linux 6.4之后,如果只修改了该文件中的nproc,那么其他非root用户对应的max_user_processes并不会改变,仍然是1024,这个是因为受到了下面这个文件的影响

/etc/security/limits.d/90-nproc.conf

修改/etc/security/limits.d/90-nproc.conf将

* soft nproc 1024

修改为:

* soft nproc 65535

或者

修改/etc/security/limits.conf,将

* soft nofile 10240

修改为

Oracle  soft nofile 10240

方法二:这样为每一个运行bash shell的用户执行此文件,当bash shell被打开时,该文件被读取。也就是说,当用户shell执行了bash时,运行这个文件,如果这个服务器上有多个用户,最好是用方法一。

修改了/etc/bashrc,成功了,并且不用重启。

vi /etc/bashrc

添加 :

ulimit -u 65535

退出session,从新开session再次ulimit -a 发现已经变化了

[root@i-iivphroy ~]# ulimit -a

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 62842

max locked memory       (kbytes, -l) 64

max memory size         (kbytes, -m) unlimited

open files                      (-n) 1024

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) 10240

cpu time               (seconds, -t) unlimited

max user processes              (-u) 65535

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

并且把mysql的open-files-limit改小。

[root@i-iivphroy ~]# cat /etc/my.cnf

[mysqld_safe]

open-files-limit=65000

重启了mysql服务,问题解决。。。。

原因分析:

操作系统连接数太小。(比如centos 6 默认的  max user process只有 1024个。当mysql process大于这个值时 就会出现Can't create a new thread的问题)

连接数超限处理的办法:

ulimit -a

查看max user processes 这一项

要是这个值比较的小 当mysql中process的数目超过这个数的时候 就会抱标题相应的错误。

一个过程process可以视为一个打开的文件

也就是说 下面几个参数共同控制这mysql的 create a new thread

1) mysql的 /etc/my.cnf

open-files-limit=65535

2)linux  参数   open files和max user processes

[root@S243 ~]# ulimit

unlimited

[root@S243 ~]# ulimit -a

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 1032207

max locked memory       (kbytes, -l) 64

max memory size         (kbytes, -m) unlimited

open files                      (-n) 50000

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) 10240

cpu time               (seconds, -t) unlimited

max user processes              (-u) 65535

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

感谢各位的阅读,以上就是“怎么解决mysql中的ERROR 1135 (HY000)报错问题”的内容了,经过本文的学习后,相信大家对怎么解决mysql中的ERROR 1135 (HY000)报错问题这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI