Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the problem of ERROR 1135 (HY000) error reporting in mysql". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the problem of ERROR 1135 (HY000) error reporting in mysql".

Received an error:

[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

Immediately google, some people said that the disk space may be full, after inspection found that the disk is really full, (this disk has been very tight (95%), I am highly vigilant, check every day, but yesterday I carried out a big business, generated a lot of binlog, suddenly burst)

Immediately delete the binlog and some other unneeded data from a few days ago, release the space to 80%, and log in to mysql again.

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

Still report an error:

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 again and find the following document:

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

Generally speaking, the reason for this error: because the parameter open-files-limit of mysql configuration file / etc/my.cnf is larger than the value of linux max user processes, you need to expand the limit of linux system by modifying linux configuration file / etc/security/limits.d/90-nproc.conf, that is, this error is due to the small max user processes threshold of linux.

Check my configuration right away:

The open-files-limit of mysql, as follows:

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

[mysqld_safe]

Open-files-limit=85216

The max user processes of linux, as shown in red:

[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

You will check the situation described in the previous document and modify the max user processes immediately.

Method 1:

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

* soft nofile 65535

* hard nofile 65535

* soft nproc 65535

* hard nproc 65535

Where nofile corresponds to open_files

Nproc corresponds to max_user_processes

But after Linux 6.4. if only the nproc in this file is modified, then the corresponding max_user_processes of other non-root users will not change, which is still 1024. This is due to the influence of the following file.

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

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

* soft nproc 1024

Modified to:

* soft nproc 65535

Or

Modify / etc/security/limits.conf to set the

* soft nofile 10240

Modify to

Oracle soft nofile 10240

Method 2: this executes the file for each user running bash shell, and when the bash shell is opened, the file is read. That is, when the user shell executes bash, run this file, and if there are multiple users on the server, it is best to use method one.

Modified / etc/bashrc, succeeded, and no reboot is required.

Vi / etc/bashrc

Add:

Ulimit-u 65535

Quit session, reopen session and ulimit-a found that it has changed.

[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

And make mysql's open-files-limit smaller.

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

[mysqld_safe]

Open-files-limit=65000

The mysql service was restarted and the problem was resolved.

Cause analysis:

The number of operating system connections is too small. (for example, the default max user process for centos 6 is only 1024. The problem of Can't create a new thread occurs when mysql process is greater than this value)

How to deal with the number of connections exceeding the limit:

Ulimit-a

Check the max user processes entry.

If this value is small, when the number of process in mysql exceeds this number, there will be a corresponding error in the title.

A procedure process can be treated as an open file

That is to say, the following parameters jointly control the create a new thread of the mysql

1) / etc/my.cnf of mysql

Open-files-limit=65535

2) linux parameters open files and 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

Thank you for reading, the above is the content of "how to solve the problem of ERROR 1135 (HY000) error report in mysql". After the study of this article, I believe you have a deeper understanding of how to solve the problem of ERROR 1135 (HY000) error report in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.