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
-Original Message-
From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com]
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:
> [[...]]
>
> We received an error on a MySQL database this morning
which
> caused it to be unavailable for connections.
>
> Error:
>
> ERROR 1135 (0): 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
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@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=arch...@jab.org
Re: Mysql error causing database to not accept threads
Hi!
Machiel Richards wrote:
> [[...]]
>
> We received an error on a MySQL database this morning which
> caused it to be unavailable for connections.
>
> Error:
>
> ERROR 1135 (0): 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
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@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=arch...@jab.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:schackenb...@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 (0): 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.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 (0): 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=arch...@jab.org
Mysql error causing database to not accept threads
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 (0): 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
