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

Site off-line
The site is currently not available due to technical problems. Please try again later. Thank you for your understanding.


--------------------------------------------------------------------------------

If you are the maintainer of this site, please check your database settings in the settings.php file and ensure that your hosting provider's database server is running. For more help, see the handbook, or contact your hosting provider.

The mysqli error was: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug.


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


- Stopping mysql
mysql Waiting for PIDS

- 100330 11:20:02 [Note] Error reading relay log event: slave SQL thread was killed
100330 11:20:10 [ERROR] Can't create thread to kill server

http://www.haqthegibson.com/article/34


==========

/boot/defaults/loader.conf
/boot/loader.conf
kern.maxfiles


> Here are some other kernel variables that may be of interest:
> kern.maxfiles: 15000
> kern.maxfilesperproc: 7408
> kern.ipc.maxsockets: 8232

http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/configtuning-kernel-limits.html

http://www.krellis.org/unix-stuff/mysql-freebsd-threads.html
============
[mysqld_safe]
open-files-limit=10240
============


The command `fstat -p X` where X is the PID of the dccifd daemon might
be illuminating.

# fstat -p `cat /usr/local/mysql/bsd-sql.nai360.com.pid`

# fstat -p `cat /usr/local/mysql/china-en.nai360.com.pid`

# pstat -T

# limits

# sysctl -a | grep -i kern.max

# sysctl -a | grep -i file

# sysctl -a|grep -i mem

# sysctl -a | grep -i vm

# cat /etc/my.cnf | grep -i open

# freecolor -t -m -o

mysql> SHOW STATUS LIKE '%open%';

mysql> SHOW STATUS LIKE '%file%';

mysql> SHOW STATUS LIKE '%thread%';

mysql> SHOW STATUS LIKE '%conn%';

mysql> SHOW STATUS WHERE Variable_name IN (
'Open_files',
'Open_tables',
'Threads_connected',
'Threads_created',
'Threads_running',
'Max_used_connections'
);

mysql> SHOW GLOBAL VARIABLES LIKE ‘binlog_cache_size’;

mysql> SHOW GLOBAL STATUS LIKE ‘Binlog%’

mysql> SHOW GLOBAL VARIABLES LIKE '%open%';

mysql> SHOW GLOBAL VARIABLES LIKE '%max%';

mysql> SHOW GLOBAL VARIABLES LIKE '%thread%';

mysql> SHOW GLOBAL VARIABLES LIKE '%innodb%';

mysql> SHOW GLOBAL VARIABLES WHERE Variable_name IN (
'innodb_buffer_pool_size',
'innodb_open_files',
'innodb_thread_concurrency',
'max_connections',
'innodb_open_files',
'open_files_limit',
'table_open_cache'
);

mysql> SHOW FULL PROCESSLIST\G

# mtop
http://mtop.sourceforge.net/mtop.html

# mkill

# mysqladmin -u root -p extended-status

# mysqladmin kill
===
There's nothing in /boot/loader.conf or /etc/sysctl.conf about the kern.maxproc or anything like that.

===
what is your max_connections Setting? For every connection one (or more) files are to be opened. Try to reduce. Also table_cache Setting can influence the number of open files. See mysql manual for more help.

Both max_connections and table_cache have been tuned to be as small as possible while leaving us some headroom for spikes. Thanks for the answer. – Conor McDermottroe Nov 24 at 14:21
http://serverfault.com/questions/87333/max-open-files-mysql-5-0-on-freebsd-7-0

http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html

http://lists.mysql.com/mysql/217340

=======
MySQL Thread Problems On FreeBSD
Last Updated: 4/21/05

At DynDNS, we're big fans of Open Source technologies. One of the major parts of our system is the MySQL Database Server. MySQL is a wonderful piece of software, and serves us and our customers very well by providing the backend database for every hit on our website.

Running MySQL effectively in a multi-processor environment on FreeBSD 4.x requires the use of LinuxThreads, because FreeBSD 4.x's native pthreads cannot scale across CPUs. MySQL provides binaries that are linked against LinuxThreads, which allows each thread to appear to the OS as a separate process, allowing them to run on separate CPUs.

Several months ago we started running into problems where we couldn't create more than about 700 threads. We would always see the following error:

Can't create a new thread (errno 35); if you are not out of available
memory, you can consult the manual for a possible OS-dependent bug
We searched and searched and couldn't come up with a solution. Eventually we simply re-tuned our application so it did not need as many threads, largely by turning down the MySQL wait_timeout variable, causing idle threads to timeout more quickly, since our application is smart enough to re-connect a thread that has been disconnected by the server.

Recently, we decided it was time to purchase a MySQL Network subscription, both as a way of contributing back to the development of MySQL, and for the technical support resources that it would make available to us. One of the issues we requested help on was this thread issue.

After some troubleshooting steps back and forth, Sinisa Milivojevic provided us with the solution we needed. There were actually two problems that needed to be corrected. First, it turns out that the LinuxThreads version used in the FreeBSD ports system allocates a static amount of stack memory to every thread that is created, and that this is hard compiled as 2MB per thread! Second, this LinuxThreads version also hard-codes the maximum number of threads per process to 1024.

