Mysql error causing database to not accept threads

------=_NextPart_000_0021_01CB1774.5038B550
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Good morning all



Hope everyone is well....



We received an error on a MySQL database this morning which
caused it to be unavailable for connections.



Error:



ERROR 1135 (00000): 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 checked the system memory but this seemed to be fine.



When I tried to restart the database, the shutdown just kept
on running the whole time without shutting down the database.



We then went to the drastic measure of restarting the server
itself which then seemed to have sorted out the problem.



The amount of connections to via the website was very
minimal at that stage, however there was a script running which takes a data
dump from an oracle database and imports this into the Mysql database and I
suspect that it has something to do with this.



I am trying to find out which settings / variables/ etc...
we should check to find out where the problem is.



One forum suggested changing the thread_cache_size to 512 ,
currently this is on 256.







Hoping that someone can assist with this and much
appreciated.





Regards

Machiel




------=_NextPart_000_0021_01CB1774.5038B550--

Re: Mysql error causing database to not accept threads

Note that some of the memory settings are per connection, if you have
63 connections, you may have up to 63 copies of the buffers, and you
have pretty huge buffers even with 30GB of memory. Join buffers close to
1 G, tmp_table_size of 2 G, 63 connections could mean upto 63 copies of
these, and if each connection uses the full buffers, thats close to 200GB!

You should adjust these to cater to the maximum number of connections
expected.

Did you try to check how much free memory you have when the problem occurs?
Am 29.06.2010 10:17, schrieb Machiel Richards:
> ERROR 1135 (00000): Can't create a new thread


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Mysql error causing database to not accept threads

Hi

Yes, we did check the amount of free O/S memory and this was the
same as during normal operating times when the system is working.
Even the load averages on the system was very low.

At the moment of this incident there was only 300 odd connections
from the website where there are usually about 2000 + connections.

As stated, the only other process running at that stage was the
oracle import process, however after speaking to the person who implemented
the imports scripts / process it was stated that this import makes one
single connection and then imports relevant data within a single
transaction.

This import runs every 30 minutes so the amount of data being import
is not that much and have not increased drastically so I am not yet sue as
to where else to look.

The only log created was that there was that no more threads could
be created, even when trying to shutdown the MySQL database.

I am not sure whether this could make a difference, however, but the
server also runs an oracle database on the same server (not the database
being imported from, only a slave oracle database)...

I have also checked the ulimit settings and it seems that all the
options in forums found thus far has been set to unlimited or to more than
what is being suggested.


Machiel Richards


-----Original Message-----
From: Benedikt Schackenberg [mailto:schackenberg@termindoc.de]
Sent: 29 June 2010 10:57 AM
To: mysql@lists.mysql.com
Subject: Re: Mysql error causing database to not accept threads

Note that some of the memory settings are per connection, if you have
63 connections, you may have up to 63 copies of the buffers, and you
have pretty huge buffers even with 30GB of memory. Join buffers close to
1 G, tmp_table_size of 2 G, 63 connections could mean upto 63 copies of
these, and if each connection uses the full buffers, thats close to 200GB!

You should adjust these to cater to the maximum number of connections
expected.

Did you try to check how much free memory you have when the problem occurs?
Am 29.06.2010 10:17, schrieb Machiel Richards:
> ERROR 1135 (00000): Can't create a new thread


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=machiel.richards@gmail.co m


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Mysql error causing database to not accept threads

Hi!


Machiel Richards wrote:
> [[...]]
>=20
> We received an error on a MySQL database this morni=
ng which
> caused it to be unavailable for connections.
>=20
> Error:
>=20
> ERROR 1135 (00000): Can't create a new thread (errn=
o 11); if
> you are not out of available memory, you can consult the manual for=
a
> possible OS-dependent bug
>
> [[...]]

You don't say anything about the MySQL version you are running, or yo=
ur
platform.

I hope this here will help you, though:


| U@H:~> fgrep 11 /usr/include/asm-generic/errno-base.h
| #define EAGAIN 11 /* Try again */


| U@H:~> man pthread_create
| PTHREAD_CREATE(3P) POSIX Programmer's Manual
PTHREAD_CREATE(3P)
|
|
|
| NAME
| pthread_create - thread creation
|
| [[...]]
|
| ERRORS
| The pthread_create() function shall fail if:
|
| EAGAIN The system lacked the necessary resources to crea=
te
another thread, or the system-imposed
| limit on the total number of threads in a process
{PTHREAD_THREADS_MAX} would be exceeded.
|
| [[...]]


Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Mysql error causing database to not accept threads

Hi Joerg

Thank you very much for the response.

I found an answer this morning (about 15 minutes ago) where the
server's Thread cache was used up and the amount of max connections =
exceeded
causing the same error.

We have not yet been able to establish the source of the amount of
connections though.

Machiel Richards
MySQL DBA
Relational Database Consulting=20




-----Original Message-----
From: Joerg.Bruehe@Sun.COM [mailto:Joerg.Bruehe@Sun.COM]=20
Sent: 30 June 2010 10:53 AM
To: mysql@lists.mysql.com
Cc: Machiel Richards
Subject: Re: Mysql error causing database to not accept threads

Hi!


Machiel Richards wrote:
> [[...]]
>=20
> We received an error on a MySQL database this morning
which
> caused it to be unavailable for connections.
>=20
> Error:
>=20
> ERROR 1135 (00000): 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
>
> [[...]]

You don't say anything about the MySQL version you are running, or your
platform.

I hope this here will help you, though:


| U@H:~> fgrep 11 /usr/include/asm-generic/errno-base.h
| #define EAGAIN 11 /* Try again */


| U@H:~> man pthread_create
| PTHREAD_CREATE(3P) POSIX Programmer's Manual
PTHREAD_CREATE(3P)
|
|
|
| NAME
| pthread_create - thread creation
|
| [[...]]
|
| ERRORS
| The pthread_create() function shall fail if:
|
| EAGAIN The system lacked the necessary resources to create
another thread, or the system-imposed
| limit on the total number of threads in a process
{PTHREAD_THREADS_MAX} would be exceeded.
|
| [[...]]


Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg