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
Triage: Needs Triage: D2 (Serious)

[9 Dec 2014 19:39] Eric Bergen
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.
[9 Dec 2014 19:44] Santosh Praneeth Banda
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
[9 Dec 2014 20:22] Sveta Smirnova
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.
[9 Dec 2014 21:04] Eric Bergen
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.
[11 Dec 2014 15:22] Sinisa Milivojevic
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.
[12 Dec 2014 1:03] Sveta Smirnova
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.
[21 Aug 2015 19:04] Paul Dubois
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.
[31 Aug 2015 13:51] Paul Dubois
This fix was reverted. Cancel previous comment.
[31 Aug 2015 19:15] Eric Bergen
Is the bug going to be reopened?
[23 Nov 2015 16:59] Paul Dubois
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.
[21 Jan 2016 12:26] Ajo Robert
5.6 Patch for Bug-75155

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

[21 Jan 2016 12:28] Ajo Robert
Attached patch for MySQL 5.6. Please test the same and let us know the feedback.
-Ajo
[21 Jan 2016 15:29] Eric Bergen
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.