Mysql large number of running threads, causing hangs

Operating System & Version
Cloudlinux 8
cPanel & WHM Version
104.0.8
Jan 9, 2018
22
2
3
.
cPanel Access Level
Root Administrator
Hello,

I'm trying to figure out a weird mysql issue. We have a cpanel server running mysql 8.0.30 which works fine but every once or twice a day, mysql becomes unresponsive or painfully slow. RAM, SWAP, IO usage increases but CPU is fine.

Upon checking,

Code:
# mysqladmin status
Uptime: 27795  Threads: 751  Questions: 32905673  Slow queries: 15  Opens: 2708499  Flush tables: 3  Open tables: 4000  Queries per second avg: 1183.870

| Connections       | 456613 |
| Threads_cached    | 10     |
| Threads_connected | 705    |
| Threads_created   | 20043  |
| Threads_running   | 687    |
So, Threads_running is usually around 5-10 but it keeps building up and becomes unresponsive at around 1500.

There is no single abusive user in processlist and seems pretty normal:

I monitored a few threads for sometime and even after few minutes they are sometimes in processlist with same or other Query. I guess it's because mysql is very slowly responding to php and thus connection is kept alive. There are hardly any sleeping query.

Also, server is running Cloudlinux Governor along with it, I've tried disabling it and changing my.cnf configs.

Cloudflare blocked me from pasting process list and config file. Please check it here: https://pastebin.com/raw/zk40xeLw
 
Last edited by a moderator:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
11,780
1,872
363
cPanel Access Level
Root Administrator
Hey there! From that output, my concerns would be the number of queries per second and if your server can handle that, and the number of open tables. Can you run this on the command line and paste the output here?

Code:
mysql -e "show variables like '%open%';"
 
Jan 9, 2018
22
2
3
.
cPanel Access Level
Root Administrator
Hey there! From that output, my concerns would be the number of queries per second and if your server can handle that, and the number of open tables. Can you run this on the command line and paste the output here?

Code:
mysql -e "show variables like '%open%';"
Code:
# mysql -e "show variables like '%open%';"
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| have_openssl               | YES     |
| innodb_open_files          | 4000    |
| open_files_limit           | 1048576 |
| table_open_cache           | 4000    |
| table_open_cache_instances | 16      |
+----------------------------+---------+
Server has these queries avg during regular time period as well and it works. Specs are pretty good AMD EPYC 7302P with 128GB ram.
 
Jan 9, 2018
22
2
3
.
cPanel Access Level
Root Administrator
I'm just wondering if you're hitting that open tables limit and then that's when the slowness happens.
Now that I think about it, there were errors related to mysql hitting these limits some weeks ago but we set it to 1048576 which should be lot more than required. Normal usage is only around 2000. Error hasn't reappeared since in logs. Do you think I should still try increasing it further?

Also, I tried that before and it threw this error:
Code:
2022-08-05T11:52:37.122007Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 1048576 (request: 9999999)
Code:
# grep -i limit /etc/systemd/system/mysql.service
# but not limited to OpenSSL) that is licensed under separate terms,
# Sets open_files_limit
LimitNOFILE = 10000