With this information (and patches) in hand, we made the appropriate changes, re-compiled and re-installed LinuxThreads, re-ran the test code that we had gotten from MySQL, and lo and behold, we were able to create up to 4096 threads without a problem. A quick re-start of our MySQL daemon later, and our test script was able to use the full 2048 maximum connections specified in our configuration file.

The two patches below change the STACK_SIZE to 128K, and the PTHREAD_THREADS_MAX to 4096, which should be sufficient for most people's needs. (Note: you may need a larger STACK_SIZE if your threaded applications are stack-intense - 128K is the default used by MySQL, though, and generally ought to be enough for most purposes.) These patches are against the FreeBSD port devel/linuxthreads with version number 2.2.3_16.

internals.patch
sysdeps.patch
To use these patches, do the following (as root, or whatever user on your system has the appropriate privileges to mess with ports):

cd /usr/ports/devel/linuxthreads
make clean
make patch (this will download, extract, and run the port-specific patches on the LinuxThreads distribution)
cd work/linuxthreads-2.2.3_16
patch -p0 < /path/to/internals.patch
patch -p0 < /path/to/sysdeps.patch
cd ../..
make all
make install
Viola, you will now have LinuxThreads installed with the appropriate patches.

Many thanks to Sinisa Milivojevic and Victoria Reznichenko of MySQL for helping us to find and solve this problem, and for giving me permission to post the solution here. We highly recommend MySQL, and if you need top-notch technical support for MySQL, there's no better place to get it than direct from the source through the MySQL Network.



http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Opened_files

http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Open_files

http://forums.freebsd.org/showthread.php?t=1553

http://serverfault.com/questions/87333/max-open-files-mysql-5-0-on-freebsd-7-0


http://unix.derkeiler.com/Mailing-Lists/FreeBSD/questions/2008-09/msg01325.html

========
DB Servers: One Master, Two Read Only (replication)
4 GB of Memory on each server
FreeBSD 6.3-RELEASE-p3 FreeBSD 6.3-RELEASE-p3
MySQL 5.0.1



Here are some relevent items from my.cnf:
- set-variable = max_connections=1000
- set-variable = key_buffer_size=384M
- set-variable = read_buffer_size=64M
- set-variable = read_rnd_buffer_size=32M
- set-variable = thread_cache_size=20


You're shooting yourself in the foot:
1000*2MB=2G for thread stack
+ 384M
+ 1000 * (sort_buffer_size+64M+binlog_cache_size_innodb)

You don't have that much memory.
http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

There's a similar formula for MyISAM, but can't seem to find it at the moment.

Hi Vince,

Thanks for the advice. We have already raised the memory limits:

kern.maxdsiz="1843M" # 1.8GB
kern.dfldsiz="1843M" # 1.8GB

Any other ideas?

http://unix.derkeiler.com/Mailing-Lists/FreeBSD/questions/2008-09/msg01358.html



=====

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

==========

Re: Maximum memory allocation per process

--------------------------------------------------------------------------------

From: Jeremy Chadwick <koitsu@xxxxxxxxxxx>
Date: Thu, 22 May 2008 06:38:19 -0700

--------------------------------------------------------------------------------
On Thu, May 22, 2008 at 11:00:37PM +1000, Adrian Thearle wrote:

I have a problem with a perl script running out of memory. From my googling
I have found that perl itself does not seem to impose any memory limits,
and I have check ulimit and login.conf for any userclass limitations but
found nothing that seems to be limiting my memory.

I have 128MBytes of RAM and a 2Gbyte swap partition.

I am currently running
FreeBSD albert 6.2-STABLE FreeBSD 6.2-STABLE #11: Sun Sep 2 00:45:05 EST
2007
which I guess isn't exactly the latest... but the same thing happens on my
REL7.0 Box

The process (imapsync in this case) runs out of ram at pretty much 512MB. I
read on a forum that BSD 6 imposes such a limit of 512MB per process, but i
have found no where to tune this, or even see what it is.


You need to modify some kernel settings via /boot/loader.conf and
reboot. Here's what we use on our production RELENG_6 and RELENG_7
boxes:

# Increase maximum allocatable memory on a process to 2GB.
# (We don't choose 3GB (our max RAM) since that would
# exhaust all memory, and result in a kernel panic.)
# Set default memory size as 768MB.
# Maximum stack size is 256MB.
#
kern.maxdsiz="2048M"
kern.dfldsiz="768M"
kern.maxssiz="256MB"


I have also read that there are two sysctl namely, kern.maxdsiz and
kern.maxssiz, that can tune memory allocation but what happend to them in
Freebsd 6.


These are not sysctls, they are kernel settings. They exist on both
RELENG_6 and RELENG_7.

http://unix.derkeiler.com/Mailing-Lists/FreeBSD/hackers/2008-05/msg00258.html

http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Connections