Bug #100049 Querying INFORMATION_SCHEMA.PROCESSLIST frequency stops all query execution
Submitted: 30 Jun 2020 11:43 Modified: 30 Jun 2020 14:48
Reporter: Dean Herbert Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Information schema Severity:S5 (Performance)
Version:8.0.20 OS:Debian (Buster)
Assigned to: CPU Architecture:x86 (DigitalOcean dedicated 24 cpu)

[30 Jun 2020 11:43] Dean Herbert
Description:
Querying PROCESSLIST with any query at a rate of one time per second will cause queries to stop completing execution and pile up.

How to repeat:
Have a load running against the database which generally leaves the PROCESSLIST empty on individual queries.

Run in background:

watch -n0.1 "mysql -e \"SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep'\""

Observe that the count will continue to increase indefinitely until the server falls over. For my workload, it goes from 0 to 200 running queries within two seconds. If I leave it running queries will not complete until i stop it running.
[30 Jun 2020 12:55] MySQL Verification Team
Hi Mr. Herbert,

Thank you for your bug report.

I have run a heavy sysbench test on my, much more modest, computer and I did not see any problems.

I have used "SHOW PROCESSLIST", so first of all, can you try that command and see if it does any better.

What do `vmstat` or `top` say about the load on your 24 CPU's, when you experience that problem.

Last, but not least, if you run any heavy query on the loaded server, do you see the same phenomenon ???

This could be a simple problem with your configuration and not our bug, at all. High power computer with plenty of RAM require long and painstaking configuration process.
[30 Jun 2020 13:46] Dean Herbert
As a quick confirmation, `SHOW PROCESSLIST` does not exhibit the same issues. This is specific to the querying via `INFORMATION_SCHEMA`. Please test using the provided query specifically as I think it may be more likely to reproduce.

I have confirmed this behaviour on two different systems.
[30 Jun 2020 13:48] MySQL Verification Team
Hi,

I have run a heavy sysbench test and the exact command that you have used.

I have encountered no problems, what so ever. So, this is , most probably, a configuration issue.
[30 Jun 2020 13:58] Dean Herbert
Thanks for the confirmation and quick response. I will test further in a local (non-virtualized) environment to see if I can reproduce on bare metal.

A quick check on `top` / `vmstat` show no obvious changes while in the bad state (CPU usage does not look to increase in any way). I am running very close to out-of-the-box configuration these days thanks to the `innodb_dedicated_server` setting.

I've included my configuration below in case anything stands out:

```
max_connections                = 4096
skip_name_resolve              = 1
open-files-limit               = 65535
default-authentication-plugin  = mysql_native_password

slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log

innodb_dedicated_server        = 1

# TODO: check if valuable or not
innodb_adaptive_hash_index     = 0
innodb_flush_log_at_trx_commit = 2

#Replication
relay_log                      = relay-log
server_id                      = 2008
gtid-mode                      = ON
enforce-gtid-consistency       = ON

read_only                      = 0
log_bin                        = mysql-bin
sync_binlog                    = 0
binlog_format                  = row
binlog_row_image               = minimal
binlog_expire_logs_seconds     = 172800
log_slave_updates              = 1
```
[30 Jun 2020 14:42] MySQL Verification Team
Most probably, this is due to the virtualised environment.

If you run a lot of SELECTs, or DML with WHERE clauses, then disabling adaptive hash index is not good for performance.

Also, for a box of that size, configuration has to be many times more detailed and precise.
[30 Jun 2020 14:48] Dean Herbert
The disabling of adaptive hash index was actually required to solve a critical performance issue previously. This was back on mysql 5.7 so I will have to reassess that one time permissive.

Allow me a couple of days to further test the PROCESSLIST specific issue!