1135 Can't create a new thread (errno 11)...

[Wagner Bianchi]

I'm opening a new topic with the same name because the other was closed too earlier. Just now, I am running two MySQL servers version 5.5.17 with the same problem when reach 1000 connections or more. This case is so serious and I have a cloud server with the same hardware of those both servers just to test it in order to discover the problem.

So, this test environment is running on Red Hat Linux, with MySQL 5.5.17 with default configuration, just have configured *max_connections*, *max_user_connections* and InnoDB logs to reach that number of concurrent operations (this box has 24GB RAM and MySQL Instance have been used only by this test).


[root@bd7 ~]# my_print_defaults mysqld
--skip-name-resolve
--skip-external-locking
--port=3306
--socket=/var/lib/mysql/mysql.sock
--query_cache_type=0
--query_cache_size=0
--lower_case_table_names=1
--max_connections=6000
--max_user_connections=2000
--thread_concurrency=8
--binlog_format=mixed
--server-id=2
--max_binlog_size=128M
--expire_logs_days=2
--relay-log=relay-log
--relay-log-index=relay-log.index
--relay-log-purge=1
--relay-log-space-limit=256M
--innodb_buffer_pool_size=16G
--innodb_buffer_pool_instances=8
--innodb_additional_mem_pool_size=64M
--innodb_log_file_size=768M
--innodb_log_files_in_group=4
--innodb_file_format=BARRACUDA
--innodb_file_per_table=1

As long as I was running the mysqlslap to simulate connections, I was enabling the options from the configuration file (my.cnf). I got errors just when I enable those variables:

--innodb_write_io_threads=8
--innodb_read_io_threads=8

After configure them, restarted the MySQL server, the mysqlslap has resulted in...

[root@bd7 ~]# mysqlslap --user=root --auto-generate-sql --concurrency=100,200,300,400,500,1000 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 0.724 seconds
Minimum number of seconds to run all queries: 0.724 seconds
Maximum number of seconds to run all queries: 0.724 seconds
Number of clients running queries: 100
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 1.420 seconds
Minimum number of seconds to run all queries: 1.420 seconds
Maximum number of seconds to run all queries: 1.420 seconds
Number of clients running queries: 200
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 2.704 seconds
Minimum number of seconds to run all queries: 2.704 seconds
Maximum number of seconds to run all queries: 2.704 seconds
Number of clients running queries: 300
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 5.108 seconds
Minimum number of seconds to run all queries: 5.108 seconds
Maximum number of seconds to run all queries: 5.108 seconds
Number of clients running queries: 400
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 5.289 seconds
Minimum number of seconds to run all queries: 5.289 seconds
Maximum number of seconds to run all queries: 5.289 seconds
Number of clients running queries: 500
Average number of queries per client: 0

mysqlslap: Error when connecting to server: 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
mysqlslap: Error when connecting to server: 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
Benchmark
Average number of seconds to run all queries: 5.666 seconds
Minimum number of seconds to run all queries: 5.666 seconds
Maximum number of seconds to run all queries: 5.666 seconds
Number of clients running queries: 1000
Average number of queries per client: 0

When I let those variables with your default values, MySQL didn't claim about connections any more:

--innodb_write_io_threads=4
--innodb_read_io_threads=4

[root@bd7 ~]# mysqlslap --user=root --auto-generate-sql --concurrency=100,200,300,400,500,1000 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 0.742 seconds
Minimum number of seconds to run all queries: 0.742 seconds
Maximum number of seconds to run all queries: 0.742 seconds
Number of clients running queries: 100
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 1.739 seconds
Minimum number of seconds to run all queries: 1.739 seconds
Maximum number of seconds to run all queries: 1.739 seconds
Number of clients running queries: 200
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 4.952 seconds
Minimum number of seconds to run all queries: 4.952 seconds
Maximum number of seconds to run all queries: 4.952 seconds
Number of clients running queries: 300
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 3.028 seconds
Minimum number of seconds to run all queries: 3.028 seconds
Maximum number of seconds to run all queries: 3.028 seconds
Number of clients running queries: 400
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 4.567 seconds
Minimum number of seconds to run all queries: 4.567 seconds
Maximum number of seconds to run all queries: 4.567 seconds
Number of clients running queries: 500
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 6.131 seconds
Minimum number of seconds to run all queries: 6.131 seconds
Maximum number of seconds to run all queries: 6.131 seconds
Number of clients running queries: 1000
Average number of queries per client: 0

But, even I run MySQL with default values, I have been getting that error when I set the concurrency = 1200...

[root@bd7 mysql]# my_print_defaults mysqld
--skip-name-resolve
--skip-external-locking
--port=3306
--socket=/var/lib/mysql/mysql.sock
--query_cache_type=0
--query_cache_size=0
--max_connections=6000
--max_user_connections=2000

[...]

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)


[...]

mysqlslap: Error when connecting to server: 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
mysqlslap: Error when connecting to server: 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
mysqlslap: Error when connecting to server: 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
mysqlslap: Error when connecting to server: 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
mysqlslap: Error when connecting to server: 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
Benchmark
Average number of seconds to run all queries: 2.752 seconds
Minimum number of seconds to run all queries: 2.752 seconds
Maximum number of seconds to run all queries: 2.752 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

Look what happened with Aborted_Connects...

mysql> show status like 'Aborted_connects';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_connects | 1195 |
+------------------+-------+
1 row in set (0.00 sec)

Any advise on this? Thanks for the advice...

Wagner Bianchi - [phone]
Profile: bit.ly/toG94v
Blog: wagnerbianchi.com/blog
Twitter: @wagnerbianchijr
Skype: wbianchijr



Edited 4 time(s). Last edit at 12/16/2011 11:42AM by Wagner Bianchi.