MySQL Bugs: #94077: Query to get processes from processlist is choking mysql to 30% speed

Bug #94077	Query to get processes from processlist is choking mysql to 30% speed
Submitted:	27 Jan 2019 6:29	Modified:	5 Feb 2019 17:15
Reporter:	John K	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S5 (Performance)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	information_schema

Description:
I recently migrated from mysql 5.7.23 and I ran into many severe performance issues.  
After debugging hundreds of code locations I found one thing that had a significant impact on performance:

On mysql 5.7 I was making "SELECT count() from information_schema.processlist" queries up to 500 times per second (within threads). 
The results were used to prevent the server from getting overloaded.  

I realized it's a problem on 8.0.13 early on, so I wrote a background daemon which issues this command once per second and caches the result for all threads.  

I now realized that each execution caused the server to drop significantly in performance. I don't know which sort of queries are affected (select/update/insert/delete).  

But I was able to see a overall performance loss of about 70% when running this once per second.

I was able to reproduce this on community-server 8.0.13 as well as on percona 8.0.13

Query used for testing:
"SELECT count(*),ID,USER,HOST,DB,COMMAND,TIME,STATE,LEFT(INFO,1024) AS INFO FROM information_schema.processlist WHERE Command != 'Sleep';"

How to repeat:
Run such a query, it takes about 0.09 seconds to execute.
while ((1)); do 
time sql_exec "SELECT count(*),ID,USER,HOST,DB,COMMAND,TIME,STATE,LEFT(INFO,1024) AS INFO FROM information_schema.processlist WHERE Command != 'Sleep';"; 
sleep 0.5;
done

You can also just execute the sql regularly manually, if you do it too fast ( a few per second) the server will completely hang.  

Suggested fix:
There needs to be a big fat warning in the documentation that a query to processlist currently impacts performance greatly.

But a query of the processlist should not have a performance impact, it wasn't the case on 5.7.

Hi,

Thank you for your bug report.

First of all, I see that you wrote a simple shell script, which is exactly what we need to reproduce.

However, what you have not disclosed to us is the type of queries that you are running while querying the I_S.

We will test it on 5.7.24 and 8.0.14.

We are also changing the severity to "Performance", which is what it is about, actually.

Hi,

I have designed a bunch of DML's running in parallel with the I_S query that you posted.

That test consists of 100 parallel threads, each one running a complex DML.

With 5.7.24 a query from I_S takes between 0.06 and 0.1 seconds.

Now, testing 8.0.14.

Hi,

With 8.0.14 I get values from 0.03 to 0.05.

Hence, I can not repeat what you are reporting.