MySQL Bugs: #79714: Thread handle leak in the connection handler

Bug #79714	Thread handle leak in the connection handler
Submitted:	20 Dec 2015 15:02	Modified:	6 Jan 2016 16:38
Reporter:	Lyric Rainy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S2 (Serious)
Version:	5.7.9	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When using mysqld under Windows, the handle count of mysqld.exe in the task manager increases gradually to millions, seemingly without bound. This causes the operating system to consume more and more memory, requiring mysqld to be restarted regularly.

By examining the open handles in Process Explorer and Windbg's !htrace feature, it seems that most of the handles come from two sources:

1. Event handles created from buffer page locks upon contention. This is probably not a bug, but only a bounded number (the number of buffer pages?) of handles can be created this way. Indeed, when I shrink the buffer pool from 18GB to 4GB by SET GLOBAL'ing innodb_buffer_pool_size on a long-running mysqld instance, the handle count decreased from 2,551,700 to 1,895,129.

2. Thread handles created in Per_thread_connection_handler:add_connection(). It seems that threads created there with mysql_thread_create() are never join'd, so once a thread exits due to thread_cache_size being exceeded, its thread handle never gets closed. In the above example, thread_cache_size is set to 38, while Threads_created in SHOW STATUS is about 1,755,000, consistent with the handle count observations.

How to repeat:
1. Set max_connection to 6000, thread_cache_size to 38 or smaller (so that we mostly create new connection threads rather than using the thread cache), and innodb_buffer_pool_size to a small value (so that we don't get too much noise due to the harmless event handles created from buffer page locks). thread_handling should be one-thread-per-connection, the default.
2. Start mysqld.
3. Create and close database connections repeatedly, with the number of simultaneous connections (over 1000 in my case) much larger than thread_cache_size.
4. The handle count of mysqld.exe in the task manager (where the handle count column may need to be enabled manually) should then increase gradually to millions, apparently without bound.

Suggested fix:
Close the thread handle in Per_thread_coinnection_handler somewhere in the main thread when it is no longer used.

As a workaround, setting thread_cache_size to a larger value than max_connections, so that Threads_created no longer increases, seems to solve the problem.

Thank you for the bug report.  Verified on 5.7.11.

mysqld.exe --no-defaults --thread-cache-size=10 --max-connections=1000 --skip-grant-tables --skip-name-resolve --port=3333 --console

mysqlslap.exe -h127.0.0.1 -P3333 --concurrency=20 --create-schema=test --iterations=100000 --number-of-queries=1

Every few seconds, check:

handle -s -p mysqld.exe
Handle type summary:
  <Unknown type>  : 32
  ALPC Port       : 3
  Desktop         : 1
  Directory       : 2
  Event           : 479
  File            : 27
  IoCompletion    : 3
  Key             : 6
  KeyedEvent      : 2
  Mutant          : 1
  Semaphore       : 10
  Thread          : 5552
  Timer           : 3
  TpWorkerFactory : 16
  WindowStation   : 2
Total handles: 6139

!avrf -leak

Noted in 5.7.11, 5.8.0 changelogs.

Thread handle resource leakage could occur when creating threads for
handling connections on Windows, which could lead to Windows servers
eventually running out of handles.