MySQL Bugs: #75155: Spamming show processlist prevents old connection threads from cleaning up.

Bug #75155	Spamming show processlist prevents old connection threads from cleaning up.
Submitted:	9 Dec 2014 19:39	Modified:	21 Jan 2016 15:29
Reporter:	Eric Bergen (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S3 (Non-critical)
Version:	5.6.20, 5.6.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Spamming a mysql instance with a few thousand parallel show processlist queries prevents old connection queries from acquiring LOCK_thd_remove and cleaning themselves up. It is possible to cause a machine to create many times more than max_connections threads causing it to run out of memory and fail. 

How to repeat:
mysql> select @@max_connections, @@max_user_connections;
+-------------------+------------------------+
| @@max_connections | @@max_user_connections |
+-------------------+------------------------+
|              5000 |                   4000 |
+-------------------+------------------------+
1 row in set (0.00 sec)

#idle state
$ ps -eLf | grep mysqld | wc -l
622
$ ps -eLf | grep mysqld | wc -l
622

#connections started
$ ps -eLf | grep mysqld | wc -l
7112
$ ps -eLf | grep mysqld | wc -l
7976
$ ps -eLf | grep mysqld | wc -l
8090

#In my tests I was able to get to 25,000 threads with max_connections of 5000

#ctrl+c pmysql before the machine runs out of memory

$ head hosts
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306
127.0.0.1:3306

$ wc -l hosts
200000 hosts

#use pmysql to generate 5000 parallel connections running this query
$ cat hosts  |  pmysql 'select count(*) from information_schema.processlist' -t 5000 2>&1 > /dev/null

From the top of the quickstack summary you can see everything is piled up on remove_global_thread waiting on LOCK_thd_remove. The threads get starved for the lock and can't recycle.

I think this will be fixed if callers acquire both LOCK_thd_remove and LOCK_thd_count before calling add_global_thread(THD *thd). This way new thread creation is blocked on LOCK_thd_remove which was acquired by 'show processlist' command

Thank you for the report.

What is the current location of pmysql? Links at http://dom.as/2010/08/12/pmysql-multi-server-mysql-client/ are broken.

This version is reasonably up to date. http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/files/head:/pmysql/

You can also use mysqlslap or anything that generates many parallel queries.

This would be a welcome feature request that would be resolved only with two-way client-server protocol. I do not think that (a possible new feature of) statement timeout would work here.

Thank you for the report.

Verified as described. Bug is repeatable only if max_connections and max_user_connections set to high value. With default settings not repeatable.

To use pmysql put into .my.cnf following (for test, started via MTR):

[client]
host=127.0.0.1
port=13000
socket=/path/to/mysq/dir/mysql-test/var/tmp/mysqld.1.sock

Then start MTR as ./mtr --start --mysqld=--open-files-limit=5000 innodb --mysqld=--max_connections=5000 --mysqld=--max_user_connections=4000, grant access to your user and user, named mysql, then repeat instructions.

Problem more noticeable on debug server.

With version 5.7.6 the issue is not repeatable.

Noted in 5.6.27, 5.7.9, 5.8.0 changelogs.

For a terminating connection, the server decremented its connection
counter, then finished connection cleanup. If there was a delay in
cleanup, the server could permit more than max_connections
connections.

This fix was reverted. Cancel previous comment.

Is the bug going to be reopened?

Noted in 5.6.29, 5.7.11, 5.8.0 changelogs.

Heavy SHOW PROCESSLIST or SELECT ... FROM
INFORMATION_SCHEMA.PROCESSLIST activity could result in the server
accepting more than max_connections connections.

5.6 Patch for Bug-75155

Attachment: bug-75155.patch (text/x-patch), 10.28 KiB.

Attached patch for MySQL 5.6. Please test the same and let us know the feedback.
-Ajo

We're seeing similar behavior outside the lab where mysqld will get behind cleaning up old threads under bursts of connections with few fast queries. It doesn't always need a show processlist to trigger that was just the first way we found it.