Mysql large number of running threads, causing hangs

[Chirag Aggarwal]

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,

# 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

 

[cPRex]

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?

mysql -e "show variables like '%open%';"

 

[Chirag Aggarwal]

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?

mysql -e "show variables like '%open%';"

# 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.

 

[cPRex]

I'm just wondering if you're hitting that open tables limit and then that's when the slowness happens.

 

[Chirag Aggarwal]

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:

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)

# 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

 

[cPRex]

I mean, I wouldn't go too crazy, but 10000 seems reasonable.

 

[Chirag Aggarwal]

I mean, I wouldn't go too crazy, but 10000 seems reasonable.

I guess my.cnf is overriding that to 1048576. So it shouldn't be the cause of problem right? Anything else I can check?

 

[cPRex]

That was the open files limit, and the 4000 value was the open tables, which is the "table_open_cache" value from that earlier command.