| Bug #71223 | Error when connecting to server: 1135 Can't create a new thread (errno -1) | ||
|---|---|---|---|
| Submitted: | 24 Dec 2013 13:00 | Modified: | 6 Jan 2014 4:24 |
| Reporter: | Anshuman Dwivedi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.6.12 | OS: | Windows (Windows server 2008) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Error when connecting to server: 1135 Can't create a new thread (errno -1) | ||
[24 Dec 2013 13:15]
Peter Laursen
max_connections=2000 will cause Performance_Schema to allocate a lot of memory if you have P_S turned on - refer http://bugs.mysql.com/bug.php?id=68514 (but not sure this is the problem here)
[24 Dec 2013 13:31]
Anshuman Dwivedi
@Peter Laursen I've got enough main memory, it is around 16 GB.I shouldn't be the issue.
[24 Dec 2013 13:42]
Peter Laursen
On an environment described as "windows-server-2008,32-bit machine and install mysql-5.6.12,32-bit." you cannot utilize more than 3 GB memory for non-system programs (and for a sinngle process - the mysqld process in case - it may be 2GB, but not quite sure about this Windows flavor in this respect). I would try to start server with P_S off and see what difference it makes. One major difference between MySQL 5.5 and 5.6 is that on 5.5 P_S is off and on 5.6 it is on as default. Also monitor memory consumption from Task Manager (or if you have something better, use it) while your mysqlslap command is starting servers.
[24 Dec 2013 13:44]
Peter Laursen
.. and besides I forgot my standard signature here! Peter (not a MySQL/Oracle person)
[24 Dec 2013 16:49]
Sveta Smirnova
Thank you for the report. Please provide output of how much memory mysqld.exe uses in time when issue occurs as Peter suggested.
[24 Dec 2013 21:09]
Peter Laursen
@Anshuman .. simply try those 4 different combinations of settings in my.ini 1) performance_schema ON and max-connections = 2000 2) performance_schema ON and max-connections = 100 1) performance_schema OFF and max-connections = 2000 2) performance_schema OFF and max-connections = 100 You won't even need to connect with a client or run your mysqlslap. Just start the server with these settings and notice how much memory the mysqld process uses after being started. I bet that the 3 environment details 1) a 32 bit server and system 2) max_connections = 2000 3) performance_schema ON .. in combination simply are not feasible.
[26 Dec 2013 5:50]
Anshuman Dwivedi
@Peter and @Sveta As suggested by Peter, I've tried following cases and noticed memory consumed by mysqld process -- 1) performance_schema ON and max-connections = 2000 ------> 13.29 % of RAM size 2) performance_schema ON and max-connections = 100 ------> 7.96 % of RAM size 1) performance_schema OFF and max-connections = 2000 ------> 1.42 % of RAM size 2) performance_schema OFF and max-connections = 100 ------> 1.42 % of RAM size Looking at the memory consumption, It seems "max-connection" is only considered when "performance_schema" is ON. Definitely Peter is right about the 32Bit:2000Connections:PerformaceSchema:ON combination, it took the largest memory among all cases. One more thing I want to highlight here.I ran mysqlslap command with 2000Connections:PerformaceSchema:ON and 2000Connections:PerformaceSchema:OFF combinations.Command is like- "mysqlslap --user=root --auto-generate-sql --concurrency=600 -p" Error is same as mentioned earlier but at the same time I'm running one more query to see status of connections, one weird thing comes up(can be seen below). Used query and its out come given below- mysql> show status like '%onn%'; +-----------------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------------+-------+ | Aborted_connects | 4156 | | Connection_errors_accept | 0 | | Connection_errors_internal | 4156 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 4439 | | Max_used_connections | 113 | | Performance_schema_session_connect_attrs_lost | 0 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 1 | +-----------------------------------------------+-------+ Here connections are 4439 and max_used_connections are only 113. How number of connections can go beyond 2000 (as mentioned in my.ini).Please suggest possible solution. Prior to 5.6.12 I was using 5.1.30 in which every thing was running fine.And it's a known fact that 5.6.12 is more optimized.
[26 Dec 2013 16:26]
Sveta Smirnova
Thank you for the feedback. But I need output of how much memory mysqld.exe uses in time when issue occurs, not when it is idle. Regarding to "Connections" please read at http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Connections: "The number of connection attempts (successful or not) to the MySQL server. " So big number here is expected.
[27 Dec 2013 5:02]
Anshuman Dwivedi
@Sveta Thanks for reply. You are right about "Connections" variable shown in result of "Show status like '%connection%'; query. After repeating same case again and again I realized that variable(connections) was showing total number of connections attempted. As demanded I ran test again with Performance_schema OFF and 2000 as max_connections with concurrency of 700. Test got failed. Observation of memory consumption of mysqld.exe process was 2.5% of available RAM. And before test when process was idle,it was consuming 2.2% of RAM. Hope this will help you in finding of any possible solution.
[27 Dec 2013 17:19]
Sveta Smirnova
Thank you for the feedback. Please send us full error log file.
[2 Jan 2014 4:40]
Anshuman Dwivedi
I have enabled general-logs and error-reporting logs . But I can't see anything significant in them. There were no general logs got created. Yeah but after a start up of mysql and performing same error few error-logs were there, which I've mentioned below - 2014-01-02 09:49:39 117656 [Note] Plugin 'FEDERATED' is disabled. 2014-01-02 09:49:39 117656 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000 2014-01-02 09:49:39 1c714 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 2014-01-02 09:49:39 117656 [Note] InnoDB: The InnoDB memory heap is disabled 2014-01-02 09:49:39 117656 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2014-01-02 09:49:39 117656 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-01-02 09:49:39 117656 [Note] InnoDB: Not using CPU crc32 instructions 2014-01-02 09:49:39 117656 [Note] InnoDB: Initializing buffer pool, size = 512.0M 2014-01-02 09:49:40 117656 [Note] InnoDB: Completed initialization of buffer pool 2014-01-02 09:49:40 117656 [Note] InnoDB: Highest supported file format is Barracuda. 2014-01-02 09:49:46 117656 [Note] InnoDB: 128 rollback segment(s) are active. 2014-01-02 09:49:46 117656 [Note] InnoDB: Waiting for purge to start 2014-01-02 09:49:46 117656 [Note] InnoDB: 5.6.12 started; log sequence number 894025193 2014-01-02 09:49:46 117656 [Note] Server hostname (bind-address): '*'; port: 3306 2014-01-02 09:49:46 117656 [Note] IPv6 is available. 2014-01-02 09:49:46 117656 [Note] - '::' resolves to '::'; 2014-01-02 09:49:46 117656 [Note] Server socket created on IP: '::'. 2014-01-02 09:49:46 117656 [Note] Event Scheduler: Loaded 0 events 2014-01-02 09:49:46 117656 [Note] E:/MySQL5.6.12/MySQL Server 5.6/bin\mysqld: ready for connections. Version: '5.6.12-log' socket: '' port: 3306 MySQL Community Server (GPL) 2014-01-02 09:49:47 117656 [Warning] Hostname 'some_host_name.some_domain_name.com' does not resolve to '192.168.--.---'. 2014-01-02 09:49:47 117656 [Note] Hostname 'some_host_name.some_domain_name.com' has the following IP addresses: 2014-01-02 09:49:47 117656 [Note] - 192.168.--.--- For confidentiality points of view I have edited few lines. Hope this help
[3 Jan 2014 21:35]
MySQL Verification Team
Sorry, but I see no evidence of a bug here. I'd suggest on a machine with only 16GB of ram, you at least install a 64-bit OS and run 64-bit version of mysqld. You can surely downsize many my.ini variables and/or use commercial thread pool plugin to try squeeze more concurrency out of it. MySQL error code 1135 (ER_CANT_CREATE_THREAD): Can't create a new thread (errno %d); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
[6 Jan 2014 4:23]
Anshuman Dwivedi
@Shane Bester Solution you just have suggested I've already implemented with 64 bit OS and 32 bit mysql and it's also working fine. But still don't why it failing on 32 bit OS.
[6 Jan 2014 4:24]
Anshuman Dwivedi
Please let me know the actual reason if possible
Description: I am trying to do a load test via "mysqlslap" command on mysql-5.6.12 but after a certain number of connections(specified in the command below) I am getting an error as follows - "mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno -1);if you are not out of available memory, you can consult the manual for a possible OS-dependent bug". The mysqlslap query I ran was - "mysqlslap --user=root --auto-generate-sql --concurrency=500 -p". In query, mentioned connections are 500. And in my.ini file max_connections=2000 has been set. Prior to this I was using mysql-5.1.30 which was working fine without any such issue.Please suggest what can be possible root cause. How to repeat: To repeat/replicate this issue, you may follow steps given below - [1] Use windows-server-2008,32-bit machine and install mysql-5.6.12,32-bit. [2] Update my.ini file and mention max_connections variable to more than 1000 [3] Run command "mysqlslap --user=root --auto-generate-sql --concurrency=500 -p". The point must be taken into consideration is value mentioned for attribute '--concurrency' must be less than or equal to "max_connections" value in my.ini file. I've mentioned conditions to replicate the issue according to the environment which I've used.