MySQL Bugs: #70256: MySQL gets stuck on shutdown if it can't create the required thread

Bug #70256	MySQL gets stuck on shutdown if it can't create the required thread
Submitted:	6 Sep 2013 7:16	Modified:	6 Sep 2013 11:07
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S3 (Non-critical)
Version:	5.5.23	OS:	Linux (CentOS 5.x)
Assigned to:		CPU Architecture:	Any

Description:
I have a server running MySQL. The server itself is configured to not allow memory overcommit to prevent it swapping which on some systems has been an issue, and it started giving memory allocation problems.

When trying to shut the server down to clear these up the thread used to perform the shutdown failed to be created and thus the shutdown procedure got stuck and mysqld never stopped.

How to repeat:
sysctl -a shows:

vm.overcommit_ratio = 100
vm.overcommit_memory = 2

We noticed some memory problems which started appearing:

[root@myhost ~]# mysql -e"show processlist"
ERROR 1135 (HY000): Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

[root@myhost ~]$ perror 12
OS error code 12: Cannot allocate memory

and so decided to shut the server down with /etc/init.d/mysql/stop.
However, mysqld reports this when trying to shutdown:

130905 11:49:39 [ERROR] Can't create thread to kill server

and then hangs and does nothing else.

This required a kill -9 <mysqld_pid> followed by a restart to get things working again.

Suggested fix:
If the thread creation fails a clean shutdown is not going to happen.
I would suggest mysqld catching this situation and it deciding to commit suicide after logging the problem.
This ensures that the server does shut down as expected which is the DBAs intention anyway, even if the shutdown is not clean.

Yes, this is an odd situation but I have seen it several times now for the same reason. If the DBA wants to shutdown the server he usually wants to do it as fast as possible (normally cleanly), and if using the normal shutdown scripts (/etc/init.d/mysql stop) and not monitoring the log file it may not be apparent for a while that mysqld is simply "stuck" and will never shutdown. (At this point in time you can not connect to mysqld either so it's "useless".)

So any improvements in coping with this particular issue would be most welcome.

I believe that this issue is present on all versions of mysqld where there's a shutdown thread (so currently 5.5, 5.6 and 5.7.1 [dev version]).

And another way would be to pre-create the shutdown thread and any memory that a shutdown might need.

Pre-creating a single thread for this unusual issue and having it stay idle seems like rather a waste. Some of the servers I manage have a MySQL uptime of over a year so this seems like an unneeded waste of resources, even if these extra resources are minimal.

The situation, under normal circumstances should not happen. Perhaps a simple assertion to check the thread is created correctly is sufficient. That failure would trigger the immediate stop anyway, which is really what I am hoping is achieved.