共计 5023 个字符,预计需要花费 13 分钟才能阅读完成。
这篇文章主要讲解了“怎么解决 mysql 中的 ERROR 1135 (HY000) 报错问题”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着丸趣 TV 小编的思路慢慢深入,一起来研究和学习“怎么解决 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) 报错问题这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是丸趣 TV,丸趣 TV 小编将为大家推送更多相关知识点的文章,欢迎关注